Table not created when using class method-CodePudding

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();?>

CodePudding user response：

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.