We help IT Professionals succeed at work.
Troubleshooting Question

Select box from table not populating

David Schure
David Schure asked
on
57 Views
Last Modified: 2020-08-26
I am trying to populate a select box from a table...
<?php
     $resultSet = $sql = ("SELECT * FROM tbl_states");
   ?>
    <span>Select Your Current Accreditations</span><br/>
    <form method="post" action="">
    <select name="States">
     <?php
     while ($rows = $resultSet->fetch_assoc())
    {
      $state_id = $rows['state_id'];
       echo "<option value='$state_id'>$state_id</option>";
      $state_name = $rows['state_name'];
       echo "<option value='$state_name'>$state_name</option>";
    }
    ?>
     </select>  
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hey David,,

You've never actually ran your query;

You need something like this:

$resultSet = $con->query("SELECT state_id, state_name FROM tbl_states ORDER BY state_name");

Open in new window

The above assumes your DB connection is stored in the $con variable.

Are you really wanting 2 options per record in your database, or should there just be the one

<?php
while ($state = $resultSet->fetch_assoc()) {
    printf('<option value="%s">%s</option>', $state['state_id'], $state['state_name']); 
}
?>

Open in new window

Couple of tips that might help you write cleaner code. First off, when running SELECT statements against a DB, it's considered good practice to specify the columns you want, rather than just doing SELECT *. It's just more efficient and gives you cleaner code - you can immediately see what your results will contain.

Have a look at the printf (and sprintf) commands in PHP - they're a great way of avoiding complicated string concatenation operations, and again can often give you cleaner code.

Author

Commented:
Thank you Chris.  That worked beautifully.  I need both fields because I need the state_id for the query and the name needs to be displayed for the user.  This select box will sort things out by state for the user. Thank you once again!

Author

Commented:
Like this....
<form method="post" action="">
    <select name="States">
     <?php
     while ($state = $resultSet->fetch_assoc()) {
    printf('<option value="%s">%s</option>', $state['state_id'], $state['state_name']); 
}
    ?>
     </select> 
     </br>     
   <?php
   
   if ($result = $con->query("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = state_id")) {   
    while ($row = mysqli_fetch_array($result)) {
    printf('<input type="checkbox" name="accreditation_name[]" value="%s"> %s<br>', $row['accreditation_id'], $row['accreditation_name']);
    }
   }
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good stuff,

For the record, when you set an option, like so:

<option value="1">Chris Stanyon</option>

It's the Chris Stanyon part that he user sees and selects, but it's the value part that gets submitted when they send the form. Normally, you only need the value bit:

echo $_POST['States']; // this will output "1"

As you currently have it, you will end up with 2 selectable options for each record in the database:

1
Alaska
2
California
3
Hawaii

This would allow your user to select 1 as a State which doesn't really make a lot of sense !

Author

