Changing Database Objects from PHP

Changing Database Objects from PHP

The SQL query string remains the common tool for giving database commands. We can just as easily create and modify database objects with standard SQL that is called the same way we execute queries. We'll begin with creating a table.

Creating a Table

We've previously created the books and authors tables but we haven't created the purchases table. We'll create one using the PHP in Figure.

Creating a table from a PHP page in create_table.php

require_once( 'DB.php' );
$connection = DB::connect( "mysql://$db_username:$db_password@$db_host/
if (!$connection)
  die ("Could not connect to the database: <br>". DB::errorMessage());
$query = '
CREATE TABLE `purchases` (
  `purchase_id` int(11) NOT NULL auto_increment,
  `user_id` varchar(10) NOT NULL,
  `title_id` int(11) NOT NULL,
  `purchased` timestamp NOT NULL,
  PRIMARY KEY  (`purchase_id`)
echo ("Table created successfully!");
$result = $connection->query($query);
if (DB::isError($result))
  die ("Could not query the database: <br>". $query. " ".DB::errorMessage($result));

Figure has the same create statement bolded that you'd use directly from the command line. The statement is assigned to the $query variable as a string. When query is executed, you no longer get a result set. Instead, the table is created. We see this as the result:

Table created successfully!

Figure shows the describe (desc) command for the table from the mysql command-line client.

Our purchases table defined from a PHP script appears everywhere

You could just as easily have substituted another database command.

In general, commands to modify databases and tables should be kept out of your PHP code to reduce the risk of a malicious user exploiting them or plain old programming mistakes that could wipe out a lot of data. We discuss them to illustrate what can be done from PHP. The only time you're likely to use these commands directly in PHP code is if you're writing a utility for web-based administration of MySQL databases such as phpMyAdmin.

If you really feel the need to use modification commands, place them in a portion of your site that is either password-protected at the Apache web server level or access-protected through your PHP code. We'll discuss restricting access to pages and logging in users in Chapter 13. With that caution in place, we'll discuss dropping tables next.

Dropping a Table

Figure drops the table you just created.

Dropping the purchases table in drop.php

$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die ("Could not connect to the database: <br />". DB::errorMessage($connection));
$query = "DROP TABLE `purchases`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database: <br />". $query." ".DB::errorMessage($result));
echo "Table dropped successfully!";

Figure returns:

Table dropped successfully!

That worked great, but you're going to need the purchases table, so let's recreate the table by calling the create_table.php code in Figure.

Errors Happen

To make sure you handle an error properlysuch as a typo in the create statement or, in this case, trying to create a table that already existsexecute the create_table.php script again. This produces the error in Figure.

Attempting to create an existing table generates this error

Next, you'll add data to an existing table based on input from the user.

 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows