Hi I have created the following which works. I am fairly new to this and could not see why the second code is not working. Any suggestions would be greatly welcomed.
class Dbcon {
private $severname;
private $username;
private $password;
private $dbname;
private $charset;
public function connect () {
$this->servername ='localhost';
$this->username = 'ucmsadmin';
$this->password = 'P@xxwxrdxxx';
$this->dbname = 'umcs';
$this->charset = 'utmf8mb4';
try {
$dsn = "mysql:host=".$this->severname.";dbname=".$this->dbname.";charset".$this->charset;
$pdo = new PDO($dsn , $this->username, $this->password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO:: ERRMODE_EXCEPTION);
echo "Connected";
$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
`ID` int(11) unsigned NOT NULL auto_increment,
`USER_NAME` varchar(255) NOT NULL default '',
`EMAIL` varchar(255) NOT NULL default '',
`PASSWORD` varchar(255) NOT NULL default '',
`ACCESS_LEVEL` tinyint(1) unsigned NOT NULL default '1',
`ACCOUNT_APPROVED` boolean NOT NULL default '0',
PRIMARY KEY (`ID`))";
if(!$pdo->query($queryCreateUsersTable)){
echo "Table creation failed: (" . $pdo->errno . ") " . $pdo->error;
} else {
echo' table created';
}
return $pdo;
} catch (PDOException $e) {
echo "connection failed: ".$e->getMessage();
}
}
}
However if I move the section that creates the database table in to a public function I don’t get any errors but the table does not get created. Here is what I did;
class Dbcon {
private $severname;
private $username;
private $password;
private $dbname;
private $charset;
public function connect () {
$this->servername ='localhost';
$this->username = 'ucmsadmin';
$this->password = 'P@33w0rd101';
$this->dbname = 'umcs';
$this->charset = 'utmf8mb4';
try {
$dsn = "mysql:host=".$this->severname.";dbname=".$this->dbname.";charset".$this->charset;
$pdo = new PDO($dsn , $this->username, $this->password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO:: ERRMODE_EXCEPTION);
echo "Connected";
return $pdo;
} catch (PDOException $e) {
echo "connection failed: ".$e->getMessage();
}
}
public function createtable() {
$this->connect();
$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
`ID` int(11) unsigned NOT NULL auto_increment,
`USER_NAME` varchar(255) NOT NULL default '',
`EMAIL` varchar(255) NOT NULL default '',
`PASSWORD` varchar(255) NOT NULL default '',
`ACCESS_LEVEL` tinyint(1) unsigned NOT NULL default '1',
`ACCOUNT_APPROVED` boolean NOT NULL default '0',
PRIMARY KEY (`ID`))";
return $queryCreateUsersTable;
}
}
in the index.php I run
$object = new Dbcon;
$object->connect();
$object2 = new Dbcon;
$object2->createTable();?>
>Solution :
The createTable() function just defines a string containing some SQL. It never sends that SQL to the database to be executed.
Based on the original code, you could write it more like this:
public function createtable() {
$pdo = $this->connect();
$queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
`ID` int(11) unsigned NOT NULL auto_increment,
`USER_NAME` varchar(255) NOT NULL default '',
`EMAIL` varchar(255) NOT NULL default '',
`PASSWORD` varchar(255) NOT NULL default '',
`ACCESS_LEVEL` tinyint(1) unsigned NOT NULL default '1',
`ACCOUNT_APPROVED` boolean NOT NULL default '0',
PRIMARY KEY (`ID`))";
if(!$pdo->query($queryCreateUsersTable)){
echo "Table creation failed: (" . $pdo->errno . ") " . $pdo->error;
} else {
echo' table created';
}
}
However I would point out that it’s very inefficient to create a new connection every time you run a query. Instead you should try to create a connection once during the lifetime of your PHP script, and keep re-using it.