Jump to content

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


rocky48
Go to solution Solved by rocky48,

Recommended Posts

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?

Link to comment
Share on other sites

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 by Psycho
Link to comment
Share on other sites

  • 1 month later...

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 by rocky48
Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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 by rocky48
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by rocky48
Link to comment
Share on other sites

  • 2 weeks later...
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.