Here is a simple way to create a multi drop down search form for your site/records. There is a function which writes the drop down based on a field/table name that gets passed to ti. It returns an html string with the drop down in it.
<?php
/*------------------------------------------------------------------------
control codes
------------------------------------------------------------------------*/
if (isset($_POST['submit']))
{
search(); //call the search function
}else{
show_form(); //call the show form function
}//end if
/*------------------------------------------------------------------------
show the search form
------------------------------------------------------------------------*/
function show_form()
{
//call the dropdown function which creates an html string to build a select box for each element
$cuenta = dropdown('cuenta','notas');
$idclase = dropdown('idclase','notas');
$anio = dropdown('anio','notas');
$periodo = dropdown('periodo','notas');
$nota = dropdown('nota','notas');
/*------------------------------------------------------------------------
run the search and show the results
------------------------------------------------------------------------*/
function search()
{
//base sql
$sql = "select * from notas where 1 ";
//get the values from the form
//NOTE: You should do way more valdation on the values before you attempt to process anything
if ((!empty($_POST['cuenta']))&&($_POST['cuenta'] != 'all'))
{
$sql .= " and cuenta like '". addslashes($_POST['cuenta'])."%' ";
}
if ((!empty($_POST['idclase']))&&($_POST['idclase'] != 'all'))
{
$sql .= " and idclase like '". addslashes($_POST['idclase'])."%' ";
}
if ((!empty($_POST['anio']))&&($_POST['anio'] != 'all'))
{
$sql .= " and anio = '". addslashes($_POST['anio'])."' ";
}
if ((!empty($_POST['periodo']))&&($_POST['periodo'] != 'all'))
{
$sql .= " and periodo = '". addslashes($_POST['periodo'])."' ";
}
if ((!empty($_POST['nota']))&&($_POST['nota'] != 'all'))
{
$sql .= " and nota = '". addslashes($_POST['nota'])."' ";
}
//add more elements (or take away) as you desire...follow the same code structure as above
//run query
$result = conn($sql);
if (!$result){ die("No results due to database error.<br>".mysql_error()); }
if (mysql_num_rows($result)==0)
{
echo "No Results found!";
}else{
/*------------------------------------------------------------------------
create the drop downs
------------------------------------------------------------------------*/
//check to see if the field is passed correctly
if (($field == "")||($table == ""))
{
die("No column or table specified to create drop down from!");
}
$sql = "select distinct($field) from $table";
//call the db function and run the query
$result = conn($sql);
//if no results are found to create a drop down return a textbox
if ((!$result) ||(mysql_num_rows($result)==0))
{
$oHTML .= "<input type='text' name='$field' value='' size='15'>";
}elseif (($result)&&(mysql_num_rows($result)>0)){
//build the select box out of the results
$oHTML .= "<select name='$field'>\n<option value='all'>All</option>\n";
while ($rows = mysql_fetch_array($result))
{
$oHTML .= "<option value='".$rows[$field]."'>".$rows[$field]."</option>\n";
}
$oHTML .= "</select>\n";
}
//send the value back to the calling code
return $oHTML;
}//end function
/*------------------------------------------------------------------------
database connection function
------------------------------------------------------------------------*/
if (!($conn=mysql_connect($host, $username, $pwd))) {
printf("error connecting to DB by user = $username and pwd=$pwd");
exit;
}
$db3=mysql_select_db($dbname,$conn) or die("Unable to connect to local database");
$result = mysql_query($sql) or die ("Can't connect because ". mysql_error());
return $result;
}//end function
?>
As always, you can make all sorts of improvements and changes as you see fit...
Mick Scott wrote :1925
This is EXACTALLY what I was looking for, ...had a problem with my form which was doing my head in, till I found this and saw where I was going wrong.
