Multiple (optional) search criteria.

Mr_Angry

Hello once again everyone. I've a question which, whilst proving impossible for me to figure out, I'm hopeful someone can help me with.

I'm in the process of setting up a search facility on a site which draws from a MySQL database. The nature of the data is such that I'll need to offer users the ability to search by multiple variables. All of the tutorials / snippets I've come across seem to facilitate a single criteria search (such as search by name etc).

I'd like to offer a search whereby users can search by occupation, geographic location and one other variable. I had hoped it would just be a matter of my just including three "drop down" menus in the search form but that seems to be a "no no".

As ever - any assistance would be very much appreciated.

sneakyimp

You could show us what you've tried and we could start there. Just asking for a solution is a bit lazy, don't you think?

Here's a query which can search multiple fields:

SELECT * FROM table WHERE occupation='Lawyer' AND zip_code='90026' AND some_other_var='yo momma';

Seems to me it would be pretty easy to write some php to alter that query to put in whatever you need.

Mr_Angry

Sneakyimp,

Many thanks for taking the time to reply - much appreciated.

Sorry for the lack of an example but I'm, quite literally, trying to build this using php / MySQL from scratch.

I have worked previously on such a database built using .asp and MS Access but I acknowledge the shortcomings of same and want to build a robust version. Even the single line of code you produced above has given me some insight as to the potential functionality of same.

Am I correct in assuming that I could use individual forms (search boxes) together on a page - each with their own variables / tables to search? If so what I'm hoping to achieve is a three or four search box form which will search by location, occupation, company name; Here is an example of the end results which I hope to come up with:

This search query :

$result = mysql_query("SELECT Name, Company, Email, Address1, Address2, Address3, Address4, Address5, Genre, Occupation FROM contacts LIMIT 0, 5 $max")
or die(mysql_error());

Gives me this output:

{
echo "<ul class='db_output'>\n";
echo "<li>Name: {$row['Name']}</li>\n";
echo "<li>Occupation: {$row['Occupation']}</li>\n";
echo "<li>Company: {$row['Company']}</li>\n";
echo "<li>Address: {$row['Address1']}</li>\n";
echo "<li> {$row['Address2']}</li>\n";
echo "<li> {$row['Address3']}</li>\n";
echo "<li> {$row['Address4']}</li>\n";
echo "<li>Clients: {$row['Clients']}</li>\n";
echo "<li>Genre: {$row['Genre']}</li>\n";
echo "<li>Email: {$row['Email']}</li>\n";
echo "</ul>\n";
echo "</ul>\n";
}

This, along with the other code in the page, actually works and gives me the exact output I require - A list of the first five alphabetic entries in my database. However, it appears that I have managed to code a "Search result" but I have no idea whatsoever what I need to put into the search forms to achieve the desired output.

If that still makes no sense I'm sorry - as I say, I'm building (backwards, it would seem) from scratch.......Ooops!

Paul

sneakyimp

So step one would be to build your form. Maybe try that first?

Mr_Angry

sneakyimp;10927610 wrote:
So step one would be to build your form. Maybe try that first?

Yes, that's what I'm hoping to do.

Am I correct in assuming that I could use individual forms (search boxes) together on a page - each with their own variables / tables to search?

sneakyimp

So far your code only references one table (contacts). Why do you keep talking about 'tables' plural?

You need to think about what you want the form to look like. Yes, forms can have multiple inputs. No you don't need more than one form. Yes you can extract search criteria from a variety of different form inputs (checkboxes, select inputs, radio buttons, text inputs, buttons, etc.). Not really sure what you are confused about.

Mr_Angry

Sneakyimp,

Thanks for engaging!!

You're quite correct - I've only one table "contacts" but If it makes for better sense / use I could split it as needs be.

The search form would consist of two text input boxes (name, company) three drop down select inputs (occupation, Genre, location) and search and reset buttons (one of each).

A search might consist of only one input on the part of the user such as "name" or by a combination of drop down selections such as Managers, all, New Zealand.

Does that make it any clearer at all?

Given the code you posted earlier am I right in thinking that search variables can be defined for each of the above individually (I hope that's right). What I'm really unsure of is how I "tie" it all together as one search facility to produce the listed type of output (result) I posted earlier.

Sorry for the ongoing lack of clarity on my part.

Paul

Here's the current type of search form:

sneakyimp

I don't think there's any need for more than one table.

What you'll need to do is check each of the form inputs to see if anything has been selected. If something has been selected or entered, you'll need to add some text to your query. Something like:

$search_criteria = array();

// put one of these for each input
if (strlen($_POST['occupation']) > 0) {
  $search_critieria[] = "(occupation='" . mysql_real_escape_string($_POST['occupation']) . "')";
}

$sql = "SELECT * FROM contacts";
if (count($search_criteria) > 0) {
  $sql .= " WHERE " . implode(' AND ', $search_criteria);
}

Mr_Angry

Hey Sneakyimp,

Fantastic!!

Thanks so much for the guidance. I'll give that a try tomorrow - it's a bit late here at the moment.

Very much appreciated.

Paul

Mr_Angry

Sneakyimp,

Am I getting close or am I waaaay off the mark here?

<form method="post" action="search.aspx?go" id="searchform">
   <p>Name: 
     <input type="text" name="name">
  <?aspx

if(isset($_POST['submit'])){
if(isset($_GET['go'])){
if(preg_match("/[A-Z | a-z]+/", $_POST['name'])){
$name=$_POST['name'];

//connect to the database
$db=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error()); 

//-select the database to use
$mydb=mysql_select_db("database");

//-query the database table
$sql="SELECT Name FROM Contacts WHERE Name LIKE '%" . $name . "%'";

$search_criteria = array();

// put one of these for each input
if (strlen($_POST['name']) > 0) { 
$search_critieria[] = "(name='" . mysql_real_escape_string($_POST['name']) . "')";
}

$sql = "SELECT name FROM contacts";
if (count($search_criteria) > 0) {
  $sql .= " WHERE " . implode(' AND ', $search_criteria);
}

sneakyimp

Looks to me like you need to do some php tutorials or something. You have two different bits of code defining your sql statement. You are also not indenting your code so that it's readable. I also see you opening some IF clauses that never seem to get closed.

Mr_Angry

Hi sneakyimp.

I'm still new to this and, at the moment, quite obviously out of my depth.

It's a challenge I'm prepared to face so I'll struggle on to try and get a better grasp of it.

I'll call back when I'm a bit better prepared.

Thanks for the pointers.

Paul

sneakyimp

As a beginner, I find it helpful to write a few lines and test, write a few lines and test. Don't expect this to jump fully formed out of your head into the script.