Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: How to capture Mondays & Thursdays in drop down list from MySQL DB

    Hello!

    For my App I have to have Mondays & Thursdays only in a drop down list
    from a mysql db.

    it should look something like this Monday 10/11/2004 mm/dd/yyyy format.

    Any immediate Help is highly appreciated.

    Thanks,
    Sean

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select date_format(datecolumn,"%W %m/%d/%Y") as showdate
      from yourtable
     where dayofweek(datecolumn) in (2,5)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Rudy,

    Thanks! so much for a prompt reply. My question is...
    I have entered dates Mondays & Thursdays only for the time being in DATE format yyyy-mm-dd...how do I get MySQL db to populate my table with these Mondays & Thursdays only in the first place...into the drop down list...I guess something like capturing todays date from MySQL DB should do the trick...?? how do i do that...also I want to display only future dates...meaning I dont want to display Monday,10/04/2004 since its already has passed...so current list of Mondays & Thursdays for the Rest of the year only should be displayed...

    Any Help Pls?

    Thanks again,
    Sean

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, that's a different question, you gave me the impression that these dates were already stored in the database

    do you acvtually want to store the dates?

    because you can generate what you want for the dropdown using only a scripting language, e.g. asp, jsp, coldfusion, php, ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2004
    Posts
    7
    Rudy,

    Thanks! Again. I am a newbie to PHP & MySQL DB. My guess is the best way...is to generate...them through...php ...anyways can u pls look at my proj descrip & help me know...what is the best way to do it & how to? I would really appreciate your Help!.

    Thanks,
    Sean


    This is a simple delivery of a variety of foods to each home
    I gave you a detailed descrip of the whole application...I will really
    appreciate If you could help me with this one.

    ------------------------------------------------------------------------

    Screen 1: Enter User Name & Password:|******|

    If Pass is correct take to screen 2

    else redirect to screen 1 for a correct password.
    I have done this Log in Screen...

    Screen 2:

    Homes: drop down list of Homes 1 to 25

    Delivery Dates: Mondays and Thursdays only(This drop down list should
    contain only Monday, 10/11/2004 (MM/DD/YYYY format and also similarly
    for Thursdays).How to get these dates in a drop down list? My guess is
    it is good to do a client side generation of dates...If so how do I do that?

    Goods will be delivered corresponding Monday only If atleast an order
    is placed by previous Friday 12 PM (for Ex 10/08 for a delivery of
    10/11) likewise will be delivered Thursday only If an order is placed
    before previous wednesday 12 PM.

    Once a User chooses let's say Home1 and 10/11/2004. If a record
    already exists for this combination it should populate the Info in
    a form(Screen 3) from MySQL Database.

    Homes should be grabbed from a Homes Table & Delivery Dates from Orders Table .

    Essentially screen 2 & 3 exist on same page

    Screen 3:

    Goods:
    ------
    Item Measurement Quantity
    -------------------------------------
    Dairy

    Reg Milk Gallons 3

    Skim Milk Gallons 3

    Choc Milk Gallons 3



    Frozen
    ------

    Icecream Boxes 3


    Submit Button Cancel Button

    There should be a client side validation for MAX num of foods one can order
    at any given time for each Home...lets say MAX limit is 5 gallons of milk for
    each home for one order/transaction for a given date. So, If someone tries
    to enter 6 in quantity then it should flag sorry you can enter only upto 5.Usually MAX qty per food item is unique to a food item & its home. For example for Home1 5 gallons of skim milk, 3 gallons of chocolate milk & 3 loafs of bread are MAX qtys for these food items for Home1.

    If a record like above does not exist A New Record should be created.
    If a record like the above appears when a Home1 and 10/11 is chosen
    from drop down lists then user should be able to modify these quantitys
    and hit submit button.

    After that a corresponding message like a a NEW order has been placed or
    Your Order has been modified should appear.

    Also in screen 1: a Link for Report should be provided.When one clicks
    on Report again...a password should be asked for...or we can enter
    report in password in screen 1 which will take us to a Report.


    Essentially report would be...

    A summary of Foods Delivery Report By Htore for a particular day with Grand total
    of each food quantity reqd.

    Goods Delivery Report
    -----------------------

    Home1 Dairy
    --------

    Reg Milk 3

    Skim Milk 3

    Choc Milk 3



    Frozen
    --------
    Icecream 3

    Likewise for each Home...

    and a Grand Total of some thing like 100 candy boxes
    100 Cereals etc...


    should be delivered on 10/11/2004.

    ------------------------------------------------------------------------

    #
    # Table structure for table `foods`
    #
    CREATE TABLE foods (
    Food_ID varchar(5) NOT NULL default '' PRIMARY KEY,
    Category varchar(5) NOT NULL,
    Food_Name varchar(25) NOT NULL default ''
    ) TYPE=MyISAM;

    # Table structure for table `homes`
    #
    CREATE TABLE homes (
    Home_Name varchar(25) NOT NULL default '' PRIMARY KEY,
    Driver_ID tinyint(2) NOT NULL default '0'
    ) TYPE=MyISAM;

    # Table structure for table `login`
    #
    CREATE TABLE login (
    username varchar(12) NOT NULL default '',
    password varchar(12) NOT NULL default '',
    name varchar(25) default NULL,
    PRIMARY KEY (username,password)
    ) TYPE=MyISAM COMMENT='Login Table for Dietary Application';

    #
    # Table structure for table `max_qty_limit_order`
    #
    CREATE TABLE max_qty_limit_order (
    Home_Name varchar(25) NOT NULL default '',
    Food_ID varchar(5) NOT NULL default '',
    Max_Qty tinyint(4) NOT NULL default '0',
    PRIMARY KEY (Home_Name,Food_ID)
    ) TYPE=MyISAM;
    # --------------------------------------------------------
    #

    #
    # Table structure for table `Order`
    #

    CREATE TABLE order (
    Order_ID INT(5) NOT NULL default 101,
    Home_Name varchar(25) NOT NULL default '',
    Delivery_Date date NOT NULL default '0000-00-00',
    Submission_Name varchar(40) NOT NULL default '',
    Actual_Date datetime NOT NULL default '0000-00-00 00:00:00',
    PRIMARY KEY (Order_ID,Delivery_Date,Home_Name)

    ) TYPE=MyISAM;

    # Table structure for table `order_transac`

    CREATE TABLE order_transac (
    Order_ID INT(5) NOT NULL default 101,
    Food_ID varchar(5) NOT NULL default '',
    Qty tinyint(4) NOT NULL default'',
    PRIMARY KEY (Order_ID,Food_ID)

    ) TYPE=MyISAM;


    Regards,
    Sean

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't do php so you may want to ask how to generate the dropdown in the php forum

    i did not look at your tables too closely, except to notice that you use the table name order, which is a bad idea, because order is a reserved word
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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