Think for a moment about the contents of
$stmt after you have run the second
prepare() pointing to the same variable. The second overwrites the first, of course, so you’ll have to do it separately. Maybe you can do it with a combined query, my sql knowledge isn’t up to it without lots of referring to samples, and you can do that as well as I can.
In this subquery as part of your second query
SELECT id FROM photos WHERE photo_id = ?
what’s the different between
id ? Wouldn’t it be more sensible to use the same id everywhere, to identify your photo?
In the main part of that query, if you’re always going to insert
NOW() as the datestamp, why not set that to be the default for that column of your table, leave it out of the query and let the database just insert that default value? If you used a consistent
id, and set the default, all you need would be
INSERT INTO hits (photo_id) VALUES (?)
I’m not sure it’s a great idea to have two tables, one which is effectively a “transactions” file (your ‘hits’ table) and another which contains the total number of entries in ‘hits’ for each photo - you can achieve the same by querying the ‘hits’ table when you need totals. By having the two, you’re just asking for the two to drift apart. If you are going to have the two separate tables, you need to run these as part of a transaction so they can’t get out of sync.
No, I was thinking only of the PHP code. In any language I’ve used, leading zeroes are not retained in numeric variables, only in strings. So there’s no functional difference between these two lines:
$photo_id = 01031901;
$photo_id = 1031901;