date different, skip line

Anon

I have a little question. I have a mysql database that has a table that stores the date in [date]

when I post my site news, i want it to print the date. But only if the date is different than the last day.

So it would look like this:

Day 1
news1
news2
news3

Day2
news1

Day3
news1
news2
news3

it would print all the news for that day under the date. when it see a different date, it would print the date and then news from that day..

i am very green..so go easy on me. I am trying to learn this.

Thanks

[deleted]

It's quite simple if you think about what you want to do:

The list is ordered by date, with today at the top, then yesterday, etc.
So you need to do ORDWER BY date_field DESC

Then you want to know which day an item belongs to, so you need the year, month and day.
So it would be something like
SELECT concat(year(date_field),month(date_field),day(date_field)) AS YMD, foo, bar
FROM table
WHERE something=something_else
ORDER BY date_field DESC

Now all items are ordered by date, and they all have an YMD value that gives you something like 20010524. That value will be the same for all items that are from the same day.

Now you can loop through the items and check if the YMD value is the same as the last one. If it's not, then you skip a line, print the date, print the item and continue.
If the YMD value is the same, just print the item.

Anon

how do i finish this off? i think it will be with if then statements.. but not sure how to do them..

Thanks Vince..

dannys

Assuming you've already run vincent's query and have the resource id stored in $result:

$ymd="";
while ($row=mysql_fetch_asscoc($result))
{
if ($row[YMD]==$ymd)
// print item stuff
else
{
$ymd=$row[YMD]; // Start new day
// Print Day Heading
// print item stuff
}
}

That should work (no time to test right now).

HTH

dannys

Why doesn't my indentation ever work?

Anon

you guys are so great.. i love you guys....

thanks

Anon

ok..it does not work.. unless i have a type..Please help me..

here is what i have:

$skipper="SELECT concat(year(n_date),month(n_date),day(n_date)) AS YMD, foo, bar FROM news WHERE n_date=$newdate ORDER BY n_date DESC";
$ymd="";
while ($newdate=mysql_fetch_asscoc($skipper));
{
if ($row[YMD]==$ymd)
// print item stuff
echo" test";

else
{
$ymd=$row[YMD]; // Start new day
// Print Day Heading
// print item stuff
echo "newday";
}
}

dannys

you've got asscoc where you need assoc.

At the IF statement, you'll need to include {} - you can only leave these out if the IF statement is only 1 line long.

You'll need:

if ($row[YMD]==$ymd)
{
// print item stuff
echo "test";
}

Anon

Warning: Supplied argument is not a valid MySQL result resource

that is the error i get.. it is referring to:

while ($row=mysql_fetch_assoc($skipper));

here is a copy/paste of what i am using:

$skipper="SELECT concat(year(n_date),month(n_date),day(n_date)) AS YMD, foo, bar FROM news WHERE n_date=$newdate ORDER BY n_date DESC";
$ymd="";
while ($row=mysql_fetch_assoc($skipper));
{
if ($row[YMD]==$ymd)
echo" test";
else
{
$ymd=$row[YMD]; // Start new day
// Print Day Heading
// print item stuff
echo "newday";
}
}

thanks again..