Query Issue

[zinja]

Why won't this work? I am trying to send a message for each record that is close to expiration (based on expiration date). When I run the query on the DB, I get the correct results (2 records), but when this is executed in the script, I get two messages sent for the same record. It is only getting the title, mls, date_expired for the first record that is returned in the query instead of having two distinct messages sent.

  $sql = 'SELECT id FROM ' . PROPERTIES_TABLE . ' WHERE userid = 28 AND active = 1 AND ( ( TO_DAYS(NOW()) - TO_DAYS(date_expired) ) <= 10)';
  $r = $db->query($sql) or error ('Critical Error', mysql_error ());

  while ($f = $db->fetcharray($r))
   {
    $sql = 'SELECT * FROM ' . PROPERTIES_TABLE . ' WHERE id = ' . $f['id'];
    $re = $db->query($sql) or error ('Critical Error', mysql_error ());
    $fe = $db->fetcharray($re);

    $sql = 'SELECT * FROM ' . USERS_TABLE . ' WHERE id = ' . $fe['userid'];
    $ru = $db->query($sql) or error ('Critical Error', mysql_error ());
    $fu = $db->fetcharray($ru);
     
    // Start a new email
    $mailout = new Mailer; 
    // From
    $mailout->from($conf['general_e_mail'], $conf['general_e_mail_name']); 
    // To
    $mailout->add_recipient($fu['email']);
    // Subject

    $mailout->subject($lang['Listing_Expiration_Subject']);

    $lang['Listing_Expiration_Email'] = str_replace('{name}', $fu['first_name'] . ' ' . $fu['last_name'] , $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{listing}', $fe['title'], $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{mls}', $fe['mls'], $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{date}', printdate($fe['date_expired']), $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{website}', $conf['website_name'], $lang['Listing_Expiration_Email']);

    $mailout->message($lang['Listing_Expiration_Email']);

    $mailout->send();

   }

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[zinja]

I also tried the following, but get the same results (identical emails sending multiple times instead of unique emails).

 $sql = 'SELECT * FROM ' . PROPERTIES_TABLE . ' WHERE userid = 34 AND active = 1 AND ((TO_DAYS(date_expired )-TO_DAYS(NOW()))<=10 )';

  $r = $db->query($sql) or error ('Critical Error', mysql_error ());

  while ($f = $db->fetcharray($r))
   {
    $sql3 = 'SELECT * FROM ' . USERS_TABLE . ' WHERE id = ' . $f['userid'];
    $ru = $db->query($sql3) or error ('Critical Error', mysql_error ());
    $fu = $db->fetcharray($ru);
     
    // Start a new email
    $mailout = new Mailer; 
    // From
    $mailout->from($conf['general_e_mail'], $conf['general_e_mail_name']); 
    // To
    $mailout->add_recipient($fu['email']);
    // Subject

    $mailout->subject($lang['Listing_Expiration_Subject']);

    $lang['Listing_Expiration_Email'] = str_replace('{name}', $fu['first_name'] . ' ' . $fu['last_name'] , $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{listing}', $f['title'], $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{mls}', $f['mls'], $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{date}', printdate($f['date_expired']), $lang['Listing_Expiration_Email']);
    $lang['Listing_Expiration_Email'] = str_replace('{website}', $conf['website_name'], $lang['Listing_Expiration_Email']);

    $mailout->message($lang['Listing_Expiration_Email']);

    $mailout->send();
   }

Please help, I know this must be something simple and that I am probably not stepping through the records properly, but I don't know what it is that I am doing wrong.



LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[Itshim]

It appears as if you are cycling through the results correctly. I know you said:

When I run the query on the DB, I get the correct results (2 records)

but...have you verified that the two records returned from the query are the ones you expect, and are not duplicate records.

 

[zinja]

Yes, they are the correct records (not duplicate). It is really weird, it seems to defy logic that it won't execute in PHP correctly.

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.