How can I make this sql request work in my php?

Question

I'm trying to make a tracking system on my website that's very basic, with just the amount of people present recorded.

Unfortunately, the code below doesn't work. I checked the error logs in my server and basically here's the issue : After the first execute there are no entities found, even though there is already an entry with that value, and so the code goes straight to the "else" and then crashes because there is already an entry with that primary key. Can someone help me find why it doesn't find the entity on the first execute?

Here is the code :

$q = "SELECT date, amount FROM tracking WHERE date = ?";
$req = $bdd->prepare($q);
$req->execute(date("Y-m-d"));
$results = $req->fetchAll();
if (count($results) != 0){
    $results["amount"] = $results["amount"] + 1;
    $track = $bdd->prepare("UPDATE tracking SET amount = ? WHERE DATE(date) = ?");
    $track->execute(array($results["amount"], date("Y-m-d")));
    exit;
}
else{
    $q = 'INSERT INTO tracking (date, amount) VALUES (:val1, :val2)';
    $req = $bdd->prepare($q);
    $req->execute(
        [
            "val1" => date(Y-m-d),
            "val2" => 1,
        ]
    );
}

Thanks

either use [bindParam](https://www.php.net/manual/en/pdostatement.bindparam.php) to bind the parameter, or pass an array in the [execute](https://www.php.net/manual/en/pdostatement.bindparam.php), `bindParam` is better way to execute queries with pdo. — Sonu Bamniya, Apr 23 '20 at 10:17
It may be worth looking at `INSERT... ON DUPLICATE KEY UPDATE...` (https://stackoverflow.com/questions/7618056/can-i-use-on-duplicate-key-update-with-an-insert-query-using-the-set-option) to save the having to check first. — Nigel Ren, Apr 23 '20 at 10:22

O. Jones · Accepted Answer · 2020-04-23T10:26:22.017

It looks like your tracking table must only have one row per date. There's a way to handle that directly in MySQL's query language.

First, make your date column the primary key of your table, or create a unique index on it. You create the unique index like this.

CREATE UNIQUE INDEX trackdate ON tracking(date);

Then use this single query to do your insertion / update.

INSERT INTO tracking (date, amount) VALUES (CURDATE(), 1)
   ON DUPLICATE KEY UPDATE amount = amount + 1;

Each time you run this query it will either insert the necessary row, or increment the amount column. And it does it "atomically," meaning that if two different php program instances try to do it concurrently, it won't get confused.

How can I make this sql request work in my php?

1 Answers1