MySQL - How do I insert an additional where clause into this full-text search (updated)

Question

I want to add a WHERE clause to a full text search query (to limit to past 24 hours), but wherever I insert it I get Low Level Error. Is it possible to add the clause and if so, how? Here is the code WITHOUT the where clause:

        $query = "SELECT *, MATCH (story_title) AGAINST ('$query' IN BOOLEAN MODE) 
AS Relevance FROM stories WHERE MATCH (story_title) AGAINST ('+$query' IN BOOLEAN MODE)
 HAVING Relevance > 0.2 ORDER BY Relevance DESC, story_time DESC;

Answer 1

Looks like you have two WHERE clauses in there:

SELECT * 
WHERE story_time > time()-86400 
AND MATCH (story_title) AGAINST ('".validate_input($_GET['q'])."' IN BOOLEAN MODE) 
AS Relevance 
FROM ".$config['db']['pre']."stories 
WHERE MATCH (story_title) AGAINST ('+".validate_input($_GET['q'])."' IN BOOLEAN MODE) 
HAVING Relevance > 0.2 
ORDER BY Relevance DESC, story_time DESC LIMIT 

Should be something like this:

SELECT *, MATCH (story_title) AGAINST ('".validate_input($_GET['q'])."' IN BOOLEAN MODE) 
AS Relevance 
FROM ".$config['db']['pre']."stories 
WHERE MATCH (story_title) AGAINST ('+".validate_input($_GET['q'])."' IN BOOLEAN MODE) 
HAVING Relevance > 0.2 
AND story_type > time() - 86400
ORDER BY Relevance DESC, story_time DESC LIMIT 

Answer 2

First, some tips about asking questions:

• Use line breaks. PHP supports string literals with line breaks, unlike some languages like Java. Formatting your SQL makes it easier to read, debug, and maintain.

• Show the SQL. You have embedded PHP variable expansion in that query, which is irrelevant to your question. This just makes it more difficult for people to decipher what you're trying to ask. Show the code you want to debug, not the code that generates the code.

Now about your query:

• You can't just put an extra WHERE clause anywhere. Add extra terms to your existing WHERE clause with the AND operator.

• Embedding a call to validate the same $_GET parameter multiple times in the same query seems wasteful. Use a variable to preserve the result of validating the parameter.

• Take advantage of short-circuit evaluation. Put the conditions that are least expensive to evaluate (e.g. those that benefit from a conventional index) leftmost.

• MySQL fulltext search in boolean mode does not return relevance. You have to use the natural language mode to get the relevance value between 0 and 1.

• Putting conditions in the HAVING clause instead of the WHERE clause makes it easier because you can use column aliases you defined in the SELECT list, but it may make the query perform worse. HAVING is for placing conditions on groups to include after a GROUP BY clause. WHERE is for placing conditions on rows to include in the query result.

• Rows are automatically sorted in order of relevance if you use MATCH() in natural language mode in the WHERE clause. If the chance of a tie is low, you might be able to skip your ORDER BY clause, and make the query even more efficient.

Here's how I would write this code:

$q = validate_input($_GET["q"]);
$bsearch = $pdo->quote("+{$q}");
$nlsearch = $pdo->quote($q);
$stories_table = $config["db"]["pre"] . "stories";
$offset = validate_input(($_GET["page"]-1)*10);

$query = "
  SELECT *, MATCH (story_title) AGAINST ({$nlsearch}) AS Relevance 
  FROM {$stories_table} 
  WHERE story_time > time()-86400
    AND MATCH (story_title) AGAINST ({$bsearch} IN BOOLEAN MODE) 
    AND MATCH (story_title) AGAINST ({$nlsearch}) > 0.2
  LIMIT {$offset}, 10";