How to insert from different tables?

Beginners - Learning PHP
#1 
<?php

$FirstName = $_POST['FirstName'];
$MiddleName = $_POST['MiddleName'];
$LastName = $_POST['LastName'];
$Age = $_POST['Age'];
$HomeAddress = $_POST['HomeAddress'];
$ContactNumber = $_POST['ContactNumber'];
$LastSchoolAttended = $_POST ['LastSchoolAttended'];
$Strand = $_POST ['Strand'];
$SchoolAddress = $_POST ['SchoolAddress'];
$Adviser = $_POST ['Adviser'];
$Honor1 = $_POST ['Honor1'];
$Honor2 = $_POST ['Honor2'];
$Honor3 = $_POST ['Honor3'];
$Honor4 = $_POST ['Honor4'];
$Father = $_POST ['Father'];
$EducationalFather = $_POST ['EducationalFather'];
$Mother = $_POST ['Mother'];
$EducationalMother = $_POST ['EducationalMother'];
$Guardian = $_POST ['Guardian'];
$Occupation = $_POST ['Occupation'];

if (!empty($FirstName) || !empty($MiddleName) || !empty($LastName) || !empty($Age) || !empty($HomeAddress) || !empty($ContactNumber) || !empty($LastSchoolAttended) || !empty($Strand) || !empty($SchoolAddress) || !empty($Adviser) || !empty($Honor1) || !empty($Honor2)|| !empty($Honor3)|| !empty($Honor4) || !empty($Father) || !empty($EducationalFather) || !empty($Mother) || !empty($EducationalMother) || !empty($Guardian) || !empty($Occupation))
{
	$host = "localhost";
	$dbUsername = "root";
	$dbPassword = "";
	$dbname = "studentsinformation";
}
	$conn = new mysqli($host, $dbUsername, $dbPassword, $dbname);
	
		if (mysqli_connect_error())
		{
			die('Connect Error('.mysqli_connect_errno().')'.mysqli_connect_error());
		}
		else
		{		

			$sql = "SELECT FirstName, MiddleName, LastName, LastSchoolAttended From personal Where FirstName = ? And MiddleName = ? And LastName = ? And LastSchoolAttended = ? Limit 1";

		$sqlhonor = "SELECT Honor1, Honor2, Honor3, Honor4 From honor Where Honor1 = ? And Honor2 = ? And Honor3 = ? And Honor4 = ? Limit 0";
		$sqlparent = "SELECT Father, EducationalFather, Mother, EducationalMother, Guardian, Occupation From parent Where Father = ? And EducationalFather = ? And Mother = ? And EducationalMother = ? AND Guardian = ? AND Occupation = ? Limit 0";

		$INSERTpersonal = "INSERT INTO personal (FirstName, MiddleName, LastName, Age, HomeAddress, ContactNumber, LastSchoolAttended, Strand, SchoolAddress, Adviser) values (?,?,?,?,?,?,?,?,?,?)";
		$INSERThonor = "INSERT INTO honor (Honor1,Honor2,Honor3,Honor4) values (?,?,?,?)";
		$INSERTparent = "INSERT INTO parent (Father, EducationalFather,Mother,EducationalMother,Guardian,Occupation) values (?,?,?,?,?,?)";


		$stmt = $conn->prepare($sql);
		$stmthonor = $conn->prepare($sqlhonor);
		$stmtparent = $conn->prepare($sqlparent);
		$stmt -> bind_param("ssss", $FirstName,$MiddleName,$LastName,$LastSchoolAttended);
		$stmthonor -> bind_param("ssss", $Honor1,$Honor2,$Honor3,$Honor4);
		$stmtparent -> bind_param("ssssss", $Father,$EducationalFather,$Mother,$EducationalMother, $Guardian, $Occupation);
		$stmt -> execute();
		$stmthonor -> execute();
		$stmtparent -> execute();
		$stmt -> bind_result($FirstName,$MiddleName,$LastName,$LastSchoolAttended);
		$stmthonor -> bind_result($Honor1,$Honor2,$Honor3,$Honor4);
		$stmtparent -> bind_result($Father,$EducationalFather,$Mother,$EducationalMother,$Guardian ,$Occupation );
		$stmt -> store_result();
		$stmthonor -> store_result();
		$stmtparent -> store_result();
		$rnum = $stmt -> num_rows;
		$rnum = $stmthonor -> num_rows;
		$rnum = $stmtparent -> num_rows;


		if ($rnum == 0 ) {	
			$stmt->close();
			$stmthonor->close();
			$stmtparent->close();
			$stmt = $conn -> prepare($INSERTpersonal);
			$stmthonor = $conn -> prepare($INSERThonor);
			$stmtparent = $conn -> prepare($INSERTparent);
			$stmt -> bind_param("sssisissss", $FirstName,$MiddleName,$LastName,$Age,$HomeAddress,$ContactNumber,$LastSchoolAttended,$Strand, $SchoolAddress,$Adviser);
			$stmthonor -> bind_param ("ssss", $Honor1, $Honor2, $Honor3, $Honor4);
			$stmtparent -> bind_param ("ssssss", $Father, $EducationalFather, $Mother, $EducationalMother, $Guardian, $Occupation);
			$stmt -> execute();
			$stmthonor -> execute();
			$stmtparent -> execute();
		}

		}
