displaying all check boxes from database even those that have not been selected

[Barand]

On 4/9/2022 at 3:44 PM, webdeveloper123 said:

I do have a database Barand, it looks like this:

Form (FormId, Name, Lastname, age, birthday, email, FavLanguage, Vehicle)

Vehicle (VehicleId, FormId (FK), VehSelection)

According to your post, your table doesn't have a "Firstname" column (it's "Name").

Also make sure the character cases in your indexes match what is returned in the results. (EG "email" or "Email"?)

(Personally, I follow the convention of having all table and column names lower case.)

[webdeveloper123]

Yes sorry it is FirstName, must have been a typo

 

[webdeveloper123]

Hi, I have added the following to my code:

$Vehicle[] = $table[0]["VehSelection"];

And I also came up with:

<?php

foreach($table as $id=>$arr) {
$chk = in_array($id,$Vehicle ?? []) ? ' checked' : '';
   echo "<label class='boxstyle'><input type='checkbox' name='vehicle[]' value='$id'$chk> I have a </label><br>\n";
}



?>

Forgetting about the label at the moment, it still only generates the number of check boxes that were originally ticked. I thought this code was supposed to always display 3 boxes, with the relevant once ticked. And it's not ticking the boxes either

Edited April 11, 2022 by webdeveloper123

[webdeveloper123]

This block of code now checks the box(but only 1 box, even if 2 or 3 were initially checked) and I am still only getting as many check boxes that were ticked, despite using in_array:

foreach($table as $id=>$arr) {
$chk = in_array($id,$Vehicle ?? []) ? 'checked' : '';
   echo "<label class='boxstyle'><input type='checkbox' name='vehicle[]' value='$id'$chk>{$arr['VehSelection']} </label><br>\n";
}

 

Edited April 11, 2022 by webdeveloper123

[webdeveloper123]

anyone around?

[gizmola]

On 4/11/2022 at 7:07 AM, webdeveloper123 said:

This block of code now checks the box(but only 1 box, even if 2 or 3 were initially checked) and I am still only getting as many check boxes that were ticked, despite using in_array:

foreach($table as $id=>$arr) {
   $chk = in_array($id, $Vehicle ?? []) ? ' checked' : '';
   echo "<label class='boxstyle'><input type='checkbox' name='vehicle[]' value='$id'$chk>{$arr['VehSelection']} </label><br>\n";
}

 

I've not following along too closely on this long and convoluted journey, but the obvious answer is that the derivation of the $chk variable (which is probably erroneous given that you check a variable you create outside a loop that seems to be a related part of the $table array)  has absolutely nothing to do with how many checkboxes you have.  Your code should output a checkbox for every row in $table.  

One thing that might be problematic in your markup, is the lack of a space between the value attribute and the word 'checked'.  That has nothing to do with why you don't have more checkboxes, but I'd still fix it like this:

 

foreach($table as $id => $arr) {
   $chk = in_array($id, $Vehicle ?? []) ? ' checked' : '';
   echo "<label class='boxstyle'><input type='checkbox' name='vehicle[]' value='$id'$chk>{$arr['VehSelection']}</label><br>\n";
}

 

 

[webdeveloper123]

Hi,

I've got this code now. It creates 3 check boxes all the time but only ticks 1, even if there are 2 or 3 values. Please can someone take a look:

<?php
 
 $VehicleArray = array("Yacht", "SuperCar", "Plane");
 if (!empty($VehSelection)) {
foreach ($VehicleArray as $v) {
  

$chk = in_array($v,$Vehicle) ? ' checked' : '';



echo  "<input type=\"checkbox\" name=\"vehicle[]\" $chk />\n";
    echo "<label for=\"$v\" class=\"boxstyle\"> I have a  $v </label><br>";

}}

?>

 

[gizmola]

There is no way for any of us to know, because you have a variable ($Vehicle) that isn't initialized anywhere in the code you have shown.  We have no idea what is in that array, and apparently you don't either.  

The way for you to debug that would be to add either print_r($Vehicle) or var_dump($Vehicle) or even die($Vehicle) before the foreach().  This will show you what is in that array.  If there is only one checkbox checked it's either:

 

• There is only one matching value for the strings in $VehicleArray
• You have one or more typos so that the values in $Vehicle should be the same, but still don't match.

