Jump to content

Check checkbox on many-to-many relation


php2014

Recommended Posts

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 :).

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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!
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by php2014
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.