?>

I have my foreign key which is the ExamineeID and I have a trouble from inserting data.

#2

Breakdown what you are actually trying to do… That is a whole lot of mess and concludes with, if the insertparent fails (which is the only one that is actually run by the way) then try to do it again?

#3

i tried something, but i only got record from the personal, but from the 2 tables, no record of it

#4

Segment it all out rather than grouping it all together. And you don’t need those extra variables.

$sql = ‘’;
$prepare()
$bind()
$execute()

now the next set that needs to be inserted

$sql = ‘’;
$prepare()
$bind()
$execute()

Not,
sql
sql
sql
prepare
prepare
prepare
execute

Not only is that not how it works, you are only doing one thing in the end.

#5

I tried it. It is still the same, I can’t get an insert with the honor’s table and parent’s table. Only the personal got a record.

		$sql = "SELECT FirstName, MiddleName, LastName, LastSchoolAttended From personal Where FirstName = ? And MiddleName = ? And LastName = ? And LastSchoolAttended = ? Limit 1";
		$sqlhonor = "SELECT Honor1, Honor2, Honor3, Honor4 From honor Where Honor1 = ? And Honor2 = ? And Honor3 = ? And Honor4 = ? Limit 0";
		$sqlparent = "SELECT Father, EducationalFather, Mother, EducationalMother, Guardian, Occupation From parent Where Father = ? And EducationalFather = ? And Mother = ? And EducationalMother = ? AND Guardian = ? AND Occupation = ? Limit 0";

	if ($stmt = $conn->prepare($sql)); {

		$INSERTpersonal = "INSERT INTO personal (FirstName, MiddleName, LastName, Age, HomeAddress, ContactNumber, LastSchoolAttended, Strand, SchoolAddress, Adviser) values (?,?,?,?,?,?,?,?,?,?)";

		$stmt -> bind_param("ssss", $FirstName,$MiddleName,$LastName,$LastSchoolAttended);
		$stmt -> execute();
		$stmt -> bind_result($FirstName,$MiddleName,$LastName,$LastSchoolAttended);
		$stmt -> store_result();
		$rnum = $stmt -> num_rows;
		
		if ($rnum == 0 ) {	
			$stmt->close();
			$stmt = $conn -> prepare($INSERTpersonal);
			$stmt -> bind_param("sssisissss", $FirstName,$MiddleName,$LastName,$Age,$HomeAddress,$ContactNumber,$LastSchoolAttended,$Strand, $SchoolAddress,$Adviser);
			$stmt -> execute();	
		}
		else if ($stmthonor = $conn->prepare($sqlhonor)) {
				$INSERThonor = "INSERT INTO honor (Honor1,Honor2,Honor3,Honor4) values (?,?,?,?)";
				$stmthonor -> bind_param("ssss", $Honor1,$Honor2,$Honor3,$Honor4);
				$stmthonor -> execute();
				$stmthonor -> bind_result($Honor1,$Honor2,$Honor3,$Honor4);
				$stmthonor -> store_result();
				$rnum = $stmthonor -> num_rows;

			if ($rnum == 0 ) {	
			$stmthonor->close();
			$stmthonor = $conn -> prepare($INSERThonor);
			$stmthonor -> bind_param ("ssss", $Honor1, $Honor2, $Honor3, $Honor4);	
			$stmthonor -> execute();	
		}

		else if ($stmtparent = $conn -> prepare ($sqlparent)) {
			$INSERTparent = "INSERT INTO parent (Father, EducationalFather,Mother,EducationalMother,Guardian,Occupation) values (?,?,?,?,?,?)";
			$stmtparent -> bind_param("ssssss", $Father,$EducationalFather,$Mother,$EducationalMother, $Guardian, $Occupation);
			$stmtparent -> execute();
			$stmtparent -> bind_result($Father,$EducationalFather,$Mother,$EducationalMother,$Guardian ,$Occupation );
			$stmtparent -> store_result();
			$rnum = $stmtparent -> num_rows;

			if ($rnum == 0 ) {	
			$stmtparent->close();
			$stmtparent = $conn -> prepare($INSERTparent);	
			$stmtparent -> bind_param ("ssssss", $Father, $EducationalFather, $Mother, $EducationalMother, $Guardian, $Occupation);	
			$stmtparent -> execute();
		}
	}
}

}

