Mysql relational DELETE problem

[gregk]

Hello,

I have a simple relational table structure like this:

table 'category'
category_id
category

table 'sub_category'
sub_category_id
sub_category
category_id

these are two tables with category_id being the relational link. Now I want to delete a category, by deleting where category [name] = $some var but also delete the sub categories which have the same catgory_id as the top level category with is being deleted. I came up with this sql which doesn't work but might help you to understand what I'm on about:

DELETE FROM category, sub_category WHERE category.category = '$deleteCategoryName' AND category.category_id = sub_category.category_id

This sql is wrong, any ideas how I can do this? Bare in mind that the top level category needs to be deleted and all of the sub categories which are nested inside of it. Thanks for any advice/help

[sdjensen]

I don't think you can do that in one delete statement.
First delete all from sub_category and the delete from category

[bad76]

Hi,
split into two query:

DELETE FROM sub_category WHERE sub_category.category_id = $deleteCategoryID

DELETE FROM categoryWHERE category.category_id = $deleteCategoryID

For security purpose make the second query only if the first is ok!
And you have to pass the id value too instead name of category.

see you

[gregk]

Thanks,

I had thought of that, but this requires 2 delete queries and a select query for finding the corrent category_id. I'm very keen on good code, and this method requires 3 db queries. What if my database server was not local? Thats extra queries and will use more bandwidth and resources? Is there not a more ecconomical way to do it?

[bad76]

As sdjensen,
i don't think you can do that in one delete statement.

For the select if you submit the id instead of name (with a combobox it's easy for user than using name...).

[mattr]

MySQL 3 does not support this. I think the latest version of MySQL 4 does, though.

Welcome to the world of MySQL! 🙂

[bad76]

You are right ! (I've only version 3.23...)

i'm get a look on the net:

http://www.mysql.com/doc/en/Deleting_from_related_tables.html
http://www.mysql.com/doc/en/DELETE.html

[gregk]

Right heres my working solution:

		$sql = "SELECT `sub_category` FROM `category`, `sub_category` WHERE category.category_id = sub_category.category_id AND `category` = '$deleteCategoryName'";
		$result1 = mysql_query($sql);
		$test = mysql_fetch_row($result1);
		if ($test != "") {
			$deleteCategory_sql = "DELETE `category`, `sub_category` FROM `category`, `sub_category` WHERE category.category_id = sub_category.category_id AND `category` = '$deleteCategoryName'";
			$deleteCategory_result = mysql_query($deleteCategory_sql) or die("error function deleteCategory, deleteCategory_sql");
		}
		else {
			$sql2 = "DELETE FROM `category` WHERE `category` = '$deleteCategoryName'";
			$reuslt2 = mysql_query($sql2);
}

I expect why your asking why this is better?
Well its better because its only 2 mysql queries per delete, insteed of the 3 queries surgested above. While there is more php this does not take up much more cpu resources. If my database is on another server and not local and I have 10,000 people all deleting a category the bandwidth between php server and database is high, so the difference bettween 2 and 3 mysql queries can make a big difference to bandwidth costs. Also it puts the mysql server under less stress. Note this code is mysql 4 only as it deletes from more than one tables, not mysql 3 compatable (I think).

[ahundiak]

But why do the SELECT at all? Just run your $deleteCategory_sql statement.

[gregk]

Thats what I thought too! However what if there is a top level category with no sub categories nested inside it? Then the condiction WHERE category.category_id = sub_category.category_id is not true and so nothing is deleted. If a top level category does have sub categories then this will work though. The select query checks to see if a top level category has any sub categories and from that chooses the correct sql. I expect is is possable (at least it should be) to do this in one delete query but I've messed around with simple mysql and I can't find a fuction or operand to do this, I surpose what is really needs is an ANDOR but dependent on the condiction is a category has sub categories? Any surguestions welcome!

[bad76]

For my help,
i've post this question just this morning 😃.

The answer is JOIN LEFT. I don't know if work with delete, but you can try.

Read the thread for more info...

http://www.phpbuilder.com/board/showthread.php?s=&threadid=10245832

see you