How To Use Dropdown Lists To Form A Mysql Query

[Nublette]

Hey there,

 

Hoping someone can help me. I am trying to create a page on a wordpress site that will enable users to select 4 seperate fields from 4 drop down lists, hit a submit button and then be presented with search results from a MYSQL query based on their selections from the drop down list. I am a complete noob at php and MYSQL having not touched either since Uni.

 

Please help out if you can.

 

Here is the code. I have omitted the database connection details for obvious reasons.

 

//Select City from Users table
$queryCity = "SELECT City FROM Users";
$resultCity = mysql_query($queryCity) or die(mysql_error());

//Select AO from Users table
$queryAO = "SELECT AO FROM Users";
$resultAO = mysql_query($queryAO) or die(mysql_error());

//Select Job from Users table
$queryJob = "SELECT Job FROM Users";
$resultJob = mysql_query($queryJob) or die(mysql_error());

//Select Skills from Users table
$querySkills = "SELECT Skills FROM Users";
$resultSkills = mysql_query($querySkills) or die(mysql_error());

//Create City dropdown list object
$dropdownCity = "<select name='selectCity'>";
while($rowCity = mysql_fetch_assoc($resultCity))
{
$dropdownCity .= "\r\n<option value='{$rowCity['City']}'>{$rowCity['City']}</option>";
}
$dropdownCity .= "\r\n</select>";

//Create AO dropdown list object
$dropdownAO = "<select name='selectAO'>";
while($rowAO = mysql_fetch_assoc($resultAO))
{
$dropdownAO .= "\r\n<option value='{$rowAO['AO']}'>{$rowAO['AO']}</option>";
}
$dropdownAO .= "\r\n</select>";
//Create Job dropdown list object
$dropdownJob = "<select name='selectJob'>";
while($rowJob = mysql_fetch_assoc($resultJob))
{
$dropdownJob .= "\r\n<option value='{$rowJob['Job']}'>{$rowJob['Job']}</option>";
}
$dropdownJob .= "\r\n</select>";

//Create Skills dropdown list object
$dropdownSkills = "<select name='selectSkills'>";
while($rowSkills = mysql_fetch_assoc($resultSkills))
{
$dropdownSkills .= "\r\n<option value='{$rowSkills['Skills']}'>{$rowSkills['Skills']}</option>";
}
$dropdownSkills .= "\r\n</select>";	


//Print the drop downlists on the page
echo "Please choose from the following fields below:";
echo "Location &nbsp&nbsp&nbsp";
echo $dropdownCity;
echo "AO Level &nbsp&nbsp&nbsp";
echo $dropdownAO;
echo "Occupation &nbsp&nbsp&nbsp";
echo $dropdownJob;
echo "Skills &nbsp&nbsp&nbsp";
echo $dropdownSkills;
?>

<form method="post" action="mypage.php">
<input type="submit" value="submit" name="Submit">

<?php
//This code is for retrieving the results of the drop down selections after the submit button is clicked
$selectionCity = $_POST["selectSkills"];
$selectionJob = $_POST["selectJob"];
//SQL query from retrieved results
//SQL query from retrieved results
$sqlresults = "SELECT * FROM Users WHERE City='"$selectionCity"' AND Job='"$selectionJob"'";
$sqlpost = mysql_query($sqlresults) or die(mysql_error());
echo $sqlpost;
?>

 

I know the page looks horrible, trying to tackle one issue at a time and get this functional.

 

Thanks for any and all help.

Edited October 18, 2012 by Nublette

[gizmola]

So are we to assume you have a single table (Users?). If so, your initial queries all need to be "SELECT DISTINCT" or your're going to get a row for every job, rather than the distinct list of jobs, for example.

 

If you had a relational design, with separate tables for jobs, users, skills and AO's, this would not be as horribly inefficient as it no doubt will be, where on each display of the page, you will tablescan the Users table at least 4x. I also don't see the correlation between a single job and a single skill. Doesn't a single job require more than one skill? I don't know what an "AO" refers to, but the same question remains.

 

Structurally, your bottom section needs a condition, so that you only query when you have a city and job to set in $_POST. Which also begs the question... why do you display other drop downs, yet you don't use them in your query?

 

Try and apply DRY and at very least, write a function to render a drop down. The code for those dropdown lists is always the same -- what varies is simply the select name. One way to handle this would be:

 

 

function makeSelect($name, $sql)
{
 $out = '';
 $result = mysql_query($sql) or die(mysql_error());
 if ($result) {
	   $out .= "<select name='$name'>";
	   while($row = mysql_fetch_row($result) {
		   //output individual select
		  $out .= "\r\n<option value='{$row[0]}'>{$row[0]}</option>";
	   }
	  $out .= "\r\n</select>";
 }
 return $out;
}

[Nublette]

So are we to assume you have a single table (Users?). If so, your initial queries all need to be "SELECT DISTINCT" or your're going to get a row for every job, rather than the distinct list of jobs, for example.

 

If you had a relational design, with separate tables for jobs, users, skills and AO's, this would not be as horribly inefficient as it no doubt will be, where on each display of the page, you will tablescan the Users table at least 4x. I also don't see the correlation between a single job and a single skill. Doesn't a single job require more than one skill? I don't know what an "AO" refers to, but the same question remains.

 

Structurally, your bottom section needs a condition, so that you only query when you have a city and job to set in $_POST. Which also begs the question... why do you display other drop downs, yet you don't use them in your query?

 

Try and apply DRY and at very least, write a function to render a drop down. The code for those dropdown lists is always the same -- what varies is simply the select name. One way to handle this would be:

 

 

function makeSelect($name, $sql)
{
 $out = '';
 $result = mysql_query($sql) or die(mysql_error());
 if ($result) {
	 $out .= "<select name='$name'>";
	 while($row = mysql_fetch_row($result) {
		 //output individual select
		 $out .= "\r\n<option value='{$row[0]}'>{$row[0]}</option>";
	 }
	 $out .= "\r\n</select>";
 }
 return $out;
}

 

Hi,

 

Thanks for the reply, I know my code is bad, I am just trying to get it functional before I start to work on making it more efficient, I will design the tables and code better and repost, hopefully I will get what I need then, thanks for the post I appreciate it, it has made me aware that I need my code and table design in a much better condition before I ask for help. I was aware that I am terrible at this but you have made it even more keenly felt, to be honest I kind of just want to uninstall windows and go cry in a corner.

[gizmola]

I'm sorry if that is the impression I gave. You're code is not at all bad for a beginner, and it takes time to figure out the way to approach particular things.

 

Certainly, I have questions about why you do certain things in your code, and I hope that I demonstrated the "DRY" concept -- try and find places where you are basically doing the same code repeatedly, varied only by one or two variables, as in the case of the html selects, and see if you can reduce those to a single function, that can then be used instead of the same 5-10 lines of code.

 

I've been doing database work for a looong time, so I find it hard to resist pointing out designs that aren't optimal, but the truth is, that for small to even medium size databases the efficiency of the design is not all that important.