#6

How are you going to do an insert with 4 values yet you list out 10 placeholders?

Turn error reporting on for the database.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($host, $dbUsername, $dbPassword, $dbname);
#7

It would seem you have a database design problem. You need to start there first. Post an SQL dump of your DB with a few sample records and we will review it.

#8

Personal Table consist of:

  • ExamineeID
  • FirstName
  • MiddleName
  • LastName
  • Age
  • HomeAddress
  • Contact Number
  • LastSchoolAttended
  • Strand
  • SchoolAddress
  • Adviser

Honor Table consist of:

  • HonorID
  • Honor1
  • Honor2
  • Honor3
  • Honor4
  • ExamineeID (FK Referenced by Personal Table)

Parent Table consist of:

  • ParentID
  • Father
  • EducationalFather
  • Mother
  • EducationalMother
  • Guardian
  • Occupation
  • ExamineeID (FK Referenced by Personal Table)
#9

It doesn’t say anything, no errors. Also, I based it in here

	$sql = "SELECT FirstName, MiddleName, LastName, LastSchoolAttended From personal Where FirstName = ? And MiddleName = ? And LastName = ? And LastSchoolAttended = ? Limit 1";
#10

Thats a start, but I did sql “SQL DUMP” along with sample records. By the way, get rid of the age column. That should be calculated on the fly when you need it.

Anytime you have consecutive column numbering that is a red flag that you have a design problem. Learn about Database Normalization.

1 Like
#11

I just followed the needed information of the school, that’s why age is in there or the information that needed to provide is way much long XD

#12

This table for instance should be more like this,

  • HonorID
  • Honor
  • ExamineeID (FK Referenced by Personal Table)

and then have as many records as you need to fulfill what it needed.

2 Likes
#13

Copy that, but I still have a problem from inserting though. The problem is, for example, if I am filling the needed information, I want them to be distributed to their own tables, but still have the connection to each other (which is why I assigned the FK), but in my situation, they aren’t distributing at all, and only the personal table was filled, while the others, it is blank.

#14

You are probably getting a php/mysql error. Do you have php’s error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your system, so that php would help you?

#15

I tried it
mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);

And it doesnt show anythinfg :frowning:

#16

That line of code causes the mysqli extension to use exceptions for errors. To see the resultant error information, php’s error_reporting and display_errors must be set to the values I mentioned in my reply.

#17

I am having an error right now and it says:

Uncaught mysqli_sql_exception: Cannot add or update a child row: a foreign key constraint fails (studentsinformation.honor, CONSTRAINT honor_ibfk_1 FOREIGN KEY (ExamineeID) REFERENCES personal (ExamineeID) ON DELETE CASCADE ON UPDATE CASCADE) in C:\xampp\htdocs\Practice\dummy.php:74 Stack trace: #0 C:\xampp\htdocs\Practice\dummy.php(74): mysqli_stmt->execute() #1 {main} thrown in C:\xampp\htdocs\Practice\dummy.php on line 74

