SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem-PHP query duplicates record instead of update

    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.
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    London
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by jurn View Post
    Is your CMS using the "replace into" mysql syntax to do these updates?
    Nope. Here's a snippit:
    PHP Code:
    $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?
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xtiansimon View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  8. #8
    SitePoint Zealot
    Join Date
    Oct 2009
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Quote Originally Posted by guido2004 View Post
    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
    Dinopad R40 - F15/XP
    APACHE 2
    MYSQL 5
    PHP 5


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •