CODEWITHSUNDEEP
phpmysqlloops
Lee Price
Lee Price

Reputation: 5212

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

Upvotes: 1

Views: 362

Answers (4)

Mithun Sreedharan
Mithun Sreedharan

Reputation: 51272

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

Upvotes: 0

Ovais Khatri
Ovais Khatri

Reputation: 3211

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

Upvotes: 0

jishi
jishi

Reputation: 24614

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.

Upvotes: 0

Yasen Zhelev
Yasen Zhelev

Reputation: 4045

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.

Upvotes: 1

Related Questions