CODEWITHSUNDEEP
phpmysqllaravel
Hamza
Hamza

Reputation: 31

trouble with WHERE in mysql

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 .

Upvotes: 1

Views: 139

Answers (4)

Arjan
Arjan

Reputation: 9874

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.

Upvotes: 0

sakhunzai
sakhunzai

Reputation: 14470

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"

Upvotes: 0

Lylo
Lylo

Reputation: 1261

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"

Upvotes: 0

Vinoth Babu
Vinoth Babu

Reputation: 6852

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

p.created_at >= MONTH(NOW())

Upvotes: 1

Related Questions