Can you use data from a database in a combo box select form?

rocky48 · October 22, 2014

I have a simple database table that has the names of countries listed with just 2 fields ID & country.

I want to use this in a form to choose the country and provide statistics regarding that country.

I know how to get the data from MySQL, but I don't know how I can use this in the form as a selection?

How do I get the options to iterate all of the countries so that when the form is displayed a combo type box is displayed with a dropdown list?

I have not shown any code as I am not sure if this is possible.

Please can anyone advise if this is possible and if so some example code?

Psycho · October 22, 2014

Yes, it's absolutely possible. If you can put something into an HTML page - then it can be built dynamically via PHP.

You say you know how to query the DB and get the results. Just iterate through those results and build the select list. I usually have a function to build dynamic select lists instead of coding them individually.

//Function to build select options based on passed array
function buildSelectOptions($options)
{
    $optionsHTML = '';
    foreach($options as $id => $label)
    {
        $optionsHTML .= "<option value='{$id}'>{$label}</option>\n";
    }
    return $optionsHTML;
}
 
//Run query to get the ID and Name from the table
//Then populate into an array
$sth = $dbh->query('SELECT id, country FROM countries');
$sth ->setFetchMode(PDO::FETCH_ASSOC);
$countries = array();
while($row = $sth->fetch()) {
    $countries[$row['id']] = $row['country'];
}

//Create select list options
$countryOptions = buildSelectOptions($countries);
 

//Later in the HTML for the page
<option name="country">
    <?php echo $countryOptions; ?>
</option>

Edited October 22, 2014 by Psycho

rocky48 · December 13, 2014

Hi Psycho

I have not looked at this code until now but I can't get it to work.

I get the following error:

Fatal error: Call to a member function query() on a non-object in /homepages/43/d344817611/htdocs/Admin/CountryOptions.php on line 18


<?
include("AddStats_admin_connect.php");
//connect to database
		doDB();
//Function to build select options based on passed array
function buildSelectOptions($options)
{
    $optionsHTML = '';
    foreach($options as $id => $label)
    {
        $optionsHTML .= "<option value='{$id}'>{$label}</option>\n";
    }
    return $optionsHTML;
}
 
//Run query to get the ID and Name from the table
//Then populate into an array
$sth = $dbh->query('SELECT CID, Country FROM Countries');
$sth ->setFetchMode(PDO::FETCH_ASSOC);
$countries = array();
while($row = $sth->fetch()) {
    $countries[$row['CID']] = $row['Country'];
}

//Create select list options
$countryOptions = buildSelectOptions($countries);
 ?>

<!DOCTYPE html>
<html lang="en">
  <head>
   <meta charset="utf-8">
    <meta name="Description" content="Ecologic Theme">
    <meta name="author" content="CoffeeCup Software, Inc.">
    <meta name="Copyright" content="Copyright (c) 2011 CoffeeCup, all rights reserved.">
    <title>Stats</title>
    <link rel="stylesheet" href="stylesheets/default.css" />
    <!--[if IE]>
    <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->
  </head>
  <body>

//Later in the HTML for the page
<option name="country">
    <?php echo $countryOptions; ?>
</option>

</body>

I have changed the column names to suit my database viz CID, Country and Countries.

I normally use mysqli queries, but you seem to have used mysql!

I am not very familar with mysql and I am getting on in years, so your help would be appreciated.

NOTE it is line 19 in the code submitted.

Edited December 13, 2014 by rocky48

Ch0cu3r · December 14, 2014

I normally use mysqli queries, but you seem to have used mysql!

No... The code given by Psycho uses PDO for interacting with the database.

You are using MySQLi, so you cant just copy and paste the code and expect it to work. What you need to do now is change it so it queries the database using mysqli

Basically what the code is doing is querying the Countries table returning the CID and Country fields. It loops over the results and populates an array, using the CID field as the key and the Country field as the value. Once array has been populated it is passed to the buildSelectOptions() function which will then generate the <option>'s for the dropdown menu.

rocky48 · December 15, 2014

Thank you!

I have changed it to mysqli:

