CODEWITHSUNDEEP
phpmysqlcodeignitertimestamp
James
James

Reputation: 2860

Select records from MYSQL database grouped by day

I have a database of records that have a timestamp asscoiated with them. I would like to pull records from the database grouped by day, so if there are other records with the same date (24 hour span) I would like them to be grouped together. Can this be done with MYSQL or will I have to pull the records and organise them into arrays using PHP?

Here is a screenshot of my table:

enter image description here

Here is my model function so far:

public function getUsersMoves($options) {   
        $query = "SELECT * FROM myzone_user_hr_records";
        if(isset($options['GUID'])) {
            $query .= " WHERE GUID = '" . $options['GUID'] . "'";
        }
        if((isset($options['minHR'])) &&  isset($options['maxHR'])) {
            $query .= " AND (hr BETWEEN '" . (int)$options['minHR'] . "' AND '" . (int)$options['maxHR'] . "')";
        } else if (isset($options['zone'])) {
            $query .= " AND zone = '" . (int)$options['zone'] . "'";
        }
        if(isset($options['activity'])) {
            $query .= " AND title = '" . $options['activity'] . "'";
        }

        $query .= " ORDER BY time DESC";

        $query = $this->db->query($query);
        return $query->result_array();
    }

And my controller code:

$moves = $this->myzone_model->getUsersMoves($options);

I want the data sorted so that these records are grouped together if they have the same date in the timestamp, for example (2012-11-20).

Thanks

Upvotes: 0

Views: 1984

Answers (2)

Mohebifar
Mohebifar

Reputation: 3411

try this :

SELECT *, DATE(time) AS date FROM [whatever you want] GROUP BY date

Upvotes: 4

James
James

Reputation: 2033

select * from TABLE where `time` like '2012-11-20%'

The option I have suggested works because the LIKE condition in the WHERE selects all dates from the time field that start with 2012-11-20. That means that it doesn't matter what time it is during the day, it will return all results for that day.

To make this work, you must remember to use LIKE and then add the wildcard at the end - %. You can also add the wildcard at the beginning if you wanted. An example to return all days (11th December) for multiple years would be:

SELECT * FROM TABLE WHERE `time` like '%-12-11%'

Upvotes: 1

Related Questions