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 13 of 13
  1. #1
    New Coder
    Join Date
    Jul 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation Help with drop down menus (was:Database relationship help!)

    Ok guys I for the life of me cant figure this out, I read about foreign keys and joining tables etc but I really need some help. I will show you my coding and if someone can help me out that would be great!

    Ok, I am trying to build a database system to keep track of busdrivers and how much this company owes them. So what I have done is I have made 3 tables. One for the driver and info like name, address, city etc. number 2 table with sales info etc. and the third = more slaes info. So what I am trying to figure out is how on lets say forms to be able to choose the driver and add sales info to it? So here are my tables:

    Database name: Bus

    Table name: drivers
    _______________________________________________________________
    Columns: PID | last_name | first_name | middle | addy | city | state | zip |
    -------------------------------------------------------------------------
    Rows: 01 | anderson | John | a | blah | blah|blah | blah|
    ________________________________________________________________

    Table name: sales
    _____________________________________________________________
    Columns: CID | date | sale_amount | percent | total |
    ----------------------------------------------------------------------
    Rows: 01 |04/87 | 100.00 | 5 | 5.00 |
    ______________________________________________________________

    php code to insert data in Table drivers:

    PHP Code:
    <?php
    $con 
    mysql_connect("localhost""root""blah");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }
    mysql_select_db("Bus"$con);$sql="INSERT INTO drivers (first_name, last_name, middle,
      addy, city, state, zip, phone, ssn)
    VALUES
    ('$_POST[first_name]','$_POST[last_name]','$_POST[middle]
    ','$_POST[addy]','$_POST[city]','$_POST[state]','$_POST[zip]
    ','$_POST[phone]','$_POST[ssn]')"
    ;if (!mysql_query($sql,$con))
      {
      die(
    'Error: ' mysql_error());
      }
    echo 
    "New driver record added";mysql_close($con)

    ?>
    Form to add data into Table drivers:

    Code:
    <style>
    label { position: absolute; text-align:right; width:130px; }
    input, textarea { margin-left: 140px; }
    label.check, label.radio { position:relative; text-align:left; }
    </style>
    <html>
    <head>
    <title> The Internet Bus Driver Database </title>
    </head>
    <body>
    
    <form action="insert.php" method="post">
    <label for="last_name">Last Name:</label>
    <input type="text" name="last_name" id="last_name" /><br />
    
    <label for="first_name">First Name:</label>
    <input type="text" name="first_name" id="first_name" /><br />
    
    <label for="middle">Middle:</label>
    <input type="text" name="middle" id="middle" /><br />
    
    <label for="addy">Address:</label>
    <input type="text" name="addy" id="addy" /><br />
    
    <label for="city">City:</label>
    <input type="text" name="city" id="city" /><br />
    
    <label for="state">State:</label>
    <input type="text" name="state" id="state" /><br />
    
    <label for="zip">Zip:</label>
    <input type="text" name="zip" id="zip" /><br />
    
    <label for="phone">Phone:</label>
    <input type="text" name="phone" id="phone" /><br />
    
    <label for="ssn">SSN:</label>
    <input type="text" name="ssn" id="ssn" /><br />
    
    <input type="submit" />
    
    </form>
    
    
    
    
    </body>
    </html>
    Ok now what I cant figure out is how to make a form to insert sales data specifiacly to lets say john anderson. Becuase I am trying to figure out a form to where it shows a drop down menu of the names I have added and I click on it then the form appears for sales and you type in sales info and wala sales info has been added to for examp john anderson! But I cant find anywhere on the net to make a relationship between tables: drivers & sales. and if I could figure that out then how to I add info to the sales table related to a driver via a form? Please someone help me. I know most dont want to waste there time and some want to get paid but I really need help. I cant figure this out. And if someone even knows a website specifically for something like this that would be so much help thank you! please if there are any questions then please ask!

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You need to store the driver ID in the sales table. That will connect the driver to the sale, and it's called a "foreign key". It's a foreigner to the sales table, and it's the key of the driver table. Foreign key.

    Then on the form, build your <select> tag using the driver names between <option> and </option> but use the driver ID as the value of each option. When the form is submitted you can then take the value of that select element and stick it in your sales table in the driver ID column.

    FYI this kind of question falls under the topic "database design", which if you google that phrase you will find tons of info. Here's one:

    http://www.samspublishing.com/articl...?p=102167&rl=1
    Last edited by Fumigator; 07-25-2007 at 04:56 AM.

  • #3
    New Coder
    Join Date
    Jul 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fumigator you are the best! you are the first person that has been able to explain something like this in plain english. I have read most bookes but they always seem so complicated and on top of that they never really explained how to put it into a form anyways, lol. Thanks man I appreciate it!

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Glad it helped! just post again if you have any other questions.

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    two other points for you

    1) you show a date column above but a date like 04/87 you are better off to use an actual date type for the column and not a varchar/char and store dates in the format of 1987-04-01 for example. Doing it that way you can take advantage of all date functions that are not available to you in the other format.

    for instance you want all sales for April, with your method you could try to use a LIKE match but for the year you would need LIKE '%87' which would force a full table scan, whereas with proper dates you won't

    2) a minor point, but in your question above you said you had three tables but showed only two. Hope you figured out what you needed for the third table as well

  • #6
    New Coder
    Join Date
    Jul 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured if I can figure out the first 2 than I can figure out the 3rd. Thanks for the date structure, I will be sure to apply it!

    Ok so I have an update of coding I need checked over cuase its not working grrr. This is supposed to be for the drop down menue of the names.

    PHP Code:
    <?php
    // Connects to the database
    $con=mysql_connect("localhost""root""blah");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
     } 
    mysql_select_db("Bus"$con);   
      
      
    $query  "SELECT last_name, first_name FROM drivers";
    $result mysql_query($query);

    while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        echo 
    "<form>";
        echo 
    "Name :<option value="">{$row['last_name, first_name']}</option> <br>" ;
        echo 
    "</form>";
           
          



    ?>
    ok so you guys are probably gonna laugh cuase I probably made a huge mistake easy for you guys to catch but remember I am still trying to make sense of it all.

    ok and I added the FOREIGN KEY via this mysql code:

    Code:
    ALTER TABLE sales 
    ADD FOREIGN KEY (drivers_DID) REFERENCES drivers(DID)
    oh and even when that drop down menu does work how do I make it choose the id when clicked on the name? ah im so confused, sorry for all the trouble!

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    I'll move this thread over to the PHP area. Folks there are much better versed in the ins-and-outs of PHP code. They will be able to help you with your drop downs.

  • #8
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    You put the form tags inside your loop. This is not what you want to do, since all the code inside the loop will be printed out for each record. You want to put the form tag, and, incidentally, the select tag, which you left out, outside the loop, and only put the option tags inside the loop:

    PHP Code:
    <?php
    // Connects to the database
    $con=mysql_connect("localhost""root""blah");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
     } 
    mysql_select_db("Bus"$con);   
      
      
    $query  "SELECT last_name, first_name FROM drivers";
    $result mysql_query($query);
    echo 
    "<form>";
    echo 
    "<select name=\"driver_name\">";
    while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        echo 
    "<option value=\"PUT THE DRIVER ID HERE\">{$row['last_name, first_name']}</option>";
    }
    echo 
    "</select></form>";
    ?>
    That code will add a new option for each row the code loops through, but it will only print out one form tag and select tag.

    You may also want to consider adding (before the loop) a blank option so that the first driver isn't selected by default:
    <option>Select A Driver</option>

    Also, if you put the driver's id inside the value attribute for the option tag, the form will send the selected drivers ID when submitted.

    Feel free to post more questions

    HTH
    Dan
    Last edited by whizard; 07-25-2007 at 05:55 PM. Reason: Code typos
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #9
    New Coder
    Join Date
    Jul 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh wow you have been a huge help Thank you so much! Ok so far everything works, I did as you suggested and added the choose driver option before the loop! Looks great! I am almost finished! Its people like you who make this world so much better! Thanks Guys! Now on to my last couple of question!

    Code:
    echo "<option value=\"PUT THE DRIVER ID HERE\">{$row['last_name, first_name']}</option>";
    where you said put the driver id there I dont understand, I need it to pull that from the database so it can send it!

    Next thing and last thing is now I need to have my form interact with that so when I pull up the sales entry form I will click on the driver I want it to add the info to and type in the form the sales information click submit and have it saved in the sales table related to that driver? Im sorry guys I am not much smarter but all I have ever done was simple form processing.

  • #10
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    in your query, select the 'id' field along with the first and last name fields, and then use it to fill the 'value' attribute for each option, much the same way you printed out the first and last name for each driver.

    Then, when the form is submitted, it will send the selected drivers id to the form processing script.

    HTH
    Dan
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION

  • #11
    New Coder
    Join Date
    Jul 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok so here is the new code that I have:

    PHP Code:
    <?php
    // Connects to the database
    $con=mysql_connect("localhost""root""blah");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
     } 
    mysql_select_db("Bus"$con);   
      
      
    $id "SELECT DID FROM drivers";
      
    $query  "SELECT last_name, first_name FROM drivers";
      
    $result mysql_query($query);

    echo 
    "<form>";
    echo 
    "<select name=\"driver_name\">";
    echo 
    "<option>Choose Driver:</option>";

    while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        echo 
    "<option value=\"$id\">{$row['last_name, first_name']}</option>";
    }
    echo 
    "</select></form>";
    ?>
    So I take it that the variable $id now goes in perfect sying with the list it produces of the names! Thanks a bunch again. Now all I need to know now is how to submit it properly to the sales table and have connected to the driver.

    Out of a wild guess and thinking I believe I make the form processor to store $id into sales table in the row that is the FOREIGN KEY of the drivers table.

    But if that is how you do it then how do I bring up the information of the drivers name with the sales information? aka using both tables. Does that make sense? By the way you guys are the most helpfull forums I have been on. Most people get mad at me and therefore get me discouraged in learning.

  • #12
    New Coder
    Join Date
    Jul 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    dang, I cant figure out how to grab the info from the drop down menu into my table. here is my insert code snippet:

    PHP Code:
    <?php
    $con 
    mysql_connect("localhost""root""blah");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      
      }
    mysql_select_db("Bus"$con);$sql="INSERT INTO sales (date, bus, sales,
      percent, meals, drivers_DID)
    VALUES
    ('$_POST[date]','$_POST[bus]','$_POST[sales]
    ','$_POST[percent]','$_POST[meals]','$_POST[$drivers_DID]')"
    ;if (!mysql_query($sql,$con))
      {
      die(
    'Error: ' mysql_error());
      }
    echo 
    "New Sales record added";mysql_close($con)

    ?>
    and then this is the form snippet:

    PHP Code:
    <?php
    // Connects to the database
    $con=mysql_connect("localhost""root""blah");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
     } 
    mysql_select_db("Bus"$con);   
      
      
    $id "SELECT PID FROM drivers";
      
    $query  "SELECT last_name, first_name, PID FROM drivers";
      
    $result mysql_query($query);

     
    //added
    echo "<form action=\"sales_insert.php\" method=\"post\">";
    echo 
    "<select name=\"drivers_DID\" id=\"drivers_DID\">";
    echo 
    "<option>Choose Driver:</option>";
    //original
    while($row mysql_fetch_array($resultMYSQL_ASSOC))
    {

        echo 
    "<option value=\"$id\">{$row['last_name, first_name'}</option>";
    Grrrrr what am I doing wrong?

  • #13
    Senior Coder whizard's Avatar
    Join Date
    Jan 2005
    Location
    Philadelphia, PA, USA
    Posts
    1,662
    Thanks
    14
    Thanked 76 Times in 76 Posts
    First, you can get rid of
    $id = "SELECT PID FROM drivers";

    That's not doing anything.

    Then, change

    echo "<option value=\"$id\">{$row['last_name, first_name'}</option>";

    to

    echo "<option value=\"{$row['PID']}\">{$row['last_name, first_name'}</option>";

    That should get your select box to be perfect.

    As to your question about selecting the driver using the foreign id in the sales table, check out this link for a good tutorial on what I think you're trying to do.

    Also, as for getting the infor from the dropdown box, it should be in

    $_POST['drivers_DID']
    not
    $_POST[$drivers_DID]

    HTH
    Dan
    PHP Tip: If you want to use short tags (<? or <?=$var) then make sure short_open_tag is set to "1". It really helps.

    Don't forget to save everyone time and mark your thread as Resolved :)

    "Also note that it is your responsibility to die() if necessary."

    DON'T USE THE MYSQL_ EXTENSION


  •  

    Posting Permissions

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