CODEWITHSUNDEEP
phpmysql
superkayrad
superkayrad

Reputation: 178

mysql - Select first row of table if any match in table

How can I return the first row of table X if there is a match in ANY ROW of table X? Within a foreach loop I have the following query below.

$sql_string="SELECT id,keyword_tags,topic_name,contents,
            MATCH(keyword_tags) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score1,
            MATCH(topic_name) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score2,
            MATCH(contents) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score3
            FROM ".$table_get." WHERE
            (MATCH(keyword_tags) AGAINST ('$keysearch' IN BOOLEAN MODE) OR
            MATCH(topic_name) AGAINST ('$keysearch' IN BOOLEAN MODE) OR
            MATCH(contents) AGAINST ('$keysearch' IN BOOLEAN MODE))
            AND id='1'
            ORDER BY score1 DESC, score2 DESC, score3 DESC";

Any thoughts on how I can return the row with id='1' if there are any matches from within the table?

Upvotes: 1

Views: 189

Answers (2)

pilcrow
pilcrow

Reputation: 58524

How can I return the first row of table X if there is a match in ANY ROW of table X?

The SQL standard EXISTS (subquery) construct can help:

SELECT ...
  FROM X
 WHERE id = 1                -- our "first" row only
       AND
       EXISTS (SELECT *      -- only if there EXISTS a MATCH anywhere in table
                 FROM X
                WHERE MATCH(field1) AGAINST ('search_val' IN BOOLEAN MODE)
                      OR
                      MATCH(field2) AGAINST ('search_val' IN BOOLEAN MODE)
                      ...
                      MATCH(fieldN) AGAINST ('search_val' IN BOOLEAN MODE));

Upvotes: 1

dlporter98
dlporter98

Reputation: 1630

Added a limit to the end of the query. 

 $sql_string="SELECT id,keyword_tags,topic_name,contents,
                MATCH(keyword_tags) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score1,
                MATCH(topic_name) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score2,
                MATCH(contents) AGAINST ('$keysearch' IN BOOLEAN MODE) AS score3
                FROM ".$table_get." WHERE
                (MATCH(keyword_tags) AGAINST ('$keysearch' IN BOOLEAN MODE) OR
                MATCH(topic_name) AGAINST ('$keysearch' IN BOOLEAN MODE) OR
                MATCH(contents) AGAINST ('$keysearch' IN BOOLEAN MODE))
                AND id='1'
                ORDER BY score1 DESC, score2 DESC, score3 DESC LIMIT 1";

Upvotes: 0

Related Questions