Finding products not in another table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Finding products not in another table

Post by rsmarsha »

Hope this makes sense. :)

First i'll paste the code, then explain it.

Code: Select all

<?php
		if (isset($_POST['show_cat']))
		{
		$cat = $_POST['show_cat'];
		}
		else
		{
		$cat = $_GET['cat'];
		}
	$p_type = $_POST['type'];
	//1st query, products already added to the form
$list = "SELECT * FROM categories,products LEFT JOIN form_products ON products.product_id = form_products.form_product WHERE products.category_id=categories.category_id AND products.category_id='$cat' AND form_products.form_id='$_POST[id]' AND products.$p_type='1' ORDER BY products.product_id ASC";
$lq = mysql_query($list) or die("Query $list Failed".mysql_error());
while ($lrow = mysql_fetch_assoc($lq))
{
	if ($lrow['form_cat']!='NULL')
	{

	?>
	<tr class="background_color2">
	<td align="left"><p class="padded_text"><?php echo ''.$lrow['description'].''; ?></td>
	<?php
			$check = 'checked';
			?>
	<td align="center"><input type="checkbox" style="border:0px;" name="<?php echo 'add['.$lrow['product_id'].']'; ?>" id="add" value="<?php echo ''.$lrow['product_id'].'' ?>" <?php echo ''.$check.''; ?>></td>
	<?php
		?>
	<td align="center"><input type="text" name="<?php echo 'order['.$lrow['product_id'].']'; ?>" id="order" value="<?php echo ''.$lrow['form_order'].'' ?>"></td>
	</tr>
	<?php
		} 
}
//end of 1st query
//2nd query, for products not in the form at present
$list = "SELECT * FROM categories,products WHERE products.category_id=categories.category_id AND products.category_id='$cat' AND products.$p_type='1' ORDER BY products.product_id ASC";
$lq = mysql_query($list) or die("Query $list Failed".mysql_error());
while ($lrow = mysql_fetch_assoc($lq))
{
	?>
	<tr class="background_color2">
	<td align="left"><p class="padded_text"><?php echo ''.$lrow['description'].''; ?></td>
	<td align="center"><input type="checkbox" style="border:0px;" name="<?php echo 'add['.$lrow['product_id'].']'; ?>" id="add" value="<?php echo ''.$lrow['product_id'].'' ?>"></td>
	<?php
		?>
	<td align="center"><input type="text" name="<?php echo 'order['.$lrow['product_id'].']'; ?>" id="order" value=""></td>
	</tr>
	<?php
}
	//end of 2nd query
	?>
Ok i have a product table and a forms table, forms are built using the product table.

The above code uses 2 queries.

The first finds all products of the active category which have already been added to the form in question.

The second query looks for all products of the active category and shows them underneath the results of the first query.

I want the second query to show only products which have not been added to the form, so those that are not in form_products with the given form_id.

I've tried a few ways and can't seem to get it to work.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There is a post from Useful Posts that talks of finding records that are not found in a joining table.
Post Reply