Need to display next results of a query each day

[kjdix]

Hey,
So basically I am setting up a menu system which rotates every 3 weeks.
So in the table we have 21 rows which have its information populated.
I have setup my form so it diaplys the data for any row I enter into the query but I need to to move to the next row the next day and so on. Then it needs to start from the top after its gone through the cycle.
Here is my code:

$query="select * from meals where id ='1'";
$result=mysql_query($query)or die ("Couldnt execute query.");
echo "<table cellpadding=0 cellspacing=0 width=100%>";
if ($result!="") {
while ($row=mysql_fetch_array($result)) {
echo "<tr><td><center><font color=#ff0000 size=6>Breakfast</font></td></tr>";
echo "<tr><td><center><font color=#000000 size=5>$row[btitle]</td></tr>";

etc

Any help would be much appreciated.

[bpat1434]

You would need to add a column to the database to use as a "flag" to track when the row was initially displayed. You may end up running up to 3 queries, but they should be relatively quick queries.

1.) Do a quick query to get the ID of the row for the current day. If one is returned, then select the rest of the data for that ID.
2.) If you don't get anything from #1, see if there is one for yesterday. If one is returned, then select the data for the next ID (or the first one if there is no next id).
3.) If nothing is returned, then run a quick query to get the data for the first item in the database.

That's basically how it would be done. There are other ways to do this (like based on the output of say date('N') or date('w')+1 and then retrieving that row's data.

[kjdix]

Hang on so currently I have a query which displays all required data for id 1 and if i change manually then i can got up to id 21. Thing is how do I change this so when a change in date is made the it then goes to the necessary id so its done automatically. I can put in extra fields into the table. I also have a field with the following data like Monday 1, Tuesday 1, Wednesday 1 etc to Monday 3, Tuesday 3, Wednesday 3, which is basically showing each day for week 1, then each day for week 2 and then each day for week 3, which is the 21 entries. I just want one displayed each day in order and then to return to the top and start again. Im fairly new to php as well, which im sure is obvious.
Thankyou for any help.

[bpat1434]

Ah, see you are describing two different scenarios (or at least I perceive it that way).

My reply was just a general "rotation" based on the current day, assuming you had 7 posts (or a post number divisible by 7 like 14, 21, 49, 56, etc.). What you want sounds like a system to take the current week number (date('W')) and based on that to see which week should be displayed. Then using date('l') decide which specific row to show. Something like:

<?php

$weeks = array();
#           Week Number
# Row 1 --> 1, 8, 15, 22, 29, 36, 43, 50
# Row 2 --> 2, 9, 16, 23, 30, 37, 44, 51
# Row 3 --> 3, 10, 17, 24, 31, 38, 45, 52
# Row 4 --> 4, 11, 18, 25, 32, 39, 46, 53
# Row 5 --> 5, 12, 19, 26, 33, 40, 47, 54
# Row 6 --> 6, 13, 20, 27, 34, 41, 48
# Row 7 --> 7, 14, 21, 28, 35, 42, 49

// Generate the $weeks array:
for($i=1, $n=1; $i<=54; $i++, $n++)
{
    if($n==8)
    {
        $n=1;
    }

$weeks[$i] = $n;
}

$week_num = date('W'); // 1 through 52
$week_day = date('N'); // Monday, Tuesday, Wednesday...

$week = $weeks[$week_num];

$sql = "SELECT * 
FROM `table`
WHERE `colum` = '". mysql_real_escape_string($week_day.' '.$week)."'";
$result = mysql_query($query);
// ... etc ...

Hope that helps.

[kjdix]

Im really sorry if im confusing you. First time ive ever posted in a forum for this sort of help.
I think the rotation you were describing the first time is closer to what I need. I have a total of 21 rows in my database that I need to display 1 after the other and then back to the top. So I will display 1 row one day and then the next row the next day and so on. So its basically a 3 week rotation. I have fields including id(which goes from 1 to 21 and will never change), a week field(which has 1 for each day of the week and then 2 for each day of the week and then 3 for each day of the week), and a day field(which has sunday to saturday 3 times). I hope this clarifies. They will never need any more entries or rows added as they will only change the information in these 21 rows. Cheers

[Installer]

You could set up two tables. One table (e.g. "meals_start_date") would have only one row and one column, which would contain the starting date of the current 21-day cycle.

The other table (e.g. "meals") would contain 21 rows, each row having a day of the week column (Monday, Tuesday, etc.) and a sequence column (1, 2, or 3), as well as columns for your menu information (e.g. "btitle", etc.).

Then check the first table; if the date in it is more than three week past, update it. Compare the date to the current date to get the current week sequence number, and then retrieve your data by matching on the week sequence number and the current day of the week.

$pdo = new PDO($dsn, $uname, $pword);
$query = "SELECT start_date FROM meals_start_date";
$row = $pdo->query($query)->fetch();
$start_ts = strtotime($row['start_date']);
$today_ts = strtotime('today');

$update = false;
while ($today_ts >= ($ts = strtotime('+3 weeks', $start_ts))) {
    $start_ts = $ts;
    $update = true;
}
if ($update) {
    $start_date = date('Y-m-d', $start_ts);
    $query = "UPDATE meals_start_date SET start_date = '" . $start_date . "'";
    $pdo->query($query);
}

$week_num = 1;
while (strtotime('+' . $week_num . ' weeks', $start_ts) <= $today_ts) {
    $week_num++;
}
$today_dow = date('l');
$query = "SELECT btitle FROM meals 
          WHERE day_of_week = '" . $today_dow . "' AND sequence = '" . $week_num . "'";
$row = $pdo->query($query)->fetch();
echo $row['btitle'];

If the cycle is perpetual or you want to change the start date manually only, you could leave out the if ($update) block and the first "while" block, and slightly alter the second "while" block to reset $week_num to 1 when it exceeds 3, and increment a $total_weeks value to use in the comparison. You'd need to add error-checking code, of course.

[kjdix]

I included this code as necessary and got this error
Fatal error: Class 'PDO' not found in E:\wwwroot\extra\noticeboards\FinalMenu.php on line 13

What does this mean?

[Weedpacket]

kjdix wrote:

I included this code as necessary and got this error

It means you haven't enabled the [man]PDO[/man] extension in php.ini (uncomment the line "extension=php_pdo.dll" and the corresponding line below that for your DBMS).

[Installer]

If you must use the older mysql functions, it's just a matter of exchanging them in for the PDO. For example, replace the first three lines with:

$dbh = mysql_connect($host, $uname, $pword);
mysql_select_db($dbname);
$query = "SELECT start_date FROM meals_start_date"; 
$res = mysql_query($query);
$row = mysql_fetch_assoc($res);

Again, you'll need to add in error-checking.

I strongly recommend enabling PDO and using it.