CODEWITHSUNDEEP
phpmysql
Ciprian
Ciprian

Reputation: 3226

mysql - Query by interval and or returning nothing

I need to select only emails from the users table where the weekly_mail (datetime) is older that one week or null and if the user's points add up to more than 10. It's not returning anything now.

points_plus table structure

id | user_id | points | date
1    62        5        2015-05-13 08:42:37
2    62        15       2015-05-14 03:12:32

query

$q = "SELECT u.email, u.weekly_mail, SUM(p.points) AS points
      FROM users u
      LEFT JOIN points_plus p
      ON u.id = p.user_id
      WHERE points > 10 AND u.weekly_mail < NOW() - INTERVAL 1 WEEK OR u.weekly_mail = NULL
      GROUP BY p.user_id";
$result = $this->db->mysqli->query($q);

if (!$result) {
    printf("Query failed: %s\n", $this->db->mysqli->error);
    exit;
}

$rows = array();
while($row = $result->fetch_row()) {
    $rows[]=$row;
}

$result->close();
return $rows;

Upvotes: 2

Views: 55

Answers (2)

prava
prava

Reputation: 3986

Check this:

$q = "SELECT u.email, u.weekly_mail, SUM(p.points) AS points
      FROM users u
      LEFT JOIN points_plus p
      ON u.id = p.user_id
      WHERE u.weekly_mail < (NOW() - INTERVAL 1 WEEK) OR u.weekly_mail IS NULL
      GROUP BY p.user_id having SUM(p.points) > 10";

Upvotes: 0

Harish Lalwani
Harish Lalwani

Reputation: 774

  SELECT u.email, u.weekly_mail, SUM(p.points) AS points
  FROM users u
  LEFT JOIN points_plus p
   ON u.id = p.user_id
  WHERE u.weekly_mail < NOW() - INTERVAL 1 WEEK OR u.weekly_mail = NULL
  GROUP BY p.user_id having SUM(p.points) > 10

Upvotes: 1

Related Questions