[RESOLVED] DB Error: Cannot add or update a child row: a foreign key constraint fails

[shozza87]

DB Error: Cannot add or update a child row: a foreign key constraint fails (f6069939/players, CONSTRAINT players_ibfk_1 FOREIGN KEY (teamid) REFERENCES team (teamid))

hi i am now getting this error from my database is this an error due to my foreign keys being in the wrong order my database would i have to type my database into here to show you and this is my php code

<?php
require ("securelogin.php");
$username = $_SESSION['username'];
?>

<?php
//Insert managers into users table
//users Table 
##### Table Structure #######
## playerid 	Firstname  	Surname		Position	Number    teamid ####
include "common.php";
//make connection to database
DBConnect();

$playerid = NULL;
$firstname = $_REQUEST['firstname'];
$surname = $_REQUEST['surname'];
$position = $_REQUEST['position'];
$number = $_REQUEST['number'];
$action = $_REQUEST['action'];

if(!$firstname&&!$surname){
$message = "Both Form Fields Not Filled In!";
header("location: enterplayers.php?message=$message");
}elseif(!$firstname){
$message ="You did not fill in the first name form field";
header("location: enterplayers.php?message=$message");
}elseif(!$surname){
$message = "You did not fill in the surname form field";
header("location: enterplayers.php?message=$message");
}else{$Link = mysql_connect($Dhost, $Duser, $Dpassword);

//Prevent MySQL injection

$Query1 = "SELECT username, teamid FROM $tbl_user WHERE teamid = '$teamid'";
$res1 = mysql_db_query ($DBName, $Query1, $Link);
// Query is inserting data into the database using the Values from the from.
$Query2 = "INSERT into $tbl_player values ('$playerid', '$firstname', '$surname', '$position', '$number', '$teamid')";
$res2 = mysql_db_query ($DBName, $Query2, $Link);

if ($res1 === true && $res2 === true){

   //Created successfully
   $message = "Player was entered into your team please enter another";

}else{ // something went wrong in mysql_query()

   $message = "Player could not be created! please try again";

   echo 'DB Error: ' . mysql_error(); // get error info
   mysql_close($Link);
   exit(); // stop the script
}
}
?>

thanks for looking and any comments are welcome to help me thanks

[anakadote]

Make sure that every column is represented in your query if you're not explicitly listing them. So your $tbl_player column should have exactly 6 columns. The alternative is to list the columns in your query so that each row does not have to be listed:

INSERT into $tbl_player (playerid, firstname, surname, position, number, teadid) values ('$playerid', '$firstname', '$surname', '$position', '$number', '$teamid')

And if you have playerid set at the primary key and is auto-incrementing, then you don't need to include that in your query, so:

INSERT into $tbl_player (firstname, surname, position, number, teadid) values ('$firstname', '$surname', '$position', '$number', '$teamid')

[halojoy]

Think it is enough if you show
your query used to "CREATE TABLE" $tbl_player
There we can see how you set PRIMARY KEY and those other KEYS
like FOREIGN KEYS.

[anakadote]

I also don't see where $teamid is being set, which could be the issue.

[shozza87]

query 1 gets the teamid from the session i when the manager is logged in it selectes the teamid from the users table or at least thats what i want it to do 😕

and i use phpmyadmin but i dont no how to view my constraints to show you my foreign keys

admin
Field Type Null Default Comments MIME
username varchar(100) No
password varchar(15) No

competition
Table comments: InnoDB free: 26624 kB

Field Type Null Default Comments MIME
compid int(11) No
compname varchar(50) No
minteams int(11) No
maxteams int(11) No
comptype varchar(30) No

entercomp
Table comments: InnoDB free: 26624 kB; (teamid) REFER f6069939/entercomp(teamid); (`compid

Field Type Null Default Links to Comments MIME
teamid int(11) No entercomp -> teamid
compid int(11) No competition -> compid

players
Table comments: InnoDB free: 26624 kB; (teamid) REFER f6069939/team(teamid)

Field Type Null Default Links to Comments MIME
playerid int(11) No
firstname varchar(50) No
surname varchar(50) No
position varchar(5) No
number int(11) No
teamid int(11) No team -> teamid

team
Table comments: InnoDB free: 26624 kB; (teamid) REFER f6069939/user(teamid)

Field Type Null Default Links to Comments MIME
teamid int(11) No user -> teamid
teamname varchar(150) No

user
Table comments: InnoDB free: 26624 kB

Field Type Null Default Comments MIME
username varchar(300) No
password varchar(15) No
name varchar(150) No
teamid int(11) No

well this is the only way i think i could show you how they all go hope this helps and many thanks for looking 🙂

[shozza87]

Hi there i know what is wrong with my code and it is query 1

$Query1 = "SELECT username, teamid FROM $tbl_user WHERE teamid = '$teamid'";
$res1 = mysql_db_query ($DBName, $Query1, $Link); 

I want this query to get the users teamid which is unique for each user as they have diffrent teams is there a way that i could check which user is logged in then do a query to select there teamid many thanks and thanks for looking

[anakadote]

I want this query to get the users teamid which is unique for each user as they have diffrent teams is there a way that i could check which user is logged in then do a query to select there teamid many thanks and thanks for looking

Maybe it's just me, but I'm having a hard time deciphering what this 'sentence' is asking for in relation to your previous questions. Also, what's the point of selecting the teamid from the database if you already know it?

[shozza87]

when my user logs in there username is stored in the session

i then want my query to get the username from the session and then select the users teamid from the database

hope this makes sense lol

[anakadote]

I'd do it in reverse, store the user id in a session and then get the user info from that. Or better yet, store both in a session. But your query as you're asking for it would be:

SELECT userid from users where username = '{$_SESSION['username']}' limit 1

[shozza87]

well i was trying to do this

$q = "SELECT * FROM " . $tbl_user . " WHERE username = '" . $_SESSION['username'] . "'teamid" = "$teamid";

my PHP skills are very poor lmao thanks for your help