Check checkbox on many-to-many relation

[php2014]

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]

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]

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]

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

Sounds like an error somewhere in your query. What does

echo $conn->error;

tell you?

[php2014]

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]

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]

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]

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]

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

[Barand]

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]

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]

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]

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]

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