Insert values in table with foreign key in php

Question

I am new to PHP and SQL. I simply want to register a user after a team of users has been created. Tables that i have used -

team

idTeam int(11) PK + AI
teamName varchar(25)

user

idUser int(11) PK + AI
name varchar(30)
username varchar(30)
password varchar(30)
team int(11) FK

I have used the following code-

<?php
session_start();
include('conn.php');

$name=$_POST['name'];
$team=$_POST['team'];
$username=$_POST['username'];
$password=$_POST['password'];

$qry="SELECT * FROM team WHERE idTeam='$team";

if(mysqli_query($con,$qry)){
  mysqli_query("INSERT INTO user(name, team, username, password)VALUES('$name', '$team', '$username', '$password')");

  header("location: add_user.php?remarks=success"); 
  mysqli_close($con);
}
else 
mysqli_error($con); 
?>

i used to get error- Mysql error 1452 - Cannot add or update a child row: a foreign key constraint failsMysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

Example - I have pre-entered the contents of team table-

idTeam - teamName
  1      Arsenal
  2      Chelsea
  3      Liverpool

Now if i want to add a user then I would add him by entering in user table-

idUser   team   name   username  password
  1        2    abc    root      pass

So here i am unable to figure out what query should i use in PHP code?

please dont use the `mysql_` database extension any more. As you are obviously learning spend your time learning `PDO` or `MYSQLI_` [It will save you time in the long run,](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — RiggsFolly, Nov 02 '15 at 12:42
You have also tried to MIX `mysql_` and `mysqli_` database extensions. **THAT DOES NOT WORK** Stick to `MYSQLI_` or `PDO` — RiggsFolly, Nov 02 '15 at 12:44
It might be a good idea if you also showed us the code that **connects this script to the database** Are you using `mysql_` or `mysqli_` for that?? — RiggsFolly, Nov 02 '15 at 12:45
i am using mysqli and i have tested the database connection using a dummy database with no Foreign Key. — Kaus, Nov 02 '15 at 12:50
_i am using mysqli_ **yes sometimes but not all the time** You are querying the `team` table using `mysql_query()` and **never actually processing the result set**. To be honest the code is a mess and needs completely refactoring. Look at you `PHP Error Log` there should be plenty of clues to what wrong in there. Alternatively add error reporting to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, Nov 02 '15 at 12:52

Gayan Fernando · Accepted Answer · 2015-11-02T13:01:04.650

1

There is a single quotation within your first sql query near idTeam='$team. idTeam is integer type. So it need not within single quotation. Make sure that you are passing value for $team variable that is exist in team table. Try following code.

$name=$_POST['name'];
$team=$_POST['team'];
$username=$_POST['username'];
$password=$_POST['password'];

$qry="SELECT * FROM team WHERE idTeam=$team";
$result = mysqli_query($qry);
$num_rows = mysqli_num_rows($result);
if($num_rows > 0){
    mysqli_query("INSERT INTO user(name, team, username, password)VALUES('$name', $team, '$username', '$password')");
}else{
    echo "Team is not valid!!!";
}
mysqli_close($con);
header("location: add_user.php?remarks=success");

edited Nov 02 '15 at 13:01

answered Nov 02 '15 at 12:49

Gayan Fernando

581
1
7
23

1

You too have not spotted the use of `mysql_` sometimes and `mysqli_` at other times – RiggsFolly Nov 02 '15 at 12:57
Thanks for the guide.Fixed that.:) – Gayan Fernando Nov 02 '15 at 13:01

score 0 · Answer 2 · answered Nov 02 '15 at 12:36

0

There needs to be exist a record in the team table that corresponds to the value being passed to $team

answered Nov 02 '15 at 12:36

Parris Varney

11,320
12
47
76

yes i have multiple records stored in team table. Now i want to add user in user table. what query should i use? – Kaus Nov 02 '15 at 12:38
If you provide the contents of a record from the team table, then I can make you an example insert query – Parris Varney Nov 02 '15 at 12:40
contents of team table- idTeam - 1, 2, 3 teamName - Arsenal, Chelsea, Liverpool Now if i want to add a user then I would add him by entering- idUser - 1 team - 2 name - abc username - root password - pass – Kaus Nov 02 '15 at 12:48
That should work, maybe the foreign key is improperly implemented. Can you also provide the table schema with `show create table user` – Parris Varney Nov 02 '15 at 13:27

score 0 · Answer 3 · answered Nov 02 '15 at 12:36

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.

It will reject any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table.

https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Insert values in table with foreign key in php

3 Answers3