MySQL is a widely used open-source relational database management system. Connecting to a MySQL database with PHP allows web developers to create dynamic websites with data-driven content.
Table of Contents
Connecting to MySQL with PHP
PHP provides a built-in extension called mysqli for connecting to a MySQL database. The following code demonstrates how to establish a connection to a MySQL database using mysqli:
<?php
// Define database credentials
$host = "localhost";
$username = "root";
$password = "password";
$database = "my_database";
// Create a connection
$conn = mysqli_connect($host, $username, $password, $database);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>
The mysqli_connect() function takes four parameters:
- $host: the hostname or IP address of the MySQL server
- $username: the username for accessing the database
- $password: the password for accessing the database
- $database: the name of the database to connect to database
Executing SQL Queries with PHP
Once you have established a connection to the MySQL database, you can execute SQL queries using the mysqli_query() function. The following code demonstrates how to retrieve data from a MySQL database using a SELECT statement:
<?php
// Define database credentials
$host = "localhost";
$username = "root";
$password = "password";
$database = "my_database";
// Create a connection
$conn = mysqli_connect($host, $username, $password, $database);
// Check the connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Execute a SELECT query
$sql = "SELECT * FROM users";
$result = mysqli_query($conn, $sql);
// Output the results
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
}
} else {
echo "0 results";
}
// Close the connection
mysqli_close($conn);
?>
The mysqli_query() function takes two parameters:
- $conn: the connection object returned by mysqli_connect()
- $sql: the SQL query to execute
Closing the Connection
It’s important to close the connection to the MySQL database once you are done using it. This frees up resources and avoids potential security vulnerabilities. You can close the connection using the mysqli_close() function, as shown in the following code:
// Close the connection
mysqli_close($conn);
Conclusion
Connecting to a MySQL database with PHP is essential for building dynamic web applications. By using the mysqli extension, executing SQL queries, and properly closing the connection, you can create robust and secure applications.
