What are some best practices for writing SQL queries within PHP code?

When writing SQL queries within PHP code, it is important to use prepared statements to prevent SQL injection attacks and improve performance. Additionally, it is recommended to separate your SQL logic from your PHP code for better maintainability and readability. 

// Example of using prepared statements in PHP to execute an SQL query
$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($id, $name);

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

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

Keywords

SQL injection PDO prepared statements mysqli error handling

Related Questions