Selected in dropdown list from database

rocky48 · September 4, 2015

I was shown how to create a dropdown list created from a database last year, the only trouble is that the first line is blank.

Can you have one of the items selected, like you do in a normal html list?

Here is the code:

    //Function to build select options based on passed array
    $Events = array($Events_info['id'], $Events_info['Event_Type']); 
    buildSelectOptions($Events);
    function buildSelectOptions($options)
    {
    $optionsHTML = "<select name=\"Event\">\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 id, Event_Type FROM Events ORDER BY id";
    $clist_res= mysqli_query($conn, $clist_sql) or die(mysqli_error($conn));
    if (mysqli_num_rows($clist_res) < 1) {
    	//this Event not exist
    	$display_block = "<p><em>You have selected an invalid Event.<br/>
    	Please try again.</em></p>";
    }
    $Events = array();
    while($Events_info = mysqli_fetch_array($clist_res)) {
           
            $Events [$Events_info['id']] = $Events_info['Event_Type'];
    }
    $EventOptions = buildSelectOptions($Events);
     
    //Function to build select options based on passed array
    $Mood = array($Mood_info['id'], $Mood_info['Event_Sub_Type']); 
    buildSelectOptions1($Mood);
    function buildSelectOptions1($options)
    {
    $optionsHTML1 = "<select name=\"Mood\">\r\n";
     
    foreach($options as $id => $label)
    {
    $optionsHTML1 .= "<option value='{$id}'>'{$label}'</option>\n";
    }
     
     
    return $optionsHTML1;
    }

Your help would be appreciated!

Ch0cu3r · September 4, 2015

the only trouble is that the first line is blank.

Of what?

cyberRobot · September 4, 2015

...the only trouble is that the first line is blank.

Was it intentionally left blank...or is that something you're looking to fix?

Can you have one of the items selected, like you do in a normal html list?

You can. You just need to run a test in your loop that builds the <option> tags. The test would check if the current <option> tag being built matches the one you are looking for. When a match is found, add the "selected" attribute.

maxxd · September 4, 2015

No offense, but the fact that the first line is empty really isn't the only trouble with this code.

Where does $Events_info get set? Or $Mood_info? Why do you have 2 identical functions doing identical things, named and called separately? And none of the SELECT elements are ever closed, so your resulting markup has to be a complete mess.

As for how to achieve the goal of the actual question, you'll need to pass a current value parameter to the buildSelectOptions function (once you're refactored it into one function, not two), compare that value to the value of $id in the while loop, and if it matches, add the string ' selected' to the option row.

Edited September 4, 2015 by maxxd

maxxd · September 4, 2015

This is entirely untested, and I'm only on my second cup of coffee after a long-ish night, but this should make a bit more sense.

function buildSelectOptions($name, array $options, $current=null){
	$htmlString  = "<select name='{$name}' id='{$name}'>\n";
	$htmlString .= "\t<option value='-1'> -- Please select -- </option>\n";
	foreach($options as $value=>$option){
		$htmlString .= "\t<option value='{$value}'";
		if($value == $current){
			$htmlString .= " selected";
		}
		$htmlString .= ">{$option}</option>\n";
	}
	$htmlString .= "</select>\n";
	return $htmlString;
}

function getEvents(){
	$qry = "SELECT	 id
			,Event_Type
		FROM Events
		ORDER BY id";
	$sql = mysqli_query($conn, $qry);
	if(mysqli_num_rows($sql) < 1){
		return array();
	}
	while($res = mysqli_fetch_array($sql)){
		$ret[$res['id']] = $res['Event_Type'];
	}
	return $ret;
}

function getMoods(){
	$qry = "SELECT	 id
			,Event_Sub_Type
		FROM Moods
		ORDER BY id";
	$sql = mysqli_query($conn, $qry);
	if(mysqli_num_rows($sql) < 1){
		return array();
	}
	while($res = mysqli_fetch_array($sql)){
		$ret[$res['id']] = $res['Event_Sub_Type'];
	}
	return $ret;
}

echo buildSelectOptions('event', getEvents(), $_GET['event']);
echo buildSelectOptions('mood', getMoods(), $_GET['mood']);

rocky48 · September 4, 2015

Of the dropdown menu!!

I see you create a common function and then run that function for each of the queries!

Do you mean mysqli_close()?

