How to display the next one on the list each day?

Databases
1

I am trying to figure out the logic involved in what I want. If I can understand the logic, I can figure out how to write code to do it.

Let’s say, I have a database of 10 items. Each item has a date field.
ID1 - Feb 21 - Canada
ID2 - Feb 20 - USA
ID3 - Feb 19 - Japan
ID4 - Feb 18 - Korea
ID5 - Feb 17 - China
ID6 - Feb 16 - Australia
ID7 - Feb 15 - Mexico
ID8 - Feb 14 - Taiwan
ID9 - Feb 13 - Russia
ID10 - Feb 12 - Ukraine

Every day I have 5 items showing. This list changes every day with 1 new item and 1 old item dropping off the list. This is done very simply by showing the item which matches today’s date and then list 5 descending so I get the previous 4 days shown below it. So if we assume today is Feb 20th, this is what would be displayed.
ID1 - Feb 21 - Canada – Not displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Displayed
ID5 - Feb 17 - China – Displayed
ID6 - Feb 16 - Australia – Displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
ID9 - Feb 13 - Russia – Not displayed
ID10 - Feb 12 - Ukraine – Not displayed

The next day Feb 21st would be…
ID1 - Feb 21 - Canada – Displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Displayed
ID5 - Feb 17 - China – Displayed
ID6 - Feb 16 - Australia – Not displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
ID9 - Feb 13 - Russia – Not displayed
ID10 - Feb 12 - Ukraine – Not displayed

Ok, the above is simple enough and I’ve managed to setup a query which does this fine–took me 3 years to figure out how to do it though and I’m hoping it won’t take another 3 to get the next problem solved. The problem is when we reach Feb 22 and there is no more data. What I’ve been doing is just using a spreadsheet to copy the data and increase the dates so that it’s a really long list that lasts a year. What I want is for it to go to the other end of the list and grab the next item instead of me having to use duplicate data in the database. For example…
ID10 - Feb 22 has no record so Ukraine is displayed here.
ID1 - Feb 21 - Canada – Displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Displayed
ID5 - Feb 17 - China – Not displayed
ID6 - Feb 16 - Australia – Not displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed
ID9 - Feb 13 - Russia – Not displayed

The next day Feb 23…
ID9 - Feb 23 has no record so Russia is displayed here
ID10 - Feb 22 has no record so Ukraine is displayed here.
ID1 - Feb 21 - Canada – Displayed
ID2 - Feb 20 - USA – Displayed
ID3 - Feb 19 - Japan – Displayed
ID4 - Feb 18 - Korea – Not displayed
ID5 - Feb 17 - China – Not displayed
ID6 - Feb 16 - Australia – Not displayed
ID7 - Feb 15 - Mexico – Not displayed
ID8 - Feb 14 - Taiwan – Not displayed

My goal is to be able to have the list change daily and just repeat so that it never ends. I also need to be able to add new records… ID 11, ID 12, ID 13 at any point in the future with no problem of messing up the coding.

This makes a little more sense now that I’ve explained it here, but I’m still a bit confused about the logic behind this. What I’m thinking is that I can assign a date to just one record which would be the starting date. The database then adds a day until it gets 5 days from today and then it starts displaying. If the ID is NULL (end of list) then ID = ID1. Something like that.

Ideas? Anyone? I think just discussing it with the people here will help me to better understand this and figure it out. So if you have questions or comments or better yet suggestions, please let me know.

2

Is the column that contains the date in a DATETIME format?
If so, before doing the select query, do an update query that adds n days to the date of all rows that have a date before [URL=“http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub”]today - x days.

3

It is very late here but I cannot sleep so came into the forum so forgive me if I am totally off track but I am thinking the dates are just complicating things.

Would it not be better to:

  1. fetch the data from the database
  2. store data in an array
  3. loop through array to find starting value
  4. show that item and the next 4
  5. store the first item shown in the database so you know where to start from the next day?

array_push might be useful to push the first items to the end of the array so it loops.

4

Interesting. Heavy though. I’ve been doing some reading and will see if I can figure it out. I am not familiar with array_push and not exactly getting it yet as I read it.

5

I would do something like this:



<?php 

// array of countries possibly from database
$countries = array('Canada', 'USA', 'Japan', 'Korea', 'China', 'Australia', 'Mexico', 'Taiwan', 'Russia', 'Ukraine');

// original array
echo 'Original array is: ';
echo '<pre>';
print_r($countries);
echo '</pre>';

// find start value
$start = 'Russia';

// how many elements to display
$display = 5;

// find first element
$key = array_search($start, $countries);

// push element to end of array
for ($i = 0; $i < $key; $i++) {
    array_push($countries, $countries[0]);
    array_shift($countries);
}

// items to display in new array
for ($i = 0; $i < $display; $i++) {
    $displayList[] = $countries[$i];
}

// new array
echo 'Items to display: ';
echo '<pre>';
print_r($displayList);
echo '</pre>';

?>

However, this does not take into account the dates. I am not sure how important the dates are to you. If you just want to display the 5 items each day, it is probably better to use PHP to determine what the current date is.

6

Since the answers here were great, but way beyond my ability to comprehend, this is what I came up with and it appears to be working so far and it takes into account the dates. For this to work I just give one of the records a date which matches tomorrow date. I then setup a cron job to run this script once a day.

$limit = 10; <– this tells it how many items to list

CONNECT TO DATABASE

$result = mysql_query(“SELECT * FROM test WHERE Displayed <= CURDATE() ORDER BY Displayed DESC LIMIT $limit”)or die(mysql_error());
----- here I am telling it to grab the record with today’s date and then continue with dates older for the WHILE -----

while($row = @mysql_fetch_array($result))
{

echo $row['country'];
echo "&lt;br&gt;";
echo $row['Displayed'];
echo "&lt;br&gt;";

---- This mostly let’s me see if it’s working. It displays the country and then the date that country was last displayed…thus the list will start with today and go back 9 more days (since my LIMIT was 10) of older dates.

}

$query = mysql_query(“SELECT COUNT(*) FROM test”);
list($count) = mysql_fetch_row($query);

---- This lets me know how many records are in the database which is important so I will know when the end of the list is. —

$result = mysql_query(“SELECT * FROM test WHERE Displayed = CURDATE()”)or die(mysql_error());

---- This selects the record with today’s date -----

while($row = @mysql_fetch_array($result))
{
$ID = $row[‘ID’];
if ($row[‘ID’] == $count) {$ID = 0;}

---- If the ID matches the count, then we know we must be at the end of the list and thus reset the ID to 0 so that we can start the list over again ----

$nextID = $ID+1;

----- This gets the next ID after the Current Date which will be the one we will want to display tomorrow —

$nextDay = date(“Y-m-d”, strtotime(‘tomorrow’));

----- Set the $nextDay variable to be tomorrow’s date ----

mysql_query(“UPDATE test SET Displayed = ‘$nextDay’ WHERE ID = ‘$nextID’”);
}

----- Update that record (the one we want to display tomorrow) with tomorrow’s date ($nextDay).

Then tomorrow when the cron job runs the script, the record we updated with tomorrow’s date is now the Current Date since it’s the next day.

Look ok? I’ve been testing this with a small test database and “seems” to work ok at the moment. I want to make 100% sure before I implement it though.