Post data to two database tables

[20pictures]

I am trying to resolve a problem by posting the date an email is sent to two database tables. I have had a go (see below) but this produces errors.

original:

$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']);
					$this->mailer->setCamp($mrecord['list_id']);
					$this->mailer->Subject = "Mailing is complete for newsletter - ".  $mrecord['msg_id'];
					$this->mailer->Body    = "Mailing is complete for newsletter with subject: ".  $mrecord['subject']."<br/><br/>Sending started on ".$startdate." and ended on ".$end.".<br/>In total ".$totalmails['totalmails']." mails were sent.";
					$this->mailer->AddAddress($this->mailer->From, $this->mailer->FromName); 
					if(!$this->mailer->Send())
					{
						echo "Error sending mail to admin";

my attempt:

$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']);
					$ldatabase->execute_query("update mail_subscribers set date_sent=now() where msg_id = ". $mrecord['msg_id']);
					$this->mailer->setCamp($mrecord['list_id']);
					$this->mailer->Subject = "Mailing is complete for newsletter - ".  $mrecord['msg_id'];
					$this->mailer->Body    = "Mailing is complete for newsletter with subject: ".  $mrecord['subject']."<br/><br/>Sending started on ".$startdate." and ended on ".$end.".<br/>In total ".$totalmails['totalmails']." mails were sent.";
					$this->mailer->AddAddress($this->mailer->From, $this->mailer->FromName); 
					if(!$this->mailer->Send())
					{
						echo "Error sending mail to admin";

Any help appreciated.

[NogDog]

What is the error?

[20pictures]

The error reported by the message turned out to be a grammatical error in my tables. I corrected this.

The php now runs without reporting errors but does not send the date to both tables ...only the first one.

[Bjom]

do an echo on this:

"update mail_subscribers set date_sent=now() where msg_id = ". $mrecord['msg_id']); 

Copy and paste the output of the echo to any DB administration tool like MySQL Administrator or phpMyAdmin and see why it doesn't work....then modifiy until it does and then go back to modifiy the php code.

[20pictures]

Sorry, I got lost at the first part...?

$ldatabase->execute_query("update mailmachine_newsletter set sent='Y',date_sent=now() where msg_id = ". $mrecord['msg_id']);
$ldatabase->execute_query("update mailmachine_subscribers set date_sent=now() where msg_id = ". $mrecord['msg_id']);
echo "update mail_subscribers set date_sent=now() where msg_id = ". $mrecord['msg_id']; 

[Bjom]

echo "update mail_subscribers set date_sent=now() where msg_id = ". $mrecord['msg_id']; 

yep that's fine - what does it show you in the browser?

[20pictures]

I have managed to post the data using:

$ldatabase->execute_query("update mail_newsletter set sent='Y',date_sent=now() where msg_id = ". $mrecord['msg_id']);
$ldatabase->execute_query("update mail_sub_msgs set date_sent=now() where msg_id = ". $mrecord['msg_id']);

However, now I have trouble retrieving the date from the database.
This is what I tried (date in last column of row):

function showList(){ 
    $list_id= (int)$_REQUEST['curr_list_id']; 
    $chk= new cquery(); 
    $chk->set_query_sql("SELECT display FROM mail_customfields_lists WHERE list_id = $list_id"); 
    $chk->run_query(); 
    if($chk->num_of_rows() != 1){ 
        cbaseform::showList(true); 
    }else{ 
        $foo= $chk->get_next_hash(); 
        $e= '<tr class="rows" onmouseover="this.className=\'hilite\'" onmouseout="this.className=\'\'">
		<td valign="top" align="left" width="26px"><input TYPE="checkbox" NAME="select[]" Value="{SUB_ID}" {SELECT}></td>'; 
        foreach(unserialize($foo['display']) as $k=>$v){ 
            $e.= '<td valign="top" align="right" nowrap><a href="{FILE}?sub_id={SUB_ID}&todo=edit">{' . strtoupper($k) . '}</a></td>';
			$chk->set_query_sql("SELECT date_sent FROM mail_sub_msgs WHERE date_sent = $date_sent");  

		    '<td><a href="cmessages.php?msg_id={MSG_ID}&todo=edit">{DATE_SENT}</a></td>';
        } 
        $e.= '</tr>'; 
        parent::showList(true,$e,count(unserialize($foo['display'])) + 1); 
    } 
}

Now I get Error Message:
Notice: Undefined variable: date_sent in /usr/local/pem/vhosts/.....

I don't know how to define the variable or if that is the right solution.
Any help?

[Bjom]

you "define" a variable in PHP by assigning it a value.

$date_sent = time();

for example.

[20pictures]

...almost resolved!

I have the data posting to a table where I can now retrieve it.
However, I am using a script which only posts the 'date' to the last 'email address'.
I'm not sure how to post to an 'array' when I send the same email to multiple addresses? Any help?

$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']);
$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = ". $this->record['sub_id']);

[Bjom]

foreach?

[20pictures]

...the final hurdle.

I have altered the code as follows:

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

but it returns Error:
Query Error: UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = SELECT sub_id FROM mail_sub_msgs WHERE msg_id = 109

I need to somehow bracket the $mysub_id variable to return a value.
This I do not know how to do ..I tried (){}[]'' and""

Any help?

[Bjom]

so $mysub_id contains this"SELECT sub_id FROM mail_sub_msgs WHERE msg_id = 109" right?

it needs ()

execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = (". "$mysub_id"));

If that is the only issue it works like this.

Carefully check this. It could contain an error that prevents the above code from running.

SELECT sub_id FROM mail_sub_msgs WHERE msg_id = 109

[20pictures]

execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = (". "$mysub_id")); 

...returned the error:
[FONT="Courier New"]Parse error: syntax error, unexpected ')'[/FONT]
...so I tried [] with no success &:

execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = ". "$mysub_id"); 

...returned the error:
[FONT="Courier New"]Query Error: UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = SELECT sub_id FROM mail_sub_msgs WHERE msg_id = 117[/FONT]

I checked:
[FONT="Courier New"]SELECT sub_id FROM mail_sub_msgs WHERE msg_id =[/FONT]
& it is OK...

[Bjom]

You need ().

here a concatenator and a double quote is missing (my fault, but you need to learn to identify such errors yourself read up on strings here and here)

execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = (". "$mysub_id")); 

correct:

execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = (" . $mysub_id . ")"); 

I deleted the " around the $mysub_id variable as they are not necessary in standard PHP - if your function requires them - throw them back in.

Bjom

[20pictures]

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

This seems to work fine processing one email at a time. However, if I send a group of emails the UPDATE fails. I have tried to introduce array() but seem to be placing it incorrectly.

I assume this is the solution but would appreciate advice. I know I may come across as dim-witted but this is my first week on php, which is why I am posting to a Newbie forum.

[Bjom]

you placed it ok. question is: what's inside?
check your array. how does it look like?

var_dump($mrecord);

what about the other variable $mysub? wont you have to update it as well?

[20pictures]

I tried:

$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']);
var_dump($mysub);
echo '<br>';
$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = ($mysub)");

Which gives message:
[FONT="Courier New"]array(1) { [0]=> string(58) "SELECT sub_id FROM mailmachine_sub_msgs WHERE msg_id = 232" }
Query Error: UPDATE mailmachine_subscribers SET last_sent=now() WHERE sub_id = (Array)[/FONT]

As I see it, somehow I have to get the $mysub=array() string to run & give values for a $mysub array. Any help?

[Bjom]

well a foreach after I'm sure you got it right with the array...

what is inside the $mrecord?

[20pictures]

[FONT="Courier New"]var_dump($mrecord);[/FONT]

Gives:
[FONT="Courier New"]array(15) { ["msg_id"]=> string(3) "231" ["list_id"]=> string(1) "1" ["variant"]=> string(32) "c36822b2f8684b3ba9844eea98eb233b" ["all_lists"]=> string(1) "0" ["type"]=> string(4) "html" ["subject"]=> string(3) "D15" ["date_sent"]=> string(19) "2009-07-31 03:03:22" ["msg_text"]=> string(0) "" ["msg_html"]=> string(193) "<html dir="ltr"> <head> <title></title> </head> <body> <p> </p> </body> </html>" ["sent"]=> string(1) "N" ["scheduled"]=> string(19) "2009-07-31 03:03:22" ["userid"]=> string(10) "20pictures" ["last_id"]=> string(1) "0" ["sfilter"]=> string(1) "0" ["is_quick_send"]=> string(1) "1" }[/FONT]

Which is correct. As this does not change I assume the problem is with $mysub?

[20pictures]

$mysub=array("SELECT sub_id FROM mail_sub_msgs WHERE msg_id = ". $mrecord['msg_id']);
foreach ($mysub as $value)
{
echo $value . "<br />";
}
$ldatabase->execute_query("UPDATE mail_subscribers SET last_sent=now() WHERE sub_id = ($mysub)");

Gives message:
[FONT="Courier New"]SELECT sub_id FROM mailmachine_sub_msgs WHERE msg_id = 234
Query Error: UPDATE mailmachine_subscribers SET last_sent=now() WHERE sub_id = (Array)[/FONT]