Multiple option and keeping selected value

Question

I have a form that includes a multiple select box populated with therapist names pulled from a database table.

This is my code:

<div class="form-group col-sm-6 col-md-4">
    <label for="therapist"><strong>Senior Practitional / SP: </strong></label>                                                
    <select name="therapist[]" id="therapist" multiple="multiple" class="form-control selectpicker" multiple data-live-search="true" data-live-search-placeholder="Search" data-actions-box="true" data-parsley-trigger="change" required-no>
        <?php
        require_once('include/database.php');

        // read current record's data
        try {
            // prepare select query
            $getUser = "select firstname, lastname, profession from user WHERE user_type = 'therapist'";
            $stmt    = $con->prepare($getUser);

            // execute our query
            $stmt->execute();

            // store retrieved row to a variable
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

                // values to fill up our form
                $firstname  = $row['firstname'];
                $lastname   = $row['lastname'];
                $profession = $row['profession'];

                echo '<option id="firstname_' . $row['id'] . '" value="' . $row['id'] . '">' . $row['firstname'] . ' ' . $row['lastname'] . ' - ' . $row['profession'] . '</option> ';
            }
        }

        // show error
        catch (PDOException $exception) {
            die('ERROR: ' . $exception->getMessage());
        }
        ?>
    </select>                                           
</div>

But the problem I am facing is that if I select multiple therapists I only get an integer of 1 in the result table.

** Second Issue **

The other issue I have is, I have a time_of_visit form field which is a multiple-select:

<?php $time_of_visit = $time_of_visit; ?>                       
<div class="form-group col-md-4">
    <label for="tov"><strong>Time of Visit: </strong></label>
    <select name="tov[]" class="form-control selectpicker" value='<?php echo $time_of_visit; ?>' multiple
        data-live-search="true" data-live-search-placeholder="Search" data-actions-box="true" data-parsley-trigger="change" required-no>
        <!-- <option selected>Select Visit Time...</option> -->
        <option name="tov[]" <?php if (isset($time_of_visit) && $time_of_visit == "am") echo "selected"; ?> value="am" selected>Am</option>
        <option name="tov[]" <?php if (isset($time_of_visit) && $time_of_visit == "lunch") echo "selected"; ?> value="lunch" selected>Lunch</option>
        <option name="tov[]" <?php if (isset($time_of_visit) && $time_of_visit == "pm") echo "selected"; ?> value="pm">Pm</option>
        <option name="tov[]" <?php if (isset($time_of_visit) && $time_of_visit == "am_pm") echo "selected"; ?> value="am_pm">Am or Pm</option>
        <option name="tov[]" <?php if (isset($time_of_visit) && $time_of_visit == "pm_care") echo "selected"; ?> value="pm_care">Pm Care</option>                                                                                                   
        <option name="tov[]" <?php if (isset($time_of_visit) && $time_of_visit == "single") echo "selected"; ?> value="5pm" selected>>5pm</option>
    </select>                                           
</div>

When the form is submitted I am getting time_of_visit cannot be null. I am guessing that I have probably set it to Yes NULL in the database which I can change later, but I can't seem to keep the selected option whether the query fails or is successful.

