Learning PHP and MySQL

Chapter 12. Modifying MySQL Objects and PHP Data

This chapter extends what you learned in Chapter 11 to show you how to perform more complicated database tasks from PHP. We’ll learn how to create and modify both data and database objects from PHP. We’ll go over a framework for dynamically creating links to expand on data from a database query. In fact, after you learn about sessions in the next chapter, you’ll have everything you need to create full-fledged applications.

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 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)) ...