0

I'm having a little trouble with duplicate keys. I'll start with my code.

// Prepare to select all liabilities
$stmt = $con->query("TRUNCATE TABLE `Students`");
$stmt = $con->prepare("SELECT student_no, legal_first_name, legal_surname, oen_number FROM csv_students");
$stmt->execute();
$stmt->bind_result($sid, $fname, $lname, $oen);
$stmt->store_result();
$updt = $con->prepare("INSERT INTO Students (Student_ID, Last_Name, First_Name, OEN) VALUES (?, ?, ?, ?) ON DUPLICATE KEY DELETE FROM `Students` WHERE OEN = ?") or die(mysql_error($con)); // Prepare to insert a new liability
$updt->bind_param("ssss", $sid, $lname, $fname, $oen, $oen) or die("Not sending var");  // Bind variables to the result of the query
while($stmt->fetch()){
    $updt->execute() or die(mysqli_error($con)); // Execute the query
}
$updt->close() or die("Not closing after send"); // Close the statement
$stmt->free_result();
$stmt->close() or die("Not closing after collection");

So I'm trying to get the variables from the first table and insert them into the second, but there's about four entries per student (one for each class). Basically, I'm getting them all and then trying to say "on a duplicate key, delete the last entry that has the same oen number. It's giving me the error "Warning: mysql_error() expects parameter 1 to be resource, object given in /var/www/ride/admin_settings/csv/students/update.php on line 23" which is on the line where we see the ON DUPLICATE KEY piece of code. Anyone have any idea what's going on?

Nathan Pringle
  • 15
  • 2
  • It does not look like your code is using `mysql_` functions, which means this wont work `die(mysql_error($con))` – datasage May 22 '13 at 17:42
  • You shouldn't be using `mysql_error()` - did you mean `mysqli_error($con)`, or something similar? – andrewsi May 22 '13 at 17:42
  • Sorry, the error it's giving me now is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM `Students` WHERE OEN = ?' at line 1 – Nathan Pringle May 22 '13 at 17:44

1 Answers1

1

You're mixing mysql and mysqli together.

You're using

or die(mysql_error($con));

in line 7. When it should be 

or die(mysqli_error($con));

It also came to my attention that you're doing

"ssss", $sid, $lname, $fname, $oen, $oen

So you're expecting 5 parameters but you only assume 4? If you're expecting a number instead of a string in the end then you should be adding a d in there.

Jonast92
  • 4,964
  • 1
  • 18
  • 32
  • The error it's giving me now is: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM `Students` WHERE OEN = ?' at line 1 – Nathan Pringle May 22 '13 at 17:44
  • Try to add a d in the "ssss" string if OEN is a number, s if it's a string. – Jonast92 May 22 '13 at 17:46
  • I've still got the exact same error after adding both an s and a d – Nathan Pringle May 22 '13 at 17:50