Trying to build search

zzz

I need to build a simple search with a few test boxes where a user can enter different parameters to search my database and if results are found -- display them.

No, I have many questions. Say i have 3 fields that a user can enter seach parameters:

First Name
Last Name
Email

What if they only enter either one and skip the rest?

I am not really sure where to begin. Can anyone point me into the right direction?

phpsharma

You are kidding PHP builders

LordShryku

You just build your search dynamically. Use [man]empty[/man] to check the fields. Also need to define if filling in multiple parameters counts for OR or AND. Here's an example:

# Assuming AND for multiples
# Say there's three form elements: fn, ln, and em

$sql  = "SELECT * FROM table WHERE 1 = 1";
$sql .= empty($_POST['fn']) ? NULL : " AND first_name LIKE '%".$_POST['fn']."%'";
$sql .= empty($_POST['ln']) ? NULL : " AND last_name LIKE '%".$_POST['ln']."%'";
$sql .= empty($_POST['em']) ? NULL : " AND email LIKE '%".$_POST['em']."%'";

That's a fairly basic example, but if they input "Someone" for first name and "blah" for email, your sql should look like this:

SELECT *
  FROM table
 WHERE 1 = 1
   AND first_name LIKE '%Someone%'
   AND email LIKE '%blah%'

You should get more specific with it though. Use case insensitive searches with strtolower or strtoupper and lower or upper sql. Do whatever other validation you need on the fields to assure they're not just serching for one letter or so forth. Maybe a validation up front to make sure they enter some search term. All that jazz.

zzz

Great! This is exactl what I was looking for. I can get it to work from here.

phpsharma I was not kidding.

zzz

Ok, syntaxed myself into woodworks. Could someone help to untangle this:

if ( $searchpmem  = $db->get_results("SELECT * FROM mytable WHERE 1 = 1") )
					 $searchpmem .= empty($_POST['fname']) ? NULL : " AND fname LIKE '%".$_POST['fname']."%'";
					 $searchpmem .= empty($_POST['lname']) ? NULL : " AND lname LIKE '%".$_POST['lname']."%'";
					 $searchpmem .= empty($_POST['email']) ? NULL : " AND email LIKE '%".$_POST['email']."%'";
{
  foreach ( $searchpmem as $spmem )
  {
   echo "$mysesults";
  }
}		         

else 
{
echo "No users found.";
}

LordShryku

No clue where you're getting your variables from there, but you need to build the sql query before executing it.

$sql  = "SELECT * FROM mytable WHERE 1 = 1";
$sql .= empty($_POST['fname']) ? NULL : " AND fname LIKE '%".$_POST['fname']."%'";
$sql .= empty($_POST['lname']) ? NULL : " AND lname LIKE '%".$_POST['lname']."%'";
$sql .= empty($_POST['email']) ? NULL : " AND email LIKE '%".$_POST['email']."%'";
if($searchpmem  = $db->get_results($sql)) {
..............................

zzz

I got that. I'm using a classs to access my db. Here's the statis code I use with search param from one field, and it works:

if ( $searchpmem = $db->get_results("SELECT * FROM mytable WHERE fname = '$fname'") )
{
foreach ( $searchpmem as $spmem )
    {
    // draw data if there is any in mytable
    }
}
else{
    echo "No users found.";
}

I'm having trouble figuring out correct syntax while accomodating dynamic code with what I've got at the moment.

blt2589

Ok, so I have to resurrect this thread for a question...
But first: Thank you for posting the code!!!!! It made my life worth living (for now)...

Anyway, I used your code LordShryku with minor mods for my own evil purposes and I am having a problem with using ORDER BY

When I use ORDER BY, the search criteria sent from the search form no longer works and my results are just the whole table.

I'm guessing that it has something to do with WHERE 1=1, but what do I know? I'm just a noob trying to write some code for an Herbarium.

$query_species = 'SELECT * FROM plants WHERE 1 = 1 ORDER BY full_sci_name';
$query_species .= empty($_GET['family']) ? NULL : " AND family LIKE '".$_GET['family']."'";
$query_species .= empty($_GET['genus']) ? NULL : " AND genus LIKE '".$_GET['genus']."%'";
$query_species .= empty($_GET['species']) ? NULL : " AND species LIKE '".$_GET['species']."%'";
$query_species .= empty($_GET['lname']) ? NULL : " AND coll_lname LIKE '".$_GET['lname']."%'";
$query_species .= empty($_GET['county']) ? NULL : " AND county LIKE '".$_GET['county']."%'";
$query_species .= empty($_GET['locality']) ? NULL : " AND locality LIKE '%".$_GET['locality']."%'";

$query_limit_species = sprintf("%s LIMIT %d, %d", $query_species, $startRow_species, $maxRows_species);


$species = mysql_query($query_limit_species, $user_id) or die(mysql_error('Cannot connect to database.'));

Thanks in advance!

LordShryku

No, the order by needs to come last, so just tack it on the end.

$query_species  = 'SELECT * FROM plants WHERE 1 = 1 ';
$query_species .= empty($_GET['family'])   ? NULL : " AND family LIKE '".$_GET['family']."' ";
$query_species .= empty($_GET['genus'])    ? NULL : " AND genus LIKE '".$_GET['genus']."%' ";
$query_species .= empty($_GET['species'])  ? NULL : " AND species LIKE '".$_GET['species']."%' ";
$query_species .= empty($_GET['lname'])    ? NULL : " AND coll_lname LIKE '".$_GET['lname']."%' ";
$query_species .= empty($_GET['county'])   ? NULL : " AND county LIKE '".$_GET['county']."%' ";
$query_species .= empty($_GET['locality']) ? NULL : " AND locality LIKE '%".$_GET['locality']."%' ";
$query_species .= 'ORDER BY full_sci_name';

blt2589

Ok, so it works perfectly with the ORDER BY where you have it except when all the search fields are empty. Then I get the error: Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /path on line 37

Just when I thought I was done with this part! And thanks for the quick response. You've already helped me a lot!

LordShryku

Let me see you're code, connection through fetch...

blt2589

I figured out my problem (well, one of my problems)...thanks for the help!