...

View Full Version : Help with drop down menus (was:Database relationship help!)



emuse
07-25-2007, 03:26 AM
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
$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:


<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! :confused:

Fumigator
07-25-2007, 04:53 AM
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/articles/article.asp?p=102167&rl=1

emuse
07-25-2007, 05:04 AM
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!

Fumigator
07-25-2007, 05:14 AM
Glad it helped! just post again if you have any other questions.

guelphdad
07-25-2007, 01:34 PM
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

emuse
07-25-2007, 03:57 PM
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
// 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($result, MYSQL_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:


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!:eek:

guelphdad
07-25-2007, 05:02 PM
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.

whizard
07-25-2007, 05:51 PM
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
// 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($result, MYSQL_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

emuse
07-25-2007, 10:30 PM
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!


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.

whizard
07-26-2007, 12:43 AM
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

emuse
07-26-2007, 01:07 AM
Ok so here is the new code that I have:


<?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($result, MYSQL_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.:thumbsup:

emuse
07-26-2007, 03:27 AM
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
$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
// 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($result, MYSQL_ASSOC))
{

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

Grrrrr what am I doing wrong?

whizard
07-26-2007, 03:50 AM
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 (http://www.tizag.com/mysqlTutorial/mysqljoins.php) 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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum