Using PHP drop down to filter MySQL table data?

Hey guys, since I’ve been told to drop MS access and use MySQL for a website based database, I decided to try it out and have been making some good progress, however I’ve come to a halt when trying to create a PHP drop down that filters through my simple table.

My table consists of 3 columns; Id, FirstName, and LastName. I want the drop down menu to filter through last names, as I have created fake names using 2 of the same last names(John Doe, Tim Doe, Bob Doe, Jim Earl, Tom Earl, etc).

Basically, my goal is for the drop down to have 3 options; All (The default option and display, which shows all names), Doe (Only display names with LastName equal to Doe), and finally Earl(Only display names with LastName equal to Earl).

The most I could come up with currently, is a drop down that displays the values of “FirstName” from my table, but again, it’s not what I’m trying to do ;/

Here is the code currently;


<?php

//some code, connecting to database, etc

$result = mysql_query("SELECT * FROM names");

print "<p>Select A First Name: \
";
print "<select name=\\"Id\\" > \
";
while ($row = mysql_fetch_array($result)){
		$Id = $row['Id'];
		$FirstName = $row['FirstName'];
		print "<option value=$Id>$FirstName\
";
		}
print "</select>\
";
print "</p>\
";

mysql_close($con);
?>


I know that’s probably know where near how I’m suppose to code the drop down with submit values to search the table, but it’s the closest I could come up with using my current knowledge.

Any ideas?

Element

I’m assuming this is possible to do all in one .php file, or do I need to create an html file linked to a php if I wana have a drop down that selects from the table on button submit?

Anyone have any ideas?

I’m not sure I follow. Do you want all of the names to show IN the select menu? Or do you want the select menu to have the three options, and then when an option is selected show the results?

Yes, the second one, I want the menu to have three options, and then when an option is selected it will pull that data from the table.

Basically, the 3 results would be something like “All” (Selects and displays every name in table), “Doe” (Selects and displys only people / data with last name = Doe), and “Earl” (Selects and displays only people / data with last name = Earl).

Hope that clarifies it :wink:

Element

You could do something like this

<select name="value">
    <option value="all">All</option>
    <option value="Doe">Doe</option>
    <option value="Earl">Earl</option>
</select>

and then check which value is posted and perform your query.

<?php
if($_POST['value'] == 'Doe')) {
    // query to get all Doe records
    $query = "SELECT * FROM names WHERE name='Doe'";
}
elseif($_POST['value'] == 'Earl') {
    // query to get all Earl records
    $query = "SELECT * FROM names WHERE name='Earl'";
} else {
    // query to get all records
    $query = "SELECT * FROM names";
}
$sql = mysql_query($query);

// print the names...
?>

Wow thanks for the reply, I’m just confused about two things;

  1. Would I need to create 2 .php forms? 1 with the drop down, and the other one with the query, or can it all be done in one?

  2. How would I print that query result?

Currently I have the following, however it is not working, assuming I did something wrong as my knowledge of PHP isn’t great. This is all in 1 form;



<select name="value">
    <option value="all">All</option>
    <option value="Fitzgerald">Fitzgerald</option>
    <option value="Herring">Herring</option>
</select>

<?php

//connect to database, checking, etc

if($_POST['value'] == 'Fitzgerald')) { 
    // query to get all Fitzgerald records 
    $query = "SELECT * FROM names WHERE name='Fitzgerald'"; 
} 
elseif($_POST['value'] == 'Herring') { 
    // query to get all Herring records 
    $query = "SELECT * FROM names WHERE name='Herring'"; 
} else { 
    // query to get all records 
    $query = "SELECT * FROM names"; 
} 
$sql = mysql_query($query); 

while ($row = mysql_fetch_array($query)){
		$Id = $row["Id"];
		$FirstName = $row["FirstName"];
		$LastName = $row["LastName"];

mysql_close($con);
?>


I’ve Also tried creating 2 forms, 1 with the select (drop down) and the other with the results, however my goal, if possible is to display the results under the drop down on the same page so the user may search again if he / she wishes

Element

I have had a little bit more luck creating 2 pages, but it’s still not what I’m trying to achieve (Everything on 1 page / form). And I can’t get it to display the table data, I can only get the drop down to appear D:

I apologise if you have done this already, but just based on the code you’ve provided:

Make sure the dropdown is in a form - a dropdown on it’s own won’t do anything.

Something like…


<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' >

    <select name="value">
        <option value="all">All</option>
        <option value="Fitzgerald">Fitzgerald</option>
        <option value="Herring">Herring</option>
    </select>
	
    <br />
	
    <input type='submit' value = 'Filter'>

</form>

<?php

//connect to database, checking, etc

if($_POST['value'] == 'Fitzgerald')) {
    // query to get all Fitzgerald records
    $query = "SELECT * FROM names WHERE name='Fitzgerald'";
}
elseif($_POST['value'] == 'Herring') {
    // query to get all Herring records
    $query = "SELECT * FROM names WHERE name='Herring'";
} else {
    // query to get all records
    $query = "SELECT * FROM names";
}
$sql = mysql_query($query);

