Search MySQL Using Drop Down List Item Selected

[Sallie_ann]

Hi...

I have connected to a MySQL database that contains information on various companies. I have created a HTML form with 4 drop down list boxes that have static information to (hopefully) search the database including company size, location, sector, etc. The user will select items from the drop down list boxes and click go to retrieve the required data.

For example, a user may want to search for all medium sized companies in Leicester in the IT sector. The code I have so far is as follows;

mysql_select_db("beacon",$db);

$result = mysql_query("SELECT name, County, sector, company_size FROM beacon",$db);

echo "<table border=1 align=center>\n";

echo "<tr><td>Name</td><td>County</td><td>Sector</td><td>Company Size</td></tr>\n";

while ($myrow = mysql_fetch_row($result)) {

	printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
	$myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo "</table>\n";

Which displays all the records from the database. An example of the Drop Down List HTML Code is as follows;

    <select name="company_size">
      <option selected>Select Company Size</option>
      <option>1 - 9</option>
      <option>10 - 49</option>
      <option>50 - 250</option>
    </select>

I cannot work out how the SQL to say SELECT records WHERE = to selected drop down list items.

Any help would be really appreciated.

[Der_Fisch]

hello,
first i would sugegst improving your drop-down menu. try this:

<select name="company_size"> 
<option selected>Select Company Size</option> 
<option value=0> </option>
<option value=1>1 - 9</option> 
<optionvalue=2>10 - 49</option> 
<optionvalue=3>50 - 250</option> 
</select> 

leave the first blank you make sure something will be selected and not the standard is taken. (that makes people thing about your form :D )

the rest is quiet easy too.

translate the value to something you can ask in sql.
[code=php]
if($_POST['company_size'] == 1)
     $where1 = 'company_size BETWEEN 1 AND 9';

this variable you place in the sql querry.

$sql = 'select * from beacon WHERE'. $where1 .'AND'. $where2 .'AND'. $where3 .'AND'. $where4; 
//so much ANDs and additional $wheres as you need to put in all informations.

the rest is no problem, right?
hope this helps you...

[Sallie_ann]

Hi... Many thanks for your help

I understand now that I needed to give each drop down list option a value - I have done this and it is not causing any problems.

I have amended my PHP code to incorporate your suggestions. My complete code is now as follows;

if($POST['company_size'] == 1)
$where1 = 'company_size BETWEEN 1 AND 9';
if($POST['company_size'] == 2)
$where2 = 'company_size BETWEEN 10 AND 49';
if($_POST['company_size'] == 3)
$where3 = 'company_size BETWEEN 50 AND 250';

$result = mysql_query("SELECT name, County, sector, company_size
FROM beacon
WHERE'. $where1 .'AND'. $where2 .'AND'. $where3",$db);

echo "<table border=1 align=center>\n";

echo "<tr><td>Name</td><td>County</td><td>Sector</td><td>Company Size</td></tr>\n";

while ($myrow = mysql_fetch_row($result)) {

	printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
	$myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo "</table>\n";

I am being given the following error message;

"Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource"

It was not giving me this error message before, it was just outputting all of the records.😕

[tbach2]

debug your code by building your query then printing it:

$query = "SELECT ....";
print  $query;
$result=mysql_query($query);

I think you'll see what the problem is 🙂

my 2 cents worth:

$sel = (int)($_POST['company_size']);
switch($sel) {
	case 1:	$where = 'company_size BETWEEN 1 AND 9'; break;
	case 2:	$where = 'company_size BETWEEN 10 AND 49'; break;
	case 3:	$where = 'company_size BETWEEN 50 AND 250'; break;
	default:	$where = "company_size='0'";
}

$query = sprintf("SELECT name, County, sector, company_size " . 
                 "FROM beacon " . 
                 "WHERE %s ", 
                 $where
                 );
// print $query;
mysql_query($query);

[Der_Fisch]

looks good, and your mistake is here:

$result = mysql_query("SELECT name, County, sector, company_size 
FROM beacon 
WHERE'. $where1 .'AND'. $where2 .'AND'. $where3",$db); 

you can't start with " and then break it up with '.

you had to put for your ', ".

this looks so:

$result = mysql_query("SELECT name, County, sector, company_size 
FROM beacon 
WHERE". $where1 ."AND". $where2 ."AND". $where3,$db); 

so must it look because the dot always mean that something is puttogether.
eG.

$var = 1;
$var.= 1;
echo $var1; 

will output "11"

and another thing is that,

if($_POST['company_size'] == 1) 
$where1 = 'company_size BETWEEN 1 AND 9'; 
if($_POST['company_size'] == 2) 
$where2 = 'company_size BETWEEN 10 AND 49'; 
if($_POST['company_size'] == 3) 
$where3 = 'company_size BETWEEN 50 AND 250'; 

there must be $where1 in all if's!

i have put in $where2 and 3 for other requirements. eg $where2 = "sector = $_POST['sector']";

so take the solve from tbach2. it looks pretty good 😉
hope now your gettin clear 😉

[Sallie_ann]

Hi... I've managed to improve my code and it now reads

mysql_select_db("beacon",$db);

if($POST['company_size'] == 1)
$where = 'company_size BETWEEN 1 AND 9';
if($POST['company_size'] == 2)
$where = 'company_size BETWEEN 10 AND 49';
if($_POST['company_size'] == 3)
$where = 'company_size BETWEEN 50 AND 250';

if($POST['County'] == 1)
$where1 = 'County LIKE Derbyshire';
if($POST['County'] == 2)
$where1 = 'County LIKE Leicestershire';
if($POST['County'] == 3)
$where1 = 'County LIKE Lincolnshire';
if($POST['County'] == 4)
$where1 = 'County LIKE Northants';
if($_POST['County'] == 5)
$where1 = 'County LIKE Nottinghamshire';

$result = mysql_query("SELECT name, County, sector, company_size
FROM beacon
WHERE". $where ."AND". $where1,$db);

echo "<table border=1 align=center>\n";

echo "<tr><td>Name</td><td>County</td><td>Sector</td><td>Company Size</td></tr>\n";

while ($myrow = mysql_fetch_row($result)) {

printf("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>\n",
$myrow[0], $myrow[1], $myrow[2], $myrow[3]);

}

echo "</table>\n";

I am still being given the same error message & assume that it is not understanding my query - is my WHERE statement correct?

Thanks again for all your help

[Sallie_ann]

Despite all your help I still can't get my code to work!

I am using the same code as my previous post and am being given the following error message;

"Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource"

Could anyone give me advise on where I am going wrong?! It can only be my WHERE & IF statements because the MySQL data was output to my table without these!

[tbach2]

The problem is still in the construction of the where clause.

If you had printed out the query, you'd have seen the problem. It's a very common mistake.

(Or should I say, "It's a verycommonmistake").

😉

That's one of the reasons I like to use sprintf-- more readable and it makes you think a little more about security.

$query = sprintf("SELECT name, County, sector, company_size " . 
                "FROM beacon " . 
                "WHERE %s AND %s",
                $where,
                $where1
                 );
// comment out next line after it works
print "DEBUG: query=$query<hr>";

mysql_query($query);

[anarcho]

this might be of help to you... http://forums.devshed.com/archive/5/2003/06/4/66640