Stumped On Delete Question

artisticre

I have two tables:

How do I delete a row in tshirts and the category that it is associated with in category? I have searched on how to delete and tried everything I could think of but can't get it.

CREATE TABLE tshirts (
id tinyint(4) NOT NULL auto_increment,
shirtname varchar(100) NOT NULL default '',
image varchar(100) NOT NULL default '',
catID int(4) NOT NULL default '0',
shirtcolors varchar(200) NOT NULL default '',
shirtavail varchar(200) NOT NULL default '',
mediumshirt varchar(30) NOT NULL default '',
largeshirt varchar(30) NOT NULL default '',
XLshirt varchar(30) NOT NULL default '',
XXLshirt varchar(30) NOT NULL default '',
XXXLshirt varchar(30) NOT NULL default '',
smallsweat varchar(30) NOT NULL default '',
mediumsweat varchar(30) NOT NULL default '',
largesweat varchar(30) NOT NULL default '',
XLsweat varchar(30) NOT NULL default '',
XXLsweat varchar(30) NOT NULL default '',
XXXLsweat varchar(30) NOT NULL default '',
XXXXLsweat varchar(30) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE category (
catID int(4) NOT NULL auto_increment,
category varchar(100) NOT NULL default '',
PRIMARY KEY (catID)
) TYPE=MyISAM;

craigh

SELECT catID from tshirts where id=$value

delete from category where catID=$returned_value

delete from tshirts where id=$value

Erkilite

use type=InnoDB instead of myISAM.

then you could do something like

then when you delete a category the t-shirt rows with that catID will be deleted as well.

artisticre

I changed the database like you suggested, but I can't seem to get the delete statement to work.

from what I understood the following should work

DELETE from tshirts, category where (tshirts.catID = category.catID) and where (tshirts.shirtname = '$DelShirtname')

But it doesn't. Can you suggest something?

Erkilite

sorry i miss understood your original post. keep your tshirt table the way it was original and use this instead.

CREATE TABLE category (
catID int(4) NOT NULL auto_increment,
category varchar(100) NOT NULL default '',
PRIMARY KEY (catID)
INDEX (catID),
FOREIGN KEY (catID) REFERENCES tshirts(catID) on update cascade on delete cascade
) TYPE=InnoDB;

what the foreign key does is say that when a row in the tshirt table is updated or deleted make the update or delete on the category table as well.

so all you have to do is

DELETE from tshirts where shirtname='$DelShirtname';

and the row from category should automatically be deleted. be careful tho because it doesn't look like shirtname is unique, not sure if that matters for you.

artisticre

OK I did that suggestion and now it deletes the row in tshirts but not the row in category

Erkilite

the foreign key should delete the row in category table. when you created the table was there an error? was there an error when you tried to delete the row?

artisticre

when I created the table, there was a syntax error but I put a comma after PRIMARY KEY (catID) and it created ok. There was no error when I ran the script but it just didn't delete the row in the category table

Erkilite

thats the entire purpose behind foreign keys, hmm.

check www.InnoDB.com, this has information on the InnoDB database and some sample code. Take a look.

Just a question but is there another record in the tshirts table that references the same category id?

artisticre

Maybe it is in my script. Keep in mind, I am new to all this stuff.

thanks

<form method="post" action="<?php echo $PHP_SELF?>">
<input type=text name=DelShirtname size=20>Delete:
<input type="Submit" value="Go">
</form>
</body>
</html>
<?

$db = mysql_connect("", "", "");
mysql_select_db("",$db);

if ($submit)
{
mysql_query("DELETE from tshirts where tshirts.shirtname = '$DelShirtname'", $db);
mysql_close($db);
echo "Record Removed.\n";
}else{
echo "Error";
}
?>

Erkilite

if ($SERVER['REQUEST_METHOD']=='POST')
{
$query='DELETE from tshirts where tshirts.shirtname = \''.$POST['DelShirtname'].'\';';
if(mysql_query($query, $db)==false){
echo mysql_error();
}
mysql_close($db);
echo "Record Removed.\n";
}else{
echo "Error";
}

try this just to see. is there another record that references the same catID?

artisticre

No each catID in the tshirt and category are unique. I tried your code and it still only deletes the tshirt row not the category row. Any other suggestions?

Erkilite

the only thing i can think of is to double check the foreign key constraints.

superwormy

As far as I know... you can't have two different WHERE clauses in SQL...

DELETE from tshirts, category where (tshirts.catID = category.catID) and where (tshirts.shirtname = '$DelShirtname')

Did you try using mysql_query (" my query " ) or die (mysql_error()) to see if your query is working...?