[RESOLVED] Updating mutiple times in a while loop

[jkpalmer]

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();

[bradgrafelman]

First and foremost: Stop Using the MySQL Extension! Also, when posting PHP code, please use the board's [noparse]

..

[/noparse] bbocode tags as they make your code much easier to read and analyze.

Next... are you doing other processing inside the while loop that utilizes the other columns you've selected? Assuming yes, note that it might be more efficient to simply collect the ID values (e.g. in an array) and later execute a single UPDATE query outside the loop, e.g.:

$sql = 'UPDATE ... WHERE id IN (' . implode(',', $array_of_ids) . ')';

jkpalmer;11039179 wrote:

The update statement in the while loop is only successful on the first update exedcuted.

And how do you know that? You should always be checking to see if the MySQL server indicates your query was executed successfully and, if not, output/log some helpful debugging information (e.g. the SQL query string itself, the error message returned by the MySQL server, etc.).

jkpalmer;11039179 wrote:

Do I have to commit after each update or do I have to close after my select

That depends on what you mean by "commit" and "close" ... ?

jkpalmer;11039179 wrote:

connect each time I plan to update and close after each update?

Most definitely not (that would be highly inefficient).

[jkpalmer]

Brad,

Thanks for your feedback and suggestions will be followed. Am in the process of "shifting" to mysqli but this was inherited code so it's a slow process. Trying to maintain a legacy system, with limited knowledge of the language. It's a cost thing, and the business has asked me to make some minor changes. Not so minor I guess.

To respond to your question - And how do you know that? This script is running from a command line/bat file and I stuck a few echos in for debugging and also saw that only one of the four rows that were originally selected had the update, which caused my to pose the question.

I'm going to migrate this code to mysqli and see what happens, since it is somewhat "separate" from the application.

[jkpalmer]

OK - I've updated my code to mysqli. Below -

I am still running into a problem on the second row, the update fails.

Building an array of the id's returned in my select statement is my next step and then I'll

<?php

// CONNECT TO THE DATABASE
	$DB_NAME = 'businessprocessmanager';
	$DB_HOST = 'localhost';
	$DB_USER = 'jim';
	$DB_PASS = 'bddbdd';

$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);

if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit();
}

// A QUICK QUERY ON A Notification TABLE

$query = "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` ";

//$query = "SELECT * FROM `users` WHERE `status`='bonkers'";

$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

//$prev_email=mysqli_result($result,$i,"Email");
//$email=$prev_email;

//$row = $result->fetch_array();

//$prev_email = "";
//$email = $prev_email;
//echo $prev_email . " " . $email;

// GOING THROUGH THE DATA
	if($result->num_rows > 0) {
		while($row = $result->fetch_assoc()) 
		{
			print_r( $row );
			$id = $row['id'];
			//echo "Row is - ".$row."Id = ".$id."\n";
			$days_to_review=$row['DaysToReview'];
			$days_past_review=$row['DaysPastReview'];
			$review_due_date=$row['reviewDueDate'];
			$assigned_days=$row['assignedDays'];
			$assigned_date=$row['assignedDate'];
			$msg=$row['Msg'];
			$name=$row['name'];
			$role=$row['role'];
			$email=$row['EMail'];
			echo "Email " . $row['EMail'];

    // Update each notification column (emailDate) with current_timestamp as you are processing the list.

	$sql = "UPDATE `notification` SET `emailDate` = current_timestamp WHERE"
	 . "`id`= ".$id."\n";

	var_dump($sql);

	$result = $mysqli->query($sql) or die($mysqli->error.__LINE__);

	var_dump($result);

	$prev_email = $email;

	}
}
else {
	echo 'NO RESULTS';	
}

// CLOSE CONNECTION
	mysqli_close($mysqli);
?>	

The output of my code is as follows:

Array
(
[id] => 1
[DaysToReview] => 0
[DaysPastReview] => 41
[reviewDueDate] => 2014-01-31 00:00:00
[assignedDays] => 0
[assignedDate] =>
[Msg] => Review Date Expired
[name] => Electronic Orders
[role] => MANAGER
[EMail] => [e-mail]
)
Email [e-mail](71) "UPDATE notification SET emailDate = cur
rent_timestamp WHEREid= 1
"
bool(true)
PHP Fatal error: Call to a member function fetch_assoc() on a non-object in H:\
PHP_Scripts\MYSQLI_NotifyA.php on line 47
PHP Stack trace:
PHP 1. {main}() H:\PHP_Scripts\MYSQLI_NotifyA.php:0

Fatal error: Call to a member function fetch_assoc() on a non-object in H:\PHP_S
cripts\MYSQLI_NotifyA.php on line 47

Call Stack:
0.0004 647712 1. {main}() H:\PHP_Scripts\MYSQLI_NotifyA.php:0

[bradgrafelman]

In your new code, you're overwriting $result inside the loop.