[SOLVED] delete where id != array[id]

[webent]

Hi, I was wondering if someone could help my brain past this task... I need to create this clean up script that deletes categories that do not have correlating products... but of course, doing something like this "delete where id != array[id]" definately does not work, as you can imagine, the first id it gets, it deletes them all...

 

It almost needs to compare the entire array at once before any deletion is made... because doing it in any kind of order, deletes everything else before it even gets a chance to compare. Here's my code...

 

$query = "SELECT DISTINCT master_categories_id FROM products";
$results = mysql_query($query);
while ($row  =  mysql_fetch_array($results))	{
$sub_query = mysql_query("DELETE FROM categories WHERE categories_id != '$row[master_categories_id]'");
$sub_query = mysql_query("DELETE FROM categories_description WHERE categories_id != '$row[master_categories_id]'");
						  						}

[fenway]

You need to to LEFT JOIN cat and cat_desc with products and check IS NULL for the join condition.

[webent]

I kind of see what you are saying, but the complexity of such a query baffles my meger mind... don't suppose I could trouble you for just a simple example of that type of syntax could I?

[fenway]

select categories_id FROM categories as c 
LEFT JOIN products as p ON ( c.category_id = p.master_categories_id ) 
WHERE p.master_categories_id IS NULL

 

Will give you the "empty" categories... it's not difficult to convert this into a delete.

[webent]

Thank you very much for your help fenway... that is definately the most complex query I have ever had to use...

[webent]

Here, does this look good?

 

$query = mysql_query("SELECT categories_id FROM categories as c 
LEFT JOIN products as p ON ( c.category_id = p.master_categories_id ) 
WHERE p.master_categories_id IS NULL");
while ($row  =  mysql_fetch_array($query))	{
$sub_query = mysql_query("DELETE FROM categories WHERE categories_id = '$row[categories_id]'");
$sub_query = mysql_query("DELETE FROM categories_description WHERE categories_id = '$row[categories_id]'");
										}

 

Or did I go about it wrong?

[webent]

Yeah, I must have gone about it wrong... got this error...

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/username/public_html/data_uploader/database_management.php on line 53

Invalid query: Unknown column 'c.category_id' in 'on clause' Whole query:

[Spaceman-Spiff]

That means the column category_id does not exist in your categories table. Check your table layout/design again.

[webent]

Yes, you were right... slap myself... I didn't check it properly...

 

LEFT JOIN products as p ON ( c.category_id = p.master_categories_id )

 

should be...

 

LEFT JOIN products as p ON ( c.categories_id = p.master_categories_id )

 

Thank you very much for your help.

[webent]

Here's an odd one... so I took that code and made it to do the manufacturers also... the thing with the manufacturers table though, is that the id in that table and the product table are the same... so I got an error... "Column 'manufacturers_id' in field list is ambiguous" So I look it up, the solution it says to use is to prepend the field with the table... like so...

 

$query = mysql_query("SELECT manufacturers.manufacturers_id FROM manufacturers as c 
LEFT JOIN products as p ON ( c.manufacturers_id = p.manufacturers_id ) 
WHERE p.manufacturers_id IS NULL");

 

Then I get the error... "Unknown table 'manufacturers' in field list" Which is bogus because that table does exist, spelled exactly that way...

[Spaceman-Spiff]

Since you made an alias for manufacturer AS c, you should use c.column_name:

 

SELECT c.manufacturers_id FROM manufacturers as c

 

[webent]

I didn't realize that you could call the alias before you defined it... I will give it a shot...

[webent]

Sure enough, you were right... Thank you very much...