Multi point searches

cornick

Not sure if that's the correct terminology for what I'm looking for.

This is my first stab at doing a multi point search in the tables in my database and I'm not totally sure how to do it. Honestly, I'm not sure where to even begin.

If someone would like to search by first name, last name, and phone number. I would like it to display any records that meet the criteria for all 3.

But if someone wants to search for just last name and phone number then I would like it to display the records for just those two pieces of criteria.

I'm not sure what it's called, so that makes it hard to search the internet for some tutorial information.

I've read through the first 200 posts that came up on my search here for this point and didn't see anything similar.

Any recommendations would be great on code and/or a tutorial that will explain it to me and/or the correct terminology for what I'm trying to do. (as I've said, hard to search something that you want to do when you don't know the name of it.)

Thanks!

bradgrafelman

You're basically just adding conditions onto the WHERE statement using OR logic:

SELECT `firstname`, `lastname`, `phone`
FROM `myTable`
WHERE `firstname` = 'Brad'
    OR `lastname` = 'Grafelman';

SELECT `firstname`, `lastname`, `phone`
FROM `MyTable`
WHERE `firstname` = 'Brad'
    OR `phone` = '555-555-5555';

cornick

You again! Did you get your raise yet? 😉

So that is for OR.

Now if I wanted to make all 3 pieces match the search for the first name, last name, and phone would be this: (Don't worry about embarrassing me if this is WAY OFF.)

SELECT `firstname`, `lastname`, `phone`
FROM `myTable`
WHERE `firstname` = 'Brad'
    AND `lastname` = 'Grafelman';
    AND `phone` = '555-555-5555';

Even if it returns back with nothing because this person doesn't exist.

bradgrafelman

cornick wrote:
You again! Did you get your raise yet? 😉

Hah! Hardly.

Anyway, yes, switching to AND would mean that a row must meet all of the given criteria in order to be selected.

The best way to see this stuff in action is to get a GUI such as phpMyAdmin and start playing around with different queries - run a SELECT query and see what is returned.

cornick

bradgrafelman wrote:
The best way to see this stuff in action is to get a GUI such as phpMyAdmin and start playing around with different queries - run a SELECT query and see what is returned.

Good call! I went and played around with that a bit, now it's time to work on the coding.

I'm going to keep this open until I get it finished, if that's okay.

Is there a specific term that describes what I'm trying to do? So for future reference I can be more clear?

Thanks Brad. 🙂

Jeff

bradgrafelman

cornick wrote:
Is there a specific term that describes what I'm trying to do?

Not really.. I mean you're just doing a simple SELECT statement and adding a WHERE clause. Using "AND"/"OR" logic isn't native to MySQL - it's the same principle in the entire world of mathematics.

leatherback

Hi,

Easiest to deal with this is:


// Let's make an array of valid column names & posted variable names
// posted name => column name
$valid = array(
'phone'=>'phonecol',
'firstname'=> 'firstnamecol',
'lastname'=> 'lastnamecol');

// Retrieve the variables. 
// You should add some sanitation on the code, to prevent user-injection though

foreach($_POST as $key => $value)
  {
  if(array_key_exists($key, $valid))
    {
    $querysets[] = "$valid[$key] = '$value'";
     }
  }

//all pieces of the query are now set in an array. 
// Create the query

$query = "SELECT `firstname`, `lastname`, `phone`
FROM `myTable`";

// change to or if you want an OR kind-a query
$subset = implode(" and ", $querysets);

if($subset ,. "")
  {
  $query .= " where ".$subset;
  }

// debugging only:
echo $query;

edit: The nicce thing about this that it makes your code really expandable: It allowsd you to add as many colums to the search as you want, without having to really go in your code: You just add them to the array & your search form