Updating Multiple Rows in a Database

[20pictures]

I am working on some php that will post the date an email is sent to a database. Each recipient of that email must have their date updated.
The first string should select an array of sub_id (subscriber id) from a data table for messages.

This is what I have so far:


$mysub=array("SELECT sub_id FROM mail_sub_msgs WHERE msg_id = ". $mrecord['msg_id']);
foreach ($mysub as $value)
var_dump($value);
$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = ($mysub[0])");


Which gives message:
string(58) "SELECT sub_id FROM mail_sub_msgs WHERE msg_id = 244" }

This only works for one email address but if there is more than one, nothing is updated!

I don't see why it is not printing a list of values for multiple sub_id.
Is there not something I have to do to run the string & SELECT the values before the var_dump()...

Any help is appreciated, this is my first week with php.

[JShor]

$myvar[0] will only return one result. Use $value instead.



$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = ($value)");

[20pictures]

...Ive just tried your recommendation but it has not made a change.

I can update one at a time but if I send two emails no updates are made.

var_dump message:
string(58) "SELECT sub_id FROM mailmachine_sub_msgs WHERE msg_id = 247"

[20pictures]

Got it, thanks!
...needed an IN...



$end=date("F j, Y, g:i a");
$ldatabase->execute_query("UPDATE mail_newsletter SET sent='Y',date_sent=now() WHERE msg_id = ". $mrecord['msg_id']);
$mysub=array("SELECT sub_id FROM mail_sub_msgs WHERE msg_id = ". $mrecord['msg_id']);
foreach ($mysub as $value)
$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id IN ($value)");

[boogyman]

$mysub=array("SELECT sub_id FROM mail_sub_msgs WHERE msg_id = ". $mrecord['msg_id']);

I am curious as to why you are assigning this return to an array?
If the database was set up correctly, the return will be either 1 value or an empty set.

Try

$mysub= sprintf("SELECT sub_id FROM mail_sub_msgs WHERE msg_id = %d", $mrecord['msg_id']);

$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=NOW() WHERE sub_id ="{$value}");


Note: %d specifies an integer. For more information, review sprintf (http://php.net/sprintf)


There is no reason you should be assigning the

[20pictures]

...but surely if I send, say 10 emails, then $mysub is an array of 10 sub_id?

If I limit it to one value, only one sub_id gets updated ...which was the problem I had in the first place.
Maybe you are suggesting I arrange some sort of loop but that just seems more complicated?