Get total number of images uploaded in one month ? SQL Query?

[AshleyUK]

Hi,
Is there a way to find out the total number of uploaded images in one given month?
For example, I want to find out how many images were uploaded in April 2016.
Is there a suitable SQL Query that I can run to give me this information?

Thanks

 

[malvernweb]

There are a few different approaches, would be something along the lines of ....

SELECT COUNT(image_date) FROM chv_images
WHERE image_date >= DATEADD(DAY,-30, GETDATE())

SQL COUNT can be a bit funny sometimes, it would be safer and cleaner to make a little PHP script then use that to count the images.

Also, image_date is a datetime field, which can also confuse SQL if it's not formatted first

"SELECT DATE_FORMAT(image_date,'%m/%d/%Y')
FROM chv_images
WHERE image_date BETWEEN NOW()- INTERVAL 30 DAY AND NOW()"

Then using PDO query ...

$no_images= $query->rowCount();

Which will return the number of rows within the 30 day time-frame

EDIT:

I just re-read your question, you wanted everything from a given month .....

"SELECT image_date FROM chv_images WHERE image_date BETWEEN '2016-04-01 00:00:00' AND '2016-04-30 23:59:59' ";

Would return the whole of April.

So many ways to do this :/

 

[AshleyUK]

Thanks, I'll give that a shot.

 

[AshleyUK]

Wow... 490,000 rows in the last 30 days.
So I take it that means, 490,000 images have been uploaded in the last 30 days?

 

[malvernweb]

One row = one image .... but that does sound like a lot :/

 

[AshleyUK]

I've just confirmed it with changing the number of days to 999, and comparing the amount of images in the admin area to the count from SQL... It's not 'exact' but it's very very close. I have nearly 6 million uploaded images. 🙂

 

[malvernweb]

COUNT will skip things it doesn't like, such as NULL fields, or badly formatted dates, so it may not be 100%, but as you say, it should be close enough.

Glad it helped 😀

 

[malvernweb]

What's your site URL, I'd love to see it?

 

[AshleyUK]

www.ultraimg.com

 

[malvernweb]

I'm really enjoying pressing the random button 🙂

 

[JLarsson]

490,000 images in a month... that's like 11 images each minute day in day out... that's a LOT of traffic....

 

[artonbej]

What about total image views?

 

[Rodolfo]

There is a hidden table called chv_stats, you should check that one.