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 4 of 4
  1. #1
    New Coder
    Join Date
    Jun 2011
    Posts
    32
    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
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,696
    Thanks
    25
    Thanked 657 Times in 656 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.

    "If you leave hydrogen alone, for long enough, it begins to think about itself."

  • Users who have thanked sunfighter for this post:

    Msuth (07-18-2014)

  • #3
    New Coder
    Join Date
    Jun 2011
    Posts
    32
    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
    27,613
    Thanks
    80
    Thanked 4,635 Times in 4,597 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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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