trouble with WHERE in mysql

Question

hi guys im using this MYSQL query to get the top uploader users and the total of thier images views in the current month :

    $users = DB::query("SELECT * ,
  COUNT(p.id) as numPics,
  SUM(p.views) as totalViews

FROM
  images p 
INNER JOIN
  users u
ON
  p.user_id = u.id
 WHERE 
 p.created_at >= \"$current_month\"
GROUP BY p.user_id
ORDER BY totalViews DESC LIMIT 10");

the trouble that the totalViews return the total of views of all pictures , what i want is to get the total of views of the pics uploaded in the current month . thanks .

can you put the direct where can i find the solution bcz the full code works fine except what i posted . — Hamza
– Hamza, Commented May 6, 2013 at 13:37
SELECT * , COUNT(p.id) as numPics,(select SUM(p.views) as totalViews where user_id=p.id) try something like this — Francis Stalin
– Francis Stalin, Commented May 6, 2013 at 13:42
Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE p.created_at >= "2013-05-01 23:59:59") — Hamza
– Hamza, Commented May 6, 2013 at 14:59

Vinoth Babu · Accepted Answer · 2013-05-06 13:34:23Z

1

Use MONTH(NOW()) in mysql for getting data for current month

p.created_at >= MONTH(NOW())

answered May 6, 2013 at 13:34

Vinoth Babu

6,85010 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jason McCreary Over a year ago

While this is good advice, it doesn't directly answer the question. Consider expanding your answer to identify the OP's current problem.

Hamza Over a year ago

im using this structure : $current_month = date('Y-m-d', strtotime("first day of this month")) . ' 23:59:59';

Skinny Pipes Over a year ago

what is the data type of column created_at?

Vinoth Babu Over a year ago

@Hamza: In mysql itself there is func for getting current month. Then why should you go for getting current month using php and implementing in query?

Yahoo Serious Over a year ago

Hmm, would it be this simple: change strto*time* in strto*date*?

|

Lylo · Accepted Answer · 2013-05-07 04:46:41Z

0

change the group by statement and the select clause to this

SELECT * ,
  (SELECT COUNT(*) FROM images where id = p.id) as numPics,
  SUM(p.views) as totalViews

FROM
  images p 
INNER JOIN
  users u
ON
  p.user_id = u.id
 WHERE 
 p.created_at >= \"$current_month\"
GROUP BY p.user_id, p.id
ORDER BY totalViews DESC LIMIT 10"

answered May 7, 2013 at 4:46

Lylo

1,2611 gold badge9 silver badges12 bronze badges

Comments

sakhunzai · Accepted Answer · 2013-05-07 05:17:45Z

0

You need to use extract method

"SELECT * ,
  (SELECT COUNT(*) FROM images where id = p.id) as numPics,
  SUM(p.views) as totalViews

FROM
  images p 
INNER JOIN
  users u
ON
  p.user_id = u.id
 WHERE 
EXTRACT(YEAR_MONTH FROM p.created_at) >= EXTRACT(YEAR_MONTH FROM \"$current_month\")
GROUP BY p.user_id, p.id
ORDER BY totalViews DESC LIMIT 10"

The name of variable is miss-leading i.e $current_month , but your comments says it has a time stamp value e.g "2013-05-01 23:59:59"

answered May 7, 2013 at 5:17

sakhunzai

14.6k27 gold badges107 silver badges163 bronze badges

Comments

Arjan · Accepted Answer · 2013-05-07 05:27:22Z

This query looks like a perfect example to make it a little more generic. When you add an upper limit for created_at you can use the same query also to find the top of last month (or any other month).

$lower_limit = date('Y-m-d', strtotime('first day of this month'));
$upper_limit = date('Y-m-d', strtotime('first day of next month'));

I do assume that you're using PDO prepared statements, so I'll just provide the SQL.

SELECT u.*,
  COUNT(p.id) as numPics,
  SUM(p.views) as totalViews
FROM
  images p 
INNER JOIN
  users u
ON
  p.user_id = u.id
WHERE 
  p.created_at >= :lower_limit
AND p.created_at < :upper_limit
GROUP BY p.user_id
ORDER BY totalViews DESC
LIMIT 10

Note that this does not selecting any images. The result set should be consistent, and when you group by user_id you have no control over the image data in the result set.

Collectives™ on Stack Overflow

trouble with WHERE in mysql

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related