I am very new to php and this forum, so please excuse any errors or misplaced questions. In the code i provided, I am just looking to CREATE a Table in the DB “mydb”. I tested the connection to the DB(It works). It is just the creating the table i am having issues with. Any advice or criticisms would be appreciated. Thx
<?php /* * * File: PDOcreateTabletcompany.php * By: Jay * Date: 24-10-13 * * This script createsTableintoDB * *==================================== * */ try { $db = new PDO("mysql:dbname=mydb;host=localhost", "root", "" ); } catch(PDOException $e) { echo $e->getMessage(); } $table= "tcompany"; $columns = "ID INT( 11 ) AUTO_INCREMENT PRIMARY KEY, Prename VARCHAR( 50 ) NOT NULL, Name VARCHAR( 250 ) NOT NULL, StreetA VARCHAR( 150 ) NOT NULL, StreetB VARCHAR( 150 ) NOT NULL, StreetC VARCHAR( 150 ) NOT NULL, County VARCHAR( 100 ) NOT NULL, Postcode VARCHAR( 50 ) NOT NULL, Country VARCHAR( 50 ) NOT NULL " ; $createTable = $db->exec("CREATE TABLE IF NOT EXISTS mydb.$table ($columns)"); if ($createTable) { echo "Table $table - Created!<br /><br />"; } else { echo "Table $table not successfully created! <br /><br />"; } ?>
Advertisement
Answer
As no rows are affected when creating table $createTable returns 0 see manual
PDO::exec() returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected,PDO::exec() returns 0.
As you are CREATING a table you will be free from SQL injection if your column names are hard coded( as in the code below). I have left $table = "tcompany";
as you want to print table created( I would leave it out myself)
I have added error-handling which will show any errors in try
block.
$table = "tcompany"; try { $db = new PDO("mysql:dbname=mydb;host=localhost", "root", "" ); $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );//Error Handling $sql ="CREATE table $table( ID INT( 11 ) AUTO_INCREMENT PRIMARY KEY, Prename VARCHAR( 50 ) NOT NULL, Name VARCHAR( 250 ) NOT NULL, StreetA VARCHAR( 150 ) NOT NULL, StreetB VARCHAR( 150 ) NOT NULL, StreetC VARCHAR( 150 ) NOT NULL, County VARCHAR( 100 ) NOT NULL, Postcode VARCHAR( 50 ) NOT NULL, Country VARCHAR( 50 ) NOT NULL);" ; $db->exec($sql); print("Created $table Table.n"); } catch(PDOException $e) { echo $e->getMessage();//Remove or change message in production code }
NOTE in answer to comment use
CREATE TABLE IF NOT EXISTS