Flagging checkboxes dynamically

[mikenl]

Hi guys,

 

I'm trying to make a list of checkboxes that are checked or not, depending on the query results. For some reason I have wasted 4 days on trying to make this work, but didn't succeed. You're input is GREATLY appreciated...

 

Table 1:

 

ingredient | product

--------------------

1 | A

2 | A

3 | A

1 | B

2 | B

3 | B

 

Table 2:

 

product_id | product | ingredient

 

500 | A | 1

501 | B | 1

501 | B | 3

 

What I'm trying to show:

 

All ingredients which are in product A (table 1) with the checkboxes flagged if the ingredient is also listed in table 2 - with product ID 500.

 

My code so far (which lists the products, but shows some flagged even though they aren't in table 2):

 

    dbsystem();
				  
$prod = $row2['subcat_id'];
$unid = $row2['unid'];

$query = " 

SELECT DISTINCT

table1.ingredient,
table2.ingredient

FROM 

table1

LEFT JOIN

table2 

ON

table1.ingredient = table2.ingredient

AND

table1.product = table2.product

WHERE

table1.product = '$prod'

AND

table2.product_id = '$unid'

ORDER BY

table1.ingredient	

";
        
$result = mysql_query($query);
while($row = mysql_fetch_row($result)){     
   
if($row[1] != '') {
$checked = "checked";
} else {
$checked = "";
} 

  echo("<div style=\"clear:both\"><div style=\"width:146px;float:left\"><input type=\"checkbox\" name=\"raw[]\" value=\"".$row[0]."\" ".$checked." /> ".$row[0]."-".$row[1]."</div><div style=\"width:125px;float:left\">".$row[2]."</div></div>\n");

	}

 

If I add:

 

AND

prostorage_prod_day_raw.prod_id = '$unid'

 

to the query just before ORDER BY I get only the ingredients that are in table 2 for that product_id...

[mikenl]

I couldn't edit my post anymore... the last line of code that I added must of course be

 

AND

table2.product_id = '$unid'

 

Without this I have a list of ingredients per product, but it flags ingredients that are not in table 2 for that product_id because other product_ids use the same ingredients...

[Psycho]

Well, you database structure makes no sense to me. What is the purpose of table 1? It seems to associate all ingredients with each product. But, you have table 2 that associates each product with the ingredients tha it appears to use. If that is the case, then table 1 only needs to be a list of ingredients. Also table 2 appears to be designed poorly as well. You should ahve a table with a single record for each product and then this table would just associate productid with the ingredient id.

 

Something like this:

 

Table: ingredients

ingredient_id | ingredient_name

 

Table: products

product_id | product_name

 

Table: prod_ingredients

product_id | ingredient_id

 

 

I tried to go through your current code to determine the problem and aside from some minor issues I dpn't know what would be causing your particular problem. Not sure why you would need to use DISTINCT in this situation. Perhaps try removing that.

 

Also in your WHERE clause you use

WHERE table1.product = '$prod'

  AND table2.product_id = '$unid'

 

That does not make sense.The product id should be directly associated with the product. So, those two clauses appear to be redundant.

 

Maybe, i just need a clearer understanding of your structure and how it is used.