Problem-PHP query duplicates record instead of update

[xtiansimon]

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.

[jurn]

Is your CMS using the "replace into" mysql syntax to do these updates?

If your unique/pk for the table in question is different, you may get different results.

Jurn

[xtiansimon]

Is your CMS using the "replace into" mysql syntax to do these updates?

Nope. Here's a snippit:

$ra_success = mysql_query("
     UPDATE 
          my_items 
     SET 
          hr_element = '$hr_element', 
          item = '$item', 
          item_desc = '$item_desc', 
          ref_cat_id = '$category_id' 
     WHERE id = '$id'", 
     $connectID) 
     or die ("ERROR_77 - Unable to Update record.".error_get_last().mysql_error($connectID)); 

If your unique/pk for the table in question is different, you may get different results.

I'm not familiar with the "pk" term. my connection id?

Could this be effected by php.ini settings, or .htaccess, or by cPanel?

[r937]

I'm not familiar with the "pk" term.

primary key

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

[xtiansimon]

r937? Is that a Porsche model? haha

The id var corresponding to the primary key is maintained in the URL like this:
http://208.xx.xx.xxx/justtemp/test_s...p?modify_id=56

and therefore accessed using this,
$id = isset($_GET['modify_id']) ? $_GET['modify_id'] : NULL;

before a PHP controller code block directs the "flow" to the query I quoted earlier.

[r937]

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

[guido2004]

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.

[xtiansimon]

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.

If your code does an insert as well, it must execute another (INSERT) query.

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!

Thanks for all you help.
Chris