Query problem with mysql ver. 8

MySQL database
#1

After upgrading to MySQL 8 some of my queries went all over the place. Some i’ve managed to fix but there is 1 that is giving me a very hard time and i don’t know how or what to do to find out what is wrong.
The query gives over and over again the error " Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near WHERE id = xxxx "
And the query is

mysqli_query($GLOBALS["___mysqli_ston"], “UPDATE articles SET " . join(”,", $updateset) . " WHERE id = " . sqlesc($articleid)) or sqlerr(FILE, LINE);

Since its a join thing i’ve looked at the codes that are required for this but cant find anything out of place. I can only assume that the data it tries to update is corrupted somehow.

#2

You need to use Prepared Statements.

#3

Ohh…yikes… I understand

#4

A few other things you should do too -

  1. Switch to the much simpler PDO extension. This is even more important when using prepared queries since the mysqli programming interface for prepared queries is completely different from the non-prepared query programming interface, which has you learning two completely different sets of php statements. The PDO extension uses the same set of php statements for both a prepared and non-prepared query. so, you might as well spend the time learning one simpler set of php statements.
  2. Build the sql query statement in a php variable, so that the sql query syntax is separated as much as possible from the php code, and so that you can echo it for debugging purposes (this would help with the current problem.)
  3. Don’t use or anything_here() for database statement error handling. Instead, use exceptions for database error handling and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database statement errors will ‘automatically’ get displayed/logged the same as php errors.) The exception to this rule is when inserting/updating duplicate or out of range user submitted data. In this case your code would catch the exception, detect if the error number is for something your code is designed to handle, then setup an error message for the visitor telling them what was wrong with the data that they submitted. For all other error numbers, just re-throw the exception and let php handle it.
1 Like
#5

I did an echo of the full query involved into the join updateset and all the values seemed normal as usual.

#6

Since you didn’t post that, we cannot help you with what is wrong with it. All we know from the information you have provided so far is the error is near the start of the WHERE keyword.

#7

one of the query is

UPDATE snatched SET ip = ‘39.53.130.11’, port = 5419, connectable = ‘no’, uploaded = uploaded + 0, downloaded = downloaded + 0, to_go = 0, upspeed = 0, downspeed = 0, seedtime = seedtime + 1648, last_action = 1596983046, seeder = ‘yes’, agent = ‘qBittorrent/4.2.5’ , hit_and_run = ‘0’ WHERE articleid = 13979 AND userid = 60820

the other one…which is the insert

INSERT INTO snatched (articleid, userid, peer_id, ip, port, connectable, uploaded, downloaded, to_go, start_date, last_action, seeder, agent) VALUES (20296, 839, ‘-lt0D80-¹8¥Öa-šPÐ’, ‘116.202.245.62’, 56019, ‘yes’, 0, 0, 0, 1596983099, 1596983099, ‘yes’, ‘rtorrent/0.9.8/0.13.8’)

there are from different users… dont mind the different data in each of them

#8

The table name and the WHERE clause in the UPDATE query you just posted is not the same as in the first post in the thread. What are the current sql errors for the actual queries you have just posted.

#9

ohh…wait… i might just pasted something else…hehehe…my bad…ooops !

ok… so i checked the logs and i have this
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE id = 11967’
and for the same id in the echo query i have this

UPDATE articles SET WHERE id = 11967
but i also have other queries which don’t appear in the error logs meaning those are working just fine looking like
UPDATE articles SET seeders = seeders + 1,visible = ‘yes’,last_action = 1596988446 WHERE id = 20269

so meaning for that 11967 id it isnt updating anything at all…thus the lack of values

Sponsor our Newsletter | Privacy Policy | Terms of Service