I have a few concerns and refinements, but I'll skip straight to the code first and explain after.
You want to generate a query that renders like this:
SELECT a.date, a.reminder, a.type, a.animal, a.mob, b.nameandid AS 'animalname'
FROM app_reminders a
LEFT JOIN app_animal b ON a.animal = b.id
WHERE a.date >= NOW() AND a.user_id = 3
Notice that my
JOIN and the
JOIN in your linked StackOverflow page's question are using
LEFT JOIN. The OP on that question is wanting only rows with values in all joined tables. The answer uses
JOIN (regular join aka
INNER JOIN) this ignores rows that have
null for the joining value.
You want something different. You want the rows from the first table regardless of if the second table contains a qualifying value to join on the row. For this, you want
LEFT JOIN. Here is an SQLFiddle Demo if you want to play around and compare the results of the joins. (If this isn't the action you are seeking, you should be able to use this link to express what isn't quite right.) With
LEFT JOIN you won't need to add the extra condition to check
- You are using the
LIKE operator but without
_ wildcards, so there is no reason to write
LIKE. Just simplify to
type are mysql KEYWORDS, they are not RESERVED KEYWORDS so they don't NEED to be wrapped in backticks. This is also true of all of your other columns and the tablenames. You are welcome to use
quoteName() on all of these entities, but it will only add bloat to your query syntax and code.
- So that your query is more human-readable, include a single space on both side of your comparison operators. (e.g.
'LIKE' is better as
' LIKE ')
- As a security precaution, cast your
$userid variable as an integer
(int) when applying it to your query. Integer values do not need to be quoted.
- As a matter of personal preference with the intention to improve readability/code-comprehension, I try to avoid using duplicate method calls as a concatenation technique for a single clause. My suggested Joomla snippet will only use one
select() and one
where() call. (When joining multiple tables, the presence of multiple
join() calls is logical/intuitive.)
Joomla Syntax (no quoting methods called):
$query = $db->getQuery(true)
->select("a.date, a.reminder, a.type, a.animal, a.mob, b.nameandid AS 'animalname'")
->joinLeft("app_animal b ON a.animal = b.id")
->where("a.date >= NOW() AND a.user_id = " . (int)$userid);
p.s. If you actually want to use
LIKE with wildcard character(s), this post will show you the secure way -- sourced from the Joomla documentation.