What best practices should be followed when handling database queries and result sets in PHP?

When handling database queries and result sets in PHP, it is important to use prepared statements to prevent SQL injection attacks. Additionally, it is recommended to properly sanitize input data before executing queries to ensure data integrity and security. Finally, always remember to close database connections after use to free up resources. 

// Example of using prepared statements to handle database queries in PHP
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Prepare and bind SQL statement
$stmt = $conn->prepare("SELECT id, name FROM users WHERE id = ?");
$stmt->bind_param("i", $id);

// Set parameters and execute
$id = 1;
$stmt->execute();

// Bind result variables
$stmt->bind_result($user_id, $user_name);

// Fetch results
while ($stmt->fetch()) {
    echo "User ID: " . $user_id . " User Name: " . $user_name . "<br>";
}

// Close statement and connection
$stmt->close();
$conn->close();

Keywords

SQL injection PDO prepared statements mysqli data validation

Related Questions