[webdeveloper123]

16 hours ago, gizmola said:

There is no way for any of us to know, because you have a variable ($Vehicle) that isn't initialized anywhere in the code you have shown

I have shown it, 6 posts up, dated Posted Monday at 02:37 PM (edited)

I will put it again:

$Vehicle[] = $table[0]["VehSelection"];

Here is when I fill the $table array with data from the result set of my Select statement (only showing relevant line)

  $VehSelection= $table[0]["VehSelection"];

And this is the print_r($Vehicle) for all 3 values (as in Plane,SuperCar and Yacht:

Array ( [0] => Yacht )  
Array ( [0] => Yacht )  
Array ( [0] => Yacht ) 

Obviously that's not right, something going on there

This is for 2 values, "Yacht" & "Plane"

Array ( [0] => Yacht ) 
Array ( [0] => Yacht )
Array ( [0] => Yacht )  

And this is for one value (SuperCar):

Array ( [0] => SuperCar ) 
Array ( [0] => SuperCar )  
Array ( [0] => SuperCar )  

So i'm pretty sure it's the array that's going wrong.

Having said that, for one value I get the desired result, even though the array is wrong.

The $VehSelection variable that I have, if I loop around it and echo the variable I get what ever values that were for that record. So If there is a record which has one value (Yacht), that will print Yacht, correctly. If I click on a record that has 2 values (SuperCar, Plane) it will print SuperCar & Plane (correctly)

And If I have all 3 (Yahct, SuperCar & Plane) when I click the record it will echo the 3 correct values.

So, because in_array as the 2nd parameter must be an array, I though i'd take the $VehSelection variable and turn it into an arrray. That's why I did this:

$Vehicle[] = $table[0]["VehSelection"];

But obviously I am not doing something right as you can see from print_r($Vehicle) results.

Is there anyway I can use 

 $VehSelection= $table[0]["VehSelection"];

as the 2nd parameter for in_array - I have tried many different variations but the error keep saying "2nd parameter must be array"

[gizmola]

18 hours ago, webdeveloper123 said:

I have shown it, 6 posts up, dated Posted Monday at 02:37 PM (edited)

There are numerous posts to you in this thread.  I don't know what your code looks like now.  People have provided you refactored code snippets covering many things.    At this point, we need a sense of what your current code is *now*.  Only you know what that is.

I can only advise you to simplify your data structures.

Also, it might be helpful for you to start using "camelCase" variable naming, which is a standard convention for php coding.   There are many other standards that can help your code readability.

See https://www.php-fig.org/psr/psr-12/

A perfect example here is your use of $Vehicle.  Rename your variable $vehicle.  Start all variable names with a lower case letter.  If the variable has multiple words, use camelcase ie.  $firstName.

On 4/11/2022 at 6:37 AM, webdeveloper123 said:

$Vehicle[] = $table[0]["VehSelection"];

What are you trying to do here?  What is $table, and why are you accessing element 0?

Why are you setting $Vehicle[] = this thing, which actually makes $Vehicle into an array, so that the "thing" needs to be accessed via $Vehicle[0]? 

If $table[0]['VehSelection'] is an array, then just assign $Vehicle = $table[0]["VehSelection"];

19 hours ago, webdeveloper123 said:

And this is the print_r($Vehicle) for all 3 values (as in Plane,SuperCar and Yacht:

Array ( [0] => Yacht )  
Array ( [0] => Yacht )  
Array ( [0] => Yacht ) 

Why were you doing this?  You don't need to output the same array inside the foreach!  All you needed was to see the value of it *before the loop*.  It doesn't change.

With that noted, what can be seen clearly, is that despite your claims as to what was actually selected, in all cases, there is only ever one value specified.  Even when your form checked "Yacht" and "Plane" this array only contains the single "Yacht" value. 

You need to determine why that is.

 

[gizmola]

On 4/11/2022 at 5:15 AM, webdeveloper123 said:

It's not a unknown variable. I have a SQL Select statement which for the result set I do this after:

$result = mysqli_query($link, $queryselect);

$table = [];

while ( $row = mysqli_fetch_assoc( $result ) ) {
   $table[] = $row; 
}

Then I do this:

if ( count($table) == 0) {
 exit;

}
else
{
 

  $first_name = $table[0]["FirstName"];
  $last_name = $table[0]["LastName"];
  $email = $table[0]["Email"];
  $age = $table[0]["Age"];
  $birthday = $table[0]["Birthdate"];
  $favlanguage = $table[0]["FavLanguage"];
  $VehSelection= $table[0]["VehSelection"];
}

But I am looking and mac_gyvers and Barands foreach loop and trying to come up with something

 

Let's go back to this as well.

What is the query?  Do you understand what you are actually doing here?  With each fetch you create a new array element in $table that contains that row.  So you end up with $table being an array of the rows of the select statement.  There is no reason for you to then be trying to access those using the 0 array element.  

What you *should* be doing is foreach() through $table, as each element should be the single row, but that brings us back to what VehSelection is.  

if (!empty($table)) {
    echo '<ul>';
    foreach ($table as $row) {
      echo "<li>{$row['FirstName']} {$row['LastName']} {$row['VehSelection']}</li>";
    }
    echo '</ul>';
}

 

This is a placeholder for how you ought to be foreach-ing through your result set row by row, and finding out what the heck you actually have in VehSelection.  I'm unclear why you aren't already doing something like this, unless you are doing a join of some sort, and your issue is that you have multiple rows for each person, because they have multiple vehicle selections.  

Looking back on the discussion of database design you had with people like Barand, it was pointed out that logically, a one-to-many from Form -> Vehicle should actually be a Many-to-Many resolution between Form >--< Vehicle.

Nomenclature and Semantics are highly valuable for an application.  Let's look at what your tables should actually be called, and structured:

• You:  Form
  • Should be:  person
• You: Vehicle
  • Should be person_vehicle
• Missing:  vehicle

You stated that you didn't want to restructure your database to use something like what Barand provided in great detail, but then also stated this is just a learning exercise.  If it's a learning exercise, then please examine what you have done so far, and look at "Refactoring" things that obfuscate your actual requirements.  You have a database structure that isn't relationally sound, and has an intrinsic problem -- you don't have a query that provides you a list of all "available" vehicles, from which a user can choose.   Thus you are hard-coding that list, even though your problems began with the fact that you don't have that list, and are trying to create it via an array variable.  

Here's a simplified version of the tables you should have, and the relationships between them.  I omitted a bunch of the other "person" fields you have for simplicity.

Hopefully you should now see that:

• SELECT * FROM vehicle can be used to populate your available checkboxes
• When saving the checked vehicles for a person, upon save you would:
  • Delete all person_vehicle rows where person_id = {the person.id of the person being updated}
  • Insert a new person_vehicle row for each checkbox that is checked
    • Setting person_id to the person you are updating this for AND vehicle_id = to the appropriate id of the vehicle (1 - 3

 

 

Barand had a fully fleshed out version of this with queries and a lot more, in an attempt to merge what you had with a many to many as illustrated here.  

I know the people who have responded to you previously, and they are exceedingly willing to help someone out, but they tend to exit a discussion when they don't see any forward progress, or work on the part of the person being helped.  When someone provides you some code, it's up to you to examine it, try and understand it, and determine if you should adopt it, or if it taught you an important concept.  Posting a reply that says:  "I tried your code but it didn't work"  is a great way to get ignored.   Show the code you attempted to use, and the specific errors you got.  Don't just say:  "It didn't work".  

 

Given that you are struggling, I am providing the database definition language SQL if you want to start over and use this structure.  You would need to alter the person table, to add the other columns you need, but it might be a good reset at this point, allowing you to get some clarity on what you are doing.  Whether you use it or not, I hope there are some things in my most recent replies that will help you find the forest through the trees.

 

# ---------------------------------------------------------------------- #
# Add table "person"                                                     #
# ---------------------------------------------------------------------- #

CREATE TABLE `person` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `firstName` VARCHAR(40),
    `lastName` VARCHAR(40),
    `birthDate` DATE,
    CONSTRAINT `PK_person` PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# ---------------------------------------------------------------------- #
# Add table "vehicle"                                                    #
# ---------------------------------------------------------------------- #

CREATE TABLE `vehicle` (
    `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(80) NOT NULL,
    CONSTRAINT `PK_vehicle` PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# ---------------------------------------------------------------------- #
# Add table "person_vehicle"                                             #
# ---------------------------------------------------------------------- #

CREATE TABLE `person_vehicle` (
    `vehicle_id` SMALLINT UNSIGNED NOT NULL,
    `person_id` INTEGER UNSIGNED NOT NULL,
    CONSTRAINT `PK_person_vehicle` PRIMARY KEY (`vehicle_id`, `person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# ---------------------------------------------------------------------- #
# Foreign key constraints                                                #
# ---------------------------------------------------------------------- #

ALTER TABLE `person_vehicle` ADD CONSTRAINT `vehicle_person_vehicle` 
    FOREIGN KEY (`vehicle_id`) REFERENCES `vehicle` (`id`);

ALTER TABLE `person_vehicle` ADD CONSTRAINT `person_person_vehicle` 
    FOREIGN KEY (`person_id`) REFERENCES `person` (`id`);

 

 

 

 

[webdeveloper123]

This is my SQL

SELECT VehicleId, VehSelection, Form.FormId, Form.FirstName, Form.LastName, Form.Email, Form.Age, Form.Birthdate, Form.FavLanguage FROM Form LEFT JOIN Vehicle ON Form.FormId = Vehicle.FormId WHERE Form.FormId = '$FormId'";

I understand about your database, I will do it on the next one. When I created this db, it was only the 2nd/3rd one in 15 years, so I didn't spot it straight away. I will take on board all comments and design the next one better. (as in the next project) Ok my db isn't that great and neither is my code, but it's not that bad surly that it won't work.  

[webdeveloper123]

3 hours ago, gizmola said:

There is no reason for you to then be trying to access those using the 0 array element.  

Could it be that i'm saving at position 0, and they are all over writing each other, instead of incrementing the array with the other values? I got the below code from a PHP book I bought, so I thought it was sound

 

3 hours ago, gizmola said:

  $first_name = $table[0]["FirstName"];
  $last_name = $table[0]["LastName"];
  $email = $table[0]["Email"];
  $age = $table[0]["Age"];
  $birthday = $table[0]["Birthdate"];
  $favlanguage = $table[0]["FavLanguage"];
  $VehSelection= $table[0]["VehSelection"];

 

[webdeveloper123]

Now I am getting conflicting advice. I was looping through the $table array and came up with this:

<?php
  if (!empty($VehSelection)) {
  foreach($table as $key => $value) {
    $VehSelection= $value["VehSelection"];
    ?>
  <input type="checkbox" id="<?php echo($value["VehSelection"]); ?>" name="vehicle[]" value="<?php echo($value["VehSelection"]); ?>" <?php echo ($VehSelection == 'Yacht' || $VehSelection == 'SuperCar' || $VehSelection == 'Plane'  ) ? 'checked' : ''; ?> />
  <label for="<?php echo($value["VehSelection"]); ?>" class="boxstyle"> <?php echo($value["VehSelection"]); ?></label><br>
   
  <?php 
  }}

?>

But that only ever generated the values in the db at the time, not all 3 all the time. Then I got this advice from another forum: @webdeveloper is me. 

@webdeveloper You need to foreach() over the list of possible values, NOT the list of values that are already checked from the DB.
 
someuser
someuser 
precisely; or, not use a foreach() loop at all, and just use separate if statements
 
someuser1

foreach (['plane', 'yacht', 'car'] as $v) {
  $checked = isset($already_checked[$v]) ? ' checked' " '';
  print "<input type=\"checkbox\" name=\"$v\" $checked />\n";
}
Or something like that.
 
webdeveloper
webdeveloper
ok thanks ill try that

[webdeveloper123]

5 hours ago, gizmola said:

and your issue is that you have multiple rows for each person, because they have multiple vehicle selections

Yes that's right

[webdeveloper123]

There is a typo in the post where I copy/pasted advice from another forum, it should read:

But that only ever generated the values in the db at the time, not all 3 all the time. Then I got this advice from another forum: @webdeveloper is me. 

 

someuser says :@webdeveloper You need to foreach() over the list of possible values, NOT the list of values that are already checked from the DB.
 

someuser1 says: 
precisely; or, not use a foreach() loop at all, and just use separate if statements
 
someuser1 says:

foreach (['plane', 'yacht', 'car'] as $v) {
  $checked = isset($already_checked[$v]) ? ' checked' " '';
  print "<input type=\"checkbox\" name=\"$v\" $checked />\n";
}
Or something like that.
 
webdeveloper says:

ok thanks ill try that

[webdeveloper123]

7 hours ago, gizmola said:

With that noted, what can be seen clearly, is that despite your claims as to what was actually selected, in all cases, there is only ever one value specified.  Even when your form checked "Yacht" and "Plane" this array only contains the single "Yacht" value. 

You need to determine why that is.

Yes that is the key thing here to make this code work. I tried using explode to convert the string into an array, but when I use:

echo $Vehicle[0]; 
echo $Vehicle[1];
echo $Vehicle[2];

only position 0 prints out, the others i get  offset errors

[Barand]

Let's shoot this horse and put it out of its misery.

There's still work to do but it illustrates another solution to your main problem.

$vehicles = [ 'Plane',
              'SuperCar',
              'Yacht'
              ];
              
$res = $db->query("SELECT  f.formid
                         , f.firstname
                         , f.lastname
                         , v.vehselection
                    FROM form f
                         LEFT JOIN 
                         vehicle v USING (formid) 
                    ORDER BY lastname
                    ");
$data = [];                    
foreach ($res as $r) {
    // process results
    // for each person create an array element
    // that contains an array of the vehicles owned
    // by that person
    if (!isset($data[$r['formid']]))   {
        $data[$r['formid']] = [ 'name' => $r['firstname'] . ' ' . $r['lastname'],
                                'vehTypes' => []
                              ];
    }
    $data[$r['formid']]['vehTypes'][] = $r['vehselection'];
}
    // the data array looks like this
    //
    //    $data = Array (
    //
    //        [193] => Array
    //            (
    //                [name] => John Atkins
    //                [vehTypes] => Array
    //                    (
    //                        [0] => SuperCar
    //                        [1] => Plane
    //                    )
    //
    //            )
    //
    //        [192] => Array
    //            (
    //                [name] => Frank Lampard
    //                [vehTypes] => Array
    //                    (
    //                        [0] => Yacht
    //                        [1] => Plane
    //                    )
    //
    //            )
    //
    //        ...
    //    )
?>

<style type='text/css'>
    table {
        border-collapse: collapse; 
        width: 600px;
        margin: 20px auto;
    }
    td, th {
        padding: 8px;
    }
</style>


<table border='1'>
    <tr>
        <th>Name</th>
        <th>Vehicles</th>
    </tr>
    <?php
    foreach ($data as $fid => $person)  {
        echo "<tr><td>{$person['name']}</td><td>";
        // now loop through the $vehicles array (top of script)
        // outputting a checkbox for each whic is checked
        // if the person owns one of that type
        foreach ($vehicles as $vtype) {
            $chk = in_array($vtype, $person['vehTypes']) ? 'checked' : '';
            echo "<label>
                  <input type='checkbox' name='vehicle[$fid][]' value='$vtype' $chk>
                  $vtype
                  </label>
                  <br>";
        }
        echo "</td></tr>\n";
    }
    ?>
</table>

[webdeveloper123]

Hey Barand, I decided to jump and try your solution, it works great. Not the layout I wanted and I wanted more on the "Edit" page by way of other data, but you weren't to know that. Thanks a lot, maybe I should have listened to you earlier. Im gonna try and change it to mysqli or add more data and HTML to your solution to make the layout/appearance the way I want. Thanks so much, and thanks for the patience! 

[webdeveloper123]

Hey Barand, the code you posted on page 1 of this thread, thats my next step isn't it? You spotted it early. The code Posted April 9 (edited) with the DELETE and INSERT statement. Am I correct?

[Barand]

36 minutes ago, webdeveloper123 said:

thats my next step isn't it?

It's one of your next steps.

The first thing you need to do is understand why that code I last posted works and yours didn't.

Edited April 16, 2022 by Barand

[webdeveloper123]

Hey I understood your code and I have incorporated it into my script, using mysqli and a different sql statement. This way I don't have to re-write my entire "Edit" page as it had a lot of other code too.