Why is MySQL statement ignored?

Question

Why is n.author='$host[id]' ignored?

$host[id]=5;
SELECT  
n.id,n.name,n.text,
r.title,
COUNT(c.news_id) comments
FROM news n LEFT JOIN rub r
 ON
 r.news_id=n.id 
 LEFT JOIN comments c 
  ON 
n.id = c.news_id AND c.status='1' AND n.author='$host[id]'
GROUP BY n.id
 ORDER BY n.id DESC LIMIT 10

I need only those rows where 'news'.'author'=$host[id], but executing this query I got all the news from the table. Why does it happen?

n is the left table, on a left join you get all entries from the left table. Educate yourself about the different join types. — hakre, Jan 05 '14 at 12:08
See [this great explanation of joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) — juergen d, Jan 05 '14 at 12:09
Please *don’t* use variables directly in your SQL statements. This leaves you open to SQL injection. You should create statements and pass input as parameters. — Martin Bean, Jan 05 '14 at 13:09

score 0 · Answer 1 · answered Jan 05 '14 at 12:12

0

$host['id']=5;
SELECT  
n.id,n.name,n.text,
r.title,
COUNT(c.news_id) comments
FROM news n LEFT JOIN rub r
 ON
 r.news_id=n.id 
 LEFT JOIN comments c 
  ON 
n.id = c.news_id AND c.status='1' AND n.author="'{$host['id']}'";
GROUP BY n.id

answered Jan 05 '14 at 12:12

dev

439
2
6

score 0 · Accepted Answer · answered Jan 05 '14 at 12:13

$host[id]=5;

SELECT n.id,n.name,n.text, r.title, COUNT(c.news_id) comments
FROM news n 
LEFT JOIN rub r ON r.news_id = n.id 
LEFT JOIN comments c ON c.news_id = n.id AND c.status = '1'
WHERE n.author = '$host[id]'
GROUP BY n.id
ORDER BY n.id DESC LIMIT 10

Why is MySQL statement ignored?

2 Answers2