-1

So my current query is this 

"SELECT * FROM articles 
WHERE id = :id "

However I'm wondering how am I able to join the articles table onto another table called users.

I'm wanting to do this so that I am able to use the author_id of each article row to grab the username of the author using the articles.author to find the username which matches the author id with the users.id.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Pepzter9
  • 72
  • 6
  • 1
    PDO is the connection method, so it is not relevant to your question. You would use [`JOIN`s](https://dev.mysql.com/doc/refman/5.0/en/join.html) to connect your tables. – Jay Blanchard Oct 14 '15 at 17:53
  • I know that PDO is the driver however i'm not sure as how to use JOINs correctly as I have never had to use them before. – Pepzter9 Oct 14 '15 at 17:54
  • 1
    I have linked the documentation for you in my above comment. Start there, try it, and then when you have a specific issue with it come back to us and we'll try to help. – Jay Blanchard Oct 14 '15 at 17:55
  • 1
    joins are rather advanced, like day 2 :) – Drew Oct 14 '15 at 17:55
  • So would this be correct? SELECT * FROM articles LEFT JOIN users ON articles.author = users.id WHERE articles.id = :id – Pepzter9 Oct 14 '15 at 17:58
  • ^ I don't quite understand the above and it slightly differs from my question. Question has been answered below. – Pepzter9 Oct 14 '15 at 18:15

1 Answers1

1

Instead of the query you have written, you can just write something along these lines:

select a.*, u.username
from articles a
left join users u on u.user_id = a.author_id
where u.user_id = :id

Always make sure that you run the query through a client (command line, PHPMyAdmin, HeidiSQL, Workbench or whatever else you prefer), improve it as needed and then plop it in your PDO's SQL statement.

zedfoxus
  • 35,121
  • 5
  • 64
  • 63
  • don't forget to honor your alias throughout – Drew Oct 14 '15 at 18:00
  • Thank you, what is the difference between inner and left on joins? Would my above comment work just like this one does? – Pepzter9 Oct 14 '15 at 18:00
  • @Pepzter9 In this case, LEFT is probably better; if the article has no author this query will return nothing, but yours would still return the book fields. – Uueerdo Oct 14 '15 at 18:03
  • Pepzter9 - [this question](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) explains joins well. Like @Uueerdo mentioned, left join may be better if you want articles even if you don't find a match in users. If you want to get articles ONLY if there's a match in users table, use inner join. – zedfoxus Oct 14 '15 at 18:08
  • select * from articles a left join users u on u.id = a.author where a.id = :id The above gives me the correct result however it selects * from the users table, how am i able to just select username from users and * from articles? – Pepzter9 Oct 14 '15 at 18:08
  • @Pepzter9 `SELECT articles.*, users.username ` or actually `SELECT a.*, u.username ` with those aliases. – Uueerdo Oct 14 '15 at 18:09
  • @Pepzter9 I've edited the answer accordingly – zedfoxus Oct 14 '15 at 18:10
  • Perfect! Thank you, I have seen the .* in use before but I thought that this actually meant it would select all tables that start with this keyword e.g. articles.something. – Pepzter9 Oct 14 '15 at 18:14
  • `*` means give me all columns from all tables in this query. `articles.*` means give me all columns from articles table. `a.*` means give me all columns from table a. If a is an alias given to articles, give me all columns from articles. – zedfoxus Oct 14 '15 at 18:15
  • Gotcha, thanks for the info will be useful in the future now that I know! – Pepzter9 Oct 14 '15 at 18:26