I think u_mulder got that one. There's also a much nicer way you can detect and set an option as selected: http://stackoverflow.com/questions/39070359/how-to-set-an-option-from-multiple-options-or-array-with-different-values-to-vie or http://stackoverflow.com/questions/18733545/selected-value-get-from-db-into-dropdown-select-box-option-using-php-mysql-error/39070055#39070055 — independent.guru, Apr 19 '17 at 18:58
@u_mulder... that didn't work well... i got a string `Array` in my result... `$getUser = "select id, firstname,`... `while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){ $id = $row['id']; $firstname = $row['firstname'];` — lekan oshodi, Apr 19 '17 at 19:10
I have an output page that fetch all the date in the task table. so in my therapy row i get `Array` result — lekan oshodi, Apr 19 '17 at 19:16
Three things you'll want to edit: 1) `require-no` and `value` are not allowed attributes for the `` tag. 2) In order to specify a multiple select tag, all you need to do is put `multiple` rather than `multiple="multiple"` and 3) The `name`attribute on the ` — Purple Lady, Apr 19 '17 at 19:24
@PurpleLady... thanks... i will amend that now... the `require-no` is me trying to disable the JS error validation to be able to test the form. when you say value not allow attribute... did you mean i need to delete this `value="'. $row['id'] .'"`? — lekan oshodi, Apr 19 '17 at 19:30
$time_of_visit = $time_of_visit; what does $time_of_visit actually equal and why is the variable written as equalling a copy of itself? — independent.guru, Apr 19 '17 at 19:31
That did not work either... have amend the multiple like this `multiple=""` and remove `require-no`. — lekan oshodi, Apr 19 '17 at 19:35
@PurpleLady... thanks for the explanation... i will amend properly now and i will let you know. — lekan oshodi, Apr 19 '17 at 19:37
@GCRdev... i get carried a way and i can't even remember why that was there. i will try amending that as well. give me a moment please — lekan oshodi, Apr 19 '17 at 19:38
This document will help a little with the coding of the select multiple function https://www.w3schools.com/tags/att_select_multiple.asp no need for a name in the options, just value, the name goes: — independent.guru, Apr 19 '17 at 19:45
As long as each option has a unique value, you should be fine in that regard. That link that @GCRdev pasted for you should help a lot. — Purple Lady, Apr 19 '17 at 19:46
I noticed that your last option in the time of visit select has mismatching values. Its original value is set to "5pm" if selected, but then you have it reset to the value "single" when the form is submitted. Is this what you meant to do? — Purple Lady, Apr 19 '17 at 19:52
@PurpleLady am getting `therapist cannot be null` having remove the value id — lekan oshodi, Apr 19 '17 at 19:52
You removed the `value`? Or `name`? The option tags still need to have the `value` attribute. — Purple Lady, Apr 19 '17 at 19:54
@PurpleLady... good eye there have amend that... the link that GCRdev is really good... but did not really help in my case — lekan oshodi, Apr 19 '17 at 19:57
@PurpleLady... am going to edit my original question to reflect just the therapist field and see if you can make it better to what you want it to be. — lekan oshodi, Apr 19 '17 at 20:00
yeah i know but then how do i do that when am pulling the data directly from database in to the drop menu. — lekan oshodi, Apr 19 '17 at 20:05
This would be easier to debug if your php was separate from your html. You could cut and paste all of your php and place it above your html output. Then instead of echoing the options, you can store them in a variable and echo that variable in your html. — Purple Lady, Apr 19 '17 at 20:22
How are you handling the form submission? Is there a submit button for the user to click? And where are your `
` tags? — Purple Lady, Apr 19 '17 at 20:25
sorry for the late reply I was driving. yeah I have a submit button on the form tag — lekan oshodi, Apr 19 '17 at 20:56

Purple Lady · Accepted Answer · 2017-04-21T16:04:20.820

I cleaned up your code a bit and rewrote it so you can test what happens when you submit the form. Try this and make sure the form outputs the correct id's that you selected.

<?php
require_once('include/database.php');

// read current record's data
try {
    // prepare select query
    $getUser = "SELECT id, firstname, lastname, profession FROM user WHERE user_type = 'therapist'";
    $stmt    = $con->prepare($getUser);

    // execute our query
    $stmt->execute();
    // initialize options variable so that it doesn't get overwritten
    $options = "";
    // store retrieved row to a variable
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        // values to fill up our form
        $firstname  = $row['firstname'];
        $lastname   = $row['lastname'];
        $profession = $row['profession'];
        /*use ".=" so that you add to the $options, rather than overwrite the last row*/
        $options .= '<option value=' . $row['id'] . '>' . $row['firstname'] . ' ' . $row['lastname'] . ' - ' . $row['profession'] . '</option>';
    }
}

