A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2008 yearmysqli prepared statements and REPLACE INTO - page 1
User InfoPosts
mysqli prepared statements and REPLACE INTO#1
I have to following code:

http://www.nomorepasting.com/getpaste.php?pasteid=22987

If PHPSESSID is not already in the table the REPLACE INTO query works just fine, however if PHPSESSID exists the call to execute succeeds but sqlstate is set to 'HY000' which isn't very helpful and $_mysqli_session_write->errno and $_mysqli_session_write->error are both empty and the data column doesn't update.

I am fairly certain that the problem is in my script somewhere, as manually executing the REPLACE INTO from mysql works fine regardless of whether of not the PHPSESSID is in the table.

posted date: 2008-12-23 19:57:00


Re: mysqli prepared statements and REPLACE INTO#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2008-12-23 19:57:01


Re: mysqli prepared statements and REPLACE INTO#3
Why are you trying to doing your prepare in the session open function? I don't believe the write function is called more then once during a session, so preparing it in the open doesn't do much for you, you might as well do that in your session write.Anyway I believe you need some whitespace after the table name, and before the column list. Without the whitespace I believe mysql would act as if you where trying to call the non-existent function named session().REPLACE INTO session (phpsessid, data) VALUES(?, ?)MySQL sees no difference between 'COUNT ()' and 'COUNT()'Interesting, when I run the below in the 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 1mysql> select count(*);+----------+| count(*) |+----------+| 1 | +----------+1 row in set (0.00 sec)

posted date: 2008-12-24 01:25:00


Re: mysqli prepared statements and REPLACE INTO#4
MySQL sees no difference between 'COUNT ()' and 'COUNT()' so if whitespace were the problem there would be no way to fix it. Also 'REPLACE' works when performing 'INSERT' but fails when performing 'UPDATE' so if this were the case the query would always fail.

posted date: 2008-12-24 08:30:00


Re: mysqli prepared statements and REPLACE INTO#5
Hmmm... it appears you are correct, unfortunately that still does not seem to solve the original problem.

posted date: 2008-12-28 09:13:00


Re: mysqli prepared statements and REPLACE INTO#6
So as it turns out there are other issues with using REPLACE that I was not aware of:Bug #10795: REPLACE reallocates new AUTO_INCREMENT (Which according to the comments is not actually a bug but the 'expected' behaviour)As a result my id field keeps getting incremented so the better solution is to use something along the lines of:INSERT INTO session(phpsessid, data) VALUES('{$id}', '{$data}') ON DUPLICATE KEY UPDATE data='{$data}'This also prevents any foreign key constraints from breaking and potential causing data integrity problems.

posted date: 2008-12-31 15:08:00


Re: mysqli prepared statements and REPLACE INTO#7
"REPLACE INTO" executes 2 queries: first a "DELETE" then an "INSERT INTO".(So a new auto_increment is "By Design")I'm also using the "REPLACE INTO" for my database sessions, but i'm using the MySQLi->query() in combination with MySQLI->real_escape_string() in stead of a MySQLi->prepare()

posted date: 2009-01-11 04:44:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.