deleting rows depending on the contents of another table

[miss_d_bus]

I have a very simple database consisting of two tables, products and categories.
It's for a basic e-commerce system. I am putting together an administration area to add, delete and edit products and categories written in PHP.

CREATE TABLE `categories` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ;

CREATE TABLE `products` (
  `prodid` int(11) NOT NULL auto_increment,
  `description` varchar(100) NOT NULL default '',
  `price` decimal(4,2) NOT NULL default '0.00',
  `category` int(11) NOT NULL default '0',
  `detail` text NOT NULL,
  `picname` varchar(30) NOT NULL default '',
  `pictype` varchar(30) NOT NULL default '',
  `picsize` int(11) NOT NULL default '0',
  `piccontent` mediumblob NOT NULL,
  PRIMARY KEY  (`prodid`)
) ;

For example I have a category called cases with the ID of 12. This at the moment this has no products associated to it. This can be deleted but say batteries with the ID of 4 cannot as its got products associated in it in the product table. The category column of the product table is the same as the ID column in the categories table. I don't want categories deleted when they have products in them as the products will not be displayed on the website and I don't think moving them to a temporary category is that ideal.

I am struggling to put together a delete statement that would do this.

I came up with this but its not right.

DELETE * categories
WHERE id = 12
IN (
SELECT category
FROM products where category = 12
)

I only want a category removed from the categories table and not delete anything from the products table.

If anyone can point me in the right direction, I would be very grateful.

Thank you,
Catherine

[btherl]

Checking for matching rows in another table is done using a left join.  For example:

[code=php:0]SELECT * FROM categories c LEFT JOIN  products p ON (c.id = p.category)
WHERE p.category IS NULL[/code]


The reason this works is that a left join puts NULL in the columns for the joined table if there is no matching row.

Combining that with your query, you can do

[code=php:0]DELETE FROM categories
WHERE id IN
( SELECT id FROM categories c LEFT JOIN products p ON (c.id = p.category) WHERE p.category IS NULL )[/code]


Make sure you have a backup of the table first, in case I made a mistake :)  There is no need to use "distinct", because a non-matching row will only be listed once in the results set (though matching rows may be listed several times).

[miss_d_bus]

Thank you for your help.
I am not sure I explained myself well enough though. I have a page that shows all the categories in the database and a link to editing the category name (which I got working) and a link to delete the category. When i click on the delete link, I would have a url formatted like the following:
http://www.domain.co.uk/admin/deletecategory.php?id=12

The idea is that the id 12 is passed to the SQL delete statement. I tried to do the following but it did not work:
[code]DELETE FROM categories
WHERE id IN
( SELECT id FROM categories c LEFT JOIN products p ON (c.id = p.category) WHERE p.category IS NULL AND id ='$id')[/code]

I cannot delete this row because of a limitation within mySQL with the selection being in use or something because of it being part of a sub query.
if someone could help me with this, it would be appreciated.

Catherine

[fenway]

You'll have to use a temporary table, or add another column to mark these fields first, then delete them.