Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Jun 2011
    Posts
    35
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Retrieve records on certain week and day

    Hi all,

    I am making a website for my daughters school using Wordpress.

    I would like a feature that displays "todays" school lunches in the sidebar.

    The menu consists of 6 options (2 starters, 2 mains and 2 puddings) per day, 5 days a week on a 4 weekly cycle (1,2,3,4,1 etc.)

    I have created a DB called test with a table called lunch that has 43 columns (Week, MonStrt1, MonStrt2, MonMain1, MonMain2, MonPud1, MonPud2 - repeated for Tuesday-Sunday)

    Now I have set up a page, where by changing the Week and manually changing the Mon - Tue etc I can display the days menu.

    I would like advice on how to do this automatically (so after the Sunday of week 4 it knows to display the Monday of Week 1)

    I'd also like for anyyime the database returns a "null" to print the text "School Closed Today"

    This is my php page:
    Code:
    <head>
    <title>Lunch Menu Testing</title>
    </head>
    
    <?PHP
    
    $user_name = "lunch";
    $password = "";
    $database = "test";
    $server = "localhost";
    
    $db_handle = mysql_connect($server, $user_name, $password);
    $db_found = mysql_select_db($database, $db_handle);
    
    if ($db_found) {
    
    $SQL = "SELECT * FROM lunch where week=1";
    $result = mysql_query($SQL);
    
    while ( $db_field = mysql_fetch_assoc($result) ) {
    
    echo "<p style=color:blue;>Starters</p>";
    print $db_field['MonStrt1'] . "<BR>";
    print $db_field['MonStrt2'] . "<BR>";
    
    echo "<p style=color:blue;>Main Courses</p>";
    print $db_field['MonMain1'] . "<BR>";
    print $db_field['MonMain2'] . "<BR>";
    
    echo "<p style=color:blue;>Desserts</p>";
    print $db_field['MonPud1'] . "<BR>";
    print $db_field['MonPud2'] . "<BR>";
    
    }
    
    mysql_close($db_handle);
    
    }
    else {
    
    print "Database NOT Found ";
    mysql_close($db_handle);
    
    }
    
    ?>
    This is my "lunch" table
    Code:
    -- phpMyAdmin SQL Dump
    -- version 4.0.9
    -- http://www.phpmyadmin.net
    --
    -- Host: 127.0.0.1
    -- Generation Time: Jul 17, 2014 at 11:26 AM
    -- Server version: 5.5.34
    -- PHP Version: 5.4.22
    
    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    
    --
    -- Database: `test`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `lunch`
    --
    
    CREATE TABLE IF NOT EXISTS `lunch` (
      `Week` int(11) NOT NULL,
      `MonStrt1` text,
      `MonStrt2` text,
      `MonMain1` text,
      `MonMain2` text,
      `MonPud1` text,
      `MonPud2` text,
      `TueStrt1` text,
      `TueStrt2` text,
      `TueMain1` text,
      `TueMain2` text,
      `TuePud1` text,
      `TuePud2` text,
      `WedStrt1` text,
      `WedStrt2` text,
      `WedMain1` text,
      `WedMain2` text,
      `WedPud1` text,
      `WedPud2` text,
      `ThuStrt1` text,
      `ThuStrt2` text,
      `ThuMain1` text,
      `ThuMain2` text,
      `ThuPud1` text,
      `ThuPud2` text,
      `FriStrt1` text,
      `FriStrt2` text,
      `FriMain1` text,
      `FriMain2` text,
      `FriPud1` text,
      `FriPud2` text,
      `SatStrt1` text,
      `SatStrt2` text,
      `SatMain1` text,
      `SatMain2` text,
      `SatPud1` text,
      `SatPud2` text,
      `SunStrt1` text,
      `SunStrt2` text,
      `SunMain1` text,
      `SunMain2` text,
      `SunPud1` text,
      `SunPud2` text,
      PRIMARY KEY (`Week`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `lunch`
    --
    
    INSERT INTO `lunch` (`Week`, `MonStrt1`, `MonStrt2`, `MonMain1`, `MonMain2`, `MonPud1`, `MonPud2`, `TueStrt1`, `TueStrt2`, `TueMain1`, `TueMain2`, `TuePud1`, `TuePud2`, `WedStrt1`, `WedStrt2`, `WedMain1`, `WedMain2`, `WedPud1`, `WedPud2`, `ThuStrt1`, `ThuStrt2`, `ThuMain1`, `ThuMain2`, `ThuPud1`, `ThuPud2`, `FriStrt1`, `FriStrt2`, `FriMain1`, `FriMain2`, `FriPud1`, `FriPud2`, `SatStrt1`, `SatStrt2`, `SatMain1`, `SatMain2`, `SatPud1`, `SatPud2`, `SunStrt1`, `SunStrt2`, `SunMain1`, `SunMain2`, `SunPud1`, `SunPud2`) VALUES
    (1, 'Week1<br>Monday Starter1', 'Week1<br>Monday Starter2', 'Week1<br>Monday Main1', 'Week1<br>Monday Main2', 'Week1<br>Monday Pud1', 'Week1<br>Monday Pud2', 'Week1<br>Tuesday Starter1', 'Week1<br>Tuesday Starter2', 'Week1<br>Tuesday Main1', 'Week1<br>Tuesday Main2', 'Week1<br>Tuesday Pud1', 'Week1<br>Tuesday Pud2', 'Week1<br>Wednesday Starter1', 'Week1<br>Wednesday Starter2', 'Week1<br>Wednesday Main1', 'Week1<br>Wednesday Main2', 'Week1<br>Wednesday Pud1', 'Week1<br>Wednesday Pud2', 'Week1<br>Thursday Starter1', 'Week1<br>Thursday Starter2', 'Week1<br>Thursday Main1', 'Week1<br>Thursday Main2', 'Week1<br>Thursday Pud1', 'Week1<br>Thursday Pud2', 'Week1<br>Friday Starter2', 'Week1<br>Friday Starter2', 'Week1<br>Friday Main1', 'Week1<br>Friday Main2', 'Week1<br>Friday Pud1', 'Week1<br>Friday Pud2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (2, 'Week2<br>Monday Starter1', 'Week2<br>Monday Starter2', 'Week2<br>Monday Main1', 'Week2<br>Monday Main2', 'Week2<br>Monday Pud1', 'Week2<br>Monday Pud2', 'Week2<br>Tuesday Starter1', 'Week2<br>Tuesday Starter2', 'Week2<br>Tuesday Main1', 'Week2<br>Tuesday Main2', 'Week2<br>Tuesday Pud1', 'Week2<br>Tuesday Pud2', 'Week2<br>Wednesday Starter1', 'Week2<br>Wednesday Starter2', 'Week2<br>Wednesday Main1', 'Week2<br>Wednesday Main2', 'Week2<br>Wednesday Pud1', 'Week2<br>Wednesday Pud2', 'Week2<br>Thursday Starter1', 'Week2<br>Thursday Starter2', 'Week2<br>Thursday Main1', 'Week2<br>Thursday Main2', 'Week2<br>Thursday Pud1', 'Week2<br>Thursday Pud2', 'Week2<br>Friday Starter2', 'Week2<br>Friday Starter2', 'Week2<br>Friday Main1', 'Week2<br>Friday Main2', 'Week2<br>Friday Pud1', 'Week2<br>Friday Pud2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (3, 'Week3<br>Monday Starter1', 'Week3<br>Monday Starter2', 'Week3<br>Monday Main1', 'Week3<br>Monday Main2', 'Week3<br>Monday Pud1', 'Week3<br>Monday Pud2', 'Week3<br>Tuesday Starter1', 'Week3<br>Tuesday Starter2', 'Week3<br>Tuesday Main1', 'Week3<br>Tuesday Main2', 'Week3<br>Tuesday Pud1', 'Week3<br>Tuesday Pud2', 'Week3<br>Wednesday Starter1', 'Week3<br>Wednesday Starter2', 'Week3<br>Wednesday Main1', 'Week3<br>Wednesday Main2', 'Week3<br>Wednesday Pud1', 'Week3<br>Wednesday Pud2', 'Week3<br>Thursday Starter1', 'Week3<br>Thursday Starter2', 'Week3<br>Thursday Main1', 'Week3<br>Thursday Main2', 'Week3<br>Thursday Pud1', 'Week3<br>Thursday Pud2', 'Week3<br>Friday Starter2', 'Week3<br>Friday Starter2', 'Week3<br>Friday Main1', 'Week3<br>Friday Main2', 'Week3<br>Friday Pud1', 'Week3<br>Friday Pud2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (4, 'Week4<br>Monday Starter1', 'Week4<br>Monday Starter2', 'Week4<br>Monday Main1', 'Week4<br>Monday Main2', 'Week4<br>Monday Pud1', 'Week4<br>Monday Pud2', 'Week4<br>Tuesday Starter1', 'Week4<br>Tuesday Starter2', 'Week4<br>Tuesday Main1', 'Week4<br>Tuesday Main2', 'Week4<br>Tuesday Pud1', 'Week4<br>Tuesday Pud2', 'Week4<br>Wednesday Starter1', 'Week4<br>Wednesday Starter2', 'Week4<br>Wednesday Main1', 'Week4<br>Wednesday Main2', 'Week4<br>Wednesday Pud1', 'Week4<br>Wednesday Pud2', 'Week4<br>Thursday Starter1', 'Week4<br>Thursday Starter2', 'Week4<br>Thursday Main1', 'Week4<br>Thursday Main2', 'Week4<br>Thursday Pud1', 'Week4<br>Thursday Pud2', 'Week4<br>Friday Starter2', 'Week4<br>Friday Starter2', 'Week4<br>Friday Main1', 'Week4<br>Friday Main2', 'Week4<br>Friday Pud1', 'Week4<br>Friday Pud2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL),
    (5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    Working demo: Lunch Menu Testing

    Now I know, you're probably thinking I'm a MySQL whizz - try not to laugh too much!

    This is my second foreay into MySQL (I once created a site that displayed a random Simpsons quote) so I'm clearly not too great.

    Any advice would be welcomed!

  • #2
    Master Coder
    Join Date
    Jan 2011
    Location
    Washington
    Posts
    5,419
    Thanks
    26
    Thanked 748 Times in 746 Posts
    You don't need43 columns. All you need is a column for the 6 options and the date.
    Then do a table row for lunch cycle day giving it a real date. Then you can easily find today's date and extract the info. Then update the date by adding 28 days to it.
    Evolution - The non-random survival of random variants.

    Because I love FLEXBOX and tired of waiting: http://davidwalsh.name/goodbye-vendor-prefixes

  • Users who have thanked sunfighter for this post:

    Msuth (07-18-2014)

  • #3
    New Coder
    Join Date
    Jun 2011
    Posts
    35
    Thanks
    13
    Thanked 0 Times in 0 Posts
    You sir, are a lot smarter than me haha!

    I'll see how I get on!

    Thanks very much for your help!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,435
    Thanks
    85
    Thanked 4,912 Times in 4,873 Posts
    While Sunfighter is right about the db design, the truth is that it's probably not worth reworking at this time.

    And it's not hard to automate this. Just change the one line:
    Code:
    $SQL = "SELECT * FROM lunch WHERE week = "
         .    " 1 + MOD( FLOOR( DATEDIFF( CURDATE(), '2014-7-13' ) / 7 ), 4 )";
    Replace 2014-7-13 with Sunday in the actual VERY FIRST week of the school year (or whatever week starts cycle 1).

    Not sure what you do about vacations: May need to restart the cycle after vacation, depending on what the school does.
    Be yourself. No one else is as qualified.

  • #5
    New Coder
    Join Date
    Jun 2011
    Posts
    35
    Thanks
    13
    Thanked 0 Times in 0 Posts


    Hello again folks.

    After doing very little with this last time, I've decided to come back and try again.

    The newest iteration of this project can be seen here I've also tried to get all fancy and set up a GitHub (apologies for the README.md I'm not sure how to format it)

    What I'm struggling with is how to display Today's Menu when the page initially loads.

    Now using the code on this page I can echo a list of all dates between X and Y repeating every 28 days

    PHP Code:
    <?php

    // Set timezone
    date_default_timezone_set('UTC');

    // Define today's date
    $Today date("o-m-d");

    // Define Week 1 Day 1 START DATE
    $StartWeek1Day1 date ("2015-03-05");

    //Define menu END DATE
    $MenuEnd date("2050-12-31");

    while (
    strtotime($StartWeek1Day1) <= strtotime($MenuEnd)) {
    echo 
    "$StartWeek1Day1 <br>";
    $StartWeek1Day1 date ("Y-m-d"strtotime("+28 day"strtotime($StartWeek1Day1)));
    }

    ?>
    What I don't know how to do is execute a specific line of code on any of the given day inside the loop. In my head it would go something like
    PHP Code:
    if $Today Any of the dates in the $StartWeek1Day1 loop {
        include 
    'week1-mobile-1.php';

    and I'd just repeat this loop 19 more times for the following start dates

    PHP Code:
    $StartWeek1Day2 "2015-03-17";
        include 
    'week1-mobile-X.php';
    $StartWeek1Day3 "2015-03-18";
        include 
    'week1-mobile-X.php';
    $StartWeek1Day4 "2015-03-19";
        include 
    'week1-mobile-X.php';
    $StartWeek1Day5 "2015-03-20";
        include 
    'week1-mobile-X.php';

    $StartWeek2Day1 "2015-03-23";
        include 
    'week2-mobile-X.php';
    $StartWeek2Day2 "2015-03-24";
        include 
    'week2-mobile-X.php';
    $StartWeek2Day3 "2015-03-25";
        include 
    'week2-mobile-X.php';
    $StartWeek2Day4 "2015-03-26";
        include 
    'week2-mobile-X.php';
    $StartWeek2Day5 "2015-03-27";
        include 
    'week2-mobile-X.php';

    $StartWeek3Day1 "2015-03-30";
        include 
    'week3-mobile-X.php';
    $StartWeek3Day2 "2015-03-31";
        include 
    'week3-mobile-X.php';
    $StartWeek3Day3 "2015-04-01";
        include 
    'week3-mobile-X.php';
    $StartWeek3Day4 "2015-04-02";
        include 
    'week3-mobile-X.php';
    $StartWeek3Day5 "2015-04-03";
        include 
    'week3-mobile-X.php';

    $StartWeek4Day1 "2015-04-06";
        include 
    'week4-mobile-X.php';
    $StartWeek4Day2 "2015-04-07";
        include 
    'week4-mobile-X.php';
    $StartWeek4Day3 "2015-04-08";
        include 
    'week4-mobile-X.php';
    $StartWeek4Day4 "2015-04-09";
        include 
    'week4-mobile-X.php';
    $StartWeek4Day5 "2015-04-10";
        include 
    'week4-mobile-X.php'
    Again, any help with this would be appreciated. I spent a couple of months doing the codecademy PHP course (I generally can't commit a lot of time to learning this even though I enjoy it) but I really feel like I'm banging my head against the wall here!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,435
    Thanks
    85
    Thanked 4,912 Times in 4,873 Posts
    Banging every bone in your body again the wall is more like it! You have taken something that is truly very very simple and turned it into a mega-project!

    Did you stick with your original database table design? Or did you go with a more rational design?

    Let's assume you went with a more rational design:
    Code:
    CREATE TABLE lunch (
        week INT,
        day INT,
        item VARCHAR(20),
        itemNum INT,
        itemName VARCHAR(50)
    );
    // example of a table dump:
    week day item    itemNum itemName
    1    1   starter 1       Calimari rings
    1    1   starter 2       Side salad
    1    1   main    1       Beef Wellington
    1    1   main    2       Lamb chops
    1    1   pudding 1       Chocolate
    1    1   pudding 2       Figgy
    1    2   starter .. etc. ...
    ...
    1    5
    2    1
    ...
    4    2
    So now you simply do
    Code:
    SELECT * FROM lunch WHERE week = 3 AND day = 2
    (for example) to get all the menu items for any given day.

    To figure out which week is week, see my post #4. And see also my question/warning about school holidays.
    Be yourself. No one else is as qualified.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,435
    Thanks
    85
    Thanked 4,912 Times in 4,873 Posts
    And this is impoassible:
    Code:
    $StartWeek1Day1 = date ("2015-03-05");
    Today is not a MONDAY!

    Remember what I wrote: Week 1 Day 1 must be the FIRST MONDAY of the calendar you will use. For 2015, that would be January 5th.

    And if indeed Jan 5, 2015, is to be your week 1, then you don't even care about this in your PHP code! Let your SQL code figure it out!

    Code:
    SELECT * FROM lunch 
    WHERE `week` = ( ( WEEK(CURDATE()) % 4 ) + 1 ) 
      AND `day` = ( DAYOFWEEK(CURDATE()) - 1 )
    The code is a tad more complex if you choose some other Monday as the beginning of week 1, but not horribly so.
    Be yourself. No one else is as qualified.

  • #8
    New Coder
    Join Date
    Jun 2011
    Posts
    35
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Sorry the 2015-03-05 was for testing as it was the current date. The first Week1Monday is 2015-03-05

    I understand that all I need to do is SELECT * FROM Lunch WHERE Week=1 AND Day=1

    I don't know how to extract them on the specific date.

    I'll look more into the CURDATE you've mentioned.

  • #9
    New Coder
    Join Date
    Jun 2011
    Posts
    35
    Thanks
    13
    Thanked 0 Times in 0 Posts
    CURDATE method worked.

    Thanks for the help.


  •  

    Posting Permissions

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