Adding search into a sql query

[1internet]

So I have a results page, e.g. a list of all the hotels for a specific city coming from the database:

"SELECT * FROM `hotels` WHERE `city`='$city'"

I want to also include a search form. They way I have tried, and assume would be the standard way is:

if(isset($_GET['search'])) {
$search = $_GET['search'];
$search = "AND `name` LIKE '%$search%'";
} else {
$search = '';
}

So it checks if the search has been set, and if it has it then creates the msql code for it. I then adjust the sql query like so:

"SELECT * FROM `hotels` WHERE `city`='$city $search'"

 

1. The search variable is not creating the correct sql result, so I think I may have done something wrong there.

2. Is this the usual way a search is added into the sql results? Would it be better to have"

 

if(isset($_GET['search'])) {
$search = $_GET['search'];
$qry = ""SELECT * FROM `hotels` WHERE `city`='$city $search'" AND `name` LIKE '%$search%'";
} else {
$qry = "SELECT * FROM `hotels` WHERE `city`='$city $search'";
}

 

Or is there an even better way?

[Barand]

Don't put $search inside the single quotes with $city

 

"SELECT * FROM `hotels` WHERE `city`='$city' $search"

[soycharliente]

I think it's worth bringing up that you should also sanitize what you're pulling from the $_GET array before putting it into your query.

 

For me, I usually concatenate the condition on the end instead of referencing an empty string. Small distinction, same end result.

 

Here's what I do:

 

$link = db_connect();
$query = "SELECT * FROM hotels WHERE city='{$city}'";
if ( isset($_GET['search']) )
{
  $search = make_safe($link, $_GET['search']);
  $query .= " AND name LIKE '%{$search}%'"
}
if ( $result = mysqli_query($link, $query, MYSQLI_STORE_RESULT) )
{
  // Show results
} else {
  // Handle error
}
mysqli_close($link);

function make_safe($db, $string)
{
  return mysqli_real_escape_string($db, $string);
}

Edited January 22, 2013 by charlieholder

[1internet]

concatenating is a nice idea, but I also need ORDER BY too, so I won't be able to concatenate that to the query.

[1internet]

Ok, what if we just keep the query as:

"SELECT * FROM `hotels` WHERE `city`='$city $search' AND `name` LIKE '%$search%' ORDER BY `name`"

Then if $search is not set, then it will = '', and all queries will match that search. Is that a good way?

[soycharliente]

The query you have won't work. You still have one of the same erros as your original post.

 

...WHERE `city`='$city $search'...

 

I would continue to concatenate:

 

$link = db_connect();
$query = "SELECT * FROM hotels WHERE city='{$city}'";
if ( isset($_GET['search']) )
{
  $search = make_safe($link, $_GET['search']);
  $query .= " AND name LIKE '%{$search}%'"
}
$query .= " ORDER BY `name` ASC"
if ( $result = mysqli_query($link, $query, MYSQLI_STORE_RESULT) )
{
  // Show results
} else {
  // Handle error
}
mysqli_close($link);

function make_safe($db, $string)
{
  return mysqli_real_escape_string($db, $string);
}

[Barand]

Don't put $search inside the single quotes with $city

 

"SELECT * FROM `hotels` WHERE `city`='$city' $search"

 

The query you have won't work. You still have one of the same erros as your original post.

 

...WHERE `city`='$city $search'...

 

 

Exactly so. I concluded there was no point talking to those who won't listen.

[1internet]

Oh right, I can keep concatenating, that will work. Sorry, I was listening, but just trying to explain what I wanted to achieve as best as possible. I still have a lot to learn on logic, but this really helps. Thanks