Check checkbox on many-to-many relation

php2014 · April 13, 2014

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 * from categorie_p")or die(mysqli_error($conn));

                      $query1=mysqli_query($conn,"SELECT

      c.id AS cat_id,

      c.namr,

      h.id AS home_id,

      IF(l.category_p_id IS NULL, 0, 1) AS checked

  FROM

     category_p AS c

 LEFT JOIN

     link_category_p_home AS l

     ON c.id = l.category_p_id AND l.home_id = 1234 

 LEFT JOIN

     home AS h

     ON l.home_id = h.id ");

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

                         $categorie_p_id=$row['cat_id'];

                     ?>

                     <tr>
                         <td width="20" style="padding-bottom: 4px"><input name="selector[]" type="checkbox" value="<?php echo $categorie_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['cbr_naam'] ?></td>

                     </tr>

                     <?php  } ?>

             </table>

Unfortunately this does not work. Let's say there are 10 categories and 1 home ID has 3 categories of it. Then I want to display all categories, but only those 3 checked. Rest remains unchecked.

Any help would be appreciated very much!!

Words on why my code does not work or why yours does is welcome too .

Ch0cu3r · April 13, 2014

It look like the query is doing the hardwork for you (look at the IF in the query).

So all you need to do in PHP is compare $row['checked'], when it is 1 you'd apply the checked attribute for the checkbox. If its any other value do not apply the attribute Eg

                while($row=mysqli_fetch_array($query1)){
                        $categorie_p_id=$row['cat_id'];

                        // define the checked attribute?
                        $checked = ($row['checked'] == 1) ? ' checked="checked"' : '';
                     ?>

                     <tr>
                         <td width="20" style="padding-bottom: 4px"><input name="selector[]" type="checkbox" value="<?php echo $categorie_p_id; ?>"<?php echo $checked; /* apply checked attribute*/?>></td>

Barand · April 14, 2014

Join the home table with the category table using a CROSS JOIN (cartesian join) so you get a row for every category/home value.

Then do a LEFT join with the link table to see which match

SELECT home.id as homeid
    , cat.id as catid
    , cat.name
    , IF(l.category_p_id IS NULL, '', 'checked') as checked
    FROM home
        CROSS JOIN category_p cat
        LEFT JOIN link_category_p_home l ON home.id = l.home_id AND cat.id = l.category_p_id
    WHERE home.id = 3
    ORDER BY cat.id

Gives

+--------+-------+--------+---------+
| homeid | catid | name   | checked |
+--------+-------+--------+---------+
|      3 |     1 | Cat 1  |         |
|      3 |     2 | Cat 2  | checked |
|      3 |     3 | Cat 3  |         |
|      3 |     4 | Cat 4  | checked |
|      3 |     5 | Cat 5  | checked |
|      3 |     6 | Cat 6  |         |
|      3 |     7 | Cat 7  |         |
|      3 |     8 | Cat 8  | checked |
|      3 |     9 | Cat 9  | checked |
|      3 |    10 | Cat 10 |         |
+--------+-------+--------+---------+

Edited April 14, 2014 by Barand

php2014 · April 16, 2014

@Ch0cu3r

With your code I receive a list of ALL category's, selected from all home_id's which have a category connected to them.

@Barand

Unfortunately I receive an error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\admin\edit.php on line 119

The code on this line is:

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

What am I doing wrong?

Many thanks for your help until now, much appreciated!

Barand · April 16, 2014

Sounds like an error somewhere in your query. What does

echo $conn->error;

tell you?

php2014 · May 8, 2014

Many thanks for all your help!!

I was able to fix the above and now I can update whilst using checkboxes.

Unfortunately I'm 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!

Barand · May 8, 2014

php is probably suffering from the same confusion as I am reading your code.

On line 1 of part 1 you refer to $id as a string (or int) value but on line 5 and thereafter you use it as though it is an array. Decide which it is.

php2014 · May 8, 2014

Ah ok I see. So I need to rename $ id in part 1 on line 5 and in part 2 then it should work right. Going to test it tomorrow! Do you think the rest of the code looks ok or could it be coded better? I know it works apart from this problem but I aint sure its logical as I am still learning

Barand · May 8, 2014

Could be better!

You shouldn't be running queries inside a loop. Use a single query to join the tables and get all the required data in a single query.

EDIT: I knew I had written similar query to do this sort of thing recently so I did a search - only to find it was earlier in this same thread.

Edited May 8, 2014 by Barand

php2014 · May 9, 2014

Lol OK! That's a good tip. Maybe got some starters or an example I can work with? Appreciate all your help.

Barand · May 9, 2014

Here's an example of updating categories using checkboxes. (Uses the tables you originally posted)

The form:

<?php

$db = new mysqli(HOST,USERNAME,PASSWORD,'test'); // use your credentials
error_reporting(-1);

$homeid = isset($_GET['homeid']) ? intval($_GET['homeid']) : '';
if ($homeid) {
    $sql = "SELECT 
        cat.id as catid
        , cat.name
        , IF(l.category_p_id IS NULL, '', 'checked') as checked
        FROM home
            CROSS JOIN category_p cat
            LEFT JOIN link_category_p_home l ON home.id = l.home_id AND cat.id = l.category_p_id
        WHERE home.id = $homeid
        ORDER BY cat.id";
    $res = $db->query($sql);
    $formdata = "<input type='hidden' name='homeid' value='$homeid'>\n";
    while (list($cid, $cat, $chk) = $res->fetch_row()) {
        $formdata .= "<input type='checkbox' name='cat[]' value='$cid' $chk /> $cat<br/>\n";
    }
    $formdata .= "<input type='submit' name='btnSub' value='Update'>\n";
}
?>
<html>
<head>
<meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)">
<title>Example- Checkboxes</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="05/09/2014">
</head>
<body>

