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=42
if (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 actor
echo "<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();
?>
English translation
You have asked to visit this site in English. For now, only the interface is translated, but not all the content yet.If you want to help me in translations, your contribution is welcome. All you need to do is register on the site, and send me a message asking me to add you to the group of translators, which will give you the opportunity to translate the pages you want. A link at the bottom of each translated page indicates that you are the translator, and has a link to your profile.
Thank you in advance.
Document created the 30/01/2003, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/php-rf-mysqli.examples-basic.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.
