Using PDO to CREATE TABLE

Question

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

Are you getting any errors? You should echo out $db->errorInfo() in the else statement to see if there were any sql errors. — Jonathan Kuhn
– Jonathan Kuhn, Commented Oct 24, 2013 at 21:35
I recommend turning on the Query Log. This way you know the exact query that is executing on the server. stackoverflow.com/questions/6479107/… — user1647708
– user1647708, Commented Oct 24, 2013 at 22:39

david strachan · Accepted Answer · 2016-04-25 15:37:36Z

52

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

edited Apr 25, 2016 at 15:37

answered Oct 25, 2013 at 18:14

david strachan

7,2282 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jay Armstrong Over a year ago

Thank you so much for your answer. I will be able to learn alot from it. I really appreciate the fast reply.

user2805663 Over a year ago

sir @david how can you create multiple tables using php pdo?

david strachan Over a year ago

Just create each table as in example. I would not try to create more than one table at a time.

JasonDavis Over a year ago

This is just what I need! thanks. I need to check that the table exist or not though so I don't like recreate it if that's even possibble?

Collectives™ on Stack Overflow

Using PDO to CREATE TABLE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related