Home > Articles > Programming > PHP

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Creating a Table

Once we've connected to the database, we're ready to create the table we'll use for events. Fortunately, we can do that directly through an SQL statement, so we don't have to worry about mucking about with other tools. Listing 2 uses the connection created in the last step to execute the SQL statement that creates the table.

Listing 2—Creating the Table

<?php

//Connect to the database
$connection = mysql_connect ( "localhost", "myusername", "mypassword" );
if ($connection) {
  echo ("Connection created ...");
} else {
  die ( "Error connecting to database!" );
}

?>
<br />
<?php

$create_table_string = "CREATE TABLE events (".
             "eventid INTEGER NOT NULL AUTO_INCREMENT, ".
             "eventMonth INTEGER, ".
             "eventDay INTEGER, ".
             "eventYear INTEGER, ".
             "eventTitle VARCHAR(50), ".
             "eventDesc VARCHAR(255), ".
             "PRIMARY KEY (eventid))";

//Execute the query
mysql_db_query ( "mysql", $create_table_string )||
 die ("Can't create the table!");

echo ("Table created successfully.");

?>

The SQL string is pretty straightforward; it's just a standard SQL statement. To execute it, we'll use the mysql_db_query() function, which simply takes any SQL statement and executes it against the database. This function doesn't care whether the statement selects data or creates a table, as it does here. Notice that nowhere in this statement are we referencing the $connection resource that we created. Instead, the connection is simply available to PHP, which uses it to locate the database named mysql and execute the statement against it. (mysql was the name that I gave the database when I installed it.)

Notice also that we've got a "success" statement right in the middle of the application, with no test to make sure that it's accurate. This probably isn't ideal, but I wanted to illustrate a point. When we executed the query, we used the "or die" notation discussed in the previous section. If the query executes successfully, the die() function will be skipped, so the echo() statement is correct; the table was indeed created, as shown in Figure 1.

Figure 1Figure 1 Creating the table successfully executes the echo() function.

You can execute this page simply by calling it in your browser. For example, if you named it createtable.php, simply point your browser to http://localhost/createtable.php.

On the other hand, if there's a problem executing the query, the page will die and the success statement will never be reached. For example, if you try to execute the page again, it won't able to create the table because the table already exists, so the page will die and you'll never see the success method (see Figure 2).

Figure 2Figure 2 If the statement doesn't execute successfully, the page dies.

You could drop the table in the same way, using a statement such as the following:

mysql_db_query ( "mysql", "drop table events" )||
 die ("Can't drop the table!");
  • + Share This
  • 🔖 Save To Your Account