PHP MySQL Database Connection and Querying

In this article, we will explore how to connect PHP to a MySQL database and perform queries effectively. Understanding these fundamental operations is crucial for developing web applications that rely on data storage and retrieval.

Understanding Database Connection in PHP

To establish a connection between PHP and a MySQL database, we utilize the MySQLi (MySQL Improved) extension or PDO (PHP Data Objects). MySQLi is specifically designed for MySQL, while PDO supports various database types. Here, we will focus on using MySQLi to keep things simple.

Start with the following code snippet to connect to your MySQL database using MySQLi:

MySQLi Connection Example:

$servername = "localhost"; 
$username = "username"; 
$password = "password"; 
$dbname = "database_name"; 

$conn = new mysqli($servername, $username, $password, $dbname); 

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";

In this code, replace "localhost", "username", "password", and "database_name" with the relevant credentials. The connect_error method allows us to verify if the connection was successful.

Executing Queries with PHP and MySQL

Once you have a successful connection, executing queries is straightforward. You can use the query() method on the connection object to run SQL statements.

Here’s an example of retrieving data from a table:

$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. "
";
    }
} else {
    echo "0 results";
}

In this snippet, we query the users table to retrieve id, name, and email. The results are then displayed in a loop. If there are no results, a message will indicate that.

Best Practices for PHP MySQL Interaction

While connecting and querying a database is straightforward, following best practices is essential for security and performance:

• Use prepared statements: Avoid SQL injection attacks by utilizing prepared statements, especially when dealing with user inputs.
• Close the connection: Always close your database connection to free up resources. Use $conn->close(); at the end.
• Handle errors gracefully: Instead of displaying raw database errors to the user, log them for debugging and show a user-friendly message.

In summary, this article covered the essential steps to connect PHP to a MySQL database and execute queries. We discussed establishing a connection, executing a basic SELECT query, and highlighted best practices to ensure secure and effective database interaction.