MySQLi extension basic examples

This example shows how to connect, execute a query, use basic
error handling, print resulting rows, and disconnect from a MySQL database.

This example uses the freely available Sakila database that
can be downloaded from » dev.mysql.com,
as described here. To get this example to work, (a) install sakila
and (b) modify the connection variables (host, your_user, your_pass).

Example #1 MySQLi extension overview example

<?php// Let's pass in a $_GET variable to our example, in this case// it's aid for actor_id in our Sakila database. Let's make it// default to 1, and cast it to an integer as to avoid SQL injection// and/or related security problems. Handling all of this goes beyond// the scope of this simple example. Example:// http://example.org/script.php?aid=42if (isset($_GET['aid']) && is_numeric($_GET['aid'])) {$aid = (int) $_GET['aid'];} else {$aid = 1;}

// Oh no! A connect_errno exists so the connection attempt failed!if ($mysqli->connect_errno) {// The connection failed. What do you want to do? // You could contact yourself (email?), log the error, show a nice page, etc. // You do not want to reveal sensitive information

// Something you should not do on a public site, but this example will show you // anyways, is print out MySQL error related information -- you might log thisecho "Error: Failed to make a MySQL connection, here is why: \n"; echo "Errno: " . $mysqli->connect_errno . "\n"; echo "Error: " . $mysqli->connect_error . "\n";

// You might want to show them something nice, but we will simply exitexit;}

// Again, do not do this on a public site, but we'll show you how // to get the error informationecho "Error: Our query failed to execute and here is why: \n"; echo "Query: " . $sql . "\n"; echo "Errno: " . $mysqli->errno . "\n"; echo "Error: " . $mysqli->error . "\n"; exit;}

// Phew, we made it. We know our MySQL connection and query // succeeded, but do we have a result?if ($result->num_rows === 0) {// Oh, no rows! Sometimes that's expected and okay, sometimes // it is not. You decide. In this case, maybe actor_id was too // large? echo "We could not find a match for ID $aid, sorry about that. Please try again."; exit;}

// Now, we know only one result will exist in this example so let's // fetch it into an associated array where the array's keys are the // table's column names$actor = $result->fetch_assoc();echo "Sometimes I see " . $actor['first_name'] . " " . $actor['last_name'] . " on TV.";

// Now, let's fetch five random actors and output their names to a list.// We'll add less error handling here as you can do that on your own now$sql = "SELECT actor_id, first_name, last_name FROM actor ORDER BY rand() LIMIT 5";if (!$result = $mysqli->query($sql)) { echo "Sorry, the website is experiencing problems."; exit;}