Previous Thread
Next Thread
Print Thread
Hop To
#228869 09/01/2009 2:08 PM
Joined: Jan 2004
Posts: 2,474
Likes: 3
D
Pooh-Bah
Pooh-Bah
D Offline
Joined: Jan 2004
Posts: 2,474
Likes: 3
Hello - I'm learning PHP sick

I want to delete a SPONSOR from the sponsor table.
So my query is...

$delete = "DELETE FROM sponsors WHERE id='$id'";

However, I also want to delete child records from other tables.
So I need to be able to do...

$delete = "DELETE FROM sponsors_notes WHERE sponsor_id='$id'";
$delete = "DELETE FROM sponsors_records WHERE sponsor_id='$id'";

How can I combine this into one query?
Or one MySQL statement?

n.b. - the 1st field is id, but the 2nd & 3rd are sponsor_id

Thanks laugh

driv #228872 09/01/2009 4:08 PM
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
You should be able to use a join. Probably a left join just in case there isn't a record in one of the child tables. So, something like this:

SQL Query
delete sponsors, sponsors_notes, sponsors_records
from sponsors
left join sponsors_notes on sponsors_notes.sponsor_id = sponsors.id
left join sponsors_records on sponsors_records.sponsor_id = sponsors.id
where sponsors.id = '$id'

Rick #228873 09/01/2009 4:25 PM
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
It should be noted to anyone that might stumble upon this that if these are INNODB tables that have foreign keys on those id fields then you can't use the query.

Rick #228874 09/01/2009 4:33 PM
Joined: Jan 2004
Posts: 2,474
Likes: 3
D
Pooh-Bah
Pooh-Bah
D Offline
Joined: Jan 2004
Posts: 2,474
Likes: 3
In the words of the Bard.... WOOT!

Cheers Rick Mate laugh

It works perfectly. I could never have come up with that query.

I've got absolutely no idea what the left join business is about - but I'm off to the research now smile

Thanks again smile

driv #228886 09/01/2009 9:04 PM
Joined: Jun 2006
Posts: 9,242
Likes: 1
R
Former Developer
Former Developer
R Offline
Joined: Jun 2006
Posts: 9,242
Likes: 1
Great, glad to help wink

To put it simply, a left join will act on all rows on the left table, in this case sponsors, even if there are no matching rows in the right table (sponsors_notes,sponsors_records).


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
3 members (rootman, Gizmo, Nightcrawler), 562 guests, and 186 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)