MySQL table not updating?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
csdavis0906
Forum Newbie
Posts: 17
Joined: Thu Mar 27, 2008 9:36 am

MySQL table not updating?

Post by csdavis0906 »

I am trying to modify a table with the code below:

Code: Select all

 
$result = mysql_query("SELECT id, source, title FROM title");
 
$newID = 0;   // initialize var to replace id
 
while($row = mysql_fetch_array($result)) {
    
    $newID++;  // increment id
    
    $insert = mysql_query("UPDATE title SET id = '$newID'") or die(mysql_error());
    
    if ($insert) {
       echo $row['id'] . " " . $row['title'];
       echo "<br />";
    }
}
 
My objective is to update the field 'id' with a numeric value simulating autoincrement. The result I keep getting is that every id field has the same value which is equal to the total number of records - or the last value of newID. Thanking you in advance for any assistance...
User avatar
N1gel
Forum Commoner
Posts: 95
Joined: Sun Apr 30, 2006 12:01 pm

Re: MySQL table not updating?

Post by N1gel »

I don't know what your trying to do but.

Your UPDATE query will update evry row in the table. So each time your are running the query you are updating every ID in the table with your newID, use a where clause.

Code: Select all

 
 $result = mysql_query("SELECT id, source, title FROM title");
  
 $newID = 0;   // initialize var to replace id
  
 while($row = mysql_fetch_array($result)) 
 {
    
     $newID++;  // increment id
    
     $insert = mysql_query("UPDATE title SET id = '$newID' where id=".$row['id']) or die(mysql_error());
    
     if ($insert) 
    {
        echo "Old ID : ".$row['id'] . " New ID : $newID Title: ".$row['title'];
        echo "<br />";
     }
 }
 
csdavis0906
Forum Newbie
Posts: 17
Joined: Thu Mar 27, 2008 9:36 am

Re: MySQL table not updating?

Post by csdavis0906 »

The 'WHERE' clause did not work. What I'm trying to do is loop through the table - one record at a time - replacing the 'id' field with a variable that increments with each UPDATE so that the 'id' field contains a number that simulate the autoincrement property of a MySQL field. Hope this explains my intention more clearly. Thanks again!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL table not updating?

Post by califdon »

You must, of course, have a WHERE clause, otherwise it will update all records, as you have already observed. The problem you face is identifying to MySQL exactly which record to update. There must be a unique identifier in the existing records so that you can retrieve it from $row[] and use it in the WHERE clause of your UPDATE query.
User avatar
N1gel
Forum Commoner
Posts: 95
Joined: Sun Apr 30, 2006 12:01 pm

Re: MySQL table not updating?

Post by N1gel »

Sorry that won't work if your id is not unique. You need a Primary Key in your table so each record has a unique identifier.

You could try this, it is using source and title as a composite primary key.

Code: Select all

 
  $result = mysql_query("SELECT id, source, title FROM title");
  
  $newID = 0;   // initialize var to replace id
  
  while($row = mysql_fetch_array($result))
  {
    
      $newID++;  // increment id
    
      $insert = mysql_query("UPDATE title SET id = '$newID' where id=".$row['id']." and source=".$row['source']." and title=".$row['title']) or die(mysql_error());
    
      if ($insert)
     {
         echo "Old ID : ".$row['id'] . " New ID : $newID Title: ".$row['title'];
         echo "<br />";
      }
  }
 
Post Reply