The original code has run succesfully for the past year on my website, without any errors.

I will try your suggested code and see what happens!

maxxd · September 4, 2015

I see you create a common function and then run that function for each of the queries!

Yes. That way, you only have to make a change to one part of the code when things change, and it greatly improves readability. It'll make life easier, I promise.

Do you mean mysqli_close()?

No, I mean that - given the code posted above - the HTML select elements are never closed. Let's look at the code you posted:

function buildSelectOptions($options){
	$optionsHTML = "<select name=\"Event\">\r\n";
	 
	foreach($options as $id => $label){
		$optionsHTML .= "<option value='{$id}'>{$label}</option>\n";
	}
	return $optionsHTML;
}

Given this, the HTML output will look like this:

<select name="Event">
    <option value='value'>This is an option</option>
    <option value='value2'>This is also an option</option>

<!-- more html content -->

Note that there's no '</select>' tag to close the select element. Modern browsers will (probably - honestly, I'm not sure) compensate for this, but the page won't validate properly and you're unnecessarily taxing the browser by making it figure out where you meant to end the element instead of specifically telling it. It's not much of a page load increase, but they do add up.

The original code has run succesfully for the past year on my website, without any errors.

I will try your suggested code and see what happens!

While there's nothing technically wrong with the code you posted (it'll parse and run, and you won't get any errors or warnings), as you're finding out first-hand right now, it's a nightmare to maintain. And it's going to continue to be a nightmare to maintain unless you actually do some refactoring. I'm not saying you have to (or even should, quite honestly) adopt an OO style to the code, but a good code clean up will go a long, long way for you. There's some massive code smell coming off even the small snippet you posted.

Like I said, the code that I posted is completely untested so it may not work without modification. But compare the readability of your posted code to the sample code I posted. Add some comments into the sample code and it'll actually be useful to you when you have to come back to the code in a year or two to make updates - you won't have to scroll up and down the page looking for where a variable was set, which specific version of buildSelectOption() you're using at which specific point, etc.

rocky48 · September 4, 2015

I have tried your code and am geting a lot of errors!

One thing you assumed the second table was called mood, but it is actually called Event_Type. I know you are going to say its bad identification, but I stuck with it.

The name in the table is Event_Sub_Type.

Mainly they are notices and warnings.

This one occurs five times:

Notice: Undefined variable: conn in C:\wamp\www\1066mk4\Local_Test\Event_test.php on line 2

which in turn cause:

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in C:\wamp\www\1066mk4\Local_Test\Event_test.php on line 31

and

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, null given in C:\wamp\www\1066mk4\Local_Test\Event_test.php on line 32

In the buildselectoptions you have as the first index event and mood. Where are they defined?

As for defining variables do you use define() or do you use $conn=""; ?

Thanks!

maxxd · September 4, 2015

I don't know the setup of your database, so I just used stub table names. Obviously update the field and table names to match your current setup. And sorry about the $conn thing - you'll have to pass that into the getMoods() and getEvents() functions, like so:

echo buildSelectOptions('event', getEvents($conn), $_GET['event']);
echo buildSelectOptions('mood', getMoods($conn), $_GET['mood']);

Like I said, I hadn't had my required amount of caffeine...

rocky48 · September 4, 2015

Still the same errors.

Here is the Code i used to test the code:

 <?php
define ($conn,"",false);

$sql="";
$qry="";
$res="";
$ret="";
 
include("connect_Verses4Cards_LOCAL.php");
$conn=get_db_conn_verse();
 
    function buildSelectOptions($name, array $options, $current=null){
    	$htmlString  = "<select name='{$name}' id='{$name}'>\n";
    	$htmlString .= "\t<option value='-1'> -- Please select -- </option>\n";
    	foreach($options as $value=>$option){
    		$htmlString .= "\t<option value='{$value}'";
    		if($value == $current){
    			$htmlString .= " selected";
    		}
    		$htmlString .= ">{$option}</option>\n";
    	}
    	$htmlString .= "</select>\n";
    	return $htmlString;
    }
     
    function getEvents(){
        $qry = "SELECT	 id
    			,Event_Type
    		FROM Events
    		ORDER BY id";
    	$sql = mysqli_query($conn, $qry);
    	if(mysqli_num_rows($sql) < 1){
    		return array();
    	}
    	while($res = mysqli_fetch_array($sql)){
    		$ret[$res['id']] = $res['Event_Type'];
    	}
    	return $ret;
        }
    
    function getMoods(){
    	$qry = "SELECT	 id
    			,Event_Sub_Type
    		FROM Event_Sub
    		ORDER BY id";
    	$sql = mysqli_query($conn, $qry);
    	if(mysqli_num_rows($sql) < 1){
    		return array();
    	}
    	while($res = mysqli_fetch_array($sql)){
    		$ret[$res['id']] = $res['Event_Sub_Type'];
    	}
    	return $ret;
    }
     
    echo buildSelectOptions('event', getEvents(), $_GET['event']);
    echo buildSelectOptions('mood', getMoods(), $_GET['mood']);
