Page 1 of 1

list question

Posted: Fri Aug 11, 2006 2:28 am
by rsmarsha
I have a loop

Code: Select all

while(list($key) = each($_POST["add"])) 
	{
This loop currently adds products to a table using $key.

I want to delete records from this table where the id ($key) does not occur in the loop.

So say the loop contained keys 1,2,3 i would want the query to delete all references from the table where the id's were not 1, 2 or 3. Any ideas on how to do this?

I've posted this in the code and not query section as it's the code to find the id's not in the loop i need.

Posted: Fri Aug 11, 2006 2:41 am
by rsmarsha
Thought about having an array like:

Code: Select all

$ids = array();
	while(list($key) = each($_POST["add"]))
	{
	$ids[] = $key;
	}
I'm not sure how to delete the records where the id isn't in $ids though.

Posted: Fri Aug 11, 2006 2:52 am
by JayBird

Code: Select all

$sql = "DELETE FROM `table` WHERE `id` NOT IN(".implode(",", $ids).")
That should do it

Posted: Fri Aug 11, 2006 3:06 am
by rsmarsha
Ok i have :

Code: Select all

$ids = array();
	while(list($key) = each($_POST["add"])) 
	{ 
		$ids[] = $key; 
                               //update/insert queries will go here
	}
$clear = "DELETE FROM form_products WHERE form_product NOT IN('.implode(',', $ids).') AND form_cat='$_POST[cat]' AND form_id='$_POST[id]'";
$cq = mysql_query($clear) or die ("Query $clear Failed".mysql_error());
The final query seems to delete everything, not just the ones in $ids, any idea what i'm doing wrong?

Posted: Fri Aug 11, 2006 3:15 am
by JayBird

Code: Select all

$sql = "DELETE FROM `form_products` WHERE `form_product` NOT IN(".implode(",", $ids).") AND `form_cat` = '".$_POST['cat']."' AND `form_id` = '".$_POST['id']."'";

Posted: Fri Aug 11, 2006 3:18 am
by rsmarsha
I'm getting

Code: Select all

Query DELETE FROM `form_products` WHERE `form_product` NOT IN() AND `form_cat` = '' AND `form_id` = '44' FailedYou have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND `form_cat` = '' AND `form_id` = '44'' at line 1

Posted: Fri Aug 11, 2006 3:19 am
by JayBird
Well, from the looks of that, you haven't populated your $ids array from the loop and $_POST['cat'] isn't set

Posted: Fri Aug 11, 2006 3:24 am
by rsmarsha
Yeah that error is due to the query running when it shouldn't, lol, will sort that and see how it works. :)