How to display header for rows returns from MySQL grouped by date for listview


Problem :

I am trying to pull messages form mysql, group them by date, and display the results in listview with one header for each date.

I figured out how to group by date from mysql and order by desc timestamp.

My mind is blocked on how to display only one header for each date since is row is return using the while() function from php. Can someone shed some light?

Here's what I have so far.

$st = $this->db->prepare("SELECT date(timestamp) as date, message, user_id, target_id, msg_id, msg_type, target_name 
FROM message  
WHERE (user_id=? OR target_id=?)  
GROUP BY date  
ORDER BY timestamp DESC");  
            $st->bindParam(1, $y['user_id'], PDO::PARAM_INT);
            $st->bindParam(2, $y['user_id'], PDO::PARAM_INT);
            $st->execute();

            echo'<ul data-role="listview" id="message_listview">';

            if($st->rowCount() < 1){
                echo'You have no messages';
                exit();
            }       

            while($row = $st->fetch(PDO::FETCH_OBJ)){


Solution :

$date = ''; // initialize $date with an invalid value
while($row = $st->fetch(PDO::FETCH_OBJ))
{
    if($date != $row['date'])
    {
        // display date only when it changes
        $date = $r['date'];
        echo $date;
    }
    // display the other fields here
}

Mysql Tutorials

Mysql Howto..

How to take count of records using mysql

How to SUM columns from three mysql tables?

how to store list of files in mysql database and display them based on users role(s)

How to get rank in MySQL from 2 tables?

How to get the columns names along with resultset in php/mysql?

How to get closest SUMMED mySQL value to an existing value?

How to sort data with a case statement - using MySQL?

How can I avoid using $conn in every single query?

How to exclude records from mysql query count?

How to apply MySQL 'LIKE' to a column which is specified in the query?

How to integrate free or reduced products in mysql scheme?

How to edit and insert dynamicaly created form textboxes values to MySQL DB?

How to Write a query using MYSQL

How to get the local time from mysql database

MySQL left join with multiple rows on right, how to combine to one row for each on left (with the field I want from the right table concated)?

How to update datetime by +15 hours with MySQL

How to manually configure and start PostgreSQL on windows?

How to check for existence of specific date in MySQL 'date' column?

How to put a local database onto a server?

Perl DBI with mysql: how to get return value from a stored procedure?

How to get mysql on an ec2 centos box with upgraded python

How to insert multiple rows from a textarea in MySQL

How to make sure two MySQL queries are successful?

how to update sql table with a variable number of fields in the form php

How to do an IF THEN Statement in MySQL?

How to display two decimals in MYSQL/PHP?

How to call MySQL stored procedure by using prepared statements in PHP via mysqli?

How to add information to MySQL database using java code

how to use command line to connect mysql database, and the database name with Illegal character ‘-’? [duplicate]

How to embed mysql db content into 3rd party sites without providing remote access