Creating MySQL Tables with PHP

In my previous post I created MySQL tables and did inserts from phpMyAdmin. Now I'll show you how to do the same thing from a PHP program. As you'll see, this is just another way of getting the same SQL commands executed. Of course, once you can create tables and insert from PHP, then you can start building a real web application, by doing things like storing data that users submit to your site.

The first thing we need to do is tell PHP to connect to the database:

<?php
// make a connection to the mysql database server
$db = mysqli_connect("womenshoes.db.6787492.hostedresource.com",
"womenshoes", "*****", "womenshoes");

if ($db) {

Note that on GoDaddy hosting, you can't just say 'localhost' for the first parameter, which identifies the location of the MySQL database (e.g. the IP address of the machine it's hosted on). Instead you need to grab the hostname listed on the phpMySQL screen for the database. After we attempt the connection, we test whether $db is true or not. If it's true, then we successfully made the connection.

Next, I've got some code right now to totally delete my tables (i.e. "drop" them). This is something you have to be careful using since it totally erases your database table. For now I'm just using it so I can run the code many times without getting errors that something already exists.

//Drop the tables first
  $query = "DROP TABLE shoe_primary_file,shoe_secondary_file";

Next, we create one of our tables and check to see if we were successful or not. The SQL should look familiar from the previous post where we were using phpMyAdmin instead of PHP to do this. The main difference here is that we are doing this through the mysqli_query() function, passing in the database connection we created above, along with our query string. Here's the code to create our first table:

//Create the tables:
  $query = "CREATE TABLE IF NOT EXISTS shoe_primary_file (
            itemnum int(11) default NULL,
            description text,
            price int(11) default NULL
             ) ;";

  $result = mysqli_query($db, $query);
  if ($result) {
     echo "You know how to create a database table<br>\n";
  }
  else die('You dont know how to create a table');

Then we do the same thing for our secondary table:

$query = "CREATE TABLE IF NOT EXISTS shoe_secondary_file (
             id int(11) NOT NULL auto_increment,
             itemnum int(11) default NULL,
             size varchar(80) default NULL,
             width varchar(10) default NULL,
             color varchar(60) default NULL,
             inventory int(11) default NULL,
             PRIMARY KEY  (id),
             KEY itemnum (itemnum)
           ) ;";

  $result = mysqli_query($db, $query);
  if ($result) {
     echo "You <em>really</em> know how to create a database table<br>\n";
  }
  else die('You dont know how to create a table');

Next, we insert our first record, and see if that was successful.

$query = "INSERT INTO shoe_primary_file VALUES('10100', 'Elegance and pain defined. 
Classic special-occasion heel crafted in  metallic or suede leather. Cross-over straps 
at toe and double ankle strap with  gold metal self-fastening oval buckle. Suede lining 
and leather sole. 3 1/2 inch self-covered high heels that are sure hell to walk in.  
Made in the USA. Catalog/cssbakery.com only', '275');";

  $result = mysqli_query($db, $query);
  if ($result) {
     echo "You know how to insert a row in a table<br>\n";
  }
  else die('You dont know how to insert a row in a table');

Now we want to insert a number of rows into the secondary table. The way I do that here is to first construct a PHP array of all the records that I want to put into the secondary table. Then I loop through those records, calling mysqli_query on each one.

$insertList = array(
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '7', 'M', 'platinum', 7);",
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '6 1/2', 'M', 'black suede', 5);",
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '7', 'W', 'platinum', 4);",
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '8', 'M', 'navy suede', 2);"
  );
  $r = "really ";
  foreach ($insertList as $insert) {
     $result = mysqli_query($db, $insert);
     if ($result) {
         echo "You <em>$r</em> know how to insert into a table!<br>\n";
     } else die('Sorry, but you dont know how to insert into a table');
     $r .= "really ";
  }

That's all there is to it for creating and inserting data into MySQL from PHP. Once you've got the database connection open, you start sending SQL query strings to it via mysqli_query(). As a final step it's always good practice to close the connection to the MySQL database using: mysqli_close($db).

<?php
  // close the db connection:
  $db = mysqli_close($db)

Here's the entire program listing in one piece:

<?php
// make a connection to the mysql database server
$db = mysqli_connect("womenshoes.db.6787492.hostedresource.com",
"womenshoes", "*****", "womenshoes");
if ($db) {

  //Drop the tables first
  $query = "DROP TABLE shoe_primary_file,shoe_secondary_file";
  $result = mysqli_query($db, $query);
  if ($result) {
      echo "You know how to drop tables!<br>\n";
  } else die ("You dont know how to drop a table.");

  //Create the tables:
  $query = "CREATE TABLE IF NOT EXISTS shoe_primary_file (
            itemnum int(11) default NULL,
            description text,
            price int(11) default NULL
             ) ;";

  $result = mysqli_query($db, $query);
  if ($result) {
     echo "You know how to create a database table<br>\n";
  }
  else die('You dont know how to create a table');

  $query = "CREATE TABLE IF NOT EXISTS shoe_secondary_file (
             id int(11) NOT NULL auto_increment,
             itemnum int(11) default NULL,
             size varchar(80) default NULL,
             width varchar(10) default NULL,
             color varchar(60) default NULL,
             inventory int(11) default NULL,
             PRIMARY KEY  (id),
             KEY itemnum (itemnum)
           ) ;";

  $result = mysqli_query($db, $query);
  if ($result) {
     echo "You <em>really</em> know how to create a database table<br>\n";
  }
  else die('You dont know how to create a table');

$query = "INSERT INTO shoe_primary_file VALUES('10100', 'Elegance and pain defined. 
Classic special-occasion heel crafted in  metallic or suede leather. Cross-over straps 
at toe and double ankle strap with  gold metal self-fastening oval buckle. Suede lining 
and leather sole. 3 1/2 inch self-covered high heels that are sure hell to walk in.  
Made in the USA. Catalog/cssbakery.com only', '275');";

  $result = mysqli_query($db, $query);
  if ($result) {
     echo "You know how to insert a row in a table<br>\n";
  }
  else die('You dont know how to insert a row in a table');

  $insertList = array(
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '7', 'M', 'platinum', 7);",
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '6 1/2', 'M', 'black suede', 5);",
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '7', 'W', 'platinum', 4);",
   "INSERT INTO shoe_secondary_file VALUES(0, 10100, '8', 'M', 'navy suede', 2);"
  );
  $r = "really ";
  foreach ($insertList as $insert) {
     $result = mysqli_query($db, $insert);
     if ($result) {
         echo "You <em>$r</em> know how to insert into a table!<br>\n";
     } else die('Sorry, but you dont know how to insert into a table');
     $r .= "really ";
  }
}
else die("Hey Miné, You dont know how to connect to a database");

echo "\n\nDone!\n";

?>

MySQL/PHP Database Series

• Passing a Query Parameter to PHP
• Looping through Multiple Records with PHP
• Reading the Database from PHP
• Creating MySQL Tables with PHP
• Creating MySQL Tables and Inserting Records with phpMyAdmin
• MySQL Tonight - Creating the Database
• PHP Example with GoDaddy