SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Post Using LookUp Table to Assign Data

    I am really confused. I am trying to do a website that will display the current soups for the day. I am currently having trouble with the backend system where the admin will enter a date and select (checkbox) the soups available for that particular date.

    MySQL:

    Daily Table

    +----+--------+
    | ID | Date |
    +----+--------+
    | 1 |03-04-21|
    +----+--------+

    SoupLookup

    +-----+-----+
    | DID | SID |
    +-----+-----+
    | 1 | 1 |
    +-----+-----+
    | 1 | 2 |
    +-----+-----+

    Soups

    +----+---------------------+
    | ID | SoupName |
    +----+---------------------+
    | 1 | SoupType1 |
    +----+---------------------+
    | 2 | SoupType2 |
    +----+---------------------+

    Basically, I am using the same structure Kevin did with his joke database. Each date ID will be assigned multiple soup ID's in the SoupLookup table.

    I need to do just a few simple things, but am really confused and can't seem to get a grasp on it. #1. I need to be able to add a new date, and "assign" to that date different soups. My problem is the basic syntax of these commands and queries. I am totally new to PHP and it is my first language, other than HTML. #2. I need to be able to edit and delete the dates.

    I think I will be fine for editing and deleting the dates, as I can see the formating with Kevin's Joke database. PLEASE! ANY HELP IS APPRECIATED! I am looking for the basic syntax for assigning soups to dates.

    THANKS SO MUCH!
    Last edited by JeffWalden; Apr 22, 2003 at 20:51.
    TAKE A WALK OUTSIDE YOUR MIND.

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your "Daily Soup" table could look something like this:

    fldDay of type DATE
    fldSoupId of type SMALLINT (<- depending on how many soups you want to plan for) ZEROFILL, no primary key

    Then you enter all your soups for the day ie:
    Code:
      fldDay   |   fldSoupId
    2003-04-21 	 001
    2003-04-21	 012
    2003-04-21	 005
    2003-04-22	 125
    2003-04-22	 025
    2003-04-22	 009
    Then you look up the date and retrieve the soups:
    Code:
    SELECT S.fldSoupName, D.fldSoupId FROM tblSoups as S, tblDaily as D
    		   WHERE
    		   D.fldDay = the date AND
    		   S.fldSoupId = D.fldSoupId
    Give that a try.
    John

  3. #3
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I am having problems. The code I have is:

    PHP Code:
    $show =  @mysql_query("SELECT S.SoupID, D.SoupId FROM Soups as S, DailySoups as D
         WHERE
         D.Day = the date AND
         S.SoupID = D.SoupId"
    );
    if (!
    $show) {
    echo(
    "<p>Error performing query: " mysql_error() . "</p>");
    exit();
    }
         
    echo(
    $show); 
    The error message I am getting is:

    PHP Code:
    Error performing queryYou have an error in your SQL syntax near 'date AND S.SoupID = D.SoupId' at line 3 
    I can't seem to figure out what is wrong here. Complete newbie at this, sorry this is probably a dumb question.
    TAKE A WALK OUTSIDE YOUR MIND.

  4. #4
    SitePoint Zealot oivaf's Avatar
    Join Date
    Apr 2003
    Location
    Mexico
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if the code you've shown here is the real application code, then this is wrong:
    Code:
    $show =  @mysql_query("SELECT S.SoupID, D.SoupId FROM Soups as S, DailySoups as D
         WHERE
         D.Day = the date AND
         S.SoupID = D.SoupId" );
    Notice the red part of the code; mmm you have a column named Day in your DailySoups table right?

    Now suppose that in the form you're using to enter the date you have an input tag and its name is "the_date" then the correct code to make the query would be:
    PHP Code:
    $show =  @mysql_query("SELECT S.SoupID, D.SoupId FROM Soups as S, DailySoups as D
         WHERE
         D.Day = 
    $the_date AND S.SoupID = D.SoupId" );
    // but you would do better if you use the superglobals i.e. $_POST['the_date'] 
    if, after this, you're still having problems just ask

  5. #5
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Post Syntax Error

    I am still getting this error:

    PHP Code:
    Error performing queryYou have an error in your SQL syntax near 'AND S.SoupID = D.SoupId' at line 3 
    with this code:

    PHP Code:
    $show =  @mysql_query("SELECT S.SoupID, D.SoupId FROM Soups as S, DailySoups as D 
         WHERE 
         D.Day = 
    $newdate AND S.SoupID = D.SoupId" ); 
    Why is it better to use a superglobal like the one you mentioned above? What will that do for me?
    TAKE A WALK OUTSIDE YOUR MIND.

  6. #6
    SitePoint Zealot oivaf's Avatar
    Join Date
    Apr 2003
    Location
    Mexico
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi !
    if the variable $newdate is a string, for example 2003-04-23 (just an example ok :P ) you have to enclose it in quotes like this:
    PHP Code:
    $show =  @mysql_query("SELECT S.SoupID, D.SoupId FROM Soups as S, DailySoups as D 
         WHERE 
         D.Day = '
    $newdate' AND S.SoupID = D.SoupId" ); 
    or by using the $_POST vars
    PHP Code:
    $show =  @mysql_query("SELECT S.SoupID, D.SoupId FROM Soups as S, DailySoups as D 
         WHERE 
         D.Day = '" 
    $_POST['newdate'] . "' AND S.SoupID = D.SoupId" ); 
    and to answer your question: the only thing that superglobals are going to do for you is complicate your life , just kidding; there are a lot (too many) of posts that deal with your questions. But basically is about security and performance of your scripts and here are some links:

    PHP and MySQL tips
    http://www.sitepointforums.com/showt...threadid=54074

    Security Tips
    http://www.sitepointforums.com/showt...hreadid=104107

    PHP manual: security and register globals
    http://www.php.net/manual/en/securit...terglobals.php

    just do a search in the forums and prepare to be glued to your computer for the next days, really mucho interesting information !

  7. #7
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a real basic question:
    Are all your "SoupId" fields spelled out as "SoupId" or do you have "SoupID" in some tables (note the difference in capitalization)?
    John

  8. #8
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    It is always stated "SoupID" with both the I and D caped.
    TAKE A WALK OUTSIDE YOUR MIND.

  9. #9
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason I ask is because you have in your queries that you posted BOTH formats: "SoupId" and "SoupID".

    That may be the source of your trouble because database, table, and field names are case sensitive.
    John


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
  •