Comments/Ratings for a Single Item

I thought of one more thing that could be going on, and I updated update_row to return a warning when it happens. This function now reads the row from the database and checks its values against the values you want to update, and it adds to the SQL query only those values that are different from what is already stored in the database. So, if you tried to update a comment without changing anything, it wouldn't add any values to the query. In case this happens, it will now exit early with a warning.

I successfully tried updating a couple of my comments, including one on this page, and I successfully updated the comment you referred to, though that was with a different script than you would use. So, I have not been able to repeat what you did.

Looking at the PHP error log, I see multiple errors looking like this:

Failed to prepare SQL in update_row: UPDATE `Comment` SE WHERE `CommentID` = :keyvalue

This was added to the error log by a catch clause I have in update_row().

That "SE" shouldn't be there, but there should be the word "SET" followed by a series of parameters being assigned to table columns. Since we appear to be in the same time zone, these were just over an hour and a half ago, and I was not doing any programming then.

Looking at the individual lines modifying $sql, most lines just append to it, and one uses substr to delete the last two characters. That points to one thing that happened. The $sql string could have this value if it were passed an empty array. After appending nothing to "UPDATE Comment SET ", it chopped off the last two characters and appended " WHERE CommentID = :keyvalue". I added some code to update_row to catch this error before this happens, but I'm still not sure why it happened. This is about what arguments were passed in the function call and not about how the function operated.

Also, I am getting this error whenever I try to update my previous comment:

Attempting to update comment.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE `CommentID` = ?' at line 1

Update of Comment with ID of 46749 failed.

The Review New Submissions page seems to be skewed towards new submissions now, rather than submissions that have new changes ready for review. This makes approving pages much harder, as you will only see the newest pages rather than the ones that have changes ready for review. Perhaps if the page is sorted by the timestamp of the last action taken on each article this can be rectified without having to worry about the unreliable modification date.

update test

Ok, but then what is the problem?

Yes, I was thinking that we no longer need the quickedit script. It was made back when I was still accessing the site through dial-up. Now that we're all using broadband, dial-up speeds are no longer a bottleneck. Besides that, I have since reduced the size of the edititem script by replacing four separate but identical drop-down lists with a single datalist that gets used by four different fields.

update test

I cannot use the item quick-edit script (as an editor) because the form does not have the new BoardRealms input (though perhaps the quick edit can be deprecated now that the author list population isn't so slow), and the database apparently won't allow it to be null. Again, I really think allowing nulls for non-game items would be best, and BoardRealms should be defaulted to 1 in any forms.

There was a syntax error, which I've now corrected. As a test, I updated your profile and changed it back.

No improvement. Now it just goes to an empty white page (without updating anything in the database)

I have now modified it to use the update_row function, which should fix things.

modifyperson.php doesn't seem to work at all

I had to make a change to a column name in the script, and I still couldn't leave the board size fields blank, but I successfully made the submission. I again got a 401 inside the submission page, but this time it seems to have been because IsMemberSubmitted was 0; I modified the script to set that flag. There's another column IsLink which is set as zero (not being set in the script; both here and in another older ML page of mine, so it's been like this for some time), but doesn't appear to have an adverse effect.

Should this script be calling update_row (or some other indexing func) instead of its ad hoc query?

Yes, these functions are more secure and error-proof than ad hoc code may be, and it's best to not reinvent the wheel each time we need to access the database. So, I have now rewritten the script to use update_row and replace_row. I have left the other code in comments in case I made a mistake and need to reference it.

What I've done for now is provide an individual default for each parameter instead of giving them all an empty string as the default. I'll do some more work later.

@Fergus, thanks! I had emptied boardrows in my submission.

I thought in the past I could put board rows/columns/levels/cells as empty and they'd populate as nulls. Either I'm wrong about that, or something has changed. I think being able to leave those fields blank for non-game pages is the most natural.

Should this script be calling update_row (or some other indexing func) instead of its ad hoc query?

7:44 am CST would be 13:44 UTC, and I got this for that time:

[23-Jan-2023 13:44:07 UTC] PHP Fatal error:  Uncaught PDOException: SQLSTATE[22007]: 
Invalid datetime format: 1366 Incorrect integer value: '' for column
`chessvariants`.`Item`.`BoardRows` at row 1 in /home/chessvariants/public_html/index/membersubmission2.php:385
Stack trace:
#0 /home/chessvariants/public_html/index/membersubmission2.php(385): PDOStatement->execute()
#1 {main}
  thrown in /home/chessvariants/public_html/index/membersubmission2.php on line 385

Instead of using replace, I should probably use insert if the row doesn't exist and update if it does, as I have already fixed this problem for the update_row function. Since BoardRows is not even a datetime field, this is a weird message, but the underlying problem is that replace is getting an empty string instead of whatever it is expecting.

I just tried again, 7:44am CST, itemid MLlichess. I had added MSlichess before realizing I had forgotten to switch to a link item, so that's likely the problem, but I removed the Item and IndexEntry entries (and the MemberSubmissions never got created), and any other locations shouldn't affect the Replace into Item query?

Also, as someone else reported recently: I can't edit another test entry (MLtestlink) to have empty content, but without empty content I cannot use the forms to delete it. Which of these protections do we want to keep?

I'm not sure if this is due to the server move, but member-submission of External Link pages doesn't seem to be working.

As a preliminary test, I made a link page to PyChess, and it worked. So, I have not been able to repeat the problem you encountered. If you have any further problem with it, let me know the time and details do that I can check the error log.

I'll look into it tomorrow.

I'm not sure if this is due to the server move, but member-submission of External Link pages doesn't seem to be working. I started doing some debugging, and it seems to die in membersubmissions2.php when trying to execute the replaceitem query (but the error handling there doesn't trigger either), what is currently line 385.

For some reason, when using an image for the board in Game Courier, the file labels are positioned higher than they should be.

This is now fixed. In Chu Shogi, which you gave as an example, the file labels are now positioned at the right level.

For some reason, when using an image for the board in Game Courier, the file labels are positioned higher than they should be. Here's an example from Chu Shogi.

The differences between page revisions was handled by code I copied from another website. Fortunately, with the help of the PHP error log, I was able to fix it.

The diffs between page revisions don't seem to work anymore, possibly due to the server move. I get a short part of the introduction and no other text, and generally no highlighted diffs.