Can my code be made more efficient?

I am wondering whether my code can be made more efficient. I am counting the number of times a link is pressed each day.

I have a simple SQLite database/table:

CREATE TABLE referals (
  idate INTEGER PRIMARY KEY,
  count INTEGER
)

and my code:

$db = new PDO('sqlite:adrefer.sqlite');
$date = intdiv(time(), 86400); // 60 * 60 * 24
$sql = 'SELECT count(*) FROM referals WHERE idate = ?;';
$stmt = $db->prepare($sql);
$stmt->execute([$date]);
$count = $stmt->fetchColumn();
if ($count > 0) {
  $sql = 'UPDATE referals SET count = count + 1 WHERE idate = ?;';
  $db->prepare($sql)->execute([$date]);
} else {
  $sql = 'INSERT INTO referals VALUES (?, 1);';
  $db->prepare($sql)->execute([$date]);
}

I was kinda hoping it might be possible to have a single SQL query each time the count is updated, but maybe I’m only dreaming.

You should be able to use “ON CONFLICT” (similar to MySQL ON DUPLICATE KEY):
https://www.sqlite.org/lang_UPSERT.html

INSERT INTO referrals (idate, count)
VALUES (?, 1)
ON CONFLICT(idate) DO UPDATE SET count = count + 1;

Note. I have not tested the code.

1 Like

Ah, thanks. I’ll give that a whirl.

Very small improvement… The $count value returned could be a bool false otherwise it is true, so no need to test if greater than zero.

Thanks John. That’s not even needed with ON CONFLICT, which works fine using my misspelled table name and the WHERE clause.

1 Like

<mildly off-topic>
“Can my code be made more efficient?”
The answer is always yes, for every coder, at all times :wink:
</off-topic>

1 Like

Too true!

Okay, so I updated my code:

$db = new PDO('sqlite:adrefer.sqlite');
$date = intdiv(time(), 86400); // 60 * 60 * 24
$sql = 'INSERT INTO referals (idate, count) VALUES (?, 1)
        ON CONFLICT (idate) DO
        UPDATE SET count = count + 1 WHERE idate = ?';
$db->prepare($sql)->execute([$date, $date]);

which works fine on my localhost, and for the first time worked fine live. When it got to the next day it still worked fine on localhost, creating a new node, but on my live site, nothing was updated - no new node created and no errors reported.

Can anyone see a problem with my code?

Is your live server configured for the same timezone you are in?

They’re just an hour different and it’s not as if yesterday’s count was updated. Nothing gets updated which seems odd.

If the code is executed, and it worked initially it should still work.

This leads me to believe it did not work initially, but it looked like it due to your updating an existing table.I would create a new table and see what happen with that just to verify.

Note. I suspect you need to update the SQLite version to a newer one that supports ON CONFLICT

1 Like

I’ve just checked and my local version is 3.33.0 and my host is running 3.7.17, so yes that might be the issue. I can’t find a list of what features are supported by which versions, so I’m guessing this could be the issue.

I have deleted the database and recreated the table so many times I’m pretty sure it works for the first day.

That seems to be on this page: Release History Of SQLite

1 Like

Thanks. That’s jolly handy. Let’s hope my host agrees it’s time to upgrade.