Commented:
Thank you.  That is what I was trying to say.....
if ($result = $con->query("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = $_POST['States'];")) {      
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmm. The first bit of your code look OK, assuming you have the $resultSet = $con->query() bit

The second query doesn't really make sense, particularly the WHERE bit:

WHERE accreditation_state_id = state_id

That;s not using any state_id that the user has selected, so unles you've missed out a large chunk of your code, it's not going to work. You only have access to the <select> value AFTER the user has submitted the form, and because that's user input, you'd need to be running a prepared statement and binding to the $_POST['States'] value
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Sorry - we cross posted.  That's the general idea, but move to using a prepared statement

Author

Commented:
Now you lost me....
if ($result = $con->query("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = $state")) {   

Author

Commented:
if ($result = $con->query("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = ' .$state.'")) {   
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Sorry David,

I think we cross-posted a couple of times.

Basically, you have a <select name="States">...</select>. When the user submits the form, the value they selected will be available in the $_POST['States'] key.

Your query above is basically correct, but you shouldn't be passing the value of $state straight into it.  Because this qualifies as User Input, you should always use a Prepared Statement. Failing to do this will leave your app vulnerable to SQL Injection attacks. Something like this:

$state_id = $_POST['States'];

$stmt = $con->prepare("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = ?");
$stmt->bind_param("i", $state_id);
$stmt->bind_result($id, $name);
$stmt->execute();

while ($stmt->fetch()) {
    printf('<input type="checkbox" name="accreditation_name[]" value="%s"> %s<br>', $id, $name);
}

Open in new window

Author

Commented:
It coughed this up...
Notice: Undefined index: States in /home/audiodigz/public_html/THERAPIST/edit-accreditations.php on line 96

Fatal error: Uncaught Error: Call to a member function free_result() on boolean in /home/audiodigz/public_html/THERAPIST/edit-accreditations.php:119 Stack trace: #0 {main} thrown in /home/audiodigz/public_html/THERAPIST/edit-accreditations.php on line 119

Author

Commented:
Notice: Undefined index: States in /home/audiodigz/public_html/THERAPIST/edit-accreditations.php on line 96
The offending line.
$state_id = $_POST['States'];


Author

Commented:
Some context...
<?php
     $resultSet = $con->query("SELECT state_id, state_name FROM tbl_states ORDER BY state_name");
   ?>
    <span>Select Your Current Accreditations</span><br/>
    <form method="post" action="">
    <select name="States">
     <?php
     while ($state = $resultSet->fetch_assoc()) {
    printf('<option value="%s">%s</option>', $state['state_id'], $state['state_name']); 
}
    ?>
     </select> 
     </br>     
   <?php
   
   $state_id = $_POST['States'];

$stmt = $con->prepare("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = ?");
$stmt->bind_param("i", $state_id);
$stmt->bind_result($id, $name);
$stmt->execute();

while ($stmt->fetch()) {
    printf('<input type="checkbox" name="accreditation_name[]" value="%s"> %s<br>', $id, $name);
}
    ?>
    <div>&nbsp;</div>
    <input type="submit" value="Submit" class="btn btn-o btn-primary" name="submit">
   <form>
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
If your <select> is called States:

<select name="States">...</select>

AND you've submitted your form, then the States key should exist. If this is all on the same page as the <select> then you'll need to add a conditional check to make sure you've submitted the form:

if (!empty($_POST)) {
    // the form has been submitted so we should be good to go:
    $state_id = $_POST['States'];
    ...
}

Open in new window

Author

Commented:
No errors popped up.  
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Always a good sign :)

Now select a State and submit the form - you should see a list of the checkboxes that match records from your DB

Author

Commented:
It's back!
Notice: Undefined index: States in /home/audiodigz/public_html/THERAPIST/edit-accreditations.php on line 98
 $state_id = $_POST['States'];
is 'States' the name of the select box?
When I hard code a number in.  It works..
if ($result = $con->query("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = 31"))
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yes - States is the name of the <select> and it will be a key of the relevant array - POST or GET depending on your <form>s METHOD, so if you have this:

<form method="post">
    <select name="States">
        <option value="1">Alaska</option>
        <option value="2">California</option>
        <option value="3">Hawaii</option>
    </select>
    <input type="submit" value="Go">
</form>

Open in new window

After the User has selected an option and click the Go button, you would access the value with this:

$value = $_POST['States'];

Author

Commented:
but it's not doing that for whatever reason....anything wrong here?

Author

Commented:
This brings up every record that doesn't have a state attached to it.
$state_id = isset($_POST['States']) ? $_POST['States'] : '';
This brings up all of the accreditation's of Delaware  7
 $state_id = isset($_POST['States']) ? $_POST['States'] : '7';
So it seems that the state_id is not being passes from the select box.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. Time for some debugging.

Run this code and see what you get:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
require_once 'db.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>EE // 29192685</title>
</head>
<body>

    <form method="post">
        <select name="States">
            <?php
            $states = $con->query("SELECT state_id, state_name FROM tbl_states ORDER BY state_name");
            while ($state = $states->fetch_assoc()) {
                printf('<option value="%s">%s</option>', $state['state_id'], $state['state_name']); 
            }
            ?>
        </select>
        <input type="submit" value="Go">
    </form>

    <h2>RESPONSE</h2>

    <?php
    if (!empty($_POST)) {
        var_dump($_POST);
    } else {
        echo "<p>FORM NOT SUBMITTED YET</p>";
    }
    ?>

</body>
</html>

Open in new window

It assumes your db.php sets up your connection in the $con variable
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
This brings up every record that doesn't have a state attached to it.

That tells me that your code isn't seeing the $_POST['States'] key, so it's setting state_id to an empty string and using that in the query.

Author

Commented:

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Good so far.  What do you get when you submit the form

Author

Commented:
This...
array(3) { ["States"]=> string(1) "1" ["accreditation_name"]=> array(3) { [0]=> string(2) "14" [1]=> string(2) "15" [2]=> string(2) "16" } ["submit"]=> string(6) "Submit" }
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. So you clearly have the States key there with a value of "1". So now just drop your next query into the place where you have the var_dump():

<?php
if (!empty($_POST)) {
    $state_id = $_POST['States'];
    // now run your other query
} else {
    echo "<p>FORM NOT SUBMITTED YET</p>";
}
?>

Open in new window

Author

Commented:
Does the select box need to be on a separate form to be submit so that the second part loads up?  Posting again for clarity.
<?php
     $resultSet = $con->query("SELECT state_id, state_name FROM tbl_states ORDER BY state_name");
   ?>
    <span>Select Your State of Practice</span><br/>
    <form method="post" action="">
    <select name="States">
     <?php
     while ($state = $resultSet->fetch_assoc()) {
    printf('<option value="%s">%s</option>', $state['state_id'], $state['state_name']); 
   }
   ?>
    </select>
     </br> 
     </br>     
   <span>Select Your Accreditation's</span><br/>
   <?php
   $state_id = isset($_POST['States']) ? $_POST['States'] : '';   
   
   $stmt = $con->prepare("SELECT accreditation_id, accreditation_name FROM tbl_accreditation WHERE accreditation_state_id = ?");
   $stmt->bind_param("i", $state_id);
   $stmt->bind_result($id, $name);
   $stmt->execute();

   while ($stmt->fetch()) {
    printf('<input type="checkbox" name="accreditation_name[]" value="%s"> %s<br>', $id, $name);
   }
   ?>
    <input type="submit" value="Submit" class="btn btn-o btn-primary" name="submit">
      </form>
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Errr. no. I think the problem you've having is down to how you understand form data. Your script will NEVER have the States key until AFTER the form (select) has been submitted, so you can't run your second query until your user has actually selected a State and then submitted the form.

You're trying to run the second query before you've ever got the data (the state_id) you need to run it. The POST array is empty until you've submitted the form

Author

Commented:
Your right...my understanding of execution is limited.. learning.  So how do I submit the select box so that the next query can run?
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Haha - no worries. We're all learning ... there's so many moving parts when you're working on Web Development, it can get tricky :)

The way to submit the States value is exactly like we have been doing. You have your <select> in a form along with a Submit button. Once the User clicks the Submit button, you'll have the value selected available in the POST array - specifically the $_POST['States'] key. Once you've got that value, you can use it in your next query. We check whether the form has been submitted like I showed earlier - by seeing if the $_POST array is empty or not.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
There is actually another approach to this, but it gets more tricky. It involves splitting your code up and implementing some Javascript so that you can make an AJAX request. This would allow you to submit the selected States 'behind-the-scenes' and then pull in an HTML partial for the Checkboxes. For now though, I would probably avoid that - it just adds additional complication.

Author

Commented:
It's working now but it's ugly! LOL!  I have the select box on a separate form.  When I used to program in MS Access they had an after update on select boxes that would filter out the list.  Is there a way of doing that in this case?  after the select box updates (without a c
submit button) the checkboxes will display accoedingly?

The state is New York but after submitting it goes back to Alaska the first choice in the select box.  Like I said. It's ugly. But working..

Author

Commented:
I like the Ajax idea.  It sounds so much cleaner.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmmm. The AJAX is the preferred way of doing it. By using Javascript, you can attach an event-handler to different actions within the browser, so in your case, you would attach an event handler to the 'change' event of the <select>. This would then make an Asynchronous Background Request to the server - passing the selected State back to a script. That script would use that value to query the database and retrieve the correct records. It would then build the HTML for the checkboxes and then send that back to your Javascript code. Your JS code would then inject it into the HTML document - all of this would happen without the user leaving the page or having to submit the form.

Like I said though - it is more complicated as we're adding in a few new moving parts, but if that's the route you want to take, I can help you through it.

Author

Commented:
Thank you Chris....I am always willing to learn.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Gimme a few minutes and I'll post up some code

Author

Commented:
Sure...thank you.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
putting it together...

Author

Commented:
getting this message...

Author

Commented:
ran it without checkboxes.php
this came back
<br />

<b>Notice</b>:  Undefined index: accreditation_name in <b>/home/audiodigz/public_html/THERAPIST/edit-accreditations.php</b> on line <b>19</b><br />

<br />

<b>Warning</b>:  Invalid argument supplied for foreach() in <b>/home/audiodigz/public_html/THERAPIST/edit-accreditations.php</b> on line <b>19</b><br />

Author

Commented:
foreach ($_POST['accreditation_name'] as $accreditation_id) {  this line  accreditation_name not in tbl_therapist_accreditation 

Author

Commented:

Author

Commented:
hi Chris.  I’m fading.  I’ve been up since 5:00 am... can we pick up tomorrow? please. thank you.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Morning David,

OK. the screen shot of your code is a Dreamwever issues, so ignore it - it's irrelevant.

The Undefined index: accreditation_name error you're getting has nothing to do with the code I posted (I don't ever use it), so it looks like you've already started adding in your own code. I advised against doing that for the time being. Start basic with the code I provided. Once that's working, then you can start to add in your parts.

The Uncaught ReferenceError: $ is not defined tells me that you haven't loaded up the jQuery library. Again, you've gone ahead and started doing your own thing. Your code shows that you're dropping the script onto line 307, where as in my code, it was on line 10, so you're not using the code I provided.

The reason I suggseted using my code is so that we're both on the same page. It will help you to understand what's going on. If you're just trying to add some of my code to some of your code without really understanding it, it's likely to fail. It's like trying to run before you can walk.

Go back to my previous code example and run it exactly as I suggested. Keep it simple, remove all the white noise and just work on the fundamentals of a very simple AJAX request.

Author

Commented:
My apologies Chris.  Like I said, I faded after 15 hours of work!  This is what I have now...

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. What's happening in your WebDev Console. When you change the select box (i.e. choose a different option), the AJAX request should be fired off to the server-side script (checkboxes.php) and that script should query the database, builld the HTML for the checkboxes and return it.

Make sure you Console is open, reload your page and then try changing the <select>. See what's reported in the console (any errors / responses etc).

Author

Commented:
Morning Chris...

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK. So the checkboxes.php scripts has a server error 500, so we need to debug that. Easiest way to do that is to submit directly to it, rather than by using AJAX.

For now, change the name of the <select> to 'state' and add an action property to the form:

<form method="post" action="checkboxes.php">
    <select name="state">
        ...
    </select>

    <div id="checkboxes"></div>

    <input type="submit" value="Go">
</form>

Open in new window

Now reload your page, select a state from the dropdown and click the Submit button. Instead of making an AJAX request, it will now post the form directly to the checkboxes.php script.

If you haven't already, make sure error reporting is turned on (easiest way is to add it to the top of your db.php connection file).

When you do that, post up what you get.

Author

Commented:

Author

Commented:
When I open the checkboxes.php directly I get..
{"success":false,"message":"No State Submitted"}
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Yeah - you can't open the page directly, because there's no POST data to use, so it will kick back the error response we've generated.

The fact that you're getting a 500 Page isn't working tells me you don't have error reporting turned on, so it's just failing silently. Turn on error reporting and try again.

Author

Commented:
I had error on chris.php added it to checkboxes.php  the results
Notice: Undefined variable: con in /home/audiodigz/public_html/THERAPIST/checkboxes.php on line 18

Fatal error: Uncaught Error: Call to a member function prepare() on null in /home/audiodigz/public_html/THERAPIST/checkboxes.php:18 Stack trace: #0 {main} thrown in /home/audiodigz/public_html/THERAPIST/checkboxes.php on line 18 chris.phpcheckboxes.php
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
OK,

If you have an undefined variable $con, then you haven't included your database connection code. In my code, right at the top, I have this:

<?php
require_once 'db.php';

Open in new window

That file would contain the code that connects to your database and stores the connection in the $con variable. I'm assuming you already have a file for that otherwise the main page with the <select> would work either.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Hmm. Just checked both your files, and the require_once is including different files from different locations:

require_once('../includes/config.php');
require_once('includes/config.php');

The first one is including a file from the folder ABOVE the current folder. The second one is including a file from WITHIN the current folder. You must have 2 differnt config.php files in 2 different folders.

Author

Commented:
Chris this is what is in my config file...
<?php
define('DB_SERVER','localhost');
define('DB_USER','');
define('DB_PASS' ,'');
define('DB_NAME', '');

$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
// Check connection
if (mysqli_connect_errno())
{
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
defines removed.

Author

Commented:
That was it.  The other config file.  Both the same now.  Pure Vanilla...

CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Perfect. So we now know we're getting a proper JSON response from the checkboxes.php file, so we can go back to your main page and reinstate the AJAX call.

Remove the action="..." from the <form> tag, and in you Javascript, change the selector to 'state' (we might as leave the <select> called state and change the jQuery selector).

<form method="post">


$(function() {
    $('select[name=state]').change(function() {

Open in new window

Now load up your main page, and change the <select>. See what you get :)

Author

Commented:
Chris!  This is so perfect!!!!!!!!!  So can I drop this into my page now>  Or, do I have to change something!  I love the fact that the select box displays the check boxes automatically!
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Haha - excellent stuff David.

The point of breaking this part of the app away from your other code was so that hopefully you can see the individual moving parts without the rest of your app getting in the way (white-noise). Now you've done that, hopefully, you'll start to understand how it all works together, so the next challenge will be to integrate it back into your own page.

Basically, you need to add in the Jquery library as well as the jquery change script. Take note of the name in the selector and make sure it matches your <select name="..."> box. You'll still need the separate checkboxes.php file (call it whatever you need to). Make sure you've got a placeholder for the AJAX response to go into. In my code it was the <div id="checkboxes"> element.

Good luck with it :)

Author

Commented:
Chris it's in and working!  Thank you so very much!  Have a most wonderful day!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.