DELETING FROM 2 TABLES

[jwo1985]

I've got 2 tables: Album and Song, the tables are linked by an albumid field.  Once an albumid is entered on another webpage, the intention of this page is to actually do the deadly deed and delete the record from both tables.  The only trouble is that it doesn't work, can anyone suggest a solution?

 

$albumid = $_POST[albumid];

$sql = "DELETE FROM album, song USING album, song WHERE albumid=$albumid";

$result = mysql_query($sql);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
else {
echo "Record Deleted.";

 

 

[MadTechie]

try

 

$sql = "DELETE FROM album, song USING album, song WHERE album.albumid=$albumid";

[bubblegum.anarchy]

There is no visible join in the above WHERE clause

 

DELETE FROM album, song USING album, song WHERE album.albumid = song.albumid AND albumid = $albumid 

 

[bubblegum.anarchy]

DELETE album, song
FROM album
     INNER JOIN song ON album.albumid = song.albumid 
WHERE album.albumid = $albumid

[jwo1985]

DELETE album, song
FROM album
     INNER JOIN song ON album.albumid = song.albumid 
WHERE album.albumid = $albumid

 

Realised that my tables were actually called jwo1_album and jwo1_song so tried:

 

$sql = "DELETE jwo1_album, jwo1_song
FROM jwo1_album
     INNER JOIN jwo_song ON jwo1_album.albumid = jwo1_song.albumid 
WHERE jwo1_album.albumid = $albumid";

 

but it didn't work... any suggestions why this may be?

[bubblegum.anarchy]

how didn't it work? - did you get a mysql error? or no records removed?

 

Use a LEFT JOIN so that the album record is removed regardless of whether or not there is a connecting song record.

 

EDIT: also be sure that $albumid contains a value or a current record id.

[jwo1985]

I tried the LEFT JOIN and this is the error message that I get...

 

Invalid query: You have an error in your SQL syntax near 'jwo1_album, jwo1_song FROM jwo1_album LEFT JOIN jwo1_song O' at line 1

 

The code is...

 

<?php
$conn = mysql_connect("*****", "****", "****");
mysql_select_db("******", $conn);
//assign variables
$albumid = $_POST[albumid];
$sql = "DELETE jwo1_album, jwo1_song
FROM jwo1_album
    LEFT JOIN jwo1_song ON jwo1_album.albumid = jwo1_song.albumid 
WHERE jwo1_album.albumid = $albumid";
$result = mysql_query($sql);
if (!$result) {
die('Invalid query: ' . mysql_error());
}
else {
echo "Record Deleted.";
}
?>

 

I made sure that the albumid was moving across from the other webpage, where the user selects which album to delete, by just printing the value of $albumid to the screen. So there doesn't appear to be a problem with that variable, but there is something else stopping it from working and I can't see it!

[bubblegum.anarchy]

hide your usename and password in the mysq_connect!!!!!

 

at a glance, albumid should be quoted here:

$albumid = $_POST[albumid];

[jwo1985]

hide your usename and password in the mysq_connect!!!!!

 

 

I would if I could but I can't edit my post