Group by dates and count rows using CodeIgniter database query

Question

I am having trouble trying to figure out how to populate a multi-dimensional array. Let's say I have a table of transactions with various billing dates. First I have an array that retrieves the following 'billed' dates:

Array
(
    [0] => Array
        (
            [BILLED] => 2011-11-18 00:00:00
        )

    [1] => Array
        (
            [BILLED] => 2011-11-22 00:00:00
        )

)

I also have the following query which is currently hard-coded to one of the two 'billed' dates shown above:

$qryOrders = $this->db->query("
       SELECT tblOrders.* 
       FROM tblOrders 
       WHERE tbltc.BILLED = '2011-11-22'"); 
$data['Orders'] = $qryOrders->result_array();

I know that I can very easily determine the count of array items by using count($Orders); but how can I instead pass through each of the 'billed' dates of 2011-11-18 and 2011-11-22 to that I can determine the overall count, for both of the specified dates?

I hope I've explained it clearly enough. I am thinking that I probably need some kind of foreach loop and each time through the loop I could pass in the billed date, and keep track of a running total each time through the loop.

I'm not sure actually. Should I have mentioned that I am using CodeIgniter framework? — DanielAttard
– DanielAttard, Commented Nov 25, 2011 at 5:25
In other words, you want a count of orders received between two dates? MySQL's COUNT and BETWEEN statements will help. — Matt
– Matt, Commented Nov 25, 2011 at 5:28
Thanks Matt. Actually, that's not what I want. I don't want a count of items BETWEEN two dates. I want a count of items on each of the series of dates. Sometimes 2 or 3 or 4 or 5 etc. I must need to iterate through to get my dates, and then run COUNT foreach of the dates. — DanielAttard
– DanielAttard, Commented Nov 25, 2011 at 5:36

ajreal · Accepted Answer · 2011-11-25 05:32:56Z

1

// this is getting all results for a record
$sql = 
<<<sql
SELECT tblOrders.* 
FROM tblOrders 
WHERE tbltc.BILLED between '{$arr[0]}' and '{$arr[1]}'
sql;

// this is to get total count of matched record
// $sql = 'select count(*) from ..';

if you are using CI, you can easily using bind

example:-

$sql = 'SELECT tblOrders.* FROM tblOrders WHERE tbltc.BILLED between ? AND ?';
$this->db->query($sql, array($arr[0], $arr[1]));

edited Nov 25, 2011 at 5:32

answered Nov 25, 2011 at 5:25

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

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

6 Comments

DanielAttard Over a year ago

Thanks for the answer. I'm not sure what you mean about using bind?

ajreal Over a year ago

CI manual has the example, please refer to the updated answer and codeigniter.com/user_guide/database/queries.html (look for query binding)

DanielAttard Over a year ago

I should also add that sometimes I have 2 elements in the date array, sometimes 3 or 4 or 5. This varies, thus my thinking a loop would be needed.

ajreal Over a year ago

this is something I can't help, you have to understand how the BETWEEN works (start, end). you can do iterate to find the minimum and maximum then bind

DanielAttard Over a year ago

I've read-up on how the BETWEEN works, and it's not what I am looking for. I need to find an example that will iterate through my records and use the date value each time. thanks.

|

Collectives™ on Stack Overflow

Group by dates and count rows using CodeIgniter database query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related