Introduction
When building web applications, storing and retrieving data is vital. MySQL is one of the most popular relational database management systems used in web development. In this article, we’ll show you how to connect to a MySQL database using PHP’s MySQL Improved Extension (mysqli), and how to perform basic read operations.
Connecting to the MySQL Database
To connect to a MySQL database using mysqli, you will need to create a new instance of the mysqli class, passing in the host, username, password, and database name as parameters:
$host = 'localhost';
$username = 'user';
$password = 'password';
$database = 'mydatabase';
$mysqli = new mysqli($host, $username, $password, $database);
if ($mysqli ->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli ->connect_error;
exit();
}
You can replace the values of $host, $username, $password, and $database with your own database details.
Explanation
The $host variable is set to ‘localhost’, which is the default value if your database is hosted on the same server as your PHP script.
The $username variable is set to the MySQL user that you have set up for your database.
The $password variable is set to the password for the MySQL user.
The $database variable is set to the name of the MySQL database that you want to connect to.
If the connection to the database fails, the code inside the if statement will run and display an error message.
Reading Data from the MySQL Database
Once you have successfully connected to the MySQL database, you can perform read operations on it. One common operation is to retrieve data from a table. Here’s how you do it using mysqli:
$query = "SELECT * FROM mytable";
$result = $mysqli->query($query);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "Name: " . $row["name"] . " - Age: " . $row["age"] . "<br>";
}
} else {
echo "No results found.";
}
The above code will retrieve all the rows and columns from a table called ‘mytable’ and print the name and age of each row into the web page.
Explanation
The $query variable is set to the SQL statement that you want to execute. In this case, we are selecting all columns from the ‘mytable’ table.
The $result variable is set to the result of the SQL statement executed using $mysqli’s query() method.
The while loop will iterate through each row in the result set, until there are no more rows to fetch.
Inside the loop, we are printing the name and age of each row.
If there are no rows returned, a message indicating ‘No results found.’ will be printed.
Conclusion
Connecting to a MySQL database and reading data from it is a fundamental part of web development. In this article, we’ve shown you how to connect to a MySQL database using the mysqli extension and how to perform basic read operations. With this knowledge, you can start building more complex web applications that interact with a MySQL database.