12.1. 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. 12.1.1. 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 Example 12-1. Example 12-1. Creating a table from a PHP page in create_table.php <?php include('db_login.php'); require_once( 'DB.php' ); $connection = DB::connect( "mysql://$db_username:$db_password@$db_host/ $db_database"); 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)); } $connection->disconnect(); ?>
|
Example 12-1 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 12-1 shows the describe (desc) command for the table from the mysql command-line client. Figure 12-1. 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. |
|
12.1.2. Dropping a Table Example 12-2 drops the table you just created. Example 12-2. Dropping the purchases table in drop.php <?php require_once('db_login.php'); require_once('DB.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!"; $connection->disconnect(); ?>
|
Example 12-2 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 Example 12-1. 12.1.3. 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 12-2. Figure 12-2. Attempting to create an existing table generates this error 
Next, you'll add data to an existing table based on input from the user. | 