PHP Searching Multiple Fields

  • amiecutietoes
  • Newbie
  • Newbie
  • User avatar
  • Posts: 10
  • Loc: Los Angeles, CA

Post 3+ Months Ago

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
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

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 ...
  • amiecutietoes
  • Newbie
  • Newbie
  • User avatar
  • Posts: 10
  • Loc: Los Angeles, CA

Post 3+ Months Ago

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
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

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.

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 73 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
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.