Hi I am building my first website and need a statement to delete any records from 2 tables which contain a Patient_ID that the user specifies:
I have tried
mysql_query("DELETE FROM appointment
WHERE Patient_ID='$patid'
UNION
DELETE FROM patient
WHERE Patient_ID='$patid'");
and also
mysql_query("DELETE FROM appointment INNER JOIN patient
WHERE Patient_ID='$patid'");
would really appreciate some help with this!
thanks in advance
I'd rather do a StoredProcedure for the two delete statements.
DELIMITER $$
CREATE PROCEDURE DeletePatient(IN PatientID INT)
BEGIN
DELETE FROM appointment WHERE Patient_ID = PatientID;
DELETE FROM patient WHERE Patient_ID = PatientID;
END $$
DELIMITER ;
and on your php script,
mysql_query("CALL DeletePatient($patid)");
As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.
It is possible to do in one query:
$query = "DELETE FROM appointment, patient USING patient INNER JOIN appointment ON (patient.patient_id = appointment.patient_id) WHERE patient.patient_id = '" . intval($patid) . "'";
However, in order to maintain data integrity, the best option here would be to define a foreign key constraint on your appointment table which references your patient table and contains an ON DELETE CASCADE clause.
easy
mysql_query("DELETE FROM appointment WHERE Patient_ID='$patid'");
mysql_query("DELETE FROM patient WHERE Patient_ID='$patid'");
don't forget to format your $patid first:
$patid = mysql_real_escape_string($patid);
EDIT:
As Mark Baker pointed out in an above statement, the best way is if foreign keys are defined properly on the table. Assuming that they are not, however, they should be wrapped into a transaction.
$query1 = "DELETE FROM appointment WHERE Patient_ID='".intval($patid)."'";
$query2 = "DELETE FROM patient WHERE Patient_ID='".intval($patid)."'";
mysql_query("START TRANSACTION", $yourConnection);
mysql_query("BEGIN", $yourConnection);
mysql_query($query1, $yourConnection);
mysql_query($query2, $yourConnection);
mysql_query("COMMIT", $yourConnection);
Original Answer:
The only way to do this is to run two separate DELETE commands.
$query1 = "DELETE FROM appointment WHERE Patient_ID='".intval($patid)."'";
$query2 = "DELETE FROM patient WHERE Patient_ID='".intval($patid)."'";