<form name='form1'>
    Enter Home ID <input type="text" name="homeid" value="<?php echo $homeid?>" size="5">
    <input type="submit" name="btnSubmit" value="Get categories">
    <hr/>
</form>

<form name='form2' method='post' action='php2014_updt.php'>
    <?php 
        if (isset($formdata)) {
            echo $formdata;
        }
    ?>
</form>

</body>
</html>

The update:

<?php

$db = new mysqli(HOST,USERNAME,PASSWORD,'test'); // use your credentials
error_reporting(-1);

if (!isset($_POST['homeid']) || !isset($_POST['cat'])) {
    header("Location: php2014_form.php");
    exit;
}

$homeid = intval($_POST['homeid']);
    #
    # Delete existing category links
    #
$sql = "DELETE FROM link_category_p_home
        WHERE home_id = $homeid";
$db->query($sql) or die($db->error . "<pre>$sql</pre>");

    #
    # Add revised category links
    #
$catdata = array();
foreach ($_POST['cat'] as $catid) {
    $catdata[] = sprintf("(%d, %d)", $homeid, intval($catid));
}
$sql = "INSERT INTO link_category_p_home (home_id, category_p_id) VALUES " . join(',', $catdata);
$db->query($sql);

header ("Location: php2014_form.php");
?>

php2014 · May 10, 2014

Many thanks, but I must say that's not what I had in mind. I appreciate the code very much, but I do not understand it completely. I think as I am still learning it's just a bridge 'too far'. The code I have right now for the form, which leads to the page on my first post, is as following :

<div class="box">
<br>
<br>
<div class="container">
<a href="home.php" class="btn btn-inverse">return</a>
<form class="form-horizontal" action="home_edit_save.php" method="post">
<br />
<?php

