SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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 today - x days.

  3. #3
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Member
    Join Date
    Aug 2009
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would do something like this:

    Code:
    <?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. #6
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 "<br>";
    echo $row['Displayed'];
    echo "<br>";

    ---- 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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •