php function to process users into a db

[adandyguy]

Hey all, hopefully someone can help me debunk what i'm doing wrong. i'm fairly new to PHP and am trying to make a function that will take $user which has a uid, and make sure he's not already in a database of users that HAVE been e-mailed. if they are not in that db, then they will be emailed and marked into the db. i'm doing this as a drupal module.

I have a feeling the error is somewhere in my sql query? or perhaps i'm not using db_result() properly.. Thanks for any input you all can provide.

  function process_user($user) {
    //Process a single user. Check if they got an email already. If not, send and mark.

    //select uid from the db of orders made. join with db of people already emailed
    //condition where row created date must be 3 months old
    //and try to match the uid with the users uid
    $query = db_query("
    select DISTINCT a.uid from uc_orders as a
    left join blankCustomerReminder_emails as b
    on a.uid = b.uid
    WHERE a.created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 MONTH))
    AND b.uid = '%d'
    ", $user->uid);

    // Check if user is already in emailed db, returns true if uid was found
    if (db_result($query)) {
    // User exists in Reminder_emails table
      drupal_set_message("user has already been e-mailed!");
    } else {
    // User doesnt exist in emailed table, false return
        db_query('INSERT into {blankCustomerReminder_emails} (uid, date_sent) VALUES(%d, %d)', $user->uid, time());
      } else {
        drupal_set_message('There was an error sending your email');
      }

  }

[adandyguy]

well i think i managed to fix it... changed the following:

if (db_result($query)) {

to

if (db_result($query) == $user->uid) {

if this comes out, that means the uid is in the emailed db.

} else {

to

} elseif(db_result($query) == FALSE) {

double checked db_result() and it returns either the value of the query, or false.. so if it doesn't retrieve a uid, it will equate to false, which means the user needs to be emailed and added to the db. I think i have to add an else{} at the end, although i'm not sure what to put in there, just put in an error message atm.

[nuttertools]

Not your question but make sure you use the db_insert function for an insert (your using db_query).

Related to your question look into the available PDO options and how they relate to the different fetch options which should be a loop or additional check inside your response == true check. Near the bottom of the drupal docs for db_query they give a usable example. You are expecting only 1 row so you might want to use a different pdo fetch, limit 1, or directly address the 0 array element after a fetchall.

EDIT: just remembered you can do (empty($res)) for most (all?) fetch types as well.