Although PHP supports several popular databases, MySQL remains by far the most common database solution. PHP's MySQL support has evolved considerably in recent years, with the MySQLi (MySQL Improved) extension being the current recommended solution. Here are the most commonly used methods.
The PHP 5.3 release includes a new MySQL driver known as mysqlnd (MySQL Native Driver). This driver eliminates the need for a previously required special licensing exception (FLOSS), and eliminates the need to have MySQL installed on the same machine as PHP. It has already been integrated with the mysql and mysqli extensions, with PDO support in the works.
Connecting to MySQL
The mysqli extension provides a number of ways to connect to MySQL, but the easiest involves just passing the connection data along when instantiating the mysqli class:
mysqli new mysqli([string host [, string user [, string
pswd
[string dbname [int port [string socket]]]]]]);
Here's an example:
$mysqli = new mysqli("localhost", "webuser", "secret",
"corporate");
Handling Connection Errors
In case of connection error you can retrieve both the error number and error string using the errno() and error() methods. Example:
if ($mysqli->errno) {
printf("Unable to connect: %s", $mysqli->error);
exit();
}
Sending a Query to the Database
Once the connection has been established, you can begin querying the database. Queries are sent using the query() method:
mixed query(string $query [, int $resultmode])
Setting the optional $resultmode parameter to MYSQLI_USE_ RESULT will cause query() to return the result as an unbuffered set.
Example:
$result = $mysqli->query("SELECT callsign FROM
stations");
Sending INSERT, UPDATE, and DELETE queries works identically. For instance, sending an UPDATE query works like this:
$result = $mysqli->query("UPDATE stations SET station
= '610' WHERE callsign = 'WTVN'");
Retrieving Data
Data can be parsed from the result set using a number of data structures, including via associative and indexed arrays, and objects.
Retrieving data as an associative array:
while ($row = $result->fetch_array(MYSQLI_ASSOC) {
printf("%S", $row["callsign"]);
}
Retrieving data as an indexed array:
while ($row = $result->fetch_row() {
printf("%S", $row[0]);
}
Retrieving data as an object:
while ($row = $result->fetch_object() {
printf("%S", $row->callsign);
}
Determining the Number of Rows Affected and Retrieved
To determine the number of affected rows after sending an INSERT, UPDATE, or DELETE query, use the affected_rows property.
Example:
$result = $mysqli->query("UPDATE stations SET station =
'610' WHERE callsign = 'WTVN'");
printf("Rows affected: %d", $result->rows_affected);
To determine how many rows were returned when using a SELECT query, use the num_rows property:
$result = $mysqli->query("SELECT * FROM stations WHERE
state ='Ohio');
printf("Rows affected: %d", $result->num_rows);
Working with Prepared Statements
Prepared statements both optimize query performance and decrease the possibility of SQL injection attacks by separating the query data from the logic, fi rst passing the query to MySQL for preparation, binding variables to the query columns, and fi nally passing the data to MySQL for query execution.
To prepare a query, create the query, and then initialize a statement object using the stmt_init() method:
$query = "INSERT INTO stations VALUES(?, ?)";
$stmt = $mysqli->stmt_init();
Next the query is prepared by passing it to MySQL using the prepare() method:
$stmt->prepare($query);
Next, bind the parameters using the bind_param() method:
$stmt->bind_param('ss', "WTVN", "610");
Finally, execute the prepared statement using the execute() method:
$stmt->execute();
You can also use prepared statements to retrieve results. The general process used to execute the previous INSERT query is identical to that required for executing a SELECT query, except that the bind_param() method is not required, and you bind results following a call to the execute() method. An example follows:
$query = "SELECT callsign, frequency FROM stations
ORDER BY callsign";
$stmt = $mysqli->stmt_init();
$stmt->prepare($query);
$stmt->execute();
$stmt->bind_result($callsign, $frequency);
while ($stmt->fetch())
printf("%s: %s<br />", $callsign, $frequency);
Transactions
By default the MySQLi extension will render each query "permanent" upon successful execution, actually changing the database's contents when INSERT, UPDATE, and DELETE queries are processed. However the success of some tasks depend upon the successful execution of several queries, and until all have occur. ATM transactions and online credit card processing are common examples requiring several queries. Using transactions, you can change the MySQLi extension's behavior, committing a series of queries as you see fit.
To begin a transaction, start by disabling the autocommit feature:
$mysqli->autocommit(FALSE);
Execute the various queries as you see fit, and if everything proceeds as you expect, execute the commit() method:
$mysqli->commit();
Otherwise, if a problem occurs, execute the rollback() method:
$mysqli->rollback();