?>
<!DOCTYPE html>

<html>
<head>
    <title>Page Title</title>
</head>

<body>

<?php echo buildSelectOptions('event', getEvents($conn), $_GET['event']);
    echo buildSelectOptions('mood', getMoods($conn), $_GET['mood']);
?>

</body>
</html>

I still dont understand where event and mood are defined?

maxxd · September 4, 2015

$_GET['event'] and $_GET['mood'] are simply examples. You wanted to know how to select an option of the DOM SELECT element. You'll have to get the current value and pass it into the function in place of the $_GET variables that are there now. I can only see the code that you posted, so I don't know where those values are coming from. Basically, I used $_GET as a stub for this section of my original reply:

As for how to achieve the goal of the actual question, you'll need to pass a current value parameter to the buildSelectOptions function (once you're refactored it into one function, not two), compare that value to the value of $id in the while loop, and if it matches, add the string ' selected' to the option row.

Probably could've explained that better earlier - sorry about the confusion.

rocky48 · November 14, 2015

After a couple of months break from looking at this, due to other commitments, I have some questions about the original code maxxd posted.

Lets look at the 1st function:

function buildSelectOptions($name, array $options, $current=null){
	$htmlString  = "<select name='{$name}' id='{$name}'>\n";
	$htmlString .= "\t<option value='-1'> -- Please select -- </option>\n";
	foreach($options as $value=>$option){
		$htmlString .= "\t<option value='{$value}'";
		if($value == $current){
		$htmlString .= " selected";
		}
		$htmlString .= ">{$option}</option>\n";
		}
		$htmlString .= "</select>\n";
		return $htmlString;
		}

The variable $htmlstring is used several times. Does the subsquent code append to the variable?

