7.4 Putting Data into the Database

Assuming the connection to the database succeeds, the object returned by DB::connect( ) provides access to the data in your database. Calling that object's functions lets you send queries to the database program and access the results. To put some data into the database, pass an INSERT statement to the object's query( ) function, as shown in Example 7-8.

Example 7-8. Inserting data with query( )

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');
if (DB::isError($db)) { die("connection error: " . $db->getMessage( )); }
$q = $db->query("INSERT INTO dishes (dish_name, price, is_spicy)
    VALUES ('Sesame Seed Puff', 2.50, 0)");

Just like with the $db object that DB::connect( ) returns, the $q object that query( ) returns can be tested with DB::isError( ) to check whether the query was successful. Example 7-9 attempts an INSERT statement that has a bad column name in it. The dishes table doesn't contain a column called dish_size.

Example 7-9. Checking for errors from query( )

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');
if (DB::isError($db)) { die("connection error: " . $db->getMessage( )); }
$q = $db->query("INSERT INTO dishes (dish_size, dish_name, price, is_spicy)
    VALUES ('large', 'Sesame Seed Puff', 2.50, 0)");
if (DB::isError($q)) { die("query error: " . $q->getMessage( )); }

Example 7-9 prints:

query error: DB Error: syntax error

Instead of calling DB::isError( ) after every query to see if it succeeded or failed, it's more convenient to use the setErrorHandling( ) function to establish a default error-handling behavior. Pass the constant PEAR_ERROR_DIE to setErrorHandling( ) to have your program automatically print an error message and exit if a query fails. Example 7-10 uses setErrorHandling( ) and has the same incorrect query as Example 7-9.

Example 7-10. Automatic error handling with setErrorHandling( )

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');
if (DB::isError($db)) { die("Can't connect: " . $db->getMessage( )); }

// print a message and quit on future database errors
$db->setErrorHandling(PEAR_ERROR_DIE);

$q = $db->query("INSERT INTO dishes (dish_size, dish_name, price, is_spicy)
    VALUES ('large', 'Sesame Seed Puff', 2.50, 0)");
print "Query Succeeded!";

SQL Lesson: INSERT The INSERT command adds a row to a database table. Example 7-11 shows the syntax of INSERT. Example 7-11. Inserting data INSERT INTO table (column1[, column2, column3, ...]) VALUES (value1[, value2, value3, ...]) The INSERT query in Example 7-12 adds a new dish to the dishes table. Example 7-12. Inserting a new dish INSERT INTO dishes (dish_id, dish_name, price, is_spicy) VALUES (1, 'Braised Sea Cucumber', 6.50, 0) String values such as Braised Sea Cucumber have to have single quotes around them when used in an SQL query. Because single quotes are used as string delimiters, you need to escape single quotes with a backslash when they appear inside of a query. Example 7-13 shows how to insert a dish named General Tso's Chicken into the dishes table. Example 7-13. Quoting a string value INSERT INTO dishes (dish_id, dish_name, price, is_spicy) VALUES (2, 'General Tso\'s Chicken', 6.75, 1) The number of columns enumerated in the parentheses before VALUES must match the number of values in the parentheses after VALUES. To insert a row that contains values only for some columns, just specify those columns and their corresponding values, as shown in Example 7-14. Example 7-14. Inserting without all columns INSERT INTO dishes (dish_name, is_spicy) VALUES ('Salt Baked Scallops', 0) As a shortcut, you can eliminate the column list when you're inserting values for all columns. Example 7-15 performs the same INSERT as Example 7-12. Example 7-15. Inserting with values for all columns INSERT INTO dishes VALUES (1, 'Braised Sea Cucumber', 6.50, 0)

Example 7-10 prints:

DB Error: syntax error

Because the program quits when it encounters the query error, the last line of Example 7-10 never runs or prints its Query Succeeded! message.

The setErrorHandling( ) function belongs to the $db object, so you have to get a $db object by calling DB::connect( ) before you can call setErrorHandling( ). Therefore, one call to DB::isError( ) is still necessary in your program to see whether the connection succeeded. Once that's taken care of, however, you can call setErrorHandling( ) and not scatter the rest of your program with DB::isError( ) calls. Section 12.4 explains how to have setErrorHandling( ) print out a customized message when there is a database error.

Use the query( ) function to change data with UPDATE data as well. Example 7-16 shows some UPDATE statements.

Example 7-16. Changing data with query( )

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');
if (DB::isError($db)) { die("connection error: " . $db->getMessage( )); }
// Eggplant with Chili Sauce is spicy
$db->query("UPDATE dishes SET is_spicy = 1
            WHERE dish_name = 'Eggplant with Chili Sauce'");
// Lobster with Chili Sauce is spicy and pricy
$db->query("UPDATE dishes SET is_spicy = 1, price=price * 2
            WHERE dish_name = 'Lobster with Chili Sauce'");

Also use the query( ) function to delete data with DELETE. Example 7-17 shows query( ) with two DELETE statements.

Example 7-17. Deleting data with query( )

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');
if (DB::isError($db)) { die("connection error: " . $db->getMessage( )); }
// remove expensive dishes
if ($make_things_cheaper) {
    $db->query("DELETE FROM dishes WHERE price > 19.95");
} else {
    // or, remove all dishes
    $db->query("DELETE FROM dishes");
}

SQL Lesson: UPDATE The UPDATE command changes data already in a table. Example 7-18 shows the syntax of UPDATE. Example 7-18. Updating data UPDATE tablename SET column1=value1[, column2=value2, column3=value3, ...] [WHERE where_clause] The value that a column is changed to can be a string or number, as shown in Example 7-19. The lines in Example 7-19 that begin with ; are SQL comments. Example 7-19. Setting a column to a string or number ; Change price to 5.50 in all rows of the table UPDATE dishes SET price = 5.50 ; Change is_spicy to 1 in all rows of the table UPDATE dishes SET is_spicy = 1 The value can also be an expression that includes column names. The query in Example 7-20 doubles the price of each dish. Example 7-20. Using a column name in an UPDATE expression UPDATE dishes SET price = price * 2 The UPDATE queries shown so far each change all rows in the dishes table. To just change some rows with an UPDATE query, add a WHERE clause. This is a logical expression that describes which rows you want to change. The changes in the UPDATE query then happen only in rows that match the WHERE clause. Example 7-21 contains two UPDATE queries, each with a WHERE clause. Example 7-21. Using a WHERE clause with UPDATE ; Change the spicy status of Eggplant with Chili Sauce UPDATE dishes SET is_spicy = 1 WHERE dish_name = 'Eggplant with Chili Sauce' ; Decrease the price of General Tso's Chicken UPDATE dishes SET price = price - 1 WHERE dish_name = 'General Tso\'s Chicken' The WHERE clause is explained in more detail in the sidebar SQL Lesson: SELECT.

The affectedRows( ) function tells you how many rows were changed or removed by an UPDATE or DELETE statement. Call affectedRows( ) immediately after a query to find out how many rows that query affected. Example 7-22 reports how many rows have had their prices changed by an UPDATE query.

Example 7-22. Finding how many rows an UPDATE or DELETE affects

require 'DB.php';
$db = DB::connect('mysql://hunter:w)mp3s@db.example.com/restaurant');
if (DB::isError($db)) { die("connection error: " . $db->getMessage( )); }
// Decrease the price some some dishes
$db->query("UPDATE dishes SET price=price - 5 WHERE price > 20");
print 'Changed the price of ' . $db->affectedRows( ) . 'rows.';

If there are five rows in the dishes table whose price is more than 20, then Example 7-22 prints:

Changed the price of 5 rows.

SQL Lesson: DELETE The DELETE command removes rows from a table. Example 7-23 shows the syntax of DELETE. Example 7-23. Removing rows from a table DELETE FROM tablename [WHERE where_clause] Without a WHERE clause, DELETE removes all the rows from the table. Example 7-24 clears out the dishes table. Example 7-24. Removing all rows from a table DELETE FROM dishes With a WHERE clause, DELETE removes the rows that match the WHERE clause. Example 7-25 shows two DELETE queries with WHERE clauses. Example 7-25. Removing some rows from a table ; Delete rows in which price is greater than 10.00 DELETE FROM dishes WHERE price > 10.00 ; Delete rows in which dish_name is exactly "Walnut Bun" DELETE FROM dishes WHERE dish_name = 'Walnut Bun' There is no SQL UNDELETE command, so be careful with your DELETEs.