while ($row = mysql_fetch_array($query)){
        $Id = $row["Id"];
        $FirstName = $row["FirstName"];
        $LastName = $row["LastName"];

// Echo your rows here...

mysql_close($con);
?>

The form is posting to itself, so you only need the one page.

You only need to do this on one page. Just create your HTML form and other page elements as you normally would. Then, at the very top of the page add the PHP to process the form.

To print the result you need to echo or print it, as you did a previous post. This is just a basic example…

while($row = mysql_fetch_array($query)){
        echo 'ID = ' . $row['Id'] . '<br>';
}

Full (though incomplete) example

<?php 
//connect to database, checking, etc 

// process form when posted
if(isset($_POST['value'])) {
	if($_POST['value'] == 'Fitzgerald') {
		// query to get all Fitzgerald records  
		$query = "SELECT * FROM names WHERE name='Fitzgerald'";  
	}  
	elseif($_POST['value'] == 'Herring') {  
		// query to get all Herring records  
		$query = "SELECT * FROM names WHERE name='Herring'";  
	} else {  
		// query to get all records  
		$query = "SELECT * FROM names";  
	}  
	$sql = mysql_query($query);  
	
	while ($row = mysql_fetch_array($query)){ 
		$Id = $row["Id"]; 
		$FirstName = $row["FirstName"]; 
		$LastName = $row["LastName"]; 
		
		// Echo your rows here... 
		echo 'The user ID is:' . $row['id'];
	}
	mysql_close($con); 
}
?>
<html>
<head></head>
<body>
<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter' > 
	<select name="value"> 
		<option value="all">All</option> 
		<option value="Fitzgerald">Fitzgerald</option> 
		<option value="Herring">Herring</option> 
	</select> 
	<br /> 
	<input type='submit' value = 'Filter'> 
</form>
</body>
</html>

Full working example of a form to filter names and display the results in a data grid.



<?php
/*
* Testing configuration
*/
define('ENVIR',0);
define('ENVIR_DEV',0);
define('ENVIR_LIVE',1);

/*
* Data base credentials (replace this with your db credentials)
*/
define('DB_USER','');
define('DB_PWD','');
define('DB_HOST','localhost');
define('DB_NAME','');

/*
* Connect to database
*/
try {
	$db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PWD);
} catch(PDOExeption $e) {

	if(ENVIR == ENVIR_DEV) {
		echo '<p>',$e->getMessage(),'</p>';
	}

	echo '<p>Unable to connect to database<p>';
	exit;
}

/*
* Template variables
*/
$tpl = array(
	'filter'=>array(
		 '#action'		=> $_SERVER['SCRIPT_NAME']
		,'#method'  	=> 'get'
		,'last_name'	=> array(
			'#values'=>array(
				 array('value'=>'','label'=>'All')
			)
		)
	)
	,'grid'=>array(
		'names'=>array()
	)
);

/*
* Populate form filter last name options
*/
$stmt = $db->query('SELECT LastName FROM names GROUP BY LastName ORDER BY LastName ASC');

if($stmt === false) {
	echo '<p>Unable to populate required data to build page.</p>';
	exit;
}

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
	$tpl['filter']['last_name']['#values'][] = array(
		 'label'		=> $row['LastName']
		,'value'		=> $row['LastName']
		,'selected'		=> isset($_GET['filter'],$_GET['filter']['last_name']) && $_GET['filter']['last_name'] == $row['LastName']
	);
}


/*
* Populate user grid
*/
$stmt = $db->prepare(sprintf(
	'SELECT FirstName,LastName FROM names %s'
	, isset($_GET['filter'],$_GET['filter']['last_name']) && !empty($_GET['filter']['last_name'])?'WHERE LastName = :lastname':''
));

if($stmt === false) {
	echo '<p>Unable to populate required data to build page.</p>';
	exit;
}

