Inner join delete not working

Question

I am trying to delete a patient record and any appointments associated with their Patient ID at the same time but neither are working, could someone please tell me where I am going wrong?

My code is:

<?php
include("includes/staffmenu.php");
include("includes/staffsession.php");
@require_once("includes/dbconfig.inc");

$patid = $_GET['patid'];

$patientname = mysql_query("SELECT * From patient WHERE Patient_ID=$patid");
    while($row = mysql_fetch_array($patientname))
        {   $pfname=$row['Patient_First_Name'];
            $pmname=$row['Patient_Middle_Name'];
            $psname=$row['Patient_Surname'];
        }

  echo "<h1>Success $pfname $pmname $psname (Patient ID: $patid) has been removed from our database, along with any appointments in their name</h1>";

    mysql_query("DELETE FROM appointment, patient 
    USING patient INNER JOIN appointment ON (patient.Patient_ID = appointment.Patient_ID) 
    WHERE patient.Patient_ID='$patid'");

  ?>

It definitely get's the patient ID because the name of patient ID echoes fine...

Side note: Consider using params instead of direct entry or you leave yourself open to sql injections, especially since you are getting patid directly from GET http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php — Christian, Mar 17 '13 at 18:44
@Sebas by deleting manually do you mean from phpMyAdmIn? if so yes it works fine from there... — Tim Butterfield, Mar 17 '13 at 18:51

Christian · Accepted Answer · 2013-03-17T19:38:41.553

1

DELETE patients, appointments FROM patients
LEFT JOIN appointments USING(Patient_ID)
WHERE Patient_ID = 1

ref http://dev.mysql.com/doc/refman/5.5/en/delete.html

ps. consider using params to pass in patid as you are opening yourself up to sql injection using the value directly from GET

http://php.net/manual/en/mysqli-stmt.bind-param.php

pps. you should also put the echo last since you havent actually deleted at that point :)

edited Mar 17 '13 at 19:38

answered Mar 17 '13 at 18:52

Christian

3,708
3
39
60

thanks I have tried that but it's still not working, and yes I noticed the echo in the middle!! ;-0 – Tim Butterfield Mar 17 '13 at 19:23
i just created two tables and populated them with dummy data, ran that code and it worked. Open up mysqlworkbench/phpmyadmin and create a dummy patient with appointments and then run the code directly in mysqlworkbench/phpmyadmin. Let me know. – Christian Mar 17 '13 at 19:29
thanks! hmm maybe its because the Patient ID's I am trying it on have no appointments associated with their ID', would this make a difference? – Tim Butterfield Mar 17 '13 at 19:36
yes, very much so! Change it to LEFT JOIN instead and try – Christian Mar 17 '13 at 19:38
wow!! it worked... I guess I still have a lot to learn!! thank you so much – Tim Butterfield Mar 17 '13 at 19:44
no problema. look at using bind_param - see my ps comment – Christian Mar 17 '13 at 19:52

Inner join delete not working

1 Answers1