How can I detect if the same email appears more than once within 2 minutes?

Question

How can I detect if the same email appears more than once within 2 minuts.

This is my OutPut:

07-02-13 20:08:41   test11@gmail.com
07-02-13 20:09:41   test11@gmail.com
07-02-13 20:21:25   hottie@gmail.com
07-02-13 20:56:51   ugly@gmail.com
07-02-13 21:42:37   selma532@gmail.com
07-02-13 22:09:11   blalbla421@gmail.com

This is my SQL statement.

$results = $this->EE->db->query("SELECT t.* FROM transactions as t WHERE t.cardid > 0 ORDER BY t.created DESC");

I want them to seperate her. So if the same mail appears withing 2 minuts it has to go to bad transactions.

foreach ($results->result_array() as $filter) 
{

I can help with this: I have a $filter['created'] = This contains time and i have a $filter['email'] ) this contains emails.

if(//Filter goes here) {
  $badtransactions[]=$filter;
} else {
  $cooltransactions[]=$filter;
}

This is my

<table class="table">
  <h3>Cool Transactions (<?php print_r($sizeCoolTransactions)?>) </h3>
  <thead>
    <tr>
      <th>Time</th>
      <th>Email</th>
    </tr>
  </thead>
  <tbody>
    <?php
    //Foreach over cooltransactions
    foreach($cooltransactions as $transaction) {

      // IS NEW?
      $isactive = false;
      if($transaction['created'] > time()-$refresh_timer){
        $isactive = true;
      }

      // Get user mail.
      $member_sql = $this->EE->db->query("SELECT email FROM exp_members WHERE member_id = '".($transaction['cardid']-10000000)."'");

      $member_result = $member_sql->result_array();
      if(isset($member_result[0])) {
        $member_result1 = $member_result[0];
      }
    ?>
    <tr class="<?= $isactive ? 'alert-success' : ''; ?>">

      <td><?= date('d-m-y H:i:s', $transaction['created']); ?></td>
      <td><?= isset($member_result1['email']) ? $member_result1['email'] : '<span style="color:red">Email mangler</span>'; ?></td>
    </tr>
    <?php
      }
    ?>
  </tbody>
</table>

I had a really similar question awhile back. I think the answer applies to you: http://stackoverflow.com/a/6556239/362536 — Brad, Feb 07 '13 at 21:33
It did not help me. :/ I want min to seperate in two transactions. So if the email appears again within 2 minute = bad transactions. Otherwise, cool transactions. ... But what to write in the if statement is the big question — Zaz, Feb 07 '13 at 21:40
you could cache stuff, to get some speed, quering a database i suppose takes longer than caching things.. Another way is to check when the entries with same email where created if the difference is greater than 2min then ignore 1st mail or whatever. — Gntem, Feb 07 '13 at 21:59
@GeoPhoenix, and how would you do that? If you can, can you try out with my code as i posted above? — Zaz, Feb 07 '13 at 22:02
You really shouldn't be doing the hacky 'adjustment' of the card id to turn it into a member id - if possible, find a way to actually map it. Also, even if you might not be open to SQL Injection here, it'd still be better to use parameterized queries, to guarantee your future safety, and to stay consistent. What's your ultimate aim here? You're displaying the information to a page, but do you need to actively deny uploads or something? Doing the existence check is fairly trivial... — Clockwork-Muse, Feb 07 '13 at 22:09

score 0 · Answer 1 · answered Feb 07 '13 at 22:13

0

Is this what you're looking for? Unfortunately its written for MSSQL but hopefully you can change the syntax to work with your RDBMS.

Essentially set your current time (I defaulted it in order to fit with the data provided) and create a range using DATEADD(). Query where the date time falls within the range providing you with all the emails within the past 2 minutes. Finally wrap it in a GROUP BY Email HAVING COUNT(*) > 1 to show all duplicates.

http://sqlfiddle.com/#!3/1d759/17

answered Feb 07 '13 at 22:13

deeg

528
2
5

Close @deeg. You are filtering it in the SQL statement which is nice. But i want it do in the if statement so i can place the same email there appears within 2 minute = bad transactions and the rest cool transactions. – Zaz Feb 07 '13 at 22:19

score 0 · Answer 2 · answered Feb 07 '13 at 22:25

You could write an SQL Query like the one below. Basically all this does is, for each row returned in your database, checks the same table for entries with that email address and a timediff on the datetime field (in this case Created) that's greater than -2 (two minutes in the past) and +2 (two minutes in the future). If a record is found within two minutes of the current record, it sets the OccursWithinTimescale field value to the value of the matched record's id. Therefore, if OccursWithinTimescale is null, then that email hasn't occurred in the table within two minutes of itself, otherwise it has, and you have the id of the offending record.

select l1.id, created,
(select max(id) from log where email = l1.email 
    and (
            (minute(timediff(created, l1.created)) <= 2 and minute(timediff(created, l1.created)) >= -2)
        ) 
    and id <> l1.id
    and date(created) = date(l1.created)
) as OccursWithinTimescale, 
l1.email from log l1

There are limitations of using this method, for one I haven't included anything about indexing, which you'll need to look into with this sort of subquery. Also, subqueries can be a huge drain if used over large datasets. But it's an option at least, though perhaps not the best.

You could further adjust that query to just return all records that have occurred within two minutes of themselves by adding a where clause on OccursWithinTimescale is not null.

How can I detect if the same email appears more than once within 2 minutes?

2 Answers2