CODEWITHSUNDEEP
phpmysqlsearch
Tripping
Tripping

Reputation: 919

Search multiple columns in MySQL using PHP

I've searched the Internet numerous different ways, but cannot find the answer to my question. I am teaching myself website development and am trying to create an online real estate website.

My problem arises when I attempt to search for a property. For example, I want to search for all properties in particular area or postcode. When I attempt to search multiple fields such as address line 1, address line 2, postcode, and area (like London SW1V).

Below is the query I'm currently using, and hope to change it to work the way I need:

SELECT property.propertyid, property.propertyname
FROM   property
WHERE  property.address_1 LIKE '%txtSearchField%'
    OR property.address_2 LIKE '%txtSearchField%'
    OR property.postcode  LIKE '%txtSearchField%'
    OR property.area      LIKE '%txtSearchField%'

Upvotes: 3

Views: 8084

Answers (2)

Bud Damyanov
Bud Damyanov

Reputation: 31829

In this SQL query construct you execute it no matter what is the value of txtSearchField (even if it is empty). You also forgot to put the dollar sign $ on front of this variable txtSearchField, that's why you cannot get any results from your input form, because you always search for text txtSearchField, not the content of the variable $txtSearchField. (I guess you use an input form with HTML text input which is called txtSearchField). Remember to set your HTML form's method to "post", because if you omit it, the default is "get".

If I`m right, you should rework your code in this way:

    <?php
    //Sanitize user input
    $txtSearchField = filter_var($_POST['txtSearchField'], FILTER_SANITIZE_STRING);
    //SQL query
    $query = "SELECT property.propertyid, property.propertyname
    FROM   property
    WHERE  CONCAT(property.address_1, ' ',
                  property.address_2, ' ',
                  property.postcode, ' ',
                  property.area) LIKE '%$txtSearchField%'" //see the $ sign here
//Finally, execute query and get result
$result = mysql_query ($query) or die ('SQL error occured: '.mysql_error());
while ($array = mysql_fetch_assoc($result)) {
  echo $result['address_1'].'/'.$result['address_2'].'/'.$result['postcode'].'/'.$result['area'].'<br/>';
}
    ?>

Upvotes: 3

Adam Lukaszczyk
Adam Lukaszczyk

Reputation: 4926

Its not efficient way, but easiest and good for low traffic pages:

SELECT property.propertyid, property.propertyname
FROM   property
WHERE  CONCAT(property.address_1, ' ',
              property.address_2, ' ',
              property.postcode, ' ',
              property.area) LIKE '%txtSearchField%'

This should work for you.

Upvotes: 4

Related Questions