Multiple parameters in Mysql WHERE clause causing issues

Question

I have a query in php that pulls news articles from a database, that, when each WHERE clause is applied independently, works fine.

Example #1

//retrieve all news articles
$sql = 'SELECT post.id, post.headline_en, post.story_en, post.post_stat, post.trans_stat, post.translate, post.published_by,
post.publish_on, system_users.u_username, system_users.u_firstname, system_users.u_lastname
FROM post INNER JOIN system_users ON post.published_by=system_users.u_userid';

// If search terms have been provided...
if($_POST['query'] != '')
{
  $sql .= ' WHERE (headline_en LIKE "%'.str_replace(' ', '%', $_POST['query']).'%" OR post.story_en LIKE "%'.str_replace(' ', '%', $_POST['query']).'%")';
}

$sql .= ' ORDER BY post.publish_on DESC';

This seemingly works fine, and limits the results based on the search value provided in $_POST['query'].

Example #1

//retrieve all news articles
$sql = 'SELECT post.id, post.headline_en, post.story_en, post.post_stat, post.trans_stat, post.translate, post.published_by,
post.publish_on, system_users.u_username, system_users.u_firstname, system_users.u_lastname
FROM post INNER JOIN system_users ON post.published_by=system_users.u_userid
WHERE (post.post_stat=3 AND post.trans_stat=3) OR (post.post_stat=3 AND post.translate=0)';

// search option removed

$sql .= ' ORDER BY post.publish_on DESC';

This also works fine, and limits the results to the criteria specified in the WHERE clause.

However, when I try to combine both, it doesn't want to play nice, and I am unsure as to why.

Example #3

//retrieve all news articles
$sql = 'SELECT post.id, post.headline_en, post.story_en, post.post_stat, post.trans_stat, post.translate, post.published_by,
post.publish_on, system_users.u_username, system_users.u_firstname, system_users.u_lastname
FROM post INNER JOIN system_users ON post.published_by=system_users.u_userid
WHERE (post.post_stat=3 AND post.trans_stat=3) OR (post.post_stat=3 AND post.translate=0)';

//If search terms have been provided...
if($_POST['query'] != '')
{
  $sql .= ' AND (headline_en LIKE "%'.str_replace(' ', '%', $_POST['query']).'%" OR post.story_en LIKE "%'.str_replace(' ', '%', $_POST['query']).'%")';
}

$sql .= ' ORDER BY post.publish_on DESC';

^^ doesn't work. I've tried various permutations of the above and for the life of me I can't see what's wrong. Any feedback as always would be appreciated.

What's the output of the last code? Does it throw an Error? If so what error does it throw? — Kuro Neko, Aug 10 '21 at 08:41
(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How to include a PHP variable inside a MySQL statement"](https://stackoverflow.com/questions/7537377/how-to-include-a-php-variable-inside-a-mysql-statement) and ["How can I prevent SQL injection in PHP?"](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — sticky bit, Aug 10 '21 at 08:54
[Edit] the question and provide a [example], i.e. the `CREATE` statements of the tables or other objects involved (paste the **text**, don't use images, don't link to external sites), `INSERT` statements for sample data (dito) and the desired result with that sample data in tabular text format. — sticky bit, Aug 10 '21 at 08:55
@stickybit - thank you. I had been using bind_param but removed it for debugging as a possible source of the issue. — user6790086, Aug 10 '21 at 09:04

score 0 · Answer 1 · answered Aug 10 '21 at 08:58

It seems that I should have looked a little closer :/

I spent so much time bracketing off sections of the WHERE clause that I narrowed its scope down to not being able to return any value because all criteria could not be met...

Working code below:

//retrieve all news articles
$sql = 'SELECT post.id, post.headline_en, post.story_en, post.post_stat, post.trans_stat, post.translate, post.published_by,
post.publish_on, system_users.u_username, system_users.u_firstname, system_users.u_lastname
FROM post INNER JOIN system_users ON post.published_by=system_users.u_userid
WHERE (post.post_stat=3 AND post.trans_stat=3 OR post.post_stat=3 AND post.translate=0)';

//If search terms have been provided...
if($_POST['query'] != '')
{
  $sql .= ' AND (headline_en LIKE "%'.str_replace(' ', '%', $_POST['query']).'%" OR post.story_en LIKE "%'.str_replace(' ', '%', $_POST['query']).'%")';
}

$sql .= ' ORDER BY post.publish_on DESC';

Multiple parameters in Mysql WHERE clause causing issues

1 Answers1