Page 1 of 1
MySQL table not updating?
Posted: Thu Mar 27, 2008 9:44 am
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...
Re: MySQL table not updating?
Posted: Thu Mar 27, 2008 11:33 am
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 />";
}
}
Re: MySQL table not updating?
Posted: Thu Mar 27, 2008 2:17 pm
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!
Re: MySQL table not updating?
Posted: Thu Mar 27, 2008 9:26 pm
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.
Re: MySQL table not updating?
Posted: Fri Mar 28, 2008 4:57 am
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 />";
}
}