PHP Versions Question posted in
You can find a list of all versions here.

PHP determine if email appears more than once in SQL query results – Php versions

We are working on a small follow up email script and now need to determine if an email appears more than once in the query results. If the email appears only once then use email content A, if it appears more than once use email content B.

We are using a fairly standard PHP query and then using a while loop to tackle each row:

// query database followups for rows which fupdate = todays date
require 'user password stuff.php';
$today = date("Y-m-d");

// tourname, email, tourid, fupsent
$fups = $database->query("SELECT * FROM followups WHERE fupdate='$today'");
while ($followup = $fups->fetch(PDO::FETCH_ASSOC))
{
  $email = $followup["email"];
  // check if email appears more than once
  // the code we need...

  if ($emailcount === 1) {
    // include content A in email body
  } else {
    // include content B in email body
  }
}

Is there an auto-magical way to perform such a task? Or are we limited to setting up another query / while loop to compare the email to every other and counting how many times it matches one?

Some additional clarification:

We are working with only one table, which contains: fupdate, email, name, productid (plus a few other columns which aren’t related to this function).

Any duplicated emails would have unique productid

— Got It Sorted —

It was a tough choice as to the accepted answer, both had the same focus and were written within minutes of each other, but Qirel went a bit further with their explanation.

The final version is quite different than the original concept, as now we will first use the COUNT(*) and GROUP by email functions to fetch a list of unique emails to process, then do a 2nd query using JOIN to combine the followups and product tables to gather customer name plus product names and urls to build the email content.

My thanks to all three who provided answers/comments, as all contributed to the final ideas.

Tags:

2

Answers


  1. 0

    Use query with GROUP BY:

    SELECT email, count(*) as email_count FROM followups WHERE fupdate='$today' GROUP BY email

    In the $followup array you will have two keys: email and email_count which will show the count of current email.

    Login or Signup to reply.
  2. 0

    You should be using a prepared statement with your query, to avoid injecting variables directly into your SQL query.

    To get the number of occurrences of an entry, use GROUP BY email, and then use the aggregate function COUNT(), which will give you the count of each email matching the condition in your WHERE clause.

    The query then becomes 

    SELECT email, COUNT(*) as email_count
    FROM followups
    WHERE fupdate = ?
    GROUP BY  email

    Where ? is a placeholder, that we bind by passing an array to the execute() method. 

    $stmt = $database->prepare("SELECT email, COUNT(*) as email_count
                                FROM followups
                                WHERE fupdate = ?
                                GROUP BY  email");
$stmt->execute([$today]);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $email = $row["email"];

    if ($row['email_count'] == 1) {
        // include content A in email body
    } else {
        // include content B in email body
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search