Error is query with joins and date/time intervals

Question

I Have fetched records from two tables using the query given below.

mysql_query("SELECT Table1.username, SUM(Table2.points) 
AS total FROM Table2 INNER JOIN Table1 ON Table1.userid = Table2.userid 
GROUP BY Table1.username ORDER BY SUM(Table2.points) DESC LIMIT 10");

I am trying to fetch the same recrods with date/time limits[1 day ] 2nd case [7 days days].

mysql_query("SELECT Table1.username, SUM(Table2.points) 
AS total FROM Table2  WHERE  Table2.date BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW() 
INNER JOIN Table1 ON Table1.userid = Table2.userid 
        GROUP BY Table1.username ORDER BY SUM(Table2.points) DESC LIMIT 10");

This query is not working , i cant find the problem with this query.

Table1 does not conatin any date colomn.
Table2 contain one date coloumn that i want to use for time intervals.

I have tried all the below given queires. and receiving the same error for all.

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in

Addition: I am using Timestamp in date type.

Kindly guide.

Try moving the `WHERE` condition after the `INNER JOIN/ON` statement, just before the `GROUP BY`. — Joachim Isaksson, Oct 16 '14 at 16:29
What does the error say? Adding the message to the question would probably be a good idea. — Joachim Isaksson, Oct 16 '14 at 16:37

score 1 · Accepted Answer · edited May 23 '17 at 10:25

not only is the where in the wrong place it looks like you should use CURDATE() instead of NOW() since you said it was a date column and not a datetime column

SELECT t1.username, SUM(t2.points) AS total 
FROM Table2 t2
JOIN Table1 t1 ON t1.userid = t2.userid  
WHERE t2.date BETWEEN (CURDATE() - INTERVAL 1 DAY) AND CURDATE()     
GROUP BY t1.username 
ORDER BY total DESC 
LIMIT 10;

NOTE: mysql_* is deprecated and you shouldn't use it... you should switch over to PDO or mysqli using a parameterized query..

read about parameterization here as well as a good way to prevent sql injection incase you ever need to pass in variables

YOUR QUERY

SELECT Table1.username, SUM(Table2.points) AS total 
FROM Table2  
WHERE  Table2.date BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW() 
^------------------------------------------------------------^ = bad this should be your JOIN first and then WHERE
INNER JOIN Table1 ON Table1.userid = Table2.userid 
GROUP BY Table1.username 
ORDER BY SUM(Table2.points) DESC LIMIT 10

MY QUERY

SELECT t1.username, SUM(t2.points) AS total 
FROM Table2 t2
JOIN Table1 t1 ON t1.userid = t2.userid  
WHERE t2.date BETWEEN (CURDATE() - INTERVAL 1 DAY) AND CURDATE()     
GROUP BY t1.username 
ORDER BY total DESC 
LIMIT 10;

notice the WHERE goes after JOIN but before GROUP BY...

@AtifShabbir that means that your query is failing and returning FALSE and not a resource for your fetch_array()... so try my query... and tell me if it still errors like that — John Ruddell, Oct 16 '14 at 19:14
@AtifShabbir see [**HERE**](http://stackoverflow.com/a/2973209/2733506) — John Ruddell, Oct 16 '14 at 19:27
your query works fine... would you like to explain whats wrong with my query. with one way it works and other way not.. — Atif Shabbir, Oct 16 '14 at 19:32

score 0 · Answer 2 · answered Oct 16 '14 at 16:33

0

Hello as the sum should be applied on the interval, use having instead of where.

SELECT Table1.username, SUM(Table2.points) AS total 
  FROM Table2  
 INNER JOIN Table1 ON Table1.userid = Table2.userid 
 GROUP BY Table1.username 
 HAVING   Table2.date BETWEEN (NOW() - INTERVAL 1 DAY) AND NOW()
 ORDER BY SUM(Table2.points) DESC LIMIT 10

answered Oct 16 '14 at 16:33

Stephane BOISSEAU

86
7

updated the question with error – Atif Shabbir Oct 16 '14 at 19:05

Error is query with joins and date/time intervals

2 Answers2

YOUR QUERY

MY QUERY