Mysql/php - querying database and displaying the results in a combo box not working

PHP
1

Hi,

I’m looking to do the following:

  • Have a search form on my main page which allows me to search a database of customers for their first name. Then have a combo box created containing the results.

I have the following php file. search.php

<?php
include 'newCustomer.php';
connect('final');


		$query = $_POST['searchDB'];
        $query = htmlspecialchars($query); // stop HTML characters
        $query = mysql_real_escape_string($query); //stop SQL injection
        $data = mysql_query("SELECT * FROM customer WHERE First_Name LIKE '$query'") ;//query the DB with search field in colleumn selected//
		

					
		if($data === FALSE) {
		$error = 'Query error:'.mysql_error();
		echo $error;
		}
		else
		{
		$test = array();
		$colNames = array();
		while($results = mysql_fetch_assoc($data)){// puts data from database into array, loops until no more

		$test[] = $results;

            }
			 $anymatches = mysql_num_rows($data); //checks if the querys returned any results
			if ($anymatches != 0) {
			$_SESSION['names']=$test;

		$colNames = array_keys(reset($test));
		

        }

				if ($anymatches == 0)
						{
							echo "Sorry, but we can not find an entry to match your query<br><br>";
						}
					
        }

//header("location: newCustomer.php");
//die
?>

and the following code in my main page
<?php


if (isset($_SESSION['names'])){
echo "Customers";
$array1 = $_SESSION['names'];
echo'<select name="customers>';
foreach($array1 as $name){
	echo'<option value="'.$name["First_Name"].'">'.$name["First_Name"].'</option>';
}
echo'</select>';


}

?>

Once the form is submitted the search.php action is performed.
My database has the following names.

Dan Smith
Dan Tom
Dan Dan
Jim Smith
Jim Tom
Jim Jim.

However when i search for dan, the combo box is created with Dan repeated 24 times
when i search for Jim its created with Jim listed twice… I’m not sure what I’ve done wrong

2

My usual course of action when debugging something like this is to output the results along the way. What do you get if you do print_r($_SESSION); ?

It’s also worth echoing the MySQL query and running it directly (eg. in PHPMyAdmin) to check that the results it’s giving are what you’re expecting.

3

Unless I mistaken, $results is going to be your complete “*” query result with all fields in array form. This may account for extra results. I would query for only the First_Name and build the $test array with only these names instead of the complete query result.

while($results = mysql_fetch_assoc($data)){// puts data from database into array, loops until no more

		$test[] = $results['First_Name'];

            }

With this you’d have a clean list to loop through.

As you’ve probably already taken martbean’s suggestion and printed your session, you probably can see what is wrong.