Hereās my database class:
<?php
class Database {
private $host = DB_HOST;
private $user = DB_USER;
private $pass = DB_PASS;
private $dbname = DB_NAME;
private $dbh;
private $error;
private $stmt;
/*
* Constructor function
*/
public function __construct() {
// Set DSN
$dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
// Set options
$options = array (
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
// Create a new PDO instance
try {
$this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
} catch (PDOException $e) {
$this->error = $e->getMessage();
}
}
public function query($query) {
$this->stmt = $this->dbh->prepare($query);
}
public function bind($param, $value, $type=null) {
if (is_null($type)) {
switch (true) {
case is_int($value) :
$type = PDO::PARAM_INT;
break;
case is_bool($value) :
$type = PDO::PARAM_BOOL;
break;
case is_null($value) :
$type = PDO::PARAM_NULL;
break;
default :
$type = PDO::PARAM_STR;
break;
}
}
$this->stmt->bindvalue($param, $value, $type);
}
public function execute() {
return $this->stmt->execute();
}
/*
* Get result set of a db query (more than one record)
*/
public function getResultSet() {
$this->execute();
return $this->stmt->fetchAll(PDO::FETCH_OBJ);
}
/*
* Get single row from a db query
*/
public function getSingle() {
$this->execute();
return $this->stmt->fetch(PDO::FETCH_OBJ);
}
public function getRowCount() {
return $this->stmt->rowCount();
}
public function getLastInsertId() {
return $this->dbh->lastInsertId();
}
public function insertEntry($table, $data, $insert_values) {
$stmt = $this->query("INSERT INTO $table ($data)
VALUES ($insert_values)");
$this->execute();
return $this->getLastInsertId();
}
public function beginTransaction() {
return $this->dbh->beginTransaction();
}
public function endTransaction() {
return $this->dbh->commit();
}
public function cancelTransaction() {
return $this->dbh->rollBack();
}
}
Here is the code I am trying to run:
<?php
require_once '../config/init.php';
require_once '../libraries/database.class.php';
$db = new Database();
try {
$stmt = $db->query("SELECT id, contact_person, contact_position, phone1, phone2, email1, email2 FROM clients");
$results = $db->getResultSet();
} catch (Exception $e) {
displayErrorPage($e->getMessage());
}
foreach ($results as $row) {
$id= $row->id;
$contact_person = $row->contact_person;
$contact_position = $row->contact_position;
$phone1 = $row->phone1;
$phone2 = $row->phone2;
$email1 = $row->email1;
$email2 = $row->email2;
$data = array('client_id', 'name', 'position', 'phone1', 'phone2', 'email1', 'email2');
$insert_values = array($id, $contact_person, $contact_position, $phone1, $phone2, $email1, $email2);
$table = "contacts";
$db->insertEntry($table, $data, $insert_values);
}
This is not part of my application, it is just my attempt at automating the transfer of some of the fields of one database table into a new table, because I decided to change the structure of my database. After the transfer, I was going to drop the fields in the first table (clients) that corresponded to the ones in the new table (contacts).
I want to put all my CRUD operations in the database class, instead of having them in each of the other classes such as Client, Project, etc and just specify the fields, data, and table name in the file that calls the method.
The method in question is insertEntry(). It turns out it was missing the final ā)ā in the query line if you want to duplicate the error.
But even after fixing that syntax error, so that I can get my page to echo things, I still canāt get that method to work insert into my database. I am not using prepared statements here, because this is a personal project, but maybe I should add that here too.
