Preselecting an array of checkboxes from mysql table

Question

I have a form which puts together an estimate based on the inputs you fill in and select. One of these inputs is a group of checkboxes that determines what finishes the project receives. They go into the database as an array (name="finishes_id[]"). They are put into a table called finishes_used which looks like the following:

used_id   |   estimate_id   |   finish_id

This table links together the finishes table with the estimates table. So for example, if 3 different finishes were chosen for one estimate, it would look like this:

used_id   |   line_id   |   estimate_id   |   finish_id 
1             1             1000              2                 
2             1             1000              6                 
3             1             1000              7

I am now making an edit page for the estimate and I am having trouble figuring out how to pre-select the finishes checkboxes that were used. It is showing ONLY the checkboxes that were selected. I need the option to check the others as well.

My code for the checkboxes part of my form looks like the following. How can I get the desired results above?

<?php
    $getid     = $_GET['estimate_id'];  // The current estimate number
    $getLineid = $_GET['line_id'];      // The current line item on the estimate
?>

<label for="finish_id">Finishing</label>
<div class="checkbox_group">
    <?php
        if ($select = $db -> prepare("SELECT f.finish_id, f.finish_name, u.estimate_id, u.line_id FROM finishes AS f INNER JOIN finishes_used AS u ON u.finish_id = f.finish_id WHERE u.line_id = ? ORDER BY f.finish_name ASC"))
        {
            $select -> bind_param('s', $getLineID);
            $select -> execute();
            $select -> bind_result($finish_id, $finish_name, $used_estimate_id, $used_line_id);
            while ($select -> fetch())
            {
                echo '<div class="checkbox">';
                echo '<input type="checkbox" name="finish_id[]" id="edit_'.$finish_name.'" value="'.$finish_id.'" ';
                echo '/><label for="edit_'.$finish_name.'">'.$finish_name.'</label>';
                echo '</div>';  
            }
            $select -> close();
        }
    ?>
</div>

score 0 · Answer 1 · edited May 23 '17 at 12:08

0

It seems to me you have to use left outer join instead of inner join to achieve this.

Here is a great explanation about joins: What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited May 23 '17 at 12:08

Community

1
1

answered Nov 21 '16 at 15:56

Victor Rudkov

348
1
3
13

Thanks for the reply. I replaced the inner join with the left outer join and I get the same results as before. A var_dump of the statement returns `["num_rows"]=> int(3)`. This is my new query: `SELECT f.finish_id, f.finish_name, u.estimate_id, u.line_id FROM finishes AS f LEFT OUTER JOIN finishes_used AS u ON u.finish_id = f.finish_id WHERE u.line_id = ? ORDER BY f.finish_name ASC` – Paul Ruocco Nov 21 '16 at 19:19
I would recreate it tomorrow to see what's going on. I think we would find a solution – Victor Rudkov Nov 21 '16 at 21:16
Thanks so much, I appreciate the help – Paul Ruocco Nov 22 '16 at 14:29
If you're willing, I could still use some help with this issue. – Paul Ruocco Nov 30 '16 at 16:31

Preselecting an array of checkboxes from mysql table

1 Answers1