Working with MySQL Queries in PHP

When working with databases in PHP, it’s essential to understand how to execute and handle MySQL queries effectively. In this tutorial, we’ll explore the basics of executing MySQL queries in PHP, handling errors, and retrieving data from query results.

Executing MySQL Queries

To execute a MySQL query in PHP, you can use the mysql_query() function or its improved alternatives, such as mysqli_query() or PDO::query(). Here’s an example using mysql_query():

$username = $_POST['username'];
$password = $_POST['password'];

$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '$username'");

However, this approach has several issues. Firstly, the mysql_ functions are deprecated and have been removed in PHP 7. Secondly, the query is vulnerable to SQL injection attacks.

Handling Errors

When executing a MySQL query, it’s crucial to check for errors. You can use the mysql_error() function to retrieve the error message:

$result = mysql_query("SELECT * FROM Users WHERE UserName LIKE '$username'");

if ($result === FALSE) {
    trigger_error(mysql_error(), E_USER_ERROR);
}

However, this approach is still not recommended due to the deprecation of the mysql_ functions.

Using Prepared Statements

A better approach is to use prepared statements with either mysqli_ or PDO. Here’s an example using mysqli_:

$mysqli = new mysqli($host, $username, $password, $database);

$stmt = $mysqli->prepare("SELECT * FROM Users WHERE UserName LIKE ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['FirstName'];
}

This approach is more secure and efficient.

Retrieving Data from Query Results

Once you’ve executed a query, you can retrieve data from the result using various functions, such as mysql_fetch_array(), mysqli_fetch_assoc(), or PDO::fetch(). Here’s an example using mysqli_fetch_assoc():

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['FirstName'];
}

Best Practices

When working with MySQL queries in PHP, keep the following best practices in mind:

• Use prepared statements to prevent SQL injection attacks.
• Check for errors when executing queries.
• Use mysqli_ or PDO instead of the deprecated mysql_ functions.
• Escape user input using mysqli_real_escape_string() or PDO::quote().
• Keep your database credentials secure.

By following these guidelines and examples, you’ll be able to execute and handle MySQL queries effectively in PHP.