I tried changing the brackets and separated it fully by if instead of using else if and now I am receiving error like that. My code now:

if (mysqli_connect_error())
	{
		die('Connect Error('.mysqli_connect_errno().')'.mysqli_connect_error());
	}
	else{
		

		$sql = "SELECT FirstName, MiddleName, LastName, LastSchoolAttended From personal Where FirstName = ? And MiddleName = ? And LastName = ? And LastSchoolAttended = ? Limit 1";
		$sqlhonor = "SELECT Honor1, Honor2, Honor3, Honor4 From honor Where Honor1 = ? And Honor2 = ? And Honor3 = ? And Honor4 = ? Limit 0";
		$sqlparent = "SELECT Father, EducationalFather, Mother, EducationalMother, Guardian, Occupation From parent Where Father = ? And EducationalFather = ? And Mother = ? And EducationalMother = ? AND Guardian = ? AND Occupation = ? Limit 0";

	if ($stmt = $conn->prepare($sql)); {

		$INSERTpersonal = "INSERT INTO personal (FirstName, MiddleName, LastName, Age, HomeAddress, ContactNumber, LastSchoolAttended, Strand, SchoolAddress, Adviser) values (?,?,?,?,?,?,?,?,?,?)";

		$stmt -> bind_param("ssss", $FirstName,$MiddleName,$LastName,$LastSchoolAttended);
		$stmt -> execute();
		$stmt -> bind_result($FirstName,$MiddleName,$LastName,$LastSchoolAttended);
		$stmt -> store_result();
		$rnum = $stmt -> num_rows;
		
		if ($rnum == 0 ) {	
			$stmt->close();
			$stmt = $conn -> prepare($INSERTpersonal);
			$stmt -> bind_param("sssisissss", $FirstName,$MiddleName,$LastName,$Age,$HomeAddress,$ContactNumber,$LastSchoolAttended,$Strand, $SchoolAddress,$Adviser);
			$stmt -> execute();	
		}
	}
	 if ($stmthonor = $conn->prepare($sqlhonor)) {
				$INSERThonor = "INSERT INTO honor (Honor1,Honor2,Honor3,Honor4) values (?,?,?,?)";
				$stmthonor -> bind_param("ssss", $Honor1,$Honor2,$Honor3,$Honor4);
				$stmthonor -> execute();
				$stmthonor -> bind_result($Honor1,$Honor2,$Honor3,$Honor4);
				$stmthonor -> store_result();
				$rnum = $stmthonor -> num_rows;

			if ($rnum == 0 ) {	
			$stmthonor->close();
			$stmthonor = $conn -> prepare($INSERThonor);
			$stmthonor -> bind_param ("ssss", $Honor1, $Honor2, $Honor3, $Honor4);	
			$stmthonor -> execute();	 //LINE 74
		}
	}

		if ($stmtparent = $conn -> prepare ($sqlparent)) {
			$INSERTparent = "INSERT INTO parent (Father, EducationalFather,Mother,EducationalMother,Guardian,Occupation) values (?,?,?,?,?,?)";
			$stmtparent -> bind_param("ssssss", $Father,$EducationalFather,$Mother,$EducationalMother, $Guardian, $Occupation);
			$stmtparent -> execute();
			$stmtparent -> bind_result($Father,$EducationalFather,$Mother,$EducationalMother,$Guardian ,$Occupation );
			$stmtparent -> store_result();
			$rnum = $stmtparent -> num_rows;

			if ($rnum == 0 ) {	
			$stmtparent->close();
			$stmtparent = $conn -> prepare($INSERTparent);	
			$stmtparent -> bind_param ("ssssss", $Father, $EducationalFather, $Mother, $EducationalMother, $Guardian, $Occupation);	
			$stmtparent -> execute();
		}
	}

}

#18

So it is referencing this insert. ExamineeID is a required field to insert and you are not providing it so the query fails to execute.

#19

I inserted the ExamineeID to the honor but still in the bind_param of preparing the INSERT, the ExamineeID and I am having an error that the column cannot be null.

#20

What was the code you used for that?

Sponsor our Newsletter | Privacy Policy | Terms of Service