Which method and how-to, for removing data from multiple tables in mySQL using php

Question

I have some code that works fantastically that looks like this. You can assume safely that the DB connection is successfully made previously and there is no DB communication issues here:

if($value['caseKey'] === "Case01") {

    try {
        $stmt = $conn->prepare("SELECT * FROM tbl_Lodged, tbl_Search, tbl_Notes, tbl_Files,
        tbl_Case01_s1, tbl_Case01_s2, tbl_Case01_s3, tbl_Case01_s4, tbl_Case01_s5, tbl_Case01_s6, tbl_Case01_s7
        WHERE tbl_Lodged.uaID = tbl_Search.uaID AND tbl_Search.uaID = tbl_Notes.uaID
        AND tbl_Notes.uaID = tbl_Files.uaID AND tbl_Files.uaID = tbl_Case01_s1.uaID
        AND tbl_Case01_s1.uaID = tbl_Case01_s2.uaID AND tbl_Case01_s2.uaID = tbl_Case01_s3.uaID
        AND tbl_Case01_s3.uaID = tbl_Case01_s4.uaID AND tbl_Case01_s4.uaID = tbl_Case01_s5.uaID
        AND tbl_Case01_s5.uaID = tbl_Case01_s6.uaID AND tbl_Case01_s6.uaID = tbl_Case01_s7.uaID
        AND tbl_Lodged.uaID = :uaid");
        $stmt->bindValue(':uaid', $value['uaID']);
        $stmt->execute();
        $caseRes = $stmt->fetchAll();
    } catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

} else if($value['caseKey'] === "Case05") {

    try {
        $stmt = $conn->prepare("SELECT * FROM tbl_Lodged, tbl_Search, tbl_Notes, tbl_Files,
        tbl_Case05_s1, tbl_Case05_s2, tbl_Case05_s3, tbl_Case05_s4, tbl_Case05_s5
        WHERE tbl_Lodged.uaID = tbl_Search.uaID AND tbl_Search.uaID = tbl_Notes.uaID
        AND tbl_Notes.uaID = tbl_Files.uaID AND tbl_Files.uaID = tbl_Case05_s1.uaID
        AND tbl_Case05_s1.uaID = tbl_Case05_s2.uaID AND tbl_Case05_s2.uaID = tbl_Case05_s3.uaID
        AND tbl_Case05_s3.uaID = tbl_Case05_s4.uaID AND tbl_Case05_s4.uaID = tbl_Case05_s5.uaID
        AND tbl_Lodged.uaID = :uaid");
        $stmt->bindValue(':uaid', $value['uaID']);
        $stmt->execute();
        $caseRes = $stmt->fetchAll();
    } catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

} else ....... etc etc

That working fine for selecting everything relative to the tables that contain data for the matching caseKey with the corresponding uaID, I want to perform much the same thing but instead of SELECT I would like to perform DELETE and so I tried this:

if($value['caseKey'] === "Case01") {

    try {
        $stmt = $conn->prepare("DELETE * FROM tbl_Lodged, tbl_Search, tbl_Notes, tbl_Files,
        tbl_Case01_s1, tbl_Case01_s2, tbl_Case01_s3, tbl_Case01_s4, tbl_Case01_s5, tbl_Case01_s6, tbl_Case01_s7
        WHERE tbl_Lodged.uaID = tbl_Search.uaID AND tbl_Search.uaID = tbl_Notes.uaID
        AND tbl_Notes.uaID = tbl_Files.uaID AND tbl_Files.uaID = tbl_Case01_s1.uaID
        AND tbl_Case01_s1.uaID = tbl_Case01_s2.uaID AND tbl_Case01_s2.uaID = tbl_Case01_s3.uaID
        AND tbl_Case01_s3.uaID = tbl_Case01_s4.uaID AND tbl_Case01_s4.uaID = tbl_Case01_s5.uaID
        AND tbl_Case01_s5.uaID = tbl_Case01_s6.uaID AND tbl_Case01_s6.uaID = tbl_Case01_s7.uaID
        AND tbl_Lodged.uaID = :uaid");
        $stmt->bindValue(':uaid', $value['uaID']);
        $stmt->execute();
    } catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

} else if($value['caseKey'] === "Case05") {

    try {
        $stmt = $conn->prepare("DELETE * FROM tbl_Lodged, tbl_Search, tbl_Notes, tbl_Files,
        tbl_Case05_s1, tbl_Case05_s2, tbl_Case05_s3, tbl_Case05_s4, tbl_Case05_s5
        WHERE tbl_Lodged.uaID = tbl_Search.uaID AND tbl_Search.uaID = tbl_Notes.uaID
        AND tbl_Notes.uaID = tbl_Files.uaID AND tbl_Files.uaID = tbl_Case05_s1.uaID
        AND tbl_Case05_s1.uaID = tbl_Case05_s2.uaID AND tbl_Case05_s2.uaID = tbl_Case05_s3.uaID
        AND tbl_Case05_s3.uaID = tbl_Case05_s4.uaID AND tbl_Case05_s4.uaID = tbl_Case05_s5.uaID
        AND tbl_Lodged.uaID = :uaid");
        $stmt->bindValue(':uaid', $value['uaID']);
        $stmt->execute();
    } catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

} else ....... etc etc

I thought by changing the SELECT to DELETE and removing the $caseRes = $stmt->fetchAll(); line, I'd have a fairly good solution. Turns out I was wrong on that one, sadly.

But you can see what I am trying to do though. I did some research and discovered joins, both inner and outer versions. I don't get them at all, they don't make sense in my head because I basically revert to being 15 and remembering my woodwork teacher talking about inner and outer joins on assembly of a wooden box. I tend to see things in a very visual representation and the code above that I have quoted makes sense to me because it's simply filtering by caseKey to the next stage whereby all the tables with a common uaID that is being called, are being acting upon, in a way that's a bit like pass the parcel - or the uaID in this case.

Not liking/understanding joins I figured I would have to do each relative table manually. Painful, lots of code and error potential. I believe this might have been what's called a code smell?

More reading and I discovered something called Foreign Keys in mySQL. It appears maybe I can just have this in my php:

try {
    $stmt = $conn->prepare("DELETE * FROM tbl_Lodged WHERE uaID = :uaid");
    $stmt->bindValue(':uaid', $value['uaID']);
    $stmt->execute();
} catch(PDOException $e) { catchMySQLerror($e->getMessage()); }

And then magically anything that has the particular uaID will be eliminated.

Is this true, or am I hoping for too much?

If it is true, how can I set this up using phpmyadmin version 3.4.11.1deb2+deb7u1? I've read on how to do this but what I read is quite different to what I see in my phpmyadmin and does not correspond in any way close enough to use. Should I give up on Foreign Keys and re-try understanding joins for this operation?

the uaID is set as int(11), auto increment, Primary Key in the tbl_Lodged table. It is set the same within tbl_Search table. It is a key in the other relevant tables but is not the primary key.

Thanks in advance!

PS please keep in mind I am not a code ninja. I'm trying hard though..!

PPS - the database is using the InnoDB engine.

Thanks to Ronald (below) I should clarify I am wanting to set the FK functionality up using phpmyadmin but I don't want to resort to having to use phpmyadmin to actually delete anything. The point of setting up the FK's if it'll work is so that php can do it automatically via a cron job. Phpmyadmin is only my tool to set up and make changes to the database, not run or manage it. I cannot recreate tables that already exist but I can hopefully modify existing ones to fit what is needed to make the delete magic work.

UPDATE Does anyone know how to remove a question from here??

**UPDATE** I have now solved this question myself after many more hours of trial and error (and finally success). I'll remove this question shortly as I doubt it'll be of any use to anyone. Thank you to Ronald who had the patience to try to help me and did inadvertently push my mind in the right general direction. — df0, Mar 01 '16 at 15:04

Ronald Poon · Answer 1 · 2016-03-01T06:28:38.950

0

FOREIGN KEY ON DELETE CASCADE may help, if you have create other tables with reference to the tbl_Lodged.uaID. But I'm not sure if you are the case.

mysql> CREATE TABLE tbl_Lodged(
-> uaID INT AUTO_INCREMENT, text_field VARCHAR(20), PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE tbl_Search(
-> id INT AUTO_INCREMENT, uaID INT, text_field VARCHAR(20), PRIMARY KEY(id),
-> FOREIGN KEY(uaID) REFERENCES tbl_Lodged(uaID) ON DELETE CASCADE) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

With the above sample, execute sql statement "delete from tbl_Lodge where uaID=1" in phpmyadmin, you will remove 1 record in tbl_Lodge and all records in tbl_Search which were match uaID=1

Is that what you looking for?

edited Mar 01 '16 at 06:28

answered Mar 01 '16 at 05:01

Ronald Poon

199
8

This is what I read and was hoping to find out how to make this work - as my preferential option. However what I read on this topic and what I can see in phpmyadmin are not consistent, hence my questioning this. – df0 Mar 01 '16 at 05:04
MySQL documents have some samples on [delete](http://dev.mysql.com/doc/refman/5.6/en/delete.html) statement, foreign key on delete cascade sample, pretty straight forward – Ronald Poon Mar 01 '16 at 05:07
Ronald if I were finding these documents straight forward I would not be asking here. You might also note that I specifically mentioned I need to know how to achieve this - if it's even possible to do - within phpmyadmin. MySQL docs don't deal with phpmyadmin. Please re-read my question to understand me. Thanks! – df0 Mar 01 '16 at 05:14
If you table exists with lots of data, you may try to alter your table add constraint `ALTER TABLE tbl_Search ADD CONSTRAINT fk_Search FOREIGN KEY (uaID) REFERENCES tbl_Lodged(uaID) ON DELETE CASCADE;` – Ronald Poon Mar 01 '16 at 06:26
Hi Ronald, no that's not what I am looking for. All the tables I have mentioned already exist. I don't want to create or recreate them. I am wanting to modify them using phpmyadmin so that the delete function works using regular php. Thanks for pointing out that I was not clear about this though, it's appreciated. BTW your version of my tbl_Search is not correct - the primary key is uaID in mine, not id. – df0 Mar 01 '16 at 07:39
`ALTER TABLE tbl_Search ADD CONSTRAINT fk_Search FOREIGN KEY (uaID) REFERENCES tbl_Lodged(uaID) ON DELETE CASCADE;` - This looks interesting. How do I implement this using phpmyadmin and not doing it as a command line expression? – df0 Mar 01 '16 at 07:44
In fact you can execute the DDL in [phpmyadmin](https://www.youtube.com/watch?v=4c50g_RXPZo) just like select statement, and it will create the reference key. When constraint added to all other tables like tbl_Notes, tbl_Files...etc, If you "DELETE FROM tbl_Lodged where uaID=xxx", other tables with uaID=xxx will also be deleted. Just give it a try. – Ronald Poon Mar 01 '16 at 07:53
I'm not really understanding that. Like I said in my question, I'm not a code ninja. I don't execute command line stuff anywhere, even if it's possible in phpmyadmin. I need to physically see it in action where I can point and click. – df0 Mar 01 '16 at 08:24
Try this [setup foreign key in phpmyadmin](http://stackoverflow.com/questions/459312/setting-up-foreign-keys-in-phpmyadmin) – Ronald Poon Mar 01 '16 at 08:40
Already been there, read that and tried it. What is described there is completely different to what I see in my version of phpmyadmin. This is why I specified the version I have and mentioned I am not seeing what is being talked about. That is one of many items I read before asking here. – df0 Mar 01 '16 at 08:50

Which method and how-to, for removing data from multiple tables in mySQL using php

1 Answers1