MySQL and more complex queries

Kirby 2 Solved ✅
1

Hi,

I have successfully managed to connect to my database and to retrieve some data with the examples explained here: http://getkirby.com/docs/toolkit/databases

But now I need to run a more complex query like this:

SELECT
    DATE(`users`.`created`) AS `date`,
    COUNT(`users`.`id`) AS `count`
FROM `users`
WHERE `users`.`created` BETWEEN '2012-01-01 00:00:00' AND '2012-01-31 23:59:59'
GROUP BY `date`
ORDER BY `date

How can I run this with kirby?
Thanks so much for your help.

Best,
Chris

2

Not a 100% sure on the specific statements, but something in this direction:

$users = $db->table('users')->select(array('date', 'id AS count'))->where('created', 'BETWEEN', '"2012-01-01 00:00:00" AND "2012-01-31 23:59:59"')->group('date')->order('date ASC')->all();
3

As a last resort you can also simply use:

$results = $db->query("SELECT
    DATE(`users`.`created`) AS `date`,
    COUNT(`users`.`id`) AS `count`
FROM `users`
WHERE `users`.`created` BETWEEN '2012-01-01 00:00:00' AND '2012-01-31 23:59:59'
GROUP BY `date`
ORDER BY `date");
1 Like