$stmt->execute(isset($_GET['filter'],$_GET['filter']['last_name']) && !empty($_GET['filter']['last_name'])?array(':lastname'=>$_GET['filter']['last_name']):array());

while($row=$stmt->fetch(PDO::FETCH_ASSOC)) {
	$tpl['grid']['names'][] = $row;
}

/*
* Start template output
*/
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8">
	<title>Names</title>
</head>
<body>

<!-- user filter template -->
<form action="<?php echo $tpl['filter']['#action']; ?>" method="<?php echo $tpl['filter']['#method']; ?>">
	<fieldset>
		<legend>Filter Names</legend>
		<ul>
			<li>
				<label for="filter-last-name">Last Name</label>
				<select name="filter[last_name]" id="filter-last-name">
					<?php 
					foreach($tpl['filter']['last_name']['#values'] as &$option) {
						printf(
							'<option value="%s"%s>%s</option>'
							,htmlentities($option['value'])
							,$option['selected']?' selected':''
							,htmlentities($option['label'])
						);
					} 
					?>
				</select>
			</li>
			<li>
				<input type="submit" name="filter[submit]" value="Filter Names"> 
			</li>
		</ul>
	</fieldset>
</form>

<!-- data grid template -->
<table>
	<caption>Names</caption>
	<thead>
		<tr>
			<th>First Name</th>
			<th>Last Name</th>
		</tr>
	</thead>
	<tbody>
		<?php
			if(!empty($tpl['grid']['names'])) {
				foreach($tpl['grid']['names'] as &$name) {
					printf(
						'<tr>
							<td>%s</td>
							<td>%s</td>
						 </tr>'
						 ,htmlentities($name['FirstName'])
						 ,htmlentities($name['LastName'])
					);
				}
			} else {
				echo '<tr><td colspan="2">No names available</td></tr>';
			}
		?>
	</tbody>
</table>

</body>
</html>

This is the database schema and some test data.


CREATE table names (
	id BIGINT unsigned not null auto_increment
	,LastName varchar(56) NOT NULL
	,FirstName varchar(56) NOT NULL
	,primary key(id)
	,INDEX(LastName)
) ENGINE=innodb; default charset=utf8;

insert into names values (NULL,'Doe','John'),(NULL,'Doe','Jane'),(NULL,'Smith','John');

Thanks a ton everyone, got it sorted now thanks to all the help :wink:

Element

Hey people, I just read over this topic and I’ve tried your solutions, but somehow my working php query bugs out trying to link the dropdown form to the Query of my db.

My code atm looks like this and fails to display anything atm, I can get the form & my database but as soon as I put in:

if($_POST['value'] == '3300')) {
    // query to get all Doe records
    $query="SELECT * FROM $tabel WHERE LiszCode='3300'";
}
elseif($_POST['value'] == '3400') {
    // query to get all Earl records
    $query="SELECT * FROM $tabel WHERE LiszCode='3400'";
} else {
    // query to get all records
    $query="SELECT * FROM $tabel";
}
$sql = mysql_query($query);

I do not understand why, if anyone could help me out that would be great!

My full script should be this, although the problem lies in previous stated script (i think)
As you can see I already tried removing the variables from the dropdown into hardcoded answers but still no luck.

<HTML>
<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='form_filter'>
<select name="value">
<option value="" selected="selected">Kies uw zorgverzekeraar</option>
<option value="3300">VGZ</option>
<option value="3400">Unive</option>
</select>
<input type="submit" value='geef prijzen' />
</form>
</HTML>


<?php

//Variables
$username="******";
$password="******";
$database="prijzen";
$tabel="Tarief";
$value= $_POST["value"];

//connecting to database

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//point where it goes wrong
if($_POST['value'] == '3300')) {
    // query to get all Doe records
    $query="SELECT * FROM $tabel WHERE LiszCode='3300'";
}
elseif($_POST['value'] == '3400') {
    // query to get all Earl records
    $query="SELECT * FROM $tabel WHERE LiszCode='3400'";
} else {
    // query to get all records
    $query="SELECT * FROM $tabel";
}
$sql = mysql_query($query);

//-end wrong

$result=mysql_query($query);

$num=mysql_numrows($result);


