0

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?

Edited by chr.s

1
Contributor
1
Reply
14
Views
3 Years
Discussion Span
Last Post by chr.s
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.