//Function to build select options based on passed array
function buildSelectOptions($options)
{
    $optionsHTML = '';
    foreach($options as $id => $label)
    {
        $optionsHTML .= "<option value='{$id}'>{$label}</option>\n";
    }
    return $optionsHTML;
}
 
//Run query to get the ID and Name from the table
//Then populate into an array
$clist_sql = ”SELECT CID, Country FROM Countries”;   <<This line errors>>
$clist_res= mysqli_query($mysqli, $clist_sql) or die(mysqli_error($mysqli));
if (mysqli_num_rows($clist_res) < 1) {
	//this Country not exist
	$display_block = "<p><em>You have selected an invalid Country.<br/>
	Please try again.</em></p>";
}

	while($Ctry_info = mysqli_fetch_array($clist_res)) {
		$Ctry_ID = ($Ctry_info['CID']);
		$Ctry_Name = ($Ctry_info['Country']);
        }

//Create select list options
$countryOptions = buildSelectOptions($countries);
 ?>

However I am getting a T_STRING error on line 18

Parse error: syntax error, unexpected T_STRING in /homepages/43/d344817611/htdocs/Admin/CountryOptions.php on line 18

I have checked the usual suspects, missing quotes or semi-colon, but I can't see any thing wrong.

What am I missing?

Edited December 15, 2014 by rocky48

Psycho · December 16, 2014

What are you using to edit your files? Are you copy/pasting code it?

The problem is your quotes! When coding in PHP you can either use the strait single quotes or the strait double quotes to define strings.

These are the strait quotes: ' and "

You cannot use the angled quotes when defining strings. These are types of angled quotes: ”, “ and ‘

In the code you posted, the string you are trying to define on that line is using angled quotes.

rocky48 · December 17, 2014

Hi Psycho

I foolishly used MS Word (a thing I have never done before). I did not know there was a difference, but thats MS for you.

It now does not like the foreach line and gives this error:

Warning: Invalid argument supplied for foreach() in /homepages/43/d344817611/htdocs/Admin/CountryOptions.php on line 9

I am not sure when to go from here?

CroNiX · December 17, 2014

We don't know what you've changed/altered from the original code so "line 9" is pretty meaningless. You should post your new code.

But...from what I can see:

$countryOptions = buildSelectOptions($countries);

Where is $countries being created that you are sending to buildSelectOptions()?

Also, this isn't doing anything except continuously writing over the same variables and then you never do anything with them anyway.

while($Ctry_info = mysqli_fetch_array($clist_res)) {
        $Ctry_ID = ($Ctry_info['CID']);
        $Ctry_Name = ($Ctry_info['Country']);
}

You probably want to be doing:

