How can I make a web form with drop down boxes to insert data into a relational database

Question

I'm busy trying to create a website for my football team. The thing I'm having problems with is creating a web form with drop down boxes to select and insert the match data. I'm already able to add a match in phpmyadmin where I can just select team_home and team_away, so the relational database seems to work.

I've got the following 2 tables:

Teams

• id (pk - ai)
• name

Matches

• id (pk - ai)
• date
• team_home (foreign key -> table teams field name)
• team_away (foreign key -> table teams field name)
• score_home
• score_away

So how can I make a web form with drop down boxes so I can add matches into my database?

UPDATE:

I've got the form working with drop down boxes, but I'm getting the following error when I'm submitting the form:

Error: Cannot add or update a child row: a foreign key constraint fails (roflz.matches, CONSTRAINT matches_ibfk_1 FOREIGN KEY (team_home) REFERENCES teams (name))

I've posted my submit form code and insertmatch.php code

Submit form code

$sql="SELECT id, name FROM Teams";
$result=mysql_query($sql);

$options="";

while ($row=mysql_fetch_array($result)) {

$id=$row["id"];
$name=$row["name"];
$optionshometeam.="<OPTION VALUE=\"$id\">".$name;
$optionsawayteam.="<OPTION VALUE=\"$id\">".$name;
}
?>

<form action="insertmatch.php" method="post">
<SELECT NAME=Teams>
<OPTION VALUE=0>Home Team
<?=$optionshometeam?>
</SELECT> 
<SELECT NAME=Teams>
<OPTION VALUE=0>Away team
<?=$optionsawayteam?>
</SELECT>
Score Home team: <input type="text" name="score_home" />
Score Away team: <input type="text" name="score_away" />
Match Date: <input type="text" name="score_away" />
<input type="submit" />
</form>

insertmatch.php code

mysql_select_db("roflz", $con);

$sql="INSERT INTO matches (team_home, team_away, score_home, score_away, date)
VALUES
('$_POST[team_home]','
$_POST[team_away]','
$_POST[score_home]',' 
$_POST[score_away]'
$_POST[date]')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Match added";

mysql_close($con);
?>

So what's causing this error?

Error: Cannot add or update a child row: a foreign key constraint fails (roflz.matches, CONSTRAINT matches_ibfk_1 FOREIGN KEY (team_home) REFERENCES teams (name))

Answer 1

okay you have very basic problem. i don't know much about php but i can suggest you some logical thing which you can perform.

<select>
  <option value="t1">Team 1</option>
  <option value="t2">Team 2</option>
  <option value="t3">Team 3</option>
  <option value="t4">Team 4</option>
</select> 

this will create drop down box. What you need to do is to set your teams id(using php) in "value" and team name between "option" tag. The "value" of the particular selected team will be passed in request when you submit your form.

ok try out this..

<?
...
mysql cnx code
...

$sql="SELECT id, name FROM Teams";
$result=mysql_query($sql);

$options="";

while ($row=mysql_fetch_array($result)) {

    $id=$row["id"];
    $name=$row["name"];
    $options.="<OPTION VALUE=\"$id\">".$name;
}
?>
...
html code
...

<SELECT NAME=Teams>
<OPTION VALUE=0>Choose
<?=$options?>
</SELECT> 

but don't forget to wrap it with in the "form" tag.