Composite Keys in PHP

Beginners - Learning PHP
#1

Hello! I am new to PHP. I have a composite keys (FirstName, MiddleName, LastName, LastSchoolAttended) How can I limit it to 1 and never repeat it. I also think I have a problem with the syntax in binding parameter. I am having an error with $stmt = $conn->prepare ($SELECT) at the moment. This is my code:

$SELECT = "SELECT FirstName, MiddleName, LastName, LastSchoolAttended From information Where FirstName = ? And MiddleName = ? And LastName = ? And LastSchoolAttended = ? Limit 1";
		$INSERT	= "INSERT Into information (FirstName, MiddleName, LastName, Age, HomeAddress, ContactNumber, LastSchoolAttended) values (?,?,?,?,?,?,?)"
		$stmt = $conn->prepare($SELECT);
		$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($INSERT);
			$stmt -> bind_param("sssisis", $FirstName,$MiddleName,$LastName,$Age,$HomeAddress,$ContactNumber,$LastSchoolAttended);
			$stmt -> execute();
		}
How to direct to the same page if the key is the same and direct to another page if it is no duplicate in php?
#2

Use the query:

CREATE UNIQUE INDEX info_idx 
    ON information(FirstName, MiddleName, LastName, LastSchoolAttended);

This will enforce uniqueness on each row in the table.

From a design point of view, this is probably a bad idea: There’s no guarantee that the real world won’t give you a duplicate row at some point.

#3

We were doing a system on school and the school wanted to have a basis which is the First Name, Middle Name and Last Name, and Last School Attended. This is for the entrance examination so it will avoid repeated taking of exams. And, what I want is that, I want the First Name, Middle Name, Last Name, and Last School Attended to be limit to only 1.

#4

Fair enough. That query should work for you then.

#5

Another question: is my syntax for binding parameter is right? and should i code that into my phpmyadmin or to my phpcode?

#6

That depends what $conn is. Are you using PDO or something else?

#7

I am using something else and it is mysqli.

#8

The posted code contains a php syntax error, due to a missing ; on the $INSERT = … line, which produces an error like -

Parse error: syntax error, unexpected '$stmt' (T_VARIABLE) in ... the line with the 1st prepare(...) call

Does that mean you have already defined/created a composite key for those columns? Note: this should also be defined as being UNIQUE, not just as an index.

If you are asking about the sql query that skawid posted, you would execute that once, using phpmyadmin.

After you get the unique composite key setup in your database table, your php code would just attempt to insert the data, without first trying to select it, then detect if the query produces a duplicate key error.

#9

I am having a Fatal error for the bind_parameter, in this part

$stmt -> bind_param("ssss", $FirstName,$MiddleName,$LastName,$LastSchoolAttended);
#10

yeah, so maybe you solve it? nobody knows which error you got.

Sponsor our Newsletter | Privacy Policy | Terms of Service