CodeIgniter Forums
Help me writing a query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Help me writing a query (/showthread.php?tid=22100)

Help me writing a query - El Forum - 08-29-2009

[eluser]aryan_[/eluser]
I have a table like this-

Code:
---------------------------------------------
id | ad-id | user_id | datetime   | ip
---------------------------------------------
1  | 2     | 10      | 1248977229 | 127.0.0.1
---------------------------------------------
2  | 3     | 10      | 1248977329 | 127.0.0.1
---------------------------------------------
3  | 3     | 10      | 1251556503 | 127.0.0.1
---------------------------------------------
4  | 4     | 15      | 1251556603 | 127.0.0.1
---------------------------------------------

I want to get clicks made by a user per day with date. 'datetime' field is varchar, should I change it to MySql timestamp.

Thanks!

Help me writing a query - El Forum - 08-29-2009

[eluser]WanWizard[/eluser]
If you use CURRENT_TIMESTAMP for the datetime field, combined with "on update CURRENT_TIMESTAMP", you don't have to worry updating that, MySQL will take care of that.

As for the rest, I'm not sure what you exactly want. You have a record per click, and you want a total per user_id per day?

The query could then be something like:
Code:
SELECT user_id, COUNT(*) AS clicks, DATE(datatime) AS day
FROM TABLENAME
  WHERE datatime >= '2009-01-01 00:00:00'
    AND datatime < '2010-01-01 00:00:00'
GROUP BY user_id
which gives you clicks per user for the selected time period.

Help me writing a query - El Forum - 08-29-2009

[eluser]aryan_[/eluser]
Yes, I want retrieve data for only one user. We'll need 'WHERE user_id=$user_id'.

I want to show a user, click made by him everyday. I want to retrieve upto 15 days record from current date.

Help me writing a query - El Forum - 08-29-2009

[eluser]aryan_[/eluser]
Hey, resolved myself

SELECT COUNT(*) AS clicks, FROM_UNIXTIME(datetime, '%Y-%m-%d') AS Date FROM ad_watch GROUP BY FROM_UNIXTIME(datetime, '%Y-%m-%d')

works great!

Thanks for your help!