MySQL Phrasebook

2017-07-07 02:10:07

PHP and MySQL are often spoken in the same breatha very large number of PHP applications use a MySQL database. For a while PHP even shipped with its own version of the MySQL client library, although this is no longer the case with PHP 5.

To check for MySQL support in a PHP-enabled web server, create a script that simply contains

<?php phpinfo();?>

View this script in a web browser and look for a section named either MySQL Support or MySQLi Support. MySQLi (MySQL Improved) is a newer version of the PHP API that works with MySQL 4.1.3 and above and can be used either procedurally or in an object-oriented way. This section covers MySQLi, but the classic MySQL API is very similar to the procedural use of MySQLi.

To enable MySQLi support at compile time, use the --with-mysql=/path/to/mysql_config switch.

Connecting to MySQL

$conn = mysqli_connect("host", "user", "password", "dbname"); $conn = new mysqli("host", "user", "password","dbname");

The first syntax shown uses the mysqli_connect() function to return a database connection handle using the given arguments. The second form uses the constructor method on a mysqli object to perform the same task.

You must either use the procedural or object-oriented approach throughout your script. Subsequent database operations are either functions that pass the result from mysqli_connect() as an argument or are methods called on a new instance of a mysqli object.

Executing a Query

$result = mysqli_query(query, $conn); $result = $conn->query(query);

The query passed in the first argument to mysqli_query() or the query() method both return a result handle. Using the procedural approach, the result handle is passed as an argument to subsequent functions that process the returned data. Using objects, the result itself is an object and you invoke its methods to process the queried data.

Fetching Data from a Result Set

$row = mysqli_fetch_array($result); $result->fetch_array();

The mysqli_fetch_array() function returns one data record from the result of a query each time it is called, and the fetch_array() method on a result handle works the same way. When there is no more data to be fetched, both return NULL.

The array returned has both numeric and associative indexes. Numeric index values begin at zero and correspond to the selected columns from left to right. Associative indexes correspond to the column names or aliases in the query.

To find the number of rows returned by the query, reference mysqli_num_rows with a result handle argument or the num_rows attribute on a result object. Use mysqli_num_fields or num_fields to find the number of columns in the data set.

Displaying Error Messages

mysqli_error($conn); $conn->error();

The mysqli_error() function takes the connection handle as its argument and returns the MySQL error message if the last query could not be executed. The error() method should be invoked on a database connection object.

To find the MySQL error number, use the mysqli_errno() function and the error() method in the same way.

Closing a Connection

mysqli_close($conn); $conn->close();

MySQL resources are automatically destroyed when your PHP script ends, but if you want to free the resources in your script, use the mysqli_close() function or the close() method on a database handle object.

To free a result resource without closing the database connection, use mysqli_free_result() or the free_result() method on a result object.

A Sample PHP Script

Listing 8.3 is a sample PHP script that establishes a database connection, executes a query against the sample database, and outputs the result in an HTML table. This example uses the object-oriented MySQLi API.

Listing 8.3. Executing a Query Using PHP and MySQLi

<?php /* Establish a database connection */ $conn = new mysqli("localhost", "user", "password", "dbname"); if (!$conn) { echo "Failed to connect to database: Error " . $conn->error(). "<br>\n"; exit; } /* Execute a query */ $sql_statement = "SELECT book_id, cond, title FROM book"; $result = $conn->query($sql_statement); if (!$result) { echo "Error executing query: Error: " . $conn->error(). "<br>\n"; exit; } /* Loop through the result set to display it */ echo "<table>\n"; while ($row = $result->fetch_array()) { echo "<tr>\n"; for ($i=0; $i<$result->num_rows; $i++) { echo "<td>" . $row[$i] . "</td>\n"; } echo "</tr>\n"; } echo "</table>"; ?>

This script produces a tabular output like the previous two examples, but this time uses HTML to define the table layout. Run the script in a web browser to see the table.