Back To Programming / Scripting / Coding Forum

PHP Searching Multiple Fields

  • amiecutietoes
  • Newbie
  • Newbie
  • User avatar
  • Joined: Apr 21, 2010
  • Posts: 10
  • Loc: Los Angeles, CA
  • Status: Offline

Post April 21st, 2010, 4:16 pm

Hello Everyone!

I am working on a search form in PHP for a client's website. So everything works, the search brings results. What I am wanting to accomplish now is to be able to search more than one field, specifically first name and last name. Let's say if a user inputs let's say a first name AND a last name into the search input field and then searches, I want it to display results based on that. Right now if you try it, it says there are no results. My thought is how I've coded it is for the search box to be able to search only in ONE column field for per row.

Here is that part of my code:
Code: [ Select ]
// QUERY THE DATABASE TABLE
$sql="SELECT * FROM contracts
WHERE Status LIKE '%" . $searchTermDB . "%'
OR DealerName LIKE '%" . $searchTermDB ."%'
OR Dealer LIKE '%" . $searchTermDB ."%'
OR Contract LIKE '%" . $searchTermDB ."%'
OR LastName LIKE '%" . $searchTermDB ."%'
OR FirstName LIKE '%" . $searchTermDB ."%'
OR EffDate LIKE '%" . $searchTermDB ."%'
OR TermDate LIKE '%" . $searchTermDB ."%'
OR Year LIKE '%" . $searchTermDB ."%'
OR Make LIKE '%" . $searchTermDB ."%'
OR Model LIKE '%" . $searchTermDB ."%'
OR Serial LIKE '%" . $searchTermDB ."%'
OR TermMeter LIKE '%" . $searchTermDB ."%'
OR Plan LIKE '%" . $searchTermDB ."%'";
  1. // QUERY THE DATABASE TABLE
  2. $sql="SELECT * FROM contracts
  3. WHERE Status LIKE '%" . $searchTermDB . "%'
  4. OR DealerName LIKE '%" . $searchTermDB ."%'
  5. OR Dealer LIKE '%" . $searchTermDB ."%'
  6. OR Contract LIKE '%" . $searchTermDB ."%'
  7. OR LastName LIKE '%" . $searchTermDB ."%'
  8. OR FirstName LIKE '%" . $searchTermDB ."%'
  9. OR EffDate LIKE '%" . $searchTermDB ."%'
  10. OR TermDate LIKE '%" . $searchTermDB ."%'
  11. OR Year LIKE '%" . $searchTermDB ."%'
  12. OR Make LIKE '%" . $searchTermDB ."%'
  13. OR Model LIKE '%" . $searchTermDB ."%'
  14. OR Serial LIKE '%" . $searchTermDB ."%'
  15. OR TermMeter LIKE '%" . $searchTermDB ."%'
  16. OR Plan LIKE '%" . $searchTermDB ."%'";


Let me know of any suggestions. Thanks!

- Amie
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post April 21st, 2010, 4:16 pm

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post April 22nd, 2010, 7:14 am

PHP Code: [ Select ]
$sql = "SELECT * FROM contacts WHERE 1 ";
$sql .= ($first_name != "") ? "AND FirstName LIKE '%{$first_name}%'" : "";
$sql .= ($last_name != "") ? "AND LastName LIKE '%{$last_name}%'" : "";
  1. $sql = "SELECT * FROM contacts WHERE 1 ";
  2. $sql .= ($first_name != "") ? "AND FirstName LIKE '%{$first_name}%'" : "";
  3. $sql .= ($last_name != "") ? "AND LastName LIKE '%{$last_name}%'" : "";

etc ...
Let's leave all our *plum* where it is and go live in the jungle ...
  • amiecutietoes
  • Newbie
  • Newbie
  • User avatar
  • Joined: Apr 21, 2010
  • Posts: 10
  • Loc: Los Angeles, CA
  • Status: Offline

Post April 22nd, 2010, 11:10 am

I only want one input search box. I want it to search select fields in my table and lets say I do a search for 2 words in the input search box, I want to it to find the results based on that.

Example:

I have 50 Johns on the table, each with different last names. Other fields for each John includes contract number, car make, car type, and a lot of other fields. In order to narrow this down, I want to be able to type in "John Smith" and make a result that shows only him. Now if I just typed in "Smith" I would get a bunch of people with the last name Smith. So you see I need that one text box to be able to apply to all search fields.
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post April 22nd, 2010, 11:41 pm

that's quite a tough one ... I think what you gotta do then is firstly, explode the string, seperated by spaces, and then do the query ... something like the following ...

PHP Code: [ Select ]
<?php
  $search_words = explode(" ",$searchTermDB);
  $sql_select = "SELECT * FROM contacts WHERE 1 ";
  foreach($search_words as $word){
    $sql_select .= "AND(";
    $sql_select .= "Status LIKE '%{$word}%'
                   OR DealerName LIKE '%{$word}%'
                   OR Dealer LIKE '%{$word}%'
                   OR Contract LIKE '%{$word}%'
                   OR LastName LIKE '%{$word}%'
                   OR FirstName LIKE '%{$word}%'
                   OR EffDate LIKE '%{$word}%'
                   OR TermDate LIKE '%{$word}%'
                   OR Year LIKE '%{$word}%'
                   OR Make LIKE '%{$word}%'
                   OR Model LIKE '%{$word}%'
                   OR Serial LIKE '%{$word}%'
                   OR TermMeter LIKE '%{$word}%'
                   OR Plan LIKE '%{$word}%'";
    $sql_select .= ") ";
  }
?>
  1. <?php
  2.   $search_words = explode(" ",$searchTermDB);
  3.   $sql_select = "SELECT * FROM contacts WHERE 1 ";
  4.   foreach($search_words as $word){
  5.     $sql_select .= "AND(";
  6.     $sql_select .= "Status LIKE '%{$word}%'
  7.                    OR DealerName LIKE '%{$word}%'
  8.                    OR Dealer LIKE '%{$word}%'
  9.                    OR Contract LIKE '%{$word}%'
  10.                    OR LastName LIKE '%{$word}%'
  11.                    OR FirstName LIKE '%{$word}%'
  12.                    OR EffDate LIKE '%{$word}%'
  13.                    OR TermDate LIKE '%{$word}%'
  14.                    OR Year LIKE '%{$word}%'
  15.                    OR Make LIKE '%{$word}%'
  16.                    OR Model LIKE '%{$word}%'
  17.                    OR Serial LIKE '%{$word}%'
  18.                    OR TermMeter LIKE '%{$word}%'
  19.                    OR Plan LIKE '%{$word}%'";
  20.     $sql_select .= ") ";
  21.   }
  22. ?>

That should do the trick.
Let's leave all our *plum* where it is and go live in the jungle ...

Page 1 of 1

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 145 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.