Search multiple columns in MySQL using PHP

mysql, php, search

Solution

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/>';
}
    ?>

Problem

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%' ```

Original source