I'm so close. Need help with populating checkboxes from mysql db

[lwishon55]

Greetings,

I have searched the forum topics(and most of the www) and have not been 

able to find an answer. I am so very close, but no prize yet.

I have an update form that I am populating from a mysql db. No problems except for 

those dreaded checkboxes.

Here is what I have so far.

[code to call info from db]

/**************************************/
    <?php   
include 'dbconn.php';
        $id = $_GET['id'];
        $sql = $mysqli->query("SELECT * FROM $members_tbl WHERE id = '$id'");
         while ($row= $sql->fetch_array()) {
        $id = $row['id'];
        $busname = $row['busname'];
        $contact = $row['contact'];
        $street = $row['street'];
        $city = $row['city'];
        $stzip = $row['stzip'];
        $phone = $row['phone'];
        $cell = $row['cell'];
        $email1 = $row['email1'];
        $email2 = $row['email2'];
        $website = $row['website'];
        $hours = $row['hours'];
        $descrip = $row['descrip'];
        $cat = $row['cat'];
            //var_dump($cat);
      $arr = explode(',',$row['cat']);
             //var_dump($arr);
     include 'fullCatList.php';
    foreach ($arr as $c) {
          //var_dump($c);
    if (in_array($c, $fullCatList)) {
    $checked = 'checked';
} else {
    $checked = FALSE;
}
}
}
?>     
/*************************************/

[end code]

This is the part where all the work goes for the checkboxes.
The variable $cat holds the values of all the categories selected.

/*******************************************/
$cat = $row['cat'];
    //var_dump($cat);
$arr = explode(',',$row['cat']);
//var_dump($arr);
include 'fullCatList.php';
foreach ($arr as $c) {
//var_dump($c);
if (in_array($c, $fullCatList)) {
    $checked = 'checked';
} else {
    $checked = FALSE;
}
}
/******************************************/

The goal of all this is to re-populate the appropriate checkboxes.

You will notice the various ‘var_dump()’. Here is what each outputs

/**********************************/

$cat = $row['cat'];
    //var_dump($cat);
    
    will output a string = string(18) "busHomeSvc,citySvc"
    
$arr = explode(',',$row['cat']);
//var_dump($arr);

   will output = array(2) { [0]=> string(10) "busHomeSvc" [1]=> string(7) "citySvc" } as expected
   
