Confused on paginating results from loop

[dmacman1962]

Hi all,

I have a page that list news stories by the day ( I have a field called 'Day' and it is an enum with 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' in it).

And I loop thru and start to display the results based on what today is (ie today is Thursday and show the results for Thursday - (last) Friday.

I am trying to wrap my brain around how to test my results from a query, so here is what I am 'trying' to do...

Example:

Thursday
-->Article 1
-->Article 2
Wednesday
-->Article 1
Tuesday
-->Article 1
-->Article 2
-->Article 3
-->Article 4
Monday
-->Article 1
Friday
-->Article 1

Here is my attempt...

<table>
$x=0;
while ($row = mysql_fetch_array($result)) {
		$ID = $row['ID'];
		$Quote = $row['Quote'];
		$Source = $row['Source'];
		$Day = $row['Day'];

// HTML <tr>
//HTML <td>
if ($x ==0) {
echo $Day; // Show me the Name of that day from table
} else {
echo 'nothing';
}
//echo the article
$x = $x++; // incement x so it will not display the Day name next loop
echo $Quote. ' '.$Source;

} //close out the while loop

What I am getting is ...

Example:

Thursday
Friday
Friday
Monday
Wednesday
Tuesday

With no articles???

I know I am coding this wrong in several ways.
1) No articles
2) multiple day names
3) Days in wrong order.

Here is the link to my page...

The page on my testing server.

And zip file of the complete page...

A zip of my page.

Thanks in advance,

Don

[devinemke]

rather than store the days of the week hardcoded into an ENUM field why don't you simply store the date/time using a DATETIME field type. the day of the week can easily be derived from that:

SELECT *, DAYNAME(date_time) AS day FROM table WHERE date_time BETWEEN CURDATE() AND CURDATE() - INTERVAL 1 WEEK

[dmacman1962]

Sure, I could do it that way. That would be more efficient.

But I am still not going to sort them correctly after changing that.

Thanks,
Don

[devinemke]

dmacman1962 wrote:

But I am still not going to sort them correctly after changing that.

you can sort them any way you want using an ORDER BY clause in your query. you can then group them by using the returned DAYNAME() as array keys and the rest of the rows as the array values.

[dmacman1962]

I worked with this for awhile and figured out that this method will not not work.

I am using the Date field or the DateTime field and that if the administrator post a news article for Tuesday, that it may be timestamped Monday and the order will not work.

I need to be able to query the dow or my enum field, and show posts from today, back. (ie if today is Friday, then show friday back to monday).

I am not sure how to build the query that way.

And I havent found a similar scenario to figure out what is the first article for that day, so I can put the day (name) before the post.

ie...

Friday
->1st post
_>2nd post
Thursday.

not

Friday
->1st post
Friday
->2nd post.

Any sugestions?

Thanks again.

Don

[devinemke]

i still think you are overcomplicating this. here is a simple example:

$result = mysql_query('SELECT article_name, DAYNAME(date_time) AS day FROM table WHERE date_time BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() ORDER BY date_time DESC') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$array[$row['day']][] = $row['article_name'];}

foreach ($array as $key => $value)
{
	echo '<b>' . $key . '</b><br>';
	foreach ($value as $subvalue) {echo ' - ' . $subvalue . '<br>';}
	echo '<br>';
}

[dmacman1962]

Ok devinemke,

I finally figured out what you did here... NICE!

It works great. I adapted it to include the href to the page template for them, but I tried about 20 different ways to get the ID for each article and I either get no ID or the same one for each article. Here is what I have based on your query...

$result = mysql_query('SELECT ID, Day, Quote, DAYNAME(date_time) AS day FROM daily_events WHERE date_time BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() ORDER BY date_time DESC') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)){
($array[$row['Day']][] = $row['Quote']);
$row['ID'] = $row['Day'];
}
foreach ($array as $key => $value)
{
    echo '<b>' . $key . '</b><br>';
    foreach ($value as $subvalue) {echo ' - <a href ="../article.php?ID='.$ID.'" target="_blank">' . $subvalue . '</a><br>';}
    echo '<br>';
} 

Bu I cannot assign the article ID, where did I go wrong?

Thanks a LOT!

Don

[devinemke]

$result = mysql_query('SELECT ID, Quote, DAYNAME(date_time) AS day FROM daily_events WHERE date_time BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() ORDER BY date_time DESC') or exit(mysql_error());
while ($row = mysql_fetch_assoc($result)) {$array[$row['day']][] = $row;}

foreach ($array as $key => $value) 
{ 
    echo '<b>' . $key . '</b><br>'; 
    foreach ($value as $subvalue) {echo ' - <a href ="../article.php?ID=' . $subvalue['ID'] . '" target="_blank">' . $subvalue['Quote'] . '</a><br>';}
    echo '<br>';
}

[dmacman1962]

Hi devinemke,

I just realized that this will work, but not the way I wanted it to.

When the administrator enters a post, this requires the datetime stamp and looks up the article based on that -6 days.

The issue is that I must not somehow force a datetime stamp based on what day I want the article to appear as.

IE, if they enter it on Sunday and it is Mondays article, I have a drop-down in the form to label it as monday, but the code will act is if it is Sundays post, since that is when it was time stamped.

Is there a simple fix for this based on your approach?

Thanks,
Don

[devinemke]

the example i posted will grab any rows where the date field is between 6 days ago and today. if you insert a new row with a date that is in the future then obviously it will not be displayed in a list of rows from the last 6 days. i don't understand what you want here. you want to show all rows from the last 6 days as well of rows in the future? if so then use a query like:

SELECT ID, Quote, DAYNAME(date_time) AS day FROM daily_events WHERE date_time BETWEEN CURDATE() - INTERVAL 6 DAY AND NOW() OR date_time > NOW() ORDER BY date_time DESC

[dmacman1962]

Sorry for the confusion.

What I want it to do is get all the post for the last 6 days (remember that this is a Monday - Friday news page) and list them in reverse order.

Today is currently Wednesday, so the posts would be like...

Wednesday (which is the current day)
Tuesday
Monday
Friday
Thursday

And on Thursday they would be...

Thursday (which would be the current day on Thursday)
Wednesday
Tuesday
Monday
Friday

Currently it will do that but the articles are labelled by the day, but it looks back 6 days and the last article might have been entered 7 days ago ( but the day was set to thursday).

Here is the link I currently am using, you'll notice that there is no wednesday listed because of the above issue.

My test page.

Does that make more sense?

Thanks again,
Don