$id=$_POST['selector'];
$N = count($id);
for($i=0; $i < $N; $i++)
{
    $result = mysqli_query($conn, "SELECT * FROM home where id='$id[$i]'");
    while($row = mysqli_fetch_array($result))
      {
?>
    <div class="thumbnail">
    <br />
    <div class="control-group">
    <label class="control-label">ID</label>
    <div class="controls">
     <p style="vertical-align:middle"><?php echo $row['id'] ?></p>
    </div>
    </div>
    
    <div class="control-group">
    <label class="control-label" for="inputEmail">home</label>
    <div class="controls">
     <input name="id" type="hidden" value="<?php echo $row['id'] ?>" />
        <input name="name" type="text" value="<?php echo $row['name'] ?>" />
    </div>
    </div>

       <div class="control-group">
    <label class="control-label" for="inputEmail">Category P</label>
    <div class="controls">

            <table cellpadding="0" cellspacing="0" border="0">
                    <?php
                    $query1=mysqli_query($conn,"SELECT cat_p.id AS cat_p_id, cat_p.cbr_name, cat_p.type,
IF(link.category_p_id IS NULL, 0, 1) AS checked
FROM category_p AS cat_p
LEFT JOIN link_category_p_home AS link
ON cat_p.id = link.category_p_id AND link.home_id = '$id[$i]'");
                    while($row=mysqli_fetch_array($query1)){
                        $category_p_id=$row['cat_p_id'];
                    ?>
                    <tr>
                        <td width="20" style="padding-bottom: 4px"><input name="selector_cat_p[]" type="checkbox" value="<?php echo $category_p_id; ?>" <?php if ($row['checked']==1) {?> checked <?php } ?>></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['c_name'] ?></td>
                    </tr>
                    <?php  } ?>
            </table>
            
    </div>
     </div>   
           
    </div>
    
<?php
      }
}

?>
<br>
<input name="" class="btn btn-success" type="submit" value="Update">
</form>
</div>

</div>

Since I really want to understand what I am doing and I cannot figure out your code yet, maybe you can help me by fixing my first error? Because then I got it to work. That's the most important. Afterwards I can go and tweak the code more and more to your idea. Maybe you can help me for now how I can make sure I do not use $id as a variable and as an array?

Again, thank you very much in advance!

Edited May 10, 2014 by php2014

Barand · May 10, 2014

It may not do what you want but you asked for an example of what I meant by using a single query and not looping to fetch and update data.

Lol OK! That's a good tip. Maybe got some starters or an example I can work with? Appreciate all your help.

That is what I gave you

As for your $id problem I have no idea what you are holding in that value at the start of your code so I cannot say whether it is a simple variable or an an array, just that it won't be both. To avoid using it as both, use different variable names.

php2014 · May 10, 2014

I know that I asked for an example and for sure I am going to use it. But most important in my eyes is to have a working code first, which I can tweak later.

The $id after $insert_id is the ID of the home-table. I was able to fix it just now. What I did :

$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'])){ $id2 = $_POST['selector_cloud'];
$N = count($id2);
for($i=0; $i < $N; $i++)
{
    $result = mysqli_query($conn, "SELECT * FROM cloud where id='$id2[$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));
            }
        }
}
}

    $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'])){ $id3 = $_POST['selector_cat_p'];
$N = count($id3);
for($i=0; $i < $N; $i++)
{
    $result = mysqli_query($conn, "SELECT * FROM category_p where id='$id3[$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));
            }
        }
}
}

Do you think this is a good fix? It seems to work now...

Barand · May 10, 2014

You are doing a lot of unnecessary processing.

You can replace

$id3 = $_POST['selector_cat_p'];
$N = count($id3);
for($i=0; $i < $N; $i++)

with

foreach ($_POST['selector_cat_p'] as $catid)

Your query

$result = mysqli_query($conn, "SELECT * FROM category_p where id='$id3[$i]'");

is also a waste of time. You use SELECT * when all you want is the $row[id] column, and secondly, why retrieve a row to get the id when you already have the id to do the search in the first place? Thirdly, when you only retrieve one record, why use a while loop ?

Finally, you don't do any escaping/sanitizing of data before using in the queries.

Edited May 10, 2014 by Barand

Sign In

Check checkbox on many-to-many relation

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information