Php/docs/mysqli.examples-basic
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 [http://dev.mysql.com/doc/sakila/en/index.html » 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;}// Connecting to and selecting a MySQL database named sakila// Hostname: 127.0.0.1, username: your_user, password: your_pass, db: sakila$mysqli = new mysqli('127.0.0.1', 'your_user', 'your_pass', 'sakila');// 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 // Let's try this: echo "Sorry, this website is experiencing problems."; // 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 this echo "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 exit exit;}// Perform an SQL query$sql = "SELECT actor_id, first_name, last_name FROM actor WHERE actor_id = $aid";if (!$result = $mysqli->query($sql)) { // Oh no! The query failed. echo "Sorry, the website is experiencing problems."; // Again, do not do this on a public site, but we'll show you how // to get the error information echo "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;}// Print our 5 random actors in a list, and link to each actorecho "<ul>\n";while ($actor = $result->fetch_assoc()) { echo "<li><a href='" . $_SERVER['SCRIPT_FILENAME'] . "?aid=" . $actor['actor_id'] . "'>\n"; echo $actor['first_name'] . ' ' . $actor['last_name']; echo "</a></li>\n";}echo "</ul>\n";// The script will automatically free the result and close the MySQL// connection when it exits, but let's just do it anyways$result->free();$mysqli->close();?>
