Problem with using MYSQL data in select form

rocky48 · December 30, 2014

This is a continuation of a previous post.
I am trying to get the names of countries that are in a database table.
It's not working! All I get is a Submit button, but no data.
This is what I understand from the code that Psycho suggested:
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 .= "{$label}\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.

Note: If you make your questions easy to read, then you have better chances of a quality answer.
Use [ code ] ] tags.

boompa · December 30, 2014

<option name="country" value=<? echo $countryOptions;?>Country</option>

That should be a select element, not an option.

maxxd · December 30, 2014

Couple changes and updates. This hasn't been tested and I'm still only on my second cup of coffee, but give it a shot and let us know how it goes.

<?php  //don't use short tags to open your file - not all servers recognize them and it's bad pracatice
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 - actually, the user hasn't selected a country yet so this is just an empty result set, not user error.
	$display_block = "<p><em>You have selected an invalid Country.<br/>Please try again.</em></p>";
	//Also, you do nothing with $display_block, so why set it?
}
$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.goo...5.js"></script>
<![endif]-->
</head>
<body>
<!-- Deleted the echos and the many, many line breaks -->
<!--Later in the HTML for the page -->
<form action="Ctrystats.php" method="post">
<!-- You need to set up the select element to house the options. This will be set in $_POST as $_POST['country'] -->
<select name="country">
	<?php echo $optionsHTML; ?>
</select>
<input type="submit" value="Submit Choice">
</form>
</body>
</html>

Other things:

Don't use the mysql_* functions. They were deprecated about a decade ago and will be removed from php at some point in the future. Look into PDO and prepared statements.
When posting code to the board, please use the code formatting button (< > in the toolbar) - it preserves whitespace and tabs, which makes everything easier to read.
Take a close look at line 27 - the closing square bracket for the $countries array was misplaced and has been moved.

Hope that helps a little.

maxxd · December 30, 2014

Just got a chance to come back to this thread and realized you were using mysqli_*, not mysql_*. Sorry about that. One would think I would've learned by now not to post before my fourth cup of coffee in the morning...

hansford · December 31, 2014

My two cents:

$countries[$Ctry_info['CID'] = $Ctry_info['Country']];

What is this - trying to assign a row value into another row value and putting it in the $countries array.

If you're trying to get the country name and then the country id and assign them each to the same array, then try this:

$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']); 

buildSelectOptions( $countries );

function buildSelectOptions($options)
{
$optionsHTML = "<select name=\"countries\">\r\n";

foreach($options as $c)
{
$optionsHTML .= "<option value=\"{$c[0]}\">$c[1]</option>\r\n";
}
$optionsHTML .= "</select>\r\n";

return $optionsHTML;
}

ginerjm · December 31, 2014

No - he's building an array from the query results so that he can pass it to a function that he copied that builds his option tags.

What he should do is eliminate that other function and build his option tags in the fetch loop and forget the middle-man array.

hansford · December 31, 2014

I was going to mention cutting out the middle man, but thought - maybe he uses it for other things, besides building a select array.

But I was referring to this anomaly in the code:

$countries = array(); 
$Ctry_info = array(); 
$Ctry_info['CID'] = 107;
$Ctry_info['Country'] = 'Spain';
print_r( $Ctry_info ); // key value array
$countries[$Ctry_info['CID'] = $Ctry_info['Country']]; // error
print_r( $countries );
exit;

rocky48 · December 31, 2014

Right!

Looked at all your comments and incorperated both maxxd code where different and Handsford's 2 cents, regarding function.

Here is the code as I see it:

<?
include("AddStats_admin_connect.php");
//connect to database
		doDB();
//Function to build select options based on passed array
$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']); 

