What is the best practice for storing and updating individual user points in a MySQL database using PHP?

When storing and updating individual user points in a MySQL database using PHP, it is best practice to use prepared statements to prevent SQL injection attacks and ensure data integrity. Additionally, it is recommended to use transactions to group multiple SQL queries into a single unit of work to maintain consistency in the database. 

<?php
// Establish database connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

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

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

// Prepare SQL statement with placeholders
$stmt = $conn->prepare("UPDATE users SET points = points + ? WHERE user_id = ?");

// Bind parameters to placeholders
$stmt->bind_param("ii", $pointsToAdd, $userId);

// Set parameters and execute query within a transaction
$conn->begin_transaction();
$pointsToAdd = 10;
$userId = 1;
$stmt->execute();
$conn->commit();

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

Keywords

PHP MySQL database user points updating

Related Questions