$countries = array();
while($Ctry_info = mysqli_fetch_array($clist_res)) {
        $countries[$Ctry_info['CID'] = $Ctry_info['Country'];
}
$countryOptions = buildSelectOptions($countries);

rocky48 · December 18, 2014

Thank you for your help!

Changing that removed the foreach error.

However I still have another problem with the code you gave.

I get this error:

Parse error: syntax error, unexpected ';', expecting ']' in /homepages/43/d344817611/htdocs/Admin/CountryOptions.php on line 27

Here is the code so far:

<?
include("AddStats_admin_connect.php");
//connect to database
		doDB();
//Function to build select options based on passed array
function buildSelectOptions($options)
{
    $optionsHTML = '';
    foreach($options as $id => $label)
    {
        $optionsHTML .= "<option value='{$id}'>{$label}</option>\n";
    }
    return $optionsHTML;
}
 
//Run query to get the ID and Name from the table
//Then populate into an array
$clist_sql = "SELECT CID, Country FROM Countries";
$clist_res= mysqli_query($mysqli, $clist_sql) or die(mysqli_error($mysqli));
if (mysqli_num_rows($clist_res) < 1) {
	//this Country does not exist
	$display_block = "<p><em>You have selected an invalid Country.<br/>
	Please try again.</em></p>";
}
$countries = array();
while($Ctry_info = mysqli_fetch_array($clist_res)) {
        $countries[$Ctry_info['CID'] = $Ctry_info['Country'];
}
$countryOptions = buildSelectOptions($countries);


<!DOCTYPE html>
<html lang="en">
  <head>
   <meta charset="utf-8">
    <meta name="Description" content="Ecologic Theme">
    <meta name="author" content="CoffeeCup Software, Inc.">
    <meta name="Copyright" content="Copyright (c) 2011 CoffeeCup, all rights reserved.">
    <title>Stats</title>
    <link rel="stylesheet" href="stylesheets/default.css" />
    <!--[if IE]>
    <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->
  </head>
  <body>

<!Later in the HTML for the page
<option name="country">
    <?php echo $countryOptions; ?>
</option>

</body>
</html>

I can't spot any missing semi-colons or quotes, so what is causing this error?

mikosiko · December 18, 2014

Read the error message that you are getting very...very..very carefully again... Reason is there in plain sight

CroNiX · December 18, 2014

There is a basic syntax error I had in my code that you copied and pasted. I didn't test it. I just created it here in a post. It should be obvious if you study that line the error message states. Sorry for not just telling you what it is. This is basic php stuff and you NEED to know how to do it to debug code or you will never be able to code anything on your own. It's an essential/required skill you really need to learn.

rocky48 · December 18, 2014

Sorry guys! My sight must be failing! I must getting too old for this game.

Did not spot the stray [ bracket without its partner.

Edited December 18, 2014 by rocky48

rocky48 · December 19, 2014

Right! now the silly errors are out of the way. I have tested the script!

It's not working! All I get is a Submit button, but no data.

I am unfamiliar with the function and the use of the FOREACH constuct.

looking at the PHP manual there are two syntaxes. It appears that Psyhco has used the second form where the current element's key is assigned to $key variable for each itteration.

foreach (array_expression as $key => $value

The 2 variables he used are $id and $label

I assume that the line: $optionsHTML = ''; is the array built from the expression: $optionsHTML .= "<option value='{$id}'>{$label}</option>\n";

Because I was not familiar with the PDO method, but have used mysqli, I had to rewrite the data retrival part which I believe is OK.

I guess that the line : $countryOptions = buildSelectOptions($countries); is used in conjunction with the function to build the array.

The HTML part that Psycho wrote puts the variable into the Form format for a selection list.

Why is the $optionsHTML also inclosed in the option tags?

Have I got the HTML part correct or is it the data extraction part that is incorrect?

Here is the code:

<?
include("AddStats_admin_connect.php");
//connect to database
		doDB();
//Function to build select options based on passed array
function buildSelectOptions($options)
{
    $optionsHTML = '';
    foreach($options as $id => $label)
    {
        $optionsHTML .= "<option value='{$id}'>{$label}</option>\n";
    }
    return $optionsHTML;
}
 
//Run query to get the ID and Name from the table
//Then populate into an array
$clist_sql = "SELECT CID, Country FROM Countries";
$clist_res= mysqli_query($mysqli, $clist_sql) or die(mysqli_error($mysqli));
if (mysqli_num_rows($clist_res) < 1) {
	//this Country not exist
	$display_block = "<p><em>You have selected an invalid Country.<br/>
	Please try again.</em></p>";
}
$countries = array();
while($Ctry_info = mysqli_fetch_array($clist_res)) {
        $countries[$Ctry_info['CID'] = $Ctry_info['Country']];
}
$countryOptions = buildSelectOptions($countries);
?>

<!DOCTYPE html>
<html lang="en">
  <head>
   
    <title>Stats</title>
    <link rel="stylesheet" href="stylesheets/style.css" />
    <!--[if IE]>
    <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
    <![endif]-->
  </head>
  <body>
<? echo $countryOptions; ?>
</br></br></br></br></br></br></br></br>
<!Later in the HTML for the page
<form action="Ctrystats.php" method="post">				
                <option name="country" value=<? echo $countryOptions;?>Country</option></br></br>
		<input type="submit" value="Submit Choice">
</form></p>


</body>
</html>

I think I am nearly there so I would appreciate some help to finish this coding.

Edited December 19, 2014 by rocky48

rocky48 · December 31, 2014

SEE LATER THREAD

Sign In

Can you use data from a database in a combo box select form?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information