What are the best practices for connecting to a MySQL database in PHP, especially in the context of updating and retrieving counter values?

When connecting to a MySQL database in PHP to update or retrieve counter values, it is best practice to use prepared statements to prevent SQL injection attacks and ensure data integrity. Additionally, using error handling techniques such as try-catch blocks can help in identifying and resolving any database connection or query issues. 

<?php
// Database connection parameters
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

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

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

// Update counter value
$counter = 1; // Counter value to update
$sql = "UPDATE counters SET value = value + ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $counter, $id);
$id = 1; // Counter id
$stmt->execute();

// Retrieve counter value
$sql = "SELECT value FROM counters WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $id);
$id = 1; // Counter id
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();
$counter_value = $row['value'];

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

Keywords

MySQL PHP database connection updating retrieving counter values

Related Questions