mysql_close();
?>
<table border="1" cellspacing="4" cellpadding="6">
<tr><th colspan="4" align="center">Prijzentabel</th></tr>
<tr>
<th width="70px" align=right><font face="Arial, Helvetica, sans-serif">Prestatiecode</font></th>
<th><font face="Arial, Helvetica, sans-serif">Omschrijving</font></th>
<th width="70px"><font face="Arial, Helvetica, sans-serif">Kostprijs</font></th>
<th width="60px"><font face="Arial, Helvetica, sans-serif">Geldig t/m</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f2=mysql_result($result,$i,"Code");
$f3=mysql_result($result,$i,"Omschrijving");
$f4=mysql_result($result,$i,"Bedrag");
$f5=mysql_result($result,$i,"JaarGeldig");
?>

<tr>

<td align=right><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td align=right><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td align=right><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td align=center><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
</tr>

<?php
$i++;
}

?></TABLE>

Can’t seem to find the [EDIT] button, so i’ll just reply. I’ve fixed it for a little bit, everything gets displayed again, though the filtering/variable query does not work.

My goal is to be able to filter the (final) around 1700 results into groups based on the LiszCode value.


<HTML>

<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' name='value' >
<select name="value">
<option value="all" selected="selected">Kies uw zorgverzekeraar</option>
<option value="3330">3330 VGZ</option>
<option value="3400">3400 Unive</option>
</select>
<input type="submit" value='geef prijzen' />
</form>
</HTML>


<?php

//Variables
$username="*****";
$password="*****";
$database="prijzen";
$tabel="Tarief";
$value= $_POST["value"];

//connecting to database

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

if($value == 'all')
    $query="SELECT * FROM $tabel WHERE LiszCode='3400'";
else
    $query="SELECT * FROM $tabel WHERE LiszCode='3330'";



$sql = mysql_query($query);


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM $tabel";
$result=mysql_query($query);

$num=mysql_numrows($result);


mysql_close();
?>
<table border="1" cellspacing="4" cellpadding="6">
<tr><th colspan="4" align="center">Prijzentabel</th></tr>
<tr>
<th width="70px" align=right><font face="Arial, Helvetica, sans-serif">Prestatiecode</font></th>
<th><font face="Arial, Helvetica, sans-serif">Omschrijving</font></th>
<th width="70px"><font face="Arial, Helvetica, sans-serif">Kostprijs</font></th>
<th width="60px"><font face="Arial, Helvetica, sans-serif">Geldig t/m</font></th>
</tr>

<?php
$i=0;
while ($i < $num) {

$f2=mysql_result($result,$i,"Code");
$f3=mysql_result($result,$i,"Omschrijving");
$f4=mysql_result($result,$i,"Bedrag");
$f5=mysql_result($result,$i,"JaarGeldig");
?>

<tr>

<td align=right><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td>
<td align=right><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td>
<td align=right><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td>
<td align=center><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td>
</tr>

<?php
$i++;
}

?></TABLE>

You are getting all your naming mixed up.

Form element does not need a “value”, and certainly not named “value” - that is just too confusing.

Take a look at these changes and see if you can get it working.


<HTML>

<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' >
<select name="liszCode">
<option value="all" selected="selected">Kies uw zorgverzekeraar</option>
<option value="3330">3330 VGZ</option>
<option value="3400">3400 Unive</option>
</select>
<input type="submit" value='geef prijzen' />
</form>
</HTML>


<?php

//Variables
$username="*****";
$password="*****";
$database="prijzen";
$tabel="Tarief";
$liszCode= $_POST["liszCode"];

then:


// change this too
if($_POST['liszCode'] == '3300')) {

Got it to work now!! perfect thanks :slight_smile:

Also note to self, triple-check for unclosed arguments/tables, etc. >_<

Now for tomorrow, getting the dropdown menu content from a database too :slight_smile:

That is a client-side challenge – snagging a JS onchange event which fires the form submition – PHP does not care how the form is submitted – straight html or JS.

From a usability standpoint I would not recommend it for 2 main reasons:

a) some users may not have JS enabled, in which case the form will not submit

b) just by picking something in a drop-list does not usually cause the entire page to refresh and something new appear - and may confuse some users.

A lot depends on how/where you use this technique.

Thanx (i accidently deleted my first post) for your answer!

I made some minor changes of this and I ran into a problem or two:
I have 4 menus. 3 are simple catalogs. Also, I thought to make 4th a limit per page list (see 10 results per page, see 20 results per page etc)
I get the page to open fine. All data are shown. I can make selection, and the filter works.
The problems are:
a) my “all” options are NOT working at all - they do show at the first opening of the page, but after I select something I can’t revert to them in all 3 menus when I click “all”.
and
b) i can’t get the display number of results to work…

Please help! (when i get the basic thing to work i’ll do protection etc, i know - this is just for learning)

