Hi,
I'm still very new to MySQL and I'm having some issues trying to generate the right set of results. I have two tables, that follow the structure as below:
mod_site_content
+----+-----------+--------------+
| id | pagetitle | content |
+----+-----------+--------------+
| 1 | home | <p> ... </p> |
| 2 | project 1 | <p> ... </p> |
| 3 | comment 1 | <p> ... </p> |
| 4 | project 2 | <p> ... </p> |
| 5 | project 3 | <p> ... </p> |
| 6 | comment 2 | <p> ... </p> |
| etc ... |
+----+-----------+--------------+
mod_site_tmplvar_contentvalues
+----+-----------+-----------+-----------------+
| id | tmplvarid | contentid | value |
+----+-----------+-----------+-----------------+
| 1 | 2 | 1 | [{"key":"val"}] |
| 2 | 2 | 2 | [{"key":"val"}] |
| 3 | 5 | 1 | [{"key":"val"}] |
| 4 | 5 | 5 | [{"key":"val"}] |
| 5 | 15 | 4 | [{"key":"val"}] |
| 6 | 15 | 5 | [{"key":"val"}] |
| etc ... |
+----+-----------+-----------+-----------------+
Note: The contentid values in the second table correspond to the id values in the first.
The aim is to search through pagetitle and content in mod_site_content for a match, but if none is found, then search value in mod_site_tmplvar_contentvalues. I only want to include specific "tmplvarid"s in the search (15 and 17.) Also, the results should be ranked in pagetitle, content, value preference.
I've managed to accomplish everything so far exept combining the second table correctly. The code below works for mod_site_content only:
SELECT A1.pagetitle, A1.content, A1.uri,
CASE
WHEN A1.pagetitle LIKE ? THEN 1
WHEN A1.content LIKE ? THEN 2
END AS rank
FROM mod_site_content A1
WHERE pagetitle LIKE ?
OR content LIKE ?
ORDER BY rank
Any ideas?