PHP single loop through 2 MySQL tables

Please review this code:

    $current_sql = mysql_query("SELECT * FROM `product_categories` WHERE `product`='{$_GET['id']}'");
    $all_sql = mysql_query("SELECT * FROM `categories`");
    $in = mysql_fetch_array($current_sql);

    while($ca = mysql_fetch_array($all_sql)){

        echo("<label><input type=\"checkbox\" name=\"categories[]\" value=\"".$ca['ID']."\" class=\"cat_check\"");
        if($edit){
            if(in_array($ca['ID'], $in)){
                echo(" checked=\"checked\"");   
            }
        }
        echo("> " . $ca['category'] . "</label><br/>\n");

    }

This is the output:

<label><input type="checkbox" name="categories[]" value="1" class="cat_check" checked="checked"> PS3 Games</label><br/> 
<label><input type="checkbox" name="categories[]" value="16" class="cat_check"> Category 2</label><br/> 
<label><input type="checkbox" name="categories[]" value="17" class="cat_check" checked="checked"> Category 3</label><br/> 
<label><input type="checkbox" name="categories[]" value="18" class="cat_check"> Category 4</label><br/> 
<label><input type="checkbox" name="categories[]" value="19" class="cat_check"> Category 5</label><br/>

The problem is only checkbox value="17" is supposed to be checked, there are 2 MySQL databases here are a开发者_开发问答re the structures:

categories:

ID    |   name
1         PS3 Games
16        Category 3
17        Category 4
18        Category 5
19        Category 6

product_categories

category     |    product
1                 20
17                1

Please help!!

UPDATE:

Sorry forgot to add:

$_GET['id'] = 1

Instead

$in = mysql_fetch_array($current_sql);

do

while ($prod_cat = mysql_fetch_array($current_sql)) {
  $in[] = $prod_cat['cat_id']; // where cat_id is the category ID in product_categories table
}

Basically the problem is that mysql_fetch_array get you back 2 arrays and I guess that you have something like array([0] = 1, [1] =17, etc ...). This is causing both categories to be checked. If you print_r the $in in your code, you will see what I mean.

This will only give you an array woth the first row in your mapping table, meaning it will contain 1 and 20 (category and product).

$in = mysql_fetch_array($current_sql);

You would want to iterate through it to get all categories in order to get the right functionality. BUT:

You'd be better off joining your product_categories into the same query, and keep some sort of boolean value to indicate which ones are already selected. The way you are doing it now won't work at all.

SELECT c.*, IFNULL(pc.category, 0, 1) as checked FROM `categories` c
LEFT JOIN `product_categories` pc ON pc.category = c.ID AND product = {$_GET['id']}

then you can check

if ($resultset['checked'])

Because it would be 1 if part of product_category, and 0 if not.

But be sure to escape your parameters or use prepared statements as somebody suggested in the comments.

Try this: $current_sql = mysql_query("SELECT category FROM product_categories WHERE product='{$_GET['id']}' LIMIT 1");

if(($ca['ID']== $current_sql['ID'])){ echo(" checked=\"checked\"");
}

try

$current_sql = mysql_query("SELECT * FROM `product_categories` WHERE `product`='{$_GET['id']}'");
$all_sql = mysql_query("SELECT * FROM `categories`");
$pcs = array();
    while($in=mysql_fetch_array($current_sql);)){
        $pcs[]=$in['ID']
    }

while($ca = mysql_fetch_array($all_sql)){

    echo("<label><input type=\"checkbox\" name=\"categories[]\" value=\"".$ca['ID']."\" class=\"cat_check\"");
    if($edit){
        if(in_array($ca['ID'], $pcs)){
            echo(" checked=\"checked\"");   
        }
    }
    echo("> " . $ca['category'] . "</label><br/>\n");
}