Page 1 of 1
Search Forms
Posted: Wed Nov 09, 2005 5:57 am
by charlie12345
I have a search form with 6 fields, one or more of which can be completed.
Is there any easier/better way to determine the search criteria (and thus chose/build the SQL statement) than using a case statement, which would require 5-factoral cases? (5-factoral, because 1 field is required).
Sometimes I have a hard time thinking outside the box.
Thanks for any ideas.
Charlie
Posted: Wed Nov 09, 2005 6:25 am
by foobar
Do an if to check if any of the fields is empty.
Eg.:
Code: Select all
<form ... >
<input ... name="fields[]" />
<input ... name="fields[]" />
<input ... name="fields[]" />
...
</form>
Code: Select all
$allempty = true;
$fields = $_POST['fields'];
foreach ($fields as $field) {
$field = trim($field);
if (!empty($field)) { $allempty = false; break; }
}
Posted: Wed Nov 09, 2005 7:11 am
by charlie12345
feyd | Please use Code: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
I guess I don't understand how to build a 'dynamic' SQL statement.
Case statements would check if a field was empty, for example:
Code: Select all
case $_POST[1] <> empty, $_POST[2] == empty, $_POST[3 == empty:
$sql="SELECT * FROM table WHERE field_1=$_POST[1]
case $_POST[1] <> empty, $_POST[2] <> empty, $_POST[3] == empty:
$sql="SELECT * FROM table WHERE ( field_1=$_POST[1] AND field_2 =$_POST[2] )
case $_POST[1] <> empty, $_POST[2] <> empty, $_POST[3] <> empty:
$sql="SELECT * FROM table WHERE ( field_1=$_POST[1] AND field_3 =$_POST[3] )
That still makes n-factoral case statements
feyd | Please use Code: Select all
tags where appropriate when posting code. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]
Posted: Wed Nov 09, 2005 8:29 am
by feyd
it seems that something like the following would work
Code: Select all
<input type="text" name="field[0]" />
<input type="text" name="field[1]" />
<input type="text" name="field[2]" />
<input type="text" name="field[3]" />
Code: Select all
$fieldmap = array('field1', 'someField','field3','jump','foo');
// compose the where clause parts
$entries = array();
$where = '';
if(isset($_POST['field']) and is_array($_POST['field'])) {
foreach($_POST['field'] as $key => $value) {
if(isset($fieldmap[$key])) {
$entries[] = '`' . $fieldmap[$key] . '` LIKE \'%' . sanitize($value) . '%\'';
}
}
$where = implode(' AND ',$entries);
}
[edit]

Posted: Wed Nov 09, 2005 8:34 am
by shiznatix
maybe try somthing like
Code: Select all
<form action...>
<input type="text" name="input[1]">
<input type="text" name="input[2]">
<input type="text" name="input[3]">
</form>
Code: Select all
<?
$query_base = '
SELECT
*
FROM
table_name
WHERE
';
$query_1 = 'field_name_1 = ';
$query_2 = 'field_name_2 = ';
$query_3 = 'field_name_3 = ';
$add = '';
if (!empty($_POST['input']))
{
foreach ($_POST['input'] as $key => $val)
{
switch ($key)
{
case "1":
$add .= $query_1.$val.' AND';
break;
case "2":
$add .= $query_2.$val.' AND';
break;
case "3":
$add .= $query_3.$val.' AND';
break;
}
}
}
//remove the last AND here with str somting i can't remember right now, sorry
$query_base .= $add;
$do_query = mysql_query($query_base) or die(mysql_error().__LINE__.__FILE__);
?>
disclaimer: UNTESTED
edit: BAH feyd you and your fast fingers of fury.
Posted: Wed Nov 09, 2005 9:22 am
by charlie12345
Thanks, that looks like it will work well.
And, btw, I thought I was typing 'pseudo-code' text, not php or 'code'. Didn't mean to not conform to the code posting guidelines.
Charlie
Posted: Wed Nov 09, 2005 9:42 am
by feyd
don't worry about it too much..

Posted: Sat Nov 12, 2005 8:05 am
by charlie12345
Thought you might like to see the end-result of this...
Turns out there were 4 tables.
I had a search form that had 7 fields, 3 of which were required, but the rest were optional, so I built the query string based on which fields were completed, and which of those completed fields came from different tables.....
Here is the code I ended up with:
Code: Select all
$query_base = ' SELECT a.Id, a.First_Name, a.Last_name, a.Company_Name, a.City, a.State, a.Short_desc, a.Photo_link, d.Name ';
$add_specialty_query_base =", sp.specialty_name ";
$add_service_query_base =", se.service_name ";
$from_base= 'FROM appraisers a, discipline_list d ';
$add_service_from_base=", services_list se ";
$add_specialty_from_base=", specialty_list sp ";
$where_base= "WHERE (a.Discipline = d.Id AND ";
$add_specialty_where_base = "a.Discipline = sp.discipline_id ";
$add_service_where_base =" a.Discipline = se.discipline_id ";
$query_1 = "d.Name = '";
$query_2 = "a.City = '";
$query_3 = "a.State = '";
$query_4 = "a.Country = '";
$query_5 = "a.Discipline = sp.discipline_id AND sp.specialty_name = '";
$query_6 = "a.Discipline = se.discipline_id AND se.service_name = '";
$query_7 = "a.Last_name = '";
$add_query_base = '';
$add_from_base = '';
$add_where_base = '';
foreach ($post as $key => $val)
{
// echo "key: ".$key. " val: ".$val."<BR>";
switch ($key)
{
case "discipline_select":
$add_where_base .= $query_1.$val."' AND ";
break;
case "city_textfield":
if ( $post[city_textfield] <> "" ){ $add_where_base .= $query_2.$val."' AND "; };
break;
case "select_state":
$add_where_base .= $query_3.$val."' AND ";
break;
case "country_select":
$add_where_base .= $query_4.$val."' AND ";
break;
case "specialities_select":
if ( $post[specialities_select] <> "" ){
$add_from_base .= $add_specialty_from_base;
$add_where_base .= $query_5.$val."' AND ";
};
break;
case "services_select":
if ( $post[services_select] <> "" ){
$add_from_base .= $add_service_from_base;
$add_where_base .= $query_6.$val."' AND ";
};
break;
case "lastname_textfield":
if ( $post[lastname_textfield] <> "" ){$add_where_base .= $query_7.$val."' AND "; };
break;
}
}
$query_base .= $add_query;
$from_base .=$add_from_base;
$where_base .=$add_where_base;
$query_base .= $from_base;
$query_base .= $where_base;
$query_base=substr($query_base,0,strlen($query_base)-4);
$query_base .=")";
Regards,
Charlie