0

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))

Community
  • 1
  • 1
Johan
  • 69
  • 2
  • 10
  • You will need to create an html page, probably with a form element in it--though you could use ajax if you are comfortable with javascript. Do you have any code yet? – AlexMA Apr 04 '12 at 16:48
  • I don't have any code yet because I simply have no idea where to start. I know how to make a basic drop down box in html, but the options should be retrieved from the teams table. – Johan Apr 04 '12 at 17:05
  • You can create the page but use PHP variables instead of static text for your option and write those – AlexMA Apr 04 '12 at 18:05

4 Answers4

3

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.

Satyajitsinh Raijada
  • 382
  • 1
  • 11
  • That would be a way of doing it, but I want the drop down box to use the options that are already in the database. It's already working when I add a match in phpMyAdmin, so It shouldn't be too hard to get this to work on a frond end page. – Johan Apr 04 '12 at 17:26
  • can you please add your working code. this thing will generate options from database only. or just elaborate what actual issue is. – Satyajitsinh Raijada Apr 05 '12 at 04:18
2

This question is a bit broad and alot has to be cover prior to nailing the result, its almost asking how to build a website and that in itself could take years to learn. That said, these 2 links should give you what you need to get going.

W3C Forms

W3C MySql introduction and tutorials

Please note how much code is behind phpmyadmin (just go through its source code) and you will find there is no simple 1 click way off doing it.

Edhen
  • 345
  • 4
  • 15
0

you may try something like

<?php
$db = "database_name";
$con = mysql_connect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db($db, $con);

$query = "SELECT * FROM YOUR_TABLE";
$result = mysql_query($query);
?>

Also, in your html body part, inside your form, you may use this for generating a dropdown box

<select>
<?php
while($info = mysql_fetch_array($result)){
$name = $info["table_column_name"];
echo '<option value="'.$name.'">'.$name.'</option>';
}
?>
</select>

Hope this helps.. :)

Alfred
  • 21,058
  • 61
  • 167
  • 249
  • This worked, I'm still getting an error when I'm submitting the data though. I'll try and fix this error in a new question, this one is getting a bit off-topic. :) – Johan Apr 05 '12 at 21:31
0

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))

This is all you need to know, blah blah....foreign key constraint fails blah bla....REFERENCES teams

You are updating matches, but since matches uses foreign keys to teams you cannot add something in matches that is not in teams

joschua011
  • 4,157
  • 4
  • 20
  • 25
  • That kind of makes sense, but what would be the solution for this? Adding team_home and team_away to the teams table wouldn't wouldn't make any since they are different for every match. – Johan Apr 05 '12 at 10:19
  • check if the entry is existent in teams, if so - ok, if not write it, and then write the entry in matches – joschua011 Apr 05 '12 at 10:26
  • I have now added team_home and team_away to the teams table. In the matches table i have linked team_home to team_home (teams table) and team_away (teams table). It's now giving the following 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` (`team_home`)). Weird thing is that it's not giving any error when I add a match in phpmyadmin, it just works. – Johan Apr 05 '12 at 10:41
  • are you kidding me? add the entry and not the column. undo this, it makes no sense.use the structure you posted before. OK.....lets say you want to add a match where team_home is "darwin" and team_away is "lenny", then you must check if both teams are already in teams (as primary key, in teams.name) before writing te entry in matches, you cannot write a foreign key in matches if there is no appropriate primary key in temas, got it? – joschua011 Apr 05 '12 at 10:52
  • Ok, I've recovered the original structure. In table teams the column ID (AI) is set as the primary key, name is set as an index. Does that mean that I have to delete the column ID and set name as the primary key? – Johan Apr 05 '12 at 11:17