how do i have ALL in a drop down menu?

yellowchimp

Hello

The following code is from a form with a drop down menu:

Select a Consultee:
<select name="consultee_search">
<?
$sqlquery= "SELECT consultee_name, consultee_id from consultees;";

$process=odbc_exec($sqlconnect, $sqlquery);
while(odbc_fetch_row($process))

{
$consultee_name=odbc_result($process,"consultee_name");
$consultee_id=odbc_result($process,"consultee_id");

echo "<option value=$consultee_id>$consultee_name</option>";
}

?>
</select>

As you can see the code makes a drop down selection box where the contents of the field consultee_name from the table consultees are displayed. The page that the form results are displayed on has the code

WHERE doc_consultees.consultee_id=".$_POST['consultee_search']."

in the SQL statement.

I would like to have an option in the dropo down menu of 'ALL' though. And I just can't figure out how to do this.

Would I have to create a consultee_name of ALL within the database table consultees. Then how would I associate 'ALL' with all the consultee_id's?

Any suggestions or tips appreciated

Thank you

devinemke

code your form like this:

<select name="consultee_search">
<option value="all">all</option>
<? 
$sqlquery = 'SELECT consultee_name, consultee_id from consultees';
$process = odbc_exec($sqlconnect, $sqlquery);
while(odbc_fetch_row($process))
{ 
	$consultee_name = odbc_result($process, 'consultee_name');
	$consultee_id = odbc_result($process, 'consultee_id');
	echo '<option value="' . $consultee_id . '">' . $consultee_name . '</option>';
}
?>
</select>

and then on your form processor:

if ($_POST['consultee_search'] != 'all')
{
	// add the WHERE clause in your SQL
}

yellowchimp

I'm afaid I don't quite understand.

I still don't get the option of 'ALL' appearing in my drop down menu?

yellowchimp

apologies I did not paste all the code you offered in - missing the top line. I'm stupid.

I will try again

yellowchimp

ok - i have put the code into the form as you suggested and that works. I now have an all option

But in the other page I have put the code

$connect = odbc_connect("conserve", "sa", "");
$result = odbc_exec($connect, "SELECT doc_url, doc_title
FROM (documents
INNER JOIN doc_types
ON documents.doc_id=doc_types.doc_id)
INNER JOIN doc_consultees
ON documents.doc_id=doc_consultees.doc_id

if ($POST['consultee_search'] != 'all')
{
WHERE doc_types.type_id=".$POST['type_search']."
AND doc_consultees.consultee_id=".$POST['consultee_search']."
AND doc_date_yr = '".$POST['year_search']."'
}");

I think I have inserted the bit you suggested in properly. But it does not seem to like it. I don't get any results back from the form now. I did notice that the $_POST bit after the bit that says if does not appear in bold like all the other variables.

Thank you ever so much for your help

devinemke

$connect = odbc_connect('conserve', 'sa', '');

$sql = 'SELECT doc_url, doc_title 
FROM (documents 
INNER JOIN doc_types 
ON documents.doc_id = doc_types.doc_id) 
INNER JOIN doc_consultees 
ON documents.doc_id = doc_consultees.doc_id';

if ($_POST['consultee_search'] != 'all')
{ 
	$sql .= " WHERE doc_types.type_id = '" . $_POST['type_search'] . "' 
	AND doc_consultees.consultee_id = '" . $_POST['consultee_search'] . "' 
	AND doc_date_yr = '" . $_POST['year_search'] . "'";
}

$result = odbc_exec($connect, $sql);

yellowchimp

I'm afraid this only gives me a result if I select 'all' from the consultee drop down menu. And when it does this it ignores the fact that I have selected a particular year from the year drop down menu. It brings back all teh documents from my database.
Also if I select anything other than 'all' from the consultee drop down menu I don't get any results at all.

Thanks for your help though. It is appreciated :-)