Using PDO for CREATE TABLE

Question

I am very new to php and in this forum, so please excuse any errors or non-local issues. In the code I provided, I'm just looking to CREATE a table in the "mydb" database. I tested the connection to the database (it works). This is just creating a table with problems. Any advice or criticism would be appreciated. thanks

<?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 />"; } ?> 

Answer 1

Since no rows affect the creation of the table $ createTable returns 0, see the manual

PDO :: exec () returns the number of rows that have been modified or deleted by the SQL statement. If no lines were affected, PDO :: exec () returns 0.

Since you are creating a table, you will be free from SQL injection if the column names are hard-coded (as in the code below). I left $table = "tcompany"; , since you want to print the created table (I would leave it myself)

I added error handling , which will display errors in the 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 response to using a comment

 CREATE TABLE IF NOT EXISTS