The line if ($value == $current) {

As this is set to null, do I assume that the line value = -1 ..... is displayed?

Why?

At the end of the function you 'return $htmlstring', where is this used elsewhere in the script?

Now the function getEvents & function getMoods:

function getEvents(){
		$qry = "SELECT id ,Event_Type FROM Events ORDER BY id";
		$sql = mysqli_query($conn, $qry);
		if(mysqli_num_rows($sql) < 1){
		return array();
		}
		while($res1 = mysqli_fetch_array($sql)){
		$ret1[$res1['id']] = $res1['Event_Type'];
		}
		return $ret1;
		}

After the test for no rows you return array(), what does this do?

At the end of the function you return $ret1 (I appended a numeric value as I could not see how you could use the same variable twice in 2 differnt functions). where in the following code is this used?

Now the final display in the html page:

echo buildSelectOptions('event', getEvents(), $_GET['event']);
echo buildSelectOptions('mood', getMoods(), $_GET['mood']);

Where are the variables 'event' and 'mood' previously called?

Are they suppose to be the names of the MYSQL variables in the SELECT query?

As you can see I am struggling to understand the code maxxd wrote and it does not seem any clearer after a couple of months.

Any help would be appreciated.

Barand · November 14, 2015

The variable $htmlstring is used several times. Does the subsquent code append to the variable?

the operator ".=" appends to the variable.

The line if ($value == $current) {

As this is set to null, do I assume that the line value = -1 ..... is displayed?

Why?

$current is only set to null by default if you do not pass a third parameter when you call the function.

The option with the value that matches $current is set as selected.

If none are "selected" the first option is selected by default

At the end of the function you 'return $htmlstring', where is this used elsewhere in the script?

You would echo the result returned by the function wherever you want that menu displayed

Now the function getEvents & function getMoods:

function getEvents(){
		$qry = "SELECT id ,Event_Type FROM Events ORDER BY id";
		$sql = mysqli_query($conn, $qry);
		if(mysqli_num_rows($sql) < 1){
		return array();
		}
		while($res1 = mysqli_fetch_array($sql)){
		$ret1[$res1['id']] = $res1['Event_Type'];
		}
		return $ret1;
		}

After the test for no rows you return array(), what does this do?

It returns an array that you pass to the first function to build a menu

At the end of the function you return $ret1 (I appended a numeric value as I could not see how you could use the same variable twice in 2 differnt functions). where in the following code is this used?

Variables are local to functions. You can use the same variable name in as many functions as you like. They are different variables and only exist while the function is executing (unless static)

rocky48 · November 15, 2015

Point #2

If I wanted to make the ID 27 to be the selected value, how do I do that?

Point #3

Why would you want to do that? In my original script I get it to display an error message.

Point #4

I get that, but where is the result used in maxxd's code?

To get to the crux of the matter, why does this code not display any data from my database?

maxxd · November 15, 2015

Point #2

If I wanted to make the ID 27 to be the selected value, how do I do that?

You would do this (for the events drop-down):

echo buildSelectOptions('events', getEvents(), 27);

Remember that the last parameter in the buildSelectOptions() function is the currently selected option. Where that comes from is completely up to you - database, user input, or imagination. Go nuts. You can also not pass anything to that parameter, and the first option in the select element will be selected by default.

Point #3

Why would you want to do that? In my original script I get it to display an error message.

The array parameter $options is not optional in the function definition of buildSelectOptions(), so if you pass it anything other than an array (including not passing it anything at all), the script will fail and display an error. Passing an empty array simply outputs an empty select object.

Point #4

I get that, but where is the result used in maxxd's code?

It's printed to screen where the code says "echo buildSelectOptions(...". 'echo' prints a string to screen.

To get to the crux of the matter, why does this code not display any data from my database?

No idea. Are your queries returning any data?

To answer your other questions, 'events' and 'moods' are strings, not variables. They're used to set the name and id attributes of the resulting select elements. And, as Barand explained, the $ret variable is locally scoped - in other words, that variable doesn't exist outside of the function it's defined within. So you can use the same variable name in as many different functions as you want, as long as you don't want the data that variable contains to persist.

maxxd · November 15, 2015

Perhaps an annotated version will help.

/**
 *	Builds an HTML <select> element with options.
 *	Will mark an option selected if passed a target value and that value is in the
 *	array of options.
 *
 *	Usage examples:
 *	To print an Events select where the best event ever is already selected:
 *      echo buildSelectOptions(
 *              'event',            //name of the select element - arbitrary and totally up to you
 *              array(
 *                  1=>'Gala',
 *                  2=>'Birthday Party',
 *                  3=>'Wine Night'
 *              ),        //the data from which the user can select
 *              3        //the desired event option to be selected
 *      );
 *
 *      To print a Moods select asking the user to select an event:
 *      echo buildSelectOptions(
 *              'mood',            //name of the select element - arbitrary and totally up to you
 *              array(
 *                  1=>'Meh',
 *                  2=>'Blerg',
 *                  3=>'Woot!'
 *              ),        //the data from which the user can select
 *                      //note nothing is passed here - this is fine because $current is optional
 *      );
 *
 *	@param	string		$name			DOM element name and id
 *	@param	array		$options		Options to display in the drop-down for the user to select
 *	@param	int|string	$current		Optional currently selected value. This can come from user
 *								input, a database query, or can be a hard-coded default value.
 *	@return	string			The full HTML string defining the <select> element and it's associated <option> values.
 */
function buildSelectOptions($name, array $options, $current=null){
	$htmlString  = "<select name='{$name}' id='{$name}'>\n";
	$htmlString .= "\t<option value='-1'> -- Please select -- </option>\n";
	foreach($options as $value=>$option){
		$htmlString .= "\t<option value='{$value}'";
		if($value == $current){
			$htmlString .= " selected";
		}
		$htmlString .= ">{$option}</option>\n";
	}
	$htmlString .= "</select>\n";
	return $htmlString;
}

/**
 *	Gather and return the data in table 'Events'.
 *	@return	array		An associative array of Events and their ID's as such:
 *					$ret[event id] = event type
 */
function getEvents(){
	$qry = "SELECT	 id
			,Event_Type
		FROM Events
		ORDER BY id";
	$sql = mysqli_query($conn, $qry);
	if(mysqli_num_rows($sql) < 1){
		return array();
	}
	while($res = mysqli_fetch_array($sql)){
		$ret[$res['id']] = $res['Event_Type'];
	}
	return $ret;
}

/**
 *	Gather and return the data in table 'Moods'.
 *	@return	array		An associative array of event sub types and their ID's as such:
 *					$ret[mood id] = event sub type
 */
function getMoods(){
	$qry = "SELECT	 id
			,Event_Sub_Type
		FROM Moods
		ORDER BY id";
	$sql = mysqli_query($conn, $qry);
	if(mysqli_num_rows($sql) < 1){
		return array();
	}
	while($res = mysqli_fetch_array($sql)){
		$ret[$res['id']] = $res['Event_Sub_Type'];
	}
	return $ret;
}

/**
 *	Print the select elements to screen.
 */
echo buildSelectOptions('event', getEvents(), 3);
echo buildSelectOptions('mood', getMoods());

Using the examples in the documentation, you will see the following output on your page:

<select name='event' id='event'>
	<option value='-1'>-- Please select --</option>
	<option value='1'>Gala</option>
	<option value='2'>Birthday Party</option>
	<option value='3' selected>Wine Night</option>
</select>
<select name='mood' id='mood'>
	<option value='-1'>-- Please select --</option>
	<option value='1'>Meh</option>
	<option value='2'>Blerg</option>
	<option value='3'>Woot!</option>
</select>

Please note that this script is assuming the user has submitted a form where the option 'Wine Night' is the selected event and 'Woot!' is the selected mood upon page load. When the form is submitted, if it's redrawn, you would use the user input as the currently selected value (either $_POST['event'] or $_GET['event'] and either $_POST['mood'] or $_GET['mood'], depending on the form's action attribute).

rocky48 · November 16, 2015

I have tried checking this code on my localhost and I am getting an error that I can't figure out why it is giving this error!

Here is the code:

<?php

include ("connect_Verses4Cards_LOCAL.php");
$conn = get_db_conn_verse();
//Function to build select options based on passed array
function buildSelectOptions($name, array $options, $current=null){
	$htmlString  = "<select name='{$name}' id='{$name}'>\n";
	$htmlString .= "\t<option value='-1'> -- Please select -- </option>\n";
	foreach($options as $value=>$option){
		$htmlString .= "\t<option value='{$value}'";
		if($value == $current){
		$htmlString .= " selected";
		}
		$htmlString .= ">{$option}</option>\n";
		}
		$htmlString .= "</select>\n";
		return $htmlString;
		}
		
function getEvents(){
		$qry = "SELECT id ,Event_Type FROM Events ORDER BY id";
		$sql = mysqli_query($conn, $qry)or die(mysqli_error($conn));
		if(mysqli_num_rows($sql) < 1){
		return array();
		}
		while($res1 = mysqli_fetch_array($sql)){
		$ret1[$res1['id']] = $res1['Event_Type'];
		}
		return $ret1;
		}
		
function getMoods(){
		$qry = "SELECT id ,Event_Sub_Type FROM Event_Sub ORDER BY id";
		$sql = mysqli_query($conn, $qry)or die(mysqli_error($conn));
		if(mysqli_num_rows($sql) < 1){
		return array();
		}
		while($res2 = mysqli_fetch_array($sql)){
		$ret2[$res2['id']] = $res2['Event_Sub_Type'];
		}
		return $ret2;
		}
		?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!--Design by Free CSS Templates
http://www.freecsstemplates.org
Released for free under a Creative Commons Attribution 2.5 License

Name       : Yosemite
Description: A two-column, fixed-width design with dark color scheme.
Version    : 1.0
Released   : 20091106-->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="keywords" content="" />
<meta name="description" content="Input form for getting Verse output" />
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>1066 Cards 4U - Verse Input Form</title>
<link href="style.css" rel="stylesheet" type="text/css" media="screen" />
</head>
<body>
<div id="wrapper">
<div id="menu">
<ul>
<!--

-->

</ul>
</div><!-- end #menu -->
<div id="header">
<div id="logo">
<h1><a href="http://www.1066cards4u.co.uk">1066 Cards 4U</a></h1>
</div><!-- end #wrapper -->
</div><!-- end #header -->
<div id="page">
<div id="page-bgtop">
<div id="page-bgbtm">
<div id="content">
<h1>Verse Choices Form</h1>
<p>
<br></br>
<br></br>
You can filter the database, by selecting one of the items in each drop down menu and clicking the submit button.<br></br>
																	This will list all greetings of the chosen type of Event and Mood.<br></br>
																	If you do not make a choice the default selections will be used.</p>
<br></br>
<h3>BASIC VERSION</h3>
<br></br>
<!-- Start of FORM -->
<form action="VerseQuery.php" method="post">
	<select name="Event_Type">
		
		<option value= "<?php echo buildSelectOptions('Event_Type', getEvents(), $_GET['Event_Type']);?>"  </option>
	</select>
	<select name="Mood">
		<option value= "<?php echo buildSelectOptions('Event_Sub_Type', getMoods(), $_GET['Event_Sub_Type']);?>"  </option>
	</select>
	<input type="submit" value="submit"/>
</form><!-- End of FORM -->
<br></br>
<h3>ADVANCED VERSION</h3>
<p>If you wish to change the Font color to any color, use the following button</p>
<br></br>
<!-- Start of FORM -->
<form action="AltVerseQuery1.php" method="post">
	<select name="Event_Type">
		
		<option value= "<?php echo buildSelectOptions('event', getEvents(), $_GET['event']);?>"  </option>
	</select>
	<select name="Mood">
		<option value= "<?php echo buildSelectOptions('mood', getMoods(), $_GET['mood']);?>"  </option>
	</select>
	<input type="submit" value="submit"/>
	
</form><!-- End of FORM -->

<br></br><br></br>
<form method="post" action="Verse_Menu.php">
	<input type="submit" value="Return To Verse Menu"/>
</form>
</div><!-- end #content -->

<div id="sidebar">
	
<div id="search" >
<form method="get" action="http://1066cards4u.co.uk">
<div>
<input type="text" name="s" id="search-text" value="" />
<input type="submit" id="search-submit" value="GO" />
</div>
							</form>
</div>
<div style="clear: both;"> </div>
<h2>Useful Links</h2>
<ul.a>
	<li><p><a href="Articles/Instructions for Using Verse Database-V2.12.pdf" target="_blank">Insructions for  using Verse Printing UtilityPrintable PDF Version</a></p></li>
	<li><p><a href="Articles/Nominal Settings.PDF" target="_blank">Nominal Settings and Cutting List for Verse Database PDF Version</a></p></li>
	<li><p><a href="Articles/How to use the Color Chooser_rasterized.PDF" target="_blank">How to use the Color Picker</a></p></li>
</ul.a>

		</div><!-- end #sidebar -->
		<div style="clear: both;"> </div>
</div><!-- end #page-bgtop -->
</div><!-- end #page-bgbtm -->
</div><!-- end #page -->
</div>

<div id="footer">
	<p>Copyright (c) 2008 Sitename.com. All rights reserved. Design by <a href="http://www.freecsstemplates.org/" rel="nofollow">FreeCSSTemplates.org</a>.</p>
</div><!-- end #footer -->
</body>
</html>

I am getting ' Warning! mysqli_query() expects paramenter 1 to be mysqli, null given in.....'

I have used this method many times before with no problem, in fact the page I am trying to chage worked fine before I altered it with the suggested code. The variable $conn seems to be the problem, but this is returned in the included file.

Here is the connection file:

<?php
//set up a couple of functions for use by Verses 

function get_db_conn_verse() {

    $host_name = "localhost";
    $database = "db395629647"; // Change your database nae
    $username = "root";          // Your database user id 
    $password = "xxxxx";          // Your password

    //connect to server and select database; you may need it
    $conn = new mysqli($host_name, $username, $password, $database);

    //if connection fails, stop script execution
    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
    return $conn;
} 
?>

I can't spot any errors, so why am I getting this error?

Edited November 16, 2015 by rocky48

maxxd · November 16, 2015

You had the same problem earlier ( post #8 ), and you still haven't fixed it as I suggested ( post #9 ).

Also, the code you are using now is calling the buildOptions() functions from within an <option> tag in the HTML. The output of this method is a full <select> element, with options.

You can't simply copy and paste example code - you have to read it, think about it a little bit, and then implement it in a way that makes sense in the context of your project.

Edited November 16, 2015 by maxxd

rocky48 · November 17, 2015

But the change you suggested in #9 was in the final buildselectoption line?

The $conn is the MySqli connection string to run the MySQL query.

Is that telling me that this string is empty?

That would imply that the connection function was not working.

Any other ideas?

Barand · November 17, 2015

function getEvents(){

    $qry = "SELECT id ,Event_Type FROM Events ORDER BY id";

    $sql = mysqli_query($conn, $qry)or die(mysqli_error($conn));

    if(mysqli_num_rows($sql) < 1){

        return array();

    }

    while($res1 = mysqli_fetch_array($sql)){

        $ret1[$res1['id']] = $res1['Event_Type'];

    }

    return $ret1;

}

As I told you in reply #13, variables are local to functions

The variable $conn is unknown inside the above function. You need to pass it as a function argument.

The definition needs to be

function getEvents($conn){
    $qry = "SELECT id ,Event_Type FROM Events ORDER BY id";
    $sql = mysqli_query($conn, $qry)or die(mysqli_error($conn));
    if(mysqli_num_rows($sql) < 1){
        return array();
    }
    while($res1 = mysqli_fetch_array($sql)){
        $ret1[$res1['id']] = $res1['Event_Type'];
    }
    return $ret1;
}

then when you call the function, pass the $conn value

getEvents($conn);

rocky48 · November 17, 2015

Right getting closer!

Now runs without errors, but the lists are empty?

Can you explain where the 'events' and 'mood' come from in the following:

echo buildSelectOptions('event', getEvents($conn), $_GET['event']);

and

echo buildSelectOptions('mood', getMoods($conn), $_GET['mood']);

I can see in the actual function they are in place of the local variable $name.

I have tested the MYSQL SELECT query and that works OK, so why is the list empty?

maxxd · November 17, 2015

@Barand - thanks; I forgot to update the function definitions - my bad.

@rocky48 - the 'event' and 'mood' string values are assigned to the function parameter $name, and are used in the 'name' and 'id' attributes of the select element only. They don't have anything to do with gathering your data - you could use 'bob' and 'jenny' and it wouldn't matter to the buildSelectOptions() function. The getMoods() and getEvents() functions gather your data from the database and return it as an array.

Are you still calling the functions from within an <option> tag in the HTML? Is there a $_GET['mood'] and $_GET['event'] value to pass to buildSelectOptions()? I'm assuming you've turned on error reporting as you're getting errors...

What does the code look like at this point?

rocky48 · November 17, 2015

I believe that the Event And Mood function is not returning any data.

I tried to see if any data was being generated with print_r() but just printed Array when I put it just before the return ret.

I have shut down for the evening and will answer in full tomorrow.

rocky48 · November 18, 2015

As I said last evening, I am now sure that the Event And Mood function is not returning any data.

How can I check the array, as print_r(ret1) did not print anything apart from Array?

Here is the code so far:

<?php

include ("connect_Verses4Cards_LOCAL.php");
$conn = get_db_conn_verse();
//Function to build select options based on passed array
function buildSelectOptions($name, array $options, $current=null){
	$htmlString  = "<select name='{$name}' id='{$name}'>\n";
	$htmlString .= "\t<option value='-1'> -- Please select -- </option>\n";
	foreach($options as $value=>$option){
		$htmlString .= "\t<option value='{$value}'";
		if($value == $current){
		$htmlString .= " selected";
		}
		$htmlString .= ">{$option}</option>\n";
		}
		$htmlString .= "</select>\n";
		return $htmlString;
		}
		
function getEvents($conn){
		$qry = "SELECT id ,Event_Type FROM Events ORDER BY id";
		$sql = mysqli_query($conn, $qry)or die(mysqli_error($conn));
		if(mysqli_num_rows($sql) < 1){
		return array();
		}
		while($res1 = mysqli_fetch_array($sql)){
		$ret1[$res1['id']] = $res1['Event_Type'];
		
		}
		
		return $ret1;
		}
		
function getMoods($conn){
		$qry = "SELECT id ,Event_Sub_Type FROM Event_Sub ORDER BY id";
		$sql = mysqli_query($conn, $qry)or die(mysqli_error($conn));
		if(mysqli_num_rows($sql) < 1){
		return array();
		}
		while($res2 = mysqli_fetch_array($sql)){
		$ret2[$res2['id']] = $res2['Event_Sub_Type'];
		}
		
		return $ret2;
		}
		
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!--Design by Free CSS Templates
http://www.freecsstemplates.org
Released for free under a Creative Commons Attribution 2.5 License

Name       : Yosemite
Description: A two-column, fixed-width design with dark color scheme.
Version    : 1.0
Released   : 20091106-->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="keywords" content="" />
<meta name="description" content="Input form for getting Verse output" />
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>1066 Cards 4U - Verse Input Form</title>
<link href="style.css" rel="stylesheet" type="text/css" media="screen" />
</head>
<body>
<div id="wrapper">
<div id="menu">
<ul>
<!--

-->

</ul>
</div><!-- end #menu -->
<div id="header">
<div id="logo">
<h1><a href="http://www.1066cards4u.co.uk">1066 Cards 4U</a></h1>
</div><!-- end #wrapper -->
</div><!-- end #header -->
<div id="page">
<div id="page-bgtop">
<div id="page-bgbtm">
<div id="content">
<h1>Verse Choices Form</h1>
<p>
<br></br>
<br></br>
You can filter the database, by selecting one of the items in each drop down menu and clicking the submit button.<br></br>
																	This will list all greetings of the chosen type of Event and Mood.<br></br>
																	If you do not make a choice the default selections will be used.</p>
<br></br>
<h3>BASIC VERSION</h3>
<br></br>
<!-- Start of FORM -->
<form action="VerseQuery.php" method="post">
	<select name="Event_Type">
		
		<option value= "<?php echo buildSelectOptions('event', getEvents($conn), $_GET['event'],1);?>"  </option>
	</select>
	<select name="Mood">
		<option value= "<?php echo buildSelectOptions('mood', getMoods($conn), $_GET['mood'], 27);?>"  </option>
	</select>
	<input type="submit" value="submit"/>
</form><!-- End of FORM -->
<br></br>
<h3>ADVANCED VERSION</h3>
<p>If you wish to change the Font color to any color, use the following button</p>
<br></br>
<!-- Start of FORM -->
<form action="AltVerseQuery1.php" method="post">
	<select name="Event_Type">
		
		<option value= "<?php echo buildSelectOptions('event', getEvents($conn), $_GET['event']);?>"  </option>
	</select>
	<select name="Mood">
		<option value= "<?php echo buildSelectOptions('mood', getMoods($conn), $_GET['mood']);?>"  </option>
	</select>
	<input type="submit" value="submit"/>

</form><!-- End of FORM -->

<br></br><br></br>
<form method="post" action="Verse_Menu.php">
	<input type="submit" value="Return To Verse Menu"/>
</form>
	
</div><!-- end #content -->

<div id="sidebar">
	
<div id="search" >
<form method="get" action="http://1066cards4u.co.uk">
<div>
<input type="text" name="s" id="search-text" value="" />
<input type="submit" id="search-submit" value="GO" />
</div>
							</form>
</div>
<div style="clear: both;"> </div>
<h2>Useful Links</h2>
<ul.a>
	<li><p><a href="Articles/Instructions for Using Verse Database-V2.12.pdf" target="_blank">Insructions for  using Verse Printing UtilityPrintable PDF Version</a></p></li>
	<li><p><a href="Articles/Nominal Settings.PDF" target="_blank">Nominal Settings and Cutting List for Verse Database PDF Version</a></p></li>
	<li><p><a href="Articles/How to use the Color Chooser_rasterized.PDF" target="_blank">How to use the Color Picker</a></p></li>
</ul.a>

		</div><!-- end #sidebar -->
		<div style="clear: both;"> </div>
</div><!-- end #page-bgtop -->
</div><!-- end #page-bgbtm -->
</div><!-- end #page -->
</div>

<div id="footer">
	<p>Copyright (c) 2008 Sitename.com. All rights reserved. Design by <a href="http://www.freecsstemplates.org/" rel="nofollow">FreeCSSTemplates.org</a>.</p>
</div><!-- end #footer -->
</body>
</html>

Your help is appreciated!

Barand · November 18, 2015

Are you still calling the functions from within an <option> tag in the HTML?

The answer is clearly "Yes"

Sign In

Selected in dropdown list from database

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

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