Add additional filter in PHP SQL search funtion-CodePudding

The following is the PHP code I am using for a simple search feature in my website. The search simply shows refults if it matches the SQL column "tags". I would like to add one more filter in the SQL query. I want to filter the search results based on city. The city data is already in the SQL, but I dont know how to add it here without breaking the properly working search funtion.

I tried $data_sql .= " AND city='newyork' "; after the 8th line, but it didnt work.

$name=str_replace(' ', '%', $_POST['query']);
$newsearch = "%$name%";
$base_sql = "SELECT %s FROM posts WHERE tags LIKE ?";
$count_sql = sprintf($base_sql, "count(*)");
$stmt = $connect->prepare($count_sql);
$stmt->execute([$newsearch]);
$total_data = $stmt->fetchColumn();
$data_sql = $count_sql = sprintf($base_sql, "*")." LIMIT ?,?";
$stmt = $connect->prepare($data_sql);
$stmt->execute([$newsearch, $start, $limit]);
$result = $stmt->fetchAll();

CodePudding user response：

So your additional filter must be before LIMIT ?, ? if you try adding it after the 8th line the query will look like this:

SELECT * FROM posts WHERE tags LIKE 'search' LIMIT 0, 100 AND city='newyork'

so what can you do:

$data_sql  = sprintf($base_sql, "*");//we will add the limit before preparation 
//don't know why do you need that $count_sql here

$data_sql .=  " AND city='newyork' ";
//IF you need some GROUP BY do it here
//If you need some ORDER BY do it here

$data_sql .= " LIMIT ?, ?";
$stmt = $connect->prepare($data_sql);
$stmt->execute([$newsearch, $start, $limit]);
$result = $stmt->fetchAll();

CodePudding user response：

The line $data_sql .= " AND city='newyork' "; won't work as it will add the string after the LIMIT which is not a valid sql query.

You should instead edit the line with the base_sql like this:

$base_sql = "SELECT %s FROM posts WHERE tags LIKE ? AND city='newyork'";

And of course if 'newyork' needs to be a variable you can do thr same thing like you did for the tags

CodePudding user response：

First, let's add the new criteria:

$base_sql = "SELECT %s FROM posts WHERE tags LIKE ? and city = ?";

Then make sure that you pass the city as a parameter

$stmt->execute([$newsearch, 'newyork', $start, $limit]);