CODEWITHSUNDEEP
phpmysqlsearchsearchbar
Dan
Dan

Reputation: 997

PHP/MySQL Search for Two Fields in one Table

I am building my first PHP/MySQL site and i would like to return search results from two fields in my one table within the database - Catagory, and Location (There are 15 fields in total in the table, but there are only two required for the search).

Each option will have a drop down menu which will list a fixed number of choices in each field - for instance, there will probably be 15 or so Locations, and 7 or 8 Catagories.

I want the user to be able to select an item from either one or both drop down menus, click a 'Search' or @Go' button, and be presented with the results.

As mentioned, i am brand new to PHP/MySQL so apologies if my question is a little 'simple' and i hope someone can point me in the right direction.

Thanks Dan

EDIT:

Brad, Conner, I don't have anything thus far i'm afriad in terms of the search query specifics, i'm just starting to research how to do it hence complete lack of code in my question!

But to try and be more specific, the only fields i want to return results from are Location and Job Catagory - these are two fields of about 15 in my table that are already populated with fixed terms - e.g, Location may have London, Manchester, Leeds, and Catagory may have Administrative, Management, Clerical etc - there is no option for the visitor to input a new term, only select from a drop down menu of pre-arranged items.

The other fields in my table aren't relevant to the search (salary, company, contact email etc), so...I am thinking i only need two queries, and results can also be displayed if you only send one (e.g, you aren't bothered about job location, just speciality, you could still find all the results and decide if you fancied relocating). Is that any better or more helpful?

Thanks for you help so far. Dan

Upvotes: 1

Views: 3877

Answers (3)

Syed Hussim
Syed Hussim

Reputation: 760

Dan you need to build your sql statement dynamically. Below is a simple peice of code to demonstrate what I mean. Always besure to escapse user data to provent sql inject.

if ( isset($_POST['go']) ){
      $location = $_POST['location'];
      $category = $_POST['category'];
      $sql = "SELECT * FROM table ID IS NOT NULL ";

      if ( $location !="" ){
           $sql .= " AND location = " . $location;
      }

      if ( $category !="" ){
           $sql .= " AND category = " . $category;
      }

}

Upvotes: 1

Marc B
Marc B

Reputation: 360592

Without any details on your table structure and the data in question, the basics would be somethign like:

SELECT ...
FROM ...
WHERE (field1 = $location_option) or (field2 = $category_option)

Upvotes: 0

Connor Smith
Connor Smith

Reputation: 1314

A user enters a search query and you'd like to select the rows from your table that contain or exactly match that query? If so, something like

For if category contains $search_query

SELECT category, location 
FROM table 
WHERE concat(' ', category, ' ') LIKE '%$search_query%'

Or if it is an exact match you would use this for your conditional instead:

WHERE category = '$search_query'

I'm probably offtrack though, you need to specify like Brad said

Upvotes: 1

Related Questions