foreach ($arr as $c) {
//var_dump($c);

  will output = string(10) "busHomeSvc" string(7) "citySvc"  I get the two check values as expected
  
/************************************/

The included file ‘fullCatList.php’ is an array holding all the possible categories. I then
check each variable $c against the $fullCatList array(). If any of the $c values are in the
$fullCatList array, it then is told to turn the variable $checked into an echoed ‘checked’, which works.

The problem is it turns on all the check values, even though they are not one of the $c values.

This is the problem.

How do I keep the other checkboxes from getting “turned on”, when they have not been asked to.
I’m sure my logic is flawed somewhere, I just can’t find out where. As I said, I am so close, but no prize.

There are very good reasons I must approach it like this. I know there are other more efficient ways of
going aboutthis. If I was starting this project from scratch, I would certainly do things differently.
However, as it stands, I’m sort of stuck going this way. Any help is greatly appreciated.

[Mittineague]

It looks like you are assigning either the string “checked” or the boolean FALSE to the $checked variable each loop of the while.

But I don’t see what you’re doing with that as to associating it with anything in particular.

If you’re using it after the while won’t it’s value be whatever it was last assigned because it is constantly being over-written?

[lwishon55]

deleted a response when I posted it to #Mittineague as a reply

[lwishon55]

I think the logic behind it is it will loop through the variables ($c) and assign the variable $checked as “checked”, thus producing a checked box. Those that are not in the while loop would be considered FALSE, or not checked. That is the idea anyway.

Here is how the checkboxes are setup.

<label class="checkbox">
<input type="checkbox" class="form-group" name="cat[]" value="busHomeSvc" <?php echo $checked; ?>>
Business &#38; Home Services
</label>

All of the check boxes have the code <?php echo $checked; ?>, because of course, different categories will
be selected for different businesses. The idea is only the relevant category values will get turned on for each
business selected. These categories are used in a db search to see the individual business information.

[droopsnoot]

Where do you output the above code? It surely needs to be within the while() loop that you use to retrieve the data from the query, otherwise $checked will only have the last value from that loop.

[lwishon55]

@droopsnoot, thanks for your response. I tried putting just the checkbox section of the form within the foreach loop, but it still was turning on all the checkboxes which contained the $checked variable.
The quest continues.

[droopsnoot]

It’s hard to visualise how it goes together without seeing how the output of the checkboxes relates to the code to retrieve the data from the query.

If $fullCatList is a list of all possible categories, surely $c will always be in that list?

[lwishon55]

@droopsnoot, I would be happy to show whatever part of the code you want to see. To include all of it, with the form itself would be rather lengthy, but I would do it if it would help give a better idea of what is going on.

[Mittineague]

@lwishon55

show whatever part of the code you want to see

Something like this should be enough - where you’re getting the $selected values, where you’re assigning the $selected values, where you’re using the $selected values

$form_items_array = array(); 
while 
{database stuff}
$form_items_array['whatever'] = $row['whatever']; 
$form_items_array['select_val'] = $selected; 
end while 

for ($form_items_array) 
{output built here}
< .... $form_items_array[$i]['select_val']

[RobertSF]

Hey, checkboxes can be a hassle. So you have this code here (lightly edited for clarity):

$cat = $row['cat'];
$arr = explode(',', $row['cat']);
include 'fullCatList.php';
foreach ($arr as $c)
{
    if (in_array($c, $fullCatList))
    {
        $checked = 'checked';
    }
    else
    {
        $checked = FALSE;
    }
}

And after the above code executes, you then output results through a series of echo statements.

<label class="checkbox">
    <input type="checkbox" name="cat[]" value="busHomeSvc" <?php echo $checked; ?>>Business
</label>

The thing I’m seeing, and I realize I’m not seeing all the code, is that you assign 'checked' or FALSE to the variable $checked, but it’s the same $checked variable for every array element. You need a $checked array with elements that correspond to the elements in the array $arr because each element in $arr is either checked or not checked. Is the above HTML code within a PHP loop?

As you probably know, only checkboxes with a value are returned to PHP via the $_POST array, so a common strategy is to have a hidden field for each checkbox field that assigns the value of 0 to unchecked boxes, while checking a box returns the value of 1. That way, you get the same array returned, regardless of the user selections, and the only difference will be which elements contain 0 and which contain 1.

I can’t remember all the hassle I endured to make this survey application work, but check it out. It may be helpful. It’s pure PHP with no framework or anything. Here’s the GitHub link to the code. https://github.com/RobertHallsey/Survey-Generator And here’s the code running and outputting the HTML. http://clicketyhome.com/survey I hope it’s relevant to what you’re trying to solve, but ask more questions if it’s not.

[lwishon55]

@RobertSF, thanks for your reply and helpful suggestions. I think having looked at all the comments given and rethinking this whole thing, I am going to have to break down and put the category results in their own db table. I have been trying to avoid this due to the fact that there are over 120 businesses that are already in the current table with their categories stored as a string (inside $cat).

When this was initially set up, this all worked fine, but now there is the real possibility that the businesses themselves will be updating their information, instead of me doing it. So I have been reworking the whole system. I think in the long run, it will be much better to do the whole separate table thing. Much more work now, but in the end better for them and easier for me to manage.

So, back to the drawing board and back to work. Thanks again for everyone’s input and help. I guess I wasn’t as close as I thought.

[RobertSF]

You’re welcome, and I hope it was helpful. Let us know how it’s coming along.

[droopsnoot]

Yes, I’d certainly agree that having a table keyed by the business id and a row for each category is a better way to design the database. I’m not sure it will make a lot of difference to the precise problem you are having here, but the missing bit in the code is what happens between the bottom of where you set $checked and where you echo the checkboxes.

If the checkbox code is echoed within the foreach() loop then using an individual variable isn’t an issue, but if you want to build the variables then echo the form separately later on (no bad thing, for code readability) then as @robertsf said, you have to use multiple variables. In fact you must be doing that, in order to loop through all the available categories. I think what I’d do right now would be to lose the foreach() loop you have now, and do something like this in the area where you display the checkboxes:

$arr = explode("," , $row['cat']);
foreach ($fullCatList as $x => $catname) {
  if (in_array($x, $arr))
    $checked = "checked";
    }
  else {
    $checked = "";
  }
  // display the checkbox HTML code here
  echo '<input type="checkbox" class="form-group" name="cat[]" value="' . $x . '" ' $checked . '> ' . $catname;
} // end of foreach

That turns things on its head, runs through each possible entry for categories (which presumably you’ll be displaying, so people can select or deselect them) and for each one, checks whether it is in your business data in the form of $arr. If it is, it adds “checked”, if not, it does not. Obviously it means that your $fullCatList array would also need to include the category name to display alongside the checkbox - but while you’re changing the database layout, you really should stick those in a separate table rather than an include unless you’ve got a really good reason not to.

Still alter the database though, it’s a more “proper” way of designing it - read up on “database normalising”.