I’m using MySQL v5.1. The trouble is when I update a record through a CMS-type set of files my record is duplicated in the database (added to the end of the table). It is not being updated as the PHP’s code is written.
This only occurs on my remote shared host. This is a new problem at my shared host. This page was working correctly before at the remote host. Recently, I requested mod_security to be turned off for my domain. After this change the trouble occurred. The site is not live.
Also, I downloaded the remote site to review the files. Using Linux’s diff, I found no difference in the core files for this process.
Is there a default behavior I am unaware that adds a record to the end of a table despite the actual commands of the query? Is there any other explanation?
Chris
PS. I tested another table in the database on the remote host with a test script. The update syntax was correctly implemented–no problem.
Guido, you got it. I did some echos
and found it. I eliminated the forwards from
the controller script and stayed on the
form
page until I was able to find the lost id var.
The only change was the addition.
This effectively added a new row.
r937, you are right about it not being MySQL.
This was an early bit of this page I made
from Yank’s book. Strangely the error never
materialized in earlier versions. Only now
did it show up–many CSS edits and forms
later!
unless you want to update every row in the table, a WHERE clause in the UPDATE statement is necessary
you could update multiple rows at once with judicious choice of column condition
e.g. UPDATE items SET price = price * 0.9 WHERE vendor = ‘discontinued’
but in order to update a single row only, you need to be able to identify a single row, and the best way to do that is by specifying a value for the primary key of the table
e.g. UPDATE items SET price = 9.37 WHERE id = 456
and there would be only one row for each id, since id would be the primary key of the table
as for your duplication after the UPDATE where a row is appended to the table, look to your php for the source of that error
Trace the flow putting some echo’s here and there. Echo out the value of all queries in your script (that way not only can you check if they are correct, but you’ll also see which ones are executed). And since you put $id between quotes in your query, pass it through mysql_real_escape_string before using it in a query.
As Rudy says, the query you posted here only does an update (IF it finds rows that fulfill the where conditions). If your code does an insert as well, it must execute another (INSERT) query.
well, all i can say is that if you’re getting a row inserted at the end of the table instead of a row being updated, it’s a php problem, not a mysql problem