PHP single loop through 2 MySQL tables

Question

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 are 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

What is your the condition which will tell that this checkbox is to be checked. — Ovais Khatri
– Ovais Khatri, Commented Jun 28, 2011 at 11:36
Offtopic: you should either use prepared statements or use mysql_real_escape_string() to escape $_GET['id'] to avoid SQL injections. — binaryLV
– binaryLV, Commented Jun 28, 2011 at 11:39
can you do a var_dump($in);exit; right after $in = mysql_fetch_array($current_sql); ? — Inoryy
– Inoryy, Commented Jun 28, 2011 at 11:39

Yasen Zhelev · Accepted Answer · 2011-06-28 11:38:43Z

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.

answered Jun 28, 2011 at 11:38

Yasen Zhelev

4,0454 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jishi · Accepted Answer · 2011-06-28 11:46:05Z

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.

Ovais Khatri · Accepted Answer · 2011-06-28 11:53:19Z

0

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\"");
}

answered Jun 28, 2011 at 11:53

Ovais Khatri

3,21118 silver badges15 bronze badges

Comments

Mithun Sreedharan · Accepted Answer · 2011-06-28 11:54:08Z

0

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");
}

answered Jun 28, 2011 at 11:54

Mithun Sreedharan

51.6k72 gold badges185 silver badges237 bronze badges

Collectives™ on Stack Overflow

PHP single loop through 2 MySQL tables

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related