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!
$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...
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.
$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 />";
}
}
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!
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.
$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 />";
}
}