// show error
catch (PDOException $exception) {
    die('ERROR: ' . $exception->getMessage());
}
// echo selections on form submit
    if(isset($_POST['submit'])) {
        foreach($_POST['therapist'] as $selection) {
            echo $selection . "\n";
        }
    ?>

    <div class="form-group col-sm-6 col-md-4">
        <form action="" method="POST">
            <label for="therapist"><strong>Senior Practitional / SP: </strong></label>                                                
            <select name="therapist[]" multiple>
                <?php echo $options; ?>
            </select>
            <input type="submit" name="submit" value="Submit"/>
        </form>
    </div>

UPDATE

The ultimate cause of your problem is that you are trying to insert an array into a sql database. You cannot insert an array directly into a sql table, so you must convert the array BEFORE inserting its values. See my edit below.

if(isset($_POST['submit'])) {

// Use the implode() function to convert the array into a comma-delimited string
$therapists = implode(',', $_POST['therapist']);
// echo $therapists . "<br>"; // Uncomment this line to see how the imploded array looks
// Now you can include the therapists in your INSERT statement like so:
$sql = "INSERT INTO `table` (`column_name`) VALUES ('".$therapists."')";
}
?>

<div class="form-group col-sm-6 col-md-4">
    <form action="" method="POST">
        <label for="therapist"><strong>Senior Practitional / SP: </strong></label>                                                
        <select name="therapist[]" multiple>
            <?php echo $options; ?>
        </select>
        <input type="submit" name="submit" value="Submit"/>
    </form>
</div>

To Display Each Therapist in their Own Column

Once you have queried your table and returned the values stored in the therapists column, you can use the explode() function to convert the string back into an array to display each therapist in a new column per your comment.

Suppose you store those values in the variable $therapists:

$string_to_array = explode(",", $therapists);
// print_r($string_to_array); // Uncomment this line to see how the new array looks
// Should look like this: Array ( [0] => therapist1 [1] => therapist2 [2] => therapist3 [3] => therapist4 )

// Now loop through the array to display each name
$columns = ""; // Initialize $columns to avoid being overwritten
foreach ($string_to_array as $key => $value) {
    $columns .= "<td>$value</td>";
}
?>
<!--Echo $columns in table row-->
<table>
    <tr><?php echo $columns; ?></tr>
</table>

sorry for the delay family time last night... i love the way you structure the code... have amend it on my end as well... but am still getting `Array` result. — lekan oshodi, Apr 20 '17 at 07:38
here is the result screen shot... https://snag.gy/s4nj3S.jpg. The first line out all the correct field was manually created from db. The follow two row was the data have posted. — lekan oshodi, Apr 20 '17 at 07:45
I see...so you'll need to handle the array slightly different from other input fields. Give me a moment to update my answer. — Purple Lady, Apr 20 '17 at 18:56
Thanks... really appreciate it... let me know pls when you updated. — lekan oshodi, Apr 20 '17 at 19:26
wow thanks that works very well. i notice he display `id delimiter` that's because I set the value to id instead of fetching the name value. so I change that and remove the `insert id` and I get the result. — lekan oshodi, Apr 21 '17 at 10:27
what I want to ask if you don't mind... how do I not comma delimited and display individually in each column. — lekan oshodi, Apr 21 '17 at 10:29
Awesome, glad to hear that! And you want to display the values stored in the sql table on your web page, correct? In that case, you would use the `explode()` function to convert the string back into an array that you could loop through and echo in each column. I will add that to my answer. — Purple Lady, Apr 21 '17 at 15:46
Hi Thanks for the reply... yeah not very familiar with using explode () and loop it will be great if you can kindly update the answer. so each therapist have a separate column in case I wanted to fetch or filter in future. — lekan oshodi, Apr 21 '17 at 15:52
Answer has been updated. That should get you started with displaying each name, and you can style your table as you wish. — Purple Lady, Apr 21 '17 at 16:05

Multiple option and keeping selected value

1 Answers1