php / mysql how to delete fields with same name from different tables

Question

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

This is where cascading foreign keys is such a wonderful feature of a relational database — Mark Baker, Feb 14 '13 at 13:20
If you do use two separate delete statements, make sure you're wrapping them in a transaction — Mark Baker, Feb 14 '13 at 13:22

score 4 · Answer 1 · edited May 23 '17 at 10:32

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.

How to prevent SQL injection in PHP?

score 1 · Answer 2 · answered Feb 14 '13 at 13:19

1

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);

answered Feb 14 '13 at 13:19

Your Common Sense

156,878
40
214
345

score 1 · Accepted Answer · answered Feb 14 '13 at 13:21

1

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.

answered Feb 14 '13 at 13:21

Colin M

13,010
3
38
58

This seemed the only solution I could comprehend with my basic knowledge... and it worked, thanks!! – Tim Butterfield Feb 14 '13 at 13:34

Chris Forrence · Answer 4 · 2013-02-14T13:30:23.283

0

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)."'";

edited Feb 14 '13 at 13:30

answered Feb 14 '13 at 13:19

Chris Forrence

10,042
11
48
64

It isn't the only way to do it... a single delete can be cascaded through foreign key relationships by MySQL itself – Mark Baker Feb 14 '13 at 13:21
@MarkBaker - True, however I think more would have to be defined about the asker's tables and intentions. For example, the user may not want to always cascade deletes – Chris Forrence Feb 14 '13 at 13:23
OP should really be defining his tables properly in the first place, with foreign key constraints... even if they don't use cascading deletes – Mark Baker Feb 14 '13 at 13:25

php / mysql how to delete fields with same name from different tables

4 Answers4