Searching for part of variable in SQL from form input

Question

I am working on an assignment right now where I am trying to build a small real estate like website that builds a search from my mySQL database based on a user input city. I have it working right now to give back correct results based on if the user inputs the city exactly right, but I want it to be able to return searches if the user was to enter just part of the city.

An example is that some of the cities in the assignment are "Tomsville" and "OceanCove" I need to be able to have the search be able to return results for "Tomsville" if someone were to just input "Tom" or even "T"...the same needs to be true for "OceanCove" where they could input "ocean" and get the results.

The variable for the user input is $findhome.

Here is the part of my code relevant to this question:

 $statement  = "SELECT *";
 $statement .= "FROM u1585_homes ";

if ($findhome!= 'ALL')
{
    $statement .= "WHERE city ='".$findhome."' ";
}
    $statement .= "ORDER BY city";

    $sqlResults = selectResults($statement);

    $error_or_rows = $sqlResults[0];

    if (substr($error_or_rows, 0 , 5) == 'ERROR')
    {
        print "<br />Error on DB";
        print $error_or_rows;
        } else {

                $arraySize = $error_or_rows;

                for ($i=1; $i <= $error_or_rows; $i++)
                {
                    $image_file = $sqlResults[$i]['image_file'];
                    $city = $sqlResults[$i]['city'];
                    $price = $sqlResults[$i]['price'];
                    $bedrooms= $sqlResults[$i]['bedrooms'];
                    $baths= $sqlResults[$i]['baths'];
                    $footage = $sqlResults[$i]['footage'];
                    $realtor_firstname = $sqlResults[$i]['realtor_firstname'];
                    $realtor_lastname = $sqlResults[$i]['realtor_lastname'];
                    $grabber = $sqlResults[$i]['grabber'];
                    $description = $sqlResults[$i]['description'];

                    print "<p><img src='images/".$image_file."'>";
                    print "<h3>".$grabber."</h3><br />";
                    print "City: ".$city."<br />";
                    print "Bedrooms: ".$bedrooms."<br />";
                    print "Baths: ".$baths."<br />";
                    print "Price: ".$price."<br />";
                    print "Footage: ".$footage."<br />";
                    print "Realtor: $realtor_firstname ".$realtor_lastname."<br />";

                    print "Description: ".$description;
                    //print ""
                }               
        }
    }

It's not clear what you're using to execute your SQL, but what you're doing is terrifying if $findhome is from user input. You must escape ANY and ALL data put directly into SQL and you seem to be forgetting to do this here, something which can have very serious consequences. Escape your SQL properly or you could end up in trouble. — tadman
– tadman, Commented Aug 21, 2012 at 6:21

Fluffeh · Accepted Answer · 2012-08-21 06:10:41Z

3

Try using % around the search terms and using like instead of = as the operator:

$statement .= "WHERE city like '%".$findhome."%' ";

edited Aug 21, 2012 at 6:10

answered Aug 21, 2012 at 6:05

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

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

4 Comments

John Woo Over a year ago

by default mysql is case-insensitive.

Fluffeh Over a year ago

@JohnWoo Thanks, edited answer to remove it as it isn't needed here.

Brad Over a year ago

THANKS!! I thought it was something like that but couldn't hit the nail on the head. Really appreciate the help

John Woo Over a year ago

@Fluffeh actually it's ok but you are adding extra word on the server. hehe

babooney · Accepted Answer · 2012-08-21 06:09:20Z

0

Use LIKE operator

$statement .= "WHERE city LIKE '%".$findhome."%' ";

answered Aug 21, 2012 at 6:09

babooney

7445 silver badges15 bronze badges

1 Comment

tadman Over a year ago

Technically right, but also not how you construct a proper SQL query. Inlining arbitrary user-provided data is not cool.

Collectives™ on Stack Overflow

Searching for part of variable in SQL from form input

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related