function buildSelectOptions($options)
{
$optionsHTML = "<select name=\"countries\">\r\n";

foreach($options as $c)
{
$optionsHTML .= "<option value=\"{$c[0]}\">$c[1]</option>\r\n";
}
$optionsHTML .= "</select>\r\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['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="CountryOptions.php" method="post">
<!-- You need to set up the select element to house the options. This will be set in $_POST as $_POST['country'] -->
<select name="country">
<?php echo $optionsHTML; ?>
</select>
		<input type="submit" value="Submit Choice">
</form></p>


</body>
</html>

I now get a small empty combo box with no data in it?

I think the trouble must be the Function, but I am not an expert.

I did not understand the line:

       $countries[$Ctry_info['CID'] = $Ctry_info['Country']];

So changed it to this:

$countries = $Ctry_info['Country'];

But got the following error when run:

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

I guess that the function was not being run, as the code dropped out before it got to the function.

I'm guessing this am I right?

Any more ideas!

By the way I will look at PDO, but thats one step too far at the momment.

maxxd · December 31, 2014

You're now overwriting the value of $countries with every iteration of the loop. You wanted the array, which is why I corrected that line in my post and pointed it out specifically in the description following the code block.

$countries[$Ctry_info['CID']] = $Ctry_info['Country'];

What this line is doing is creating an associative array ($countries) containing the country names as values with that country's database ID as the index. Like so:

$countries[0] = 'United States';
$countries[1] = 'England';
$countries[3] = 'France';
$countries[4] = ...

Also, if you have the <select /> element in your HTML, you don't need it again in your buildSelectOptions() function. At line 6, $Ctry_info doesn't exist yet - you don't define it until line 31, and this should be throwing an error. You're not looping through the array correctly in the buildSelectOptions() function - remember the country names are indexed by their ID's. so you need to use $id => $label, as in my earlier example. You're still assigning an unused variable with the wrong error message if there are no records returned from the database.

rocky48 · December 31, 2014

OK!

I have corrected this line:

<option value='{$id}'>{$label}</option>\n

And this line:

foreach($options as $id => $label)

added a print_r to check the array output.

It prints all of the data, but still the combo box is empty!

The line 6:

$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']);

Why is this before the function?

I wondered that this should have replaced line 30:

$countries = array();

But this did not make any difference.

Still an empty combo box.

maxxd · December 31, 2014

Post your code here again in full so we can see what it looks like at this point.

rocky48 · December 31, 2014

Here is the code so far:

<?
include("AddStats_admin_connect.php");
//connect to database
		doDB();
//Function to build select options based on passed array
$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']); 

function buildSelectOptions($options)
{
$optionsHTML = "<select name=\"Countries\">\r\n";

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);

print_r($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="CountryOptions.php" method="post">
<!-- You need to set up the select element to house the options. This will be set in $_POST as $_POST['country'] -->
<select name="country">
<?php echo $optionsHTML; ?>
</select>
		<input type="submit" value="Submit Choice">
</form></p>


</body>
</html>

By the way the value I need to POST is the CID, but need the country for the user to identify the country.

Your help is appreciated!

Happy New Year! I guess in about 6 hours for you.

Edited December 31, 2014 by rocky48

CroNiX · December 31, 2014

<select name="country"><?php echo $optionsHTML; ?></select>

You named that $countryOptions here (not $optionsHTML)

$countryOptions = buildSelectOptions($countries);

So you need to replace $optionsHTML with $countryOptions where you are outputting your <select>

The only place $optionsHTML lives is in your buildSelectOptions() function, so only that function knows what it is.

Do you have error reporting turned on? You should be getting PHP errors/warnings for using things that aren't defined like $optionsHTML where you output it in your <select>.

Edited December 31, 2014 by CroNiX

CroNiX · December 31, 2014

Also remove this line:

$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']);

It's doing absolutely nothing and is being overwritten further down in your code here:

```
$countries = array();
```

Edited December 31, 2014 by CroNiX

CroNiX · December 31, 2014

Another thing, your <form> open tag is commented out, so the form won't work.

CroNiX · December 31, 2014

And remove this:

$optionsHTML = "<select name=\"Countries\">\r\n";

You already have a <select> open and close tag.

CroNiX · December 31, 2014

Try this:

<?php
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>
  <form action="CountryOptions.php" method="post">
  <!-- You need to set up the select element to house the options. This will be set in $_POST as $_POST['country'] -->
    <select name="country">
      <?php echo $countryOptions; ?>
    </select>
    <input type="submit" value="Submit Choice">
  </form>
</body>
</html>

Edited December 31, 2014 by CroNiX

rocky48 · January 1, 2015

Right it now works! It needed quotes around the php code:

<option value= "<?php echo $countryOptions;?>"  </option>

But I need to POST to the next programme the CID value and only show the country name in the box for the users to choose.

How do I acheive that?

The code so far:

<?
include("AddStats_admin_connect.php");
//connect to database
		doDB();
//Function to build select options based on passed array
$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']); 

function buildSelectOptions($options)
{
$optionsHTML = "<select name=\"Countries\">\r\n";

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);

//print_r($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
<!-- You need to set up the select element to house the options. This will be set in $_POST as $_POST['country'] -->
<form action="CountryOptions.php" method="post">
<select name="country">
<option value= "<?php echo $countryOptions;?>"  </option>
</select>
		<input type="submit" value="Submit Choice">
</form></p>


</body>
</html>

Nearly there!

hansford · January 1, 2015

$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']); 

buildSelectOptions( $countries );

function buildSelectOptions($options)
{
$optionsHTML = "<select name=\"countries\">\r\n";

foreach($options as $c)
{
$optionsHTML .= "<option value=\"{$c[0]}\">$c[1]</option>\r\n";
}
$optionsHTML .= "</select>\r\n";

return $optionsHTML;
}

This code is meant to demonstrate how things could be done in your code.

Wanted you to think, not just paste other's code as that does nothing for your knowledge.

Let me demonstrate how this code works and how it could be used - and there is several ways to do the same thing.

This might confuse you further or help you understand what's going on.

<?php

// lets assume this is your DB result set
$row = array(
array('CID'=>100,'Country'=>'France'),
array('CID'=>200,'Country'=>'Germany'),
array('CID'=>200,'Country'=>'Germany')
);

// define the array to pass to buildSelectOptions() function
$countries = array();

// loop through the DB result set and extract information
// with a db result set most of this work is done for you behind the scenes
// but we will have to do the work, since we don't have a result set for demo purposes

foreach ($row as $r)
{
    $c = array();

    foreach ($r as $key => $value)
    {
        $c[] = $value;
    }

    // add the CID and Country of each row in the result set
    // to a row in the $countries array
    // in your result set this is all you would need
    // array($Ctry_info['CID'], $Ctry_info['Country']);
    // here we need to use the temp array $c
    $countries[] = array($c[0], $c[1]);
}

// in your html code, you can simply do this
// call function and pass array as argument
// echo buildSelectOptions( $countries );
// for demo - use this code to see how it works
echo htmlspecialchars(buildSelectOptions( $countries ));

Here is the buildSelectOptions() function again.

function buildSelectOptions($options)
{
    $optionsHTML = "<select name=\"countries\">\r\n";

foreach($options as $c)
{
        $optionsHTML .= "<option value=\"{$c[0]}\">$c[1]</option>\r\n";
}
$optionsHTML .= "</select>\r\n";


return $optionsHTML;

}

Edited January 1, 2015 by hansford

Barand · January 1, 2015

This might confuse you further or help you understand what's going on.

Of the two I think confusion is the more likely option. You ~~brilliant~~ coding merely takes an array that looks like this

$row = array(
array('CID'=>100,'Country'=>'France'),
array('CID'=>200,'Country'=>'Germany'),
array('CID'=>300,'Country'=>'Holland')
);

and converts it to an array that looks like this

$countries = array(
array('0'=>100,'1'=>'France'),
array('0'=>200,'1'=>'Germany'),
array('0'=>300,'1'=>'Holland')
);

when what is really required is an array that looks like this

$countries = array(
'100'=>'France',
'200'=>'Germany',
'300'=>'Holland'
);

CroNiX already gave the solution in #17, so why are you muddying the water?

hansford · January 1, 2015

My apologies, but CroNiX posted the solution for you - here is the code he posted:

<?php
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>
  <form action="CountryOptions.php" method="post">
  <!-- You need to set up the select element to house the options. This will be set in $_POST as $_POST['country'] -->
    <select name="country">
      <?php echo $countryOptions; ?>
    </select>
    <input type="submit" value="Submit Choice">
  </form>
</body>
</html>

rocky48 · January 2, 2015

Thanks I have now got the code to work

Hereis the completed code snippet:

<?php
include("connect_visits.php");
//connect to database
		doDB7();
//Function to build select options based on passed array
$countries[] = array($Ctry_info['CID'], $Ctry_info['Country']); 
buildSelectOptions($countries);
function buildSelectOptions($options)
{
$optionsHTML = "<select name=\"Countries\">\r\n";

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);
		
?>

Many thanks all!

Sign In

Problem with using MYSQL data in 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

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