Search Forms

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
charlie12345
Forum Newbie
Posts: 14
Joined: Wed Nov 09, 2005 5:35 am

Search Forms

Post 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
foobar
Forum Regular
Posts: 613
Joined: Wed Sep 28, 2005 10:08 am

Post 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; }
}
charlie12345
Forum Newbie
Posts: 14
Joined: Wed Nov 09, 2005 5:35 am

Post by charlie12345 »

feyd | Please use

Code: Select all

and

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

and

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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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]:oops:
Last edited by feyd on Wed Nov 09, 2005 8:36 am, edited 1 time in total.
User avatar
shiznatix
DevNet Master
Posts: 2745
Joined: Tue Dec 28, 2004 5:57 pm
Location: Tallinn, Estonia
Contact:

Post 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.
charlie12345
Forum Newbie
Posts: 14
Joined: Wed Nov 09, 2005 5:35 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

don't worry about it too much.. :)
charlie12345
Forum Newbie
Posts: 14
Joined: Wed Nov 09, 2005 5:35 am

Post 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
Post Reply