Search multiple columns in MySQL using PHP

Question

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

What exactly is the problem? Your query looks okay. Can I confirm that you are indeed interpolating the variable correctly and are not searching the tables for "txtSearchField"? — Rawkode
– Rawkode, Commented May 15, 2012 at 9:42
if you're not using join query, no need to put the table name before the property, you could just use propertyId, propertyname, ... — jbduzan
– jbduzan, Commented May 15, 2012 at 9:52

Adam Lukaszczyk · Accepted Answer · 2012-05-15 09:43:07Z

4

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.

answered May 15, 2012 at 9:43

Adam Lukaszczyk

4,8863 gold badges24 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bud Damyanov · Accepted Answer · 2012-05-15 10:11:45Z

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

Collectives™ on Stack Overflow

Search multiple columns in MySQL using PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related