I’ve basically copied the code from an already active page which has worked for a long time. It’s only now I need to run the code using the javascript modal windows with AJAX.
I’ll double check everything again and run this PHP snippet on its own.
Yes just for viewing purposes.
The include file is basic db connect username password etc.
I’ve just download the error_log file and searching to see if I can spopt the issue.
Report back shortly once I have further information
PHP Parse error: Invalid numeric literal in /includes/update-photo.inc.php on line 11
Line 11 $photo_id = 01031901;
Reading this could be something to do with PHP 7
I’ll carry on checking.
Let me know if this is something familiar to yourself?
update
Another warning
PHP Warning: include_once(): https:// wrapper is disabled in the server configuration by allow_url_include=0 in includes/update-photo.inc.php on line 5
Something like the below @droopsnoot - not tested yet?
I also now have the photo_id coming from the AJAX POST $photo_id = $_POST['imgVal'];
require_once('../includes/mysqli_connect.inc.php');
$photo_id = $_POST['imgVal'];
if(isset($photo_id)) {
$stmt = $mysqli->prepare("
UPDATE photos
SET views = views + 1
WHERE photo_id = ?
");
// Should I be using s here?
$stmt->bind_param('i', $photo_id);
if (!$stmt) {
throw new Exception($conn->error, $conn->errno);
}
}
Updated AJAX
I wasn’t quite sure how to add the data to the $.post so used $.ajax instead.
Think for a moment about the contents of $stmt after you have run the secondprepare() 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 photo_id and 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:
I think it is possible with some sort of JOIN, though in this instance I don’t want to make things more complicated, right now anyhow
I have a FK and other indexes between the two tables which associates the id to the photo_id so everything is referenced and recorded to the same photo_id.
You might be onto something
Yes this makes sense good catch! I’ll maybe update my code to reflect this.
I only introduced the second hits table well after the photos table was created. I understand not the best design, works as needed for now without a full refactor.
The idea was to query the hits table based on dates - example most viewed this month. The photos table view count is more of a total for each photo for quick referencing.
Yes but only within a certain date. The photos table had accumulated many views before the HITS table was created. Though nowadays it’s used just more for a reference.
I only started reading about transaction last night
Now I understand the process I understand what you mean. I’ll look into having this setup for accuracy and nice to know for future work.
I might even phase out the photos view count eventually.
Thanks for the recap, as mentioned in a previous post. I think this was the reason behind me using VARCHAR in the DB. Certainly make a note of this.
Sorry, I’m not familiar with mysqli at all - I started learning with the old-style mysql calls, then came on here and saw they were out of date so switched directly to PDO.
Or perhaps a trigger. Not something I’ve tried personally, but I’ve read bits about them. Maybe something a bit like this
DELIMITER $$
CREATE TRIGGER after_hits_insert
AFTER INSERT
ON hits FOR EACH ROW
BEGIN
UPDATE photos
SET views = views + 1
WHERE photo_id = new.photo_id
END$$
DELIMITER ;
I think this would mean you only need to run your second query to insert the new row in hits.
)
