I'm still pretty new at coding in PHP.
Having trouble updating multiple rows in a while loop.
The select statement successfully returns multiple rows ($num = 4).
The update statement in the while loop is only successful on the first update exedcuted.
All $id variables have valid values and have corresponding rows on the table that I want to update.
Do I have to commit after each update or do I have to close after my select
and connect each time I plan to update and close after each update?
Help greatly appreciated...
Jim P.
// set up database connection
$dbhost = "localhost";
$dbuser = "xxx";
$dbpass = "xxx";
$db = "xxxx";
mysql_select_db($db) or die("ERROR DB:".mysql_error());
// format query
$sql = "SELECT\n"
. "`id`,`DaysToReview`,\n"
. "`DaysPastReview`,\n"
. "`reviewDueDate`,\n"
. "`assignedDays`,\n"
. "`assignedDate`,\n"
. "`Msg`,\n"
. "`name`,\n"
. "`role`,\n"
. "`EMail`\n"
. " FROM `notification`\n"
. " WHERE `emaildate` is null \n"
. " ORDER BY `Email`, `priority` ";
$result=mysql_query($sql);
$num=mysql_numrows($result);
if ($num == 0)
{
exit();
}
$i=0;
while ($i < $num)
{
$id=mysql_result($result,$i,"id");
......
......
// Update each notification column (emailDate) that was returned in the with current_timestamp as you are processing the list.
//mysql_connect($dbhost,$dbuser,$dbpass) or die("ERROR:".mysql_error());
$sql = "UPDATE `notification` SET `emailDate` = current_timestamp WHERE\n"
. "`id`= ".$id;
$update_result=mysql_query($sql);
$i++;
}
mysql_close();