Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Creating a Database

You can create a database by using the following:

mysql_create_db(database_name[, link_identifier]);

The link identifier is optional; if omitted, the last opened link will be used. This function returns

an integer result—positive (TRUE) if successful. In the example, you might write

$create_id = mysql_create_db("mydb");

echo "Creation ID is ".$create_id."<br>\n";

When you run this script, you should get

Creation ID is 1

You have now successfully created a database called mydb on your server.

Basics of PHP Database Queries

Now, imagine that you want to create a table in the database. You have mysql_db_query and
mysql_query to send just about any query to MySQL. These commands work as follows:

mysql_db_query(database, query[, link_identifier]);

mysql_query(query[, link_identifier]);

When you use these functions, they return an integer that is a result identifier to point you to the query

result. If an error occurs, they return 0 or FALSE.

The link identifier is optional; if omitted, the last opened link will be used.

mysql_db_query allows you to specify a database to query, while mysql_query runs the query on

the currently selected database. You select a database using the following syntax:

mysql_select_db(database_name[, link_identifier] );

Look at an example of selecting a database and then running a create table query.

$select = mysql_select_db ("mydb");

$sql = "CREATE TABLE photos (

num INTEGER NOT NULL PRIMARY KEY,

date_taken DATE,

description VARCHAR(200))";

$result = mysql_query ("$sql");

echo "Result of table creation is $result<br>\n";

If everything goes to plan, you should get the following output:

Result of table creation is 1

Handling Query Errors

How would you know if the query failed? To report an error and handle it appropriately, you could improve
the last two lines of your code as follows:

if ($result = mysql_query ("$sql")) {

echo "Result of table creation is $result<br>\n";

} else {

echo "Error: ".mysql_errno()."; error description: ".mysql_error();

}

If you run this now (with the photos table already in existence), you will get the following output, as

returned by mysql_errno() and mysql_error():

Error: 1050; error description: Table 'photos' already exists

You may want to make your error handling a little more slick, such as by creating a small function at the

beginning of your code:

function error_report () {