Drop Down For A 'All' Category

[roberts]

Hey Guys!

 

I've ran into a little problem and I've become confused and its doing my head in, even though I consider it as something trivial. My problem is that I'm creating a drop down menu, but would like to have an 'All' option, that would display all the results from the other options (fetches the required data from the database). I've practically ran out of ideas and I would like to here what you guys could suggest. Thanks in advance!

 

PHP Code:

<select name="season"><?php
$physios=array( "All" => "All employees",
						  "Chefs" => "Chefs",
						  "Wait" => "Wait" );

foreach ($physios as $v => $d) {
	  print "<option value=\"$v\"";	
		if (isset($_GET['sea']) && $_GET['sea'] == $v) {
		   print ' selected="selected"';
		}
	   print ">$d</option>\n";
}
?></select>

 

SQL Query:

$query = "SELECT empl_id, year, position, name, yel, rel
			   FROM played
				 WHERE empl_id=? AND position=? AND year=? AND (yel=? OR rel=?)
				   ORDER BY name DESC";

[Paul Swanson]

What I would do in this situation is use a variable for the entire WHERE clause part of the SQL, and use conditionals to set the value. Example:

$where = NULL;
if (!empty ($_POST['season']) {
if ($_POST['season'] == 'All') {
	$where = '';
} else {
	$season = (int) $_POST['season'];
	$where = "WHERE season=$season ";
   }
}

$query = "SELECT empl_id, year, position, name, yel, rel FROM played $where ORDER BY name DESC";

 

That's simplified, using only the season and assuming it's an integer value in the database, but it should give you an idea of how to approach it. You can concatenate more fields as needed. Essentially, if you want all players from all seasons you'll want $where to be an empty string.

Edited January 4, 2012 by Paul Swanson

[roberts]

Sorry Paul, I don't quite understand your suggestion. Could you give a more simplified example please.

[Josee]

I think Larry's answer to this topic:

http://www.larryullm...ch__1#entry3629

may help you too. It's similar to Paul Swanson's suggestion.

 

I hope this helps,

[Paul Swanson]

I think Larry's description in the link provided by Josee (thank you Josee!) will help you. His explanation shows how to build up your query by concatenating the pieces as you go through your validation. This is essentiallly what I was getting at, but I tend to build the WHERE clause into a variable that I insert into the query variable.

 

There was also a missing closing brace in the example I provided, which I just fixed.

[roberts]

Hey Guys,

 

First of all thanks for the replies. Second of all, I think you've misunderstood what I am trying to do. What I am trying to do is create a drop down menu that would have 3 options: All employee, Chefs and Waiters. When Chefs and Waiters are selected the data is fetched from a database that would display the selected option in a table, now this works great. The problem lays with the All employee option which should fetch both Chefs and Waiters data and display it in a form. I hope that explains my problem in better detail. I am also using $_GET and not $_POST.

 

Further code on how i get the selected option to be run

 

if (isset($_GET['check_request'])) {
 $required=array();

 $sea=false;
 if(isset($_GET['sea']) && $_GET['sea'] == NULL){
$required[]='You are required to select!';
 } else {
  $sea= trim($_GET['sea']);
 }


if (empty($required)) {

require('connection.php');

$query = "SELECT empl_id, year, position, name, yel, rel
			   FROM played
				 WHERE empl_id=? AND position=? AND year=? AND (yel=? OR rel=?)
				   ORDER BY name DESC";

 

Thanks in advance

[Josee]

Hello, roberts,

 

I think one solution would be to add "multiple" to your dropdown menu, so that "season" would become an array instead of a scalar value. Instead of:

<select name="season"><?php

$physios=array( "All" => "All employees",

"Chefs" => "Chefs",

"Wait" => "Wait" );

 

you would have:

 

<select name="season[]" multiple="multiple" size="2"><?php

$physios=array("Chefs" => "Chefs", "Wait" => "Wait");

 

Thanks to "multiple", users can select either one or both options. 'Size="2"' means two lines will be visible in your drop-down menu (both options in your case). I never remember off-hand, but I think we use the command key to select more than one option.

 

I hope this helps,

[Paul Swanson]

roberts, I have to confess that I am confused by your query. It seems like it has to do with a sports team or players, but your select element seems like it has to do with restaurant workers. I don't see the connection, unless it's a company team from a restaurant? Also, I don't get the question marks in your query. I've never seen that before: is it supposed to be part of your actual code, or is it that you aren't sure what should go there?

 

So I'm going to make up an example that I hope will demonstrate what I think you want. Let's say you have a database table named 'staff' and it has columns for id, name, and position. The position column will indicate whether the employee is a chef or a waiter. Here is what the select element might look like:

<select name="position">
<option value="a">All employees</option>
<option value="Chef">Chefs</option>
<option value="Wait">Waiters</option>
</select>

And here is how I would do the field validation and write the query:

if (isset ($_GET['position'] && strlen ($_GET['position'] > 0)) {
if ($_GET['position'] == 'a') {
	$position = '';
} else {
	$position = "WHERE position='" . mysql_real_escape_string ($_GET['position']) . "'";
}
} else {
$required[] = 'You must select a position';
}

$query = "SELECT id, name, position FROM staff $position ORDER BY name ASC";

 

If you have additional criteria in your WHERE clause (and I think you do), the $position variable could be "AND position='" . mysql_real_escape_string ($_GET['position'] . "'"; instead of being the entire WHERE clause, and you could insert that value into the query after one of your other criteria.

 

I would also suggest you try my example in a test script before modifying what you already have, and print the $query variable rather than run mysql_query($query). That way you can tweak it until you see the $query has what you are looking for.

 

I hope this helps you find your solution.