Mysqli prepared statements and REPLACE INTO
I need the following code:
http://www.nomorepasting.com/getpaste.php?pasteid=22987
If the PHPSESSID is not already in the table, the REPLACE INTO query works very well, however, if the PHPSESSID exists, the call to execute succeeds, but sqlstate is set to "HY000", which is not very useful, and $ _mysqli_session_write-> errno and $ _mysqli_session_write error -> are empty and the data column is not updated.
I'm sure the problem is in my script somewhere, since manually executing REPLACE INTO from mysql works fine, regardless of whether the PHPSESSID is in the table.
source to share
So, as it turns out, there are other problems using REPLACE that I was not aware of:
Bug # 10795: REPLACE redistributes new AUTO_INCREMENT (which, according to the comments, is not actually a bug, but "expected" behavior)
As a result, my id field keeps increasing, so the best solution is to use something line by line:
INSERT INTO session (phpsessid, data) VALUES ('{$ id}', '{$ data}') ON DUPLICATE KEY UPDATE data = '{$ data}'
It also prevents foreign key constraints from being violated and potential data integrity issues.
source to share
Why are you trying to do your preparation in an open session function? I don't believe that the write function is called more than once during a session, so preparing it in opening doesn't do much for you, you can also do that in your recording session.
Anyway, I believe you need to skip the spaces after the table name and before the list of columns. Without the spaces, I believe mysql will act as if you were trying to call a non-existent function called session ().
REPLACE INTO session (phpsessid, data) VALUES(?, ?)
MySQL sees no difference between 'COUNT ()' and 'COUNT ()'
Interestingly, when I run below in mysql CLI, I seem to get a different result.
mysql> select count (*);
ERROR 1064 (42000): 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 '*)' at line 1
mysql> select count(*);
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
source to share
"REPLACE INTO" executes 2 queries: first "DELETE", then "INSERT INTO". (So the new auto_increment is "By Design")
I also use "REPLACE INTO" for my database sessions, but I am using MySQL-> query () in combination with MySQLI-> real_escape_string () instead of MySQLi-> prepare ()
source to share