The drop down menu is:

<form action='<?php echo $_SERVER['PHP_SELF']; ?>' method='post' enctype="multipart/form-data" name="form" target="_self" id="form">
Choice1:<br>
<select name="catalog" onchange="document.form.submit();">
<option value="all">all</option>
<option value="TestA1">TestA1</option>
<option value="TestA2">TestA2</option>
<option value="TestA3">TestA3</option>
</select>
Choice2:<br>
<select name="catalog2" id="catalog2" onchange="document.form.submit();">
<option value="all">all</option>
<option value="TestB1">TestB1</option>
<option value="TestB2">TestB2</option>
<option value="TestB3">TestB3</option>
</select>
Choice3:<br>
<select name="catalog3" id="catalog3" onchange="document.form.submit();">
<option value="all">all</option>
<option value="TestC1">TestC1</option>
<option value="TestC2">TestC2</option>
</select>
Results per page:<br>
<select name="catalog4" id="catalog4" onchange="document.form.submit();">
<option value="all">all</option>
<option value="20">20 per page</option>
<option value="50">50 per page</option>
<option value="100">100 per page</option>
</select>
</form>

the php is:

<?php
$username="xxxxx";
$password="xxxxx";
$database="xxxxx";
$value= $_POST["catalog"];
$value= $_POST["catalog2"];
$value= $_POST["catalog3"];
$value= $_POST["catalog4"];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

//catalog1//
if ($_POST['catalog'] == 'TestA1')
{
$query = "SELECT * FROM mytable WHERE mycolumn = 'TestA1'";
}
elseif ($_POST['catalog'] == 'TestA2')
{
$query = "SELECT * FROM mytable WHERE mycolumn = 'TestA2'";
}
elseif ($_POST['catalog'] == 'TestA3')
{
$query = "SELECT * FROM mytable WHERE mycolumn = 'TestA3'";
}
//catalog2//
elseif ($_POST['catalog2'] == 'TestB1')
{
$query = "SELECT * FROM mytable WHERE mycolumn2 = 'TestB1'";
}
elseif ($_POST['catalog2'] == 'TestB2')
{
$query = "SELECT * FROM mytable WHERE mycolumn2 = 'TestB2'";
}
elseif ($_POST['catalog2'] == 'TestB3')
{
$query = "SELECT * FROM mytable WHERE mycolumn2 = 'TestB3' ";
}
//catalog3//
elseif ($_POST['catalog3'] == 'TestC1')
{
$query = "SELECT * FROM mytable WHERE mycolumn3 = 'TestC1'";
}
elseif ($_POST['catalog3'] == 'TestC2')
{
$query = "SELECT * FROM mytable WHERE mycolumn3 = 'TestC2'";
}
//catalog4 - NUMBER OF RECORDS PER PAGE//
elseif ($_POST['catalog4'] == '20')
{
$query = "SELECT * FROM mytable LIMIT BY 0,20";
}
elseif ($_POST['catalog4'] == '50')
{
$query = "SELECT * FROM mytable LIMIT BY 0,50";
}
elseif ($_POST['catalog4'] == '100')
{
$query = "SELECT * FROM mytable LIMIT BY 0,100";
}
else
{
$query = "SELECT * FROM mytable ORDER BY mytable_id ASC";
}
$sql = mysql_query($query);
while ($row = mysql_fetch_array($sql)) {
        $catalog = $row["mycolumn"];
        $format = $row["mycolumn2"];
        $vrsta = $row["mycolumn3"];
		$mytable_id = $row["mytable_id"];

	echo $row["myrow"]."";
	}
?>

Can you please help me?

There are some problems in the code you posted.

You keep wiping out the value of $value as you re-assign it to yet another POST variable - so $value will always end up having the value of catalog4


$value= $_POST["catalog"]; 
$value= $_POST["catalog2"]; 
$value= $_POST["catalog3"]; 
$value= $_POST["catalog4"]

Then you seemingly don’t use $value so, really, what is the point of that code at all?

Given a list of 20 “if/elseif” conditions, at the first one of those conditions that is true PHP will stop and go down to this line:


$sql = mysql_query($query); 

If we take a worst case scenario, someone who picks a value from each of the 4 drop-downs:


$_POST["catalog"] = 'TestA3'; 
$_POST["catalog2"] = 'TestA2'; 
$_POST["catalog3"] = 'TestC1'; 
$_POST["catalog4"] = '20'; 

Now, where is the matching data coming from? A mysql database?

If so do you already have that set up?

Show us the schema.