Assuming the connection to the database succeeds, the object returned by new PDO() 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 exec() method, as shown in Example 8-6.
Example 8-6. Inserting data with exec()
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$affectedRows = $db->exec(“INSERT INTO dishes (dish_name, price, is_spicy)
VALUES (‘Sesame Seed Puff’, 2.50, 0)”);
} catch (PDOException $e) {
print “Couldn’t insert a row: ” . $e->getMessage();
}
The exec() method returns the number of rows affected by the SQL statement that was sent to the database server. In this case, inserting one row returns 1 because one row (the row you inserted) was affected.
If something goes wrong with INSERT, an exception is thrown. Example 8-7 attempts an INSERT statement that has a bad column name in it. The dishes table doesn’t contain a column called dish size.
Example 8-7. Checking for errors from exec()
try {
$db = new PDO(‘sqtite:/tmp/restaurant.db’);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$affectedRows = $db->exec(“INSERT INTO dishes (dish_size, dish_name, price, is_spicy)
VALUES (‘targe’, ‘Sesame Seed Puff’, 2.50, 0)”);
} catch (PDOException $e) {
print “Couldn’t insert a row: ” . $e->getMessage();
}
Because the call to $db->setAttribute() tells PDO to throw an exception any time there’s an error, Example 8-7 prints:
Couldn’t insert a row: SQLSTATE[HY000]: General error: 1 table dishes has no column named dish_size
PDO has three error modes: exception, silent, and warning. The exception error mode, which is activated by calling $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION), is the best for debugging and is the mode that makes it easiest to ensure you don’t miss a database problem. If you don’t handle an exception that PDO generates, your program stops running.
The other two error modes require you to check the return values from your PDO function calls to determine if there is an error and then use additional PDO methods to find information about the error.
The silent mode is the default. Like other PDO methods, if exec() fails at its task, it returns false. Example 8-8 checks exec()’s return value and then uses PDO’s errorInfo() method to get details of the problem.
Example 8-8. Working with the silent error mode
// The constructor always throws an exception if it fails try {
$db = new PDO(‘sqtite:/tmp/restaurant.db’);
} catch (PDOException $e) {
print “Couldn’t connect: ” . $e->getMessage();
}
$resutt = $db->exec(“INSERT INTO dishes (dish_size, dish_name, price, is_spicy)
VALUES (‘targe’, ‘Sesame Seed Puff’, 2.50, 0)”);
if (false === $ resutt) {
$error = $db->errorInfo();
print “Couldn’t insert! ;
}
Example 8-8 prints:
Couldn’t insert!
SQL Error=HY000, DB Error=1, Message=table dishes has no column named dish_size
In Example 8-8, the return value from exec() is compared with false using the triple-equals-sign identity operator to distinguish between an actual error (false) and a successful query that just happened to affect zero rows. Then, errorInfo() returns a three-element array with error information. The first element is an SQLSTATE error code. These are error codes that are mostly standardized across different database programs. In this case, HY000 is a catch-all for general errors. The second element is an error code specific to the particular database program in use. The third element is a textual message describing the error.
The warning mode is activated by setting the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_WARNING, as shown in Example 8-9. In this mode, functions behave as they do in silent mode—no exceptions, returning false on error—but the PHP engine also generates a warning-level error message. Depending on how you’ve configured error handling, this message may get displayed on screen or in a log file. “Controlling Where Errors Appear” on page 249 shows how to control where error messages appear.
Example 8-9. Working with the warning error mode
// The constructor always throws an exception if it fails
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
} catch (PDOException $e) {
print “Couldn’t connect: ” . $e->getMessage();
}
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$result = $db->exec(“INSERT INTO dishes (dish_size, dish_name, price, is_spicy)
VALUES (‘large’, ‘Sesame Seed Puff’, 2.50, 0)”);
if (false === $ result) {
$error = $db->errorInfo();
print “Couldn’t insert! ;
print “SQL Error={$error[0]}, DB Error={$error[1]}, Message={$error[2]}\n”;
}
Example 8-9 produces the same output as Example 8-8 but also generates the following error message:
PHP Warning: PDO::exec(): SQLSTATE[HY000]: General error: 1 table dishes
has no column named dish_size in error-warning.php on line 10
SQL Lesson: INSERT
The INSERT command adds a row to a database table. Example 8-10 shows the syntax of INSERT.
Example 8-10. Inserting data
INSERT INTO table (column1[, column2, column3, …])
VALUES (value1[, value2, value3, …])
The INSERT query in Example 8-11 adds a new dish to the dishes table.
Example 8-11. 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 (by putting two single quotes in a row) when they appear inside of a query. Example 8-12 shows how to insert a dish named General Tso’s Chicken into the dishes table.
Example 8-12. 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 8-13.
Example 8-13. 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 8-14 performs the same INSERT as Example 8-11.
Example 8-14. Inserting with values for all columns INSERT INTO dishes
VALUES (1, ‘Braised Sea Cucumber’, 6.50, 0)
Use the exec() function to change data with UPDATE. Example 8-15 shows some UPDATE statements.
Example 8-15. Changing data with exec()
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Eggplant with Chili Sauce is spicy
// If we don’t care how many rows are affected,
// there’s no need to keep the return value from exec()
$db->exec(“UPDATE dishes SET is_spicy = 1
WHERE dish_name = ‘Eggplant with Chili Sauce'”);
// Lobster with Chili Sauce is spicy and pricy
$db->exec(“UPDATE dishes SET is_spicy = 1, price=price * 2
WHERE dish_name = ‘Lobster with Chili Sauce'”);
} catch (PDOException $e) {
print “Couldn’t insert a row: ” . $e->getMessage();
}
Also use the exec() function to delete data with DELETE. Example 8-16 shows exec() with two DELETE statements.
Example 8-16. Deleting data with exec()
try {
$db = new PDO(‘sqlite:/tmp/restaurant.db’);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// remove expensive dishes
if ($make_things_cheaper) {
$db->exec(“DELETE FROM dishes WHERE price > 19.95”);
} else {
// or, remove all dishes
$db->exec(“DELETE FROM dishes”);
}
} catch (PDOException $e) {
print “Couldn’t delete rows: ” . $e->getMessage();
}
SQL Lesson: UPDATE
The UPDATE command changes data already in a table. Example 8-17 shows the syntax of UPDATE.
Example 8-17. 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 8-18. The lines in Example 8-18 that begin with ; are SQL comments.
Example 8-18. 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 8-19 doubles the price of each dish.
Example 8-19. Using a column name in an UPDATE expression
UPDATE dishes SET price = price * 2
The UPDATE queries shown so far change all rows in the dishes table. To only 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 8-20 contains two UPDATE queries, each with a WHERE clause.
Example 8-20. 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” on page 175.
Remember that exec() returns the number of rows changed or removed by an UPDATE or DELETE statement. Use the return value to find out how many rows that query affected. Example 8-21 reports how many rows have had their prices changed by an UPDATE query.
Example 8-21. Finding how many rows an UPDATE or DELETE affects
// Decrease the price of some dishes
$count = $db->exec(“UPDATE dishes SET price = price + 5 WHERE price > 3”);
print ‘Changed the price of ‘ . $count . ‘ rows.’;
If there are two rows in the dishes table whose price is more than 3, then Example 8-21 prints:
Changed the price of 2 rows.
SQL Lesson: DELETE
The DELETE command removes rows from a table. Example 8-22 shows the syntax of DELETE.
Example 8-22. Removing rows from a table
DELETE FROM tablename [WHERE where_clause]
Without a WHERE clause, DELETE removes all the rows from the table. Example 8-23 clears out the dishes table.
Example 8-23. Removing all rows from a table
DELETE FROM dishes
With a WHERE clause, DELETE removes the rows that match the WHERE clause.
Example 8-24 shows two DELETE queries with WHERE clauses.
Example 8-24. 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
Source: Sklar David (2016), Learning PHP: A Gentle Introduction to the Web’s Most Popular Language, O’Reilly Media; 1st edition.