CODEWITHSUNDEEP
phpmysql
Sygon
Sygon

Reputation: 222

How to filter data in PHP/MySQL by day?

I have a PHP script which users submit a post and saves in to the database; I'm storing the date it was posted using time(). I'm trying to figure out a way to filter my data by day. I have a while loop which is returning all the posts that user made which is ordered by date, however what I'm looking to do is have headings of a date for e.g. 10 December 2011 and only have posts that was submitted on that day. I want it only to display the heading if there is data for that day.

Thanks a lot.

EDIT:

Here is my while loop at the moment:

$get_posts = mysql_query( "SELECT * FROM `posts` WHERE `userid` = '$name[id]' ORDER BY `date` DESC" );
while( $userpost = mysql_fetch_assoc( $get_posts ) )
{
    echo $userpost[ 'post' ];
}

But somehow before the loop I need to display the date heading the post or posts was submitted. I'm thinking it would need to be a loop outside of the current loop but I have no idea how to go about it.

Upvotes: 1

Views: 2078

Answers (3)

Abhay
Abhay

Reputation: 6645

As you have mentioned in one of your comments to the question that the DB column is of type integer. And so I assume you are storing the UNIX timestamp in the column. From what I understand, you are looking to display something like:

DATE HEADING 1
- data
- data

DATE HEADING 2
- data
- data

And if that is correct, this code snippet might help:

$rs = mysql_query('SELECT `column1`, `column2`, FROM_UNIXTIME(`the_time_column`, \'%d %M %Y\') AS `date_value`
FROM `your_table`
ORDER BY `the_time_column`');
$old = '';
while ($row = mysql_fetch_assoc($rs)) {
    $new = $row['date_value'];
    if ($new != $old) { // marks the beginning of a new heading
        echo 'heading = ' . $new;
    }
    // start displaying data under the heading
    echo $row['column1'];
    echo $row['column2'];
    // finish displaying data

    $old = $new;
}

Please note that I've used dummy column and table names, so please replace them with the actuals. Also, the above snippet will display heading in ascending order of date. To show in reverse, add DESC at the end of the query.

Hope the above helps!

Upvotes: 2

LordOfBerlin
LordOfBerlin

Reputation: 66

You could use BETWEEN.

Have a look at The MySQL Reference

Upvotes: 0

Tomáš Plešek
Tomáš Plešek

Reputation: 1492

You get that right from the database, i.e.:

SELECT * FROM YourTable 
WHERE 
   DAY(yourDateField)='15' AND 
   MONTH(yourDateField)='10' AND 
   YEAR(yourDateField)='2011'
;

or better yet, directly, like this:

SELECT * FROM YourTable 
WHERE yourDateFieldld=2011-12-10;

Depends how you store the data in your DB.

In any case, this should be done in the database, not PHP

Upvotes: 1

Related Questions