sql search query still not producing expected results.

Question

I need a count and search query to work properly. The count query appears to be working properly, however the search query is not.

Count query:

SELECT COUNT(DISTINCT tg_id) 
  FROM tg_keywords 
 WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' 
 ORDER BY tg_keyword LIKE 'keyword_1' AND tg_keyword LIKE 'keyword_2 DESC
Returns 1 count as expected.

Search query:

SELECT DISTINCT tg_keywords.tg_id 
  FROM tg_keywords LEFT JOIN tg_info.tg_id=tg_keywords.tg_id 
 WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' 
 ORDER BY tg_keyword LIKE 'keyword_1' AND tg_keyword LIKE 'keyword_2' DESC, tg_info.date_added LIMIT 16 OFFSET 1
Returns 0 results (1 is expected)

Any advice would be greatly appreciated

Thanks in advance, Archie

Answer 1

Remove the OFFSET 1 at the end of the query and then try it.

The OFFSET 1 tells it to give you records from the SECOND row. Either make it OFFSET 0 or remove it altogether.

Answer 2

SELECT DISTINCT tg_keywords.tg_id 
FROM tg_keywords 
LEFT JOIN tg_info.tg_id=tg_keywords.tg_id 
WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' 
ORDER BY tg_keyword LIKE 'keyword_1' AND tg_keyword LIKE 'keyword_2' DESC, tg_info.date_added 
LIMIT 16 OFFSET 1

What's with that ORDER BY part? Did you mean:

WHERE tg_keyword LIKE 'keyword_1' OR tg_keyword LIKE 'keyword_2' 
ORDER BY tg_info.date_added DESC

Answer 3

Also do you want to give the user the ability to use SQL wildcards? If not I probably ditch like and put in an =.