Check checkbox on many-to-many relation

[p.h.p]

Hi all,

I am fighting with the following issue and was hoping you can give me some insights, as I am totally stuck.
What's going on? I have 3 tables, created as follows:

table category_p
column id
column name
column type

table home
column id

table link_category_p_home
column category_p_id
column home_id

These tables are linked with eachother, where link_category_p_home is the many-to-many relation table. Now, I want to display for a selected home ID all connected category_p_id's as a checked checkbox. All category_p_id's which are not findable in the link_category_p_home table have to be displayed too, but remain unchecked. I am really trying everything but I just can't figure it out. For most of you it's probably easy stuff but for me it's just like aaahhh. Anyway, here is the code:

                <table cellpadding="0" cellspacing="0" border="0">
                    <?php 
                    $query1=mysqli_query($conn,"SELECT category_p.id, category_p.name, category_p.type, home.id
  FROM home, category_p,link_category_p_home
  WHERE link_category_p_home.home_id = home.id
  AND link_category_p_home.category_p_id = category_p.id
  AND home.id='$id[$i]'
  ORDER BY home.name ASC");
                    while($row=mysqli_fetch_array($query1)){
                        $category_p_id=$row['id'];
                    ?>
                    <tr>
                        <td width="20" style="padding-bottom: 4px"><input name="selector[]" type="checkbox" value="<?php echo $category_p_id; ?>" checked></td>
                        <td width="100" style="padding-top:3px; padding-bottom: 1px"><?php echo $row['type'] ?></td>
                            <td width="265" style="padding-top:3px; padding-bottom: 1px"><?php echo $row['name'] ?></td>
                    </tr>
                    <?php  } ?>
            </table>

If somebody could get me on the way, that would be really wonderfull!
Many thanks in advance.

[diafol]

SELECT c.id AS category_id, c.name, c.type, h.id AS home_id 
FROM category_p AS c 
LEFT JOIN category_p_home AS ch 
    ON ch.category_p_id = c.id 
LEFT JOIN home AS h 
    ON h.id = ch.home_id

That will give you a list with all categories with home_id column holding a home.id value or NULL if that category is not present in the category_p_home table.

So, when you do your recordset loop - check for NULL = unchecked or for a value (integer) = checked

<?php
//...Run query here...

$outputRows = '';        
while($row=mysqli_fetch_array($query1)){

   $cat_id=$row['category_id'];
   $home_id=$row['home_id'];
   $cat_name=$row['name'];
   $cat_type=$row['type'];
   $checked = (is_null($home_id)) ? '' : " checked";  
   $outputRows .= 

   "<tr><td><input name="selector[$cat_id]" type="checkbox"$checked></td>
    <td>$type</td>
    <td>$name</td></tr>";        
?>

<table>
<?php echo $outputRows;
</table>

I suggest that you use CSS/styling for your table layout - try to avoid inline styling if possible:

td:first-child{
    width: 20px;
    padding-bottom: 4px;
}

td:nth-child(2){
    width: 100px;
    padding-top:3px;
    padding-bottom: 1px;
}

td:last-child{
    width: 265px;
    padding-top:3px;
    padding-bottom: 1px;
}

[p.h.p]

Hi Diafol, many thanks for your great effort. Unfortunately I receive the following error :

Parse error: syntax error, unexpected 'selector' (T_STRING) in C:\xampp\htdocs\admin\home_edit.php on line 63

Line 63 is as follows :

"<tr><td><input name="selector[$cat_id]" type="checkbox"$checked></td>

Any idea what I do wrong?

[diafol]

Sorry:

"<tr><td><input name='selector[$cat_id]' type='checkbox'$checked></td>
    <td>$type</td>
    <td>$name</td></tr>";

I forgot to change the double quotes to singles :(

[p.h.p]

Thanks, it does work now, but still not how I anticipated.
It now only displays the category which is connected with the table home, column ID. But it does not dispaly all categories which are also available, but not selected for this ID. Any idea how to fix that?

EDIT: Also, I receive the following error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:/xampp/htdocs/admin/home_edit.php on line 54

Line 54 :

while($row=mysqli_fetch_array($query1)){

Thanks again.

[diafol]

Sorry I must have been drunk when I did the sql!

SELECT c.id AS category_id, c.name, c.type, h.id AS home_id 
FROM category_p AS c 
LEFT JOIN (SELECT * FROM category_p_home WHERE home_id = $home_id) AS ch 
    ON ch.category_id = c.id 
LEFT JOIN home AS h 
    ON h.id = ch.home_id     

That should work - it did for me. Notice the $home_id variable - make sure that you sanitize it before using it in the query.

The warning you got for mysqli is probably due to the query failing. Always use an 'or die()' statement with db commands.

If there are going to be less than 32 checkboxes, I would suggest a different approach - an easy one - as updating checkboxes with dbs is a bit painful. If you're interested, let me know.

[diafol]

BTW a few errors in the loop too:

$home_id = 3; //get this value from somewhere?

$db = new mysqli('localhost','root','','daniweb');
$outputRows = '';        
$result = $db->query(
"SELECT c.id AS category_id, c.name, c.type, h.id AS home_id 
FROM category_p AS c 
LEFT JOIN (SELECT * FROM category_p_home WHERE home_id = $home_id) AS ch 
    ON ch.category_id = c.id 
LEFT JOIN home AS h 
    ON h.id = ch.home_id");

while($row = $result->fetch_assoc())
{
   $cat_id=$row['category_id'];
   $home_id=$row['home_id'];
   $cat_name=$row['name'];
   $cat_type=$row['type'];
   $checked = (is_null($home_id)) ? '' : " checked";  
   $outputRows .= 
   "<tr><td><input name='selector[$cat_id]' type='checkbox'$checked></td>
    <td>$cat_type</td>
    <td>$cat_name</td></tr>";
}

[diafol]

<EDIT>
DEMO: http://demos.diafol.org/bitwise-checkboxes.php
</EDIT>

Ok, just in case you are interested, here's how I'd do it if I knew there were never going to be more than 32 checkboxes:

$home_id = 1;

$db = new mysqli('localhost','root','','daniweb');
$outputRows = '';        
$result = $db->query("SELECT cat.`id` AS category_id, cat.`name`, cat.`type`, cat.`bin`, chosen.selected
FROM category_p AS cat
LEFT JOIN (SELECT `id` AS category_id, `name`, `type`, 1 AS selected
    FROM category_p
    WHERE (SELECT cats FROM home WHERE id = $home_id) & `bin`)
    AS chosen ON chosen.category_id = cat.id");

while($row = $result->fetch_assoc())
{
   $bin=$row['bin'];
    $selected=$row['selected'];
   $cat_name=$row['name'];
   $cat_type=$row['type'];
   $checked = (is_null($selected)) ? '' : " checked";  
   $outputRows .= 
   "<tr><td><input name='selector[]' value='$bin' type='checkbox'$checked></td>
    <td>$cat_type</td>
    <td>$cat_name</td></tr>";
}

This works well for me when I tested it.

You'll notice no category_p_home table. That's because with this technique, it's not needed.

You do however need to alter the category_p and home tables:

Add a column to the category_p table called 'bin' or whatever you want to call it. This will contain the value 2^id-1

You can fill this automatically with the query:

UPDATE category_p SET `bin` = POW(2,`id`-1)

Add a column to the home table called 'cats' or whatever you want to call it. This column will contain the sum of the bin values of the categories selected for that home id.

For example, when the home id = 4, cats = 218

Which relates to 2+8+16+64+128, which selects the category inputs 2,4,5,7,8:

When you come to updating the DB from the form, it couldn't be simpler:

The value to be updated/inserted for a home id:

array_sum($_POST['selector'])

[p.h.p]

Many thanks for your thorough help and posts! I was a bit busy but it certainly helped. Unfortunately I do have more then 32 checkboxes so that last tip will not do it for me. I will save it to my favourites tough. I'm now going to code something that will delete items when unchecking checkboxes and adds them when checking them. Again, many thanks. Much apppreciated.

[diafol]

OK, no prob. Are you sure about this 32+ checkboxes - that's a LOT of checkboxes, heh. I think I'd go blind :)

[p.h.p]

Yes I'm really sure ;) problem is I have cases where I need more then 40 or even 50. But I'm actually having the following problem with this:

// PART 1

$insert_id = $id;

    $result = mysqli_query($conn, "UPDATE home SET name='$name', active='$active' where id='$id'")or die(mysqli_error($conn));

    $result = mysqli_query($conn, "DELETE FROM link_cloud_home WHERE home_id='$id'")or die(mysqli_error($conn));

if(isset($_POST['selector_cloud'])){ $id = $_POST['selector_cloud'];
$N = count($id);
for($i=0; $i < $N; $i++)
{
    $result = mysqli_query($conn, "SELECT * FROM cloud where id='$id[$i]'");
    while($row = mysqli_fetch_array($result))
        {
            echo "<br>";
            echo $insert_id;
            echo "<br>";
            echo $row['id'];
            $sql = "INSERT INTO link_cloud_home (home_id, cloud_id) VALUES ('{$insert_id}', '{$row['id']}');";
            $retval = mysqli_query($conn, $sql);
            if(! $retval )
            {
                die('Could not enter data: ' . mysqli_error($conn));
            }
        }
}
}

// PART 2

    $result = mysqli_query($conn, "DELETE FROM link_category_p_home WHERE home_id='$id'")or die(mysqli_error($conn));

if(isset($_POST['selector_cat_p'])){ $id = $_POST['selector_cat_p'];
$N = count($id);
for($i=0; $i < $N; $i++)
{
    $result = mysqli_query($conn, "SELECT * FROM category_p where id='$id[$i]'");
    while($row = mysqli_fetch_array($result))
        {
            echo "<br>";
            echo $insert_id;
            echo "<br>";
            echo $row['id'];
            $sql = "INSERT INTO link_category_p_home (home_id, category_p_id) VALUES ('{$insert_id}', '{$row['id']}');";
            $retval = mysqli_query($conn, $sql);
            if(! $retval )
            {
                die('Could not enter data: ' . mysqli_error($conn));
            }
        }
}
}

The previous code works when I use one of the parts, so for example PART 1 or PART 2. But when I use them together, I receive the following error:

Notice: Array to string conversion in C:\xampp\htdocs\admin\edit\home_edit_save.php on line 44

Line 44 is as follows:

$result = mysqli_query($conn, "DELETE FROM link_category_p_home WHERE home_id='$id'")or die(mysqli_error($conn));

Am I missing something here? Why does it work with one of those 2 parts and why doesn't it work for those 2 parts combined? Any idea? Thanks!

[p.h.p]

Ok I know I am using $id first as a variable and later as an array. How can I prevent this from happening without damaging the code? Can anybody help me please? Would aprpeciate it very much.!