Selecting Fields With the Same Name
I had two people ask me the same question today so I thought I'd share this. The dilemma occurs when joining two or more tables where a field name is the same in those tables. For example, you have a blog table with an id field and a comments table with an id field. If you just did a SELECT * while joining those two tables, you can't use the table identifier to grab the field name (E.g: $rs['blog.id'] won't work).
If you're using MySQL, the way around this is to specify the field names manually and creating aliases for anything that would be a duplicate.
SELECT blog.id AS blog_id, comment.id AS comment_id, blog.title, blog.description, comment.text FROM [...]If you're using MS SQL Server user, there's a quick shortcut where you can include all fields using the asterisk (*) and still create aliases for the other fields. Keep in mind, this will return blog_id AND blog.id.
SELECT blog.id AS blog_id, comment.id AS comment_id, * FROM [...]I'm of the school of "return only what you need from the database" and would avoid using the asterisk selector in any case.
Conversation
Could be very useful for some, good job.
There are other ways around this as well - but this is a nice and simple way of handling this. I am sure this will help some out there.
Where were these articles several years ago when I was asking these questions? hehehe.
Or another twist if you always know the order of the columns within the SQL. Take a look at mysql_fetch_array and ask it to pull back the columns with numerical indices from the query rather than (or in addition to) an associative array.
You can clean up the SQL a bit by dropping the AS parts (you don't need it anyways) and aliasing the tables.
SELECT b.id blog_id, c.id comment_id, b.title, b.description, c.text FROM blog b INNER JOIN comment c ON b.id = c.blog_id
<shameless_plug>
CakePHP auto-organizes SQL fields by model, i.e.:
Array(
'Blog' => array('id' => 1, 'title' => 'Whatever')
'Comment' => array('id' => 5...
);
</shameless_plug>
Maybe this is because I'm running a WebKit nightly, but I can't see the carat in this text field. Makes editing a wee bit difficult.
Just to clarify, the Cake-related part of the above comment should be wrapped in <shameless_plug />.
I wish more blogs had those nifty live-comment preview thingies....
[ED: Have you tried mine? :)]
Nate: I was half tempted to mention it in the article. :) I decided against it as I really wanted the post itself to be relatively agnostic.
What do you mean about that being a MS SQL user?
SELECT t1.*, t2.blog_id FROM table1 JOIN table2.....That works on every database server I have ever tried on... I could have misunderstood you though, hehe...
Dan: take notice that in the example for MS SQL users, I've just done * to include all fields. So, using your example, I couldn't do:
SELECT *, t2.blog_id [...]This would generate an error message. However, upon further testing, it seems that you can reselect all fields by specifying the table name (or alias) as you've done and this will work in MySQL as well as MS SQL Server.
You should take a look at this page in ie, Jon-o *dodges holy water*, your code boxes are a bit tight!
I tend to give aliases. I feel that I have tha control so I feel safer.
Hmm ... I just name my ID's according to the table when I create them. For example, if it's a blog table, the id would be bid, if it's a comments table, it'll be cid and so on. Unless I totally misunderstood the issue here?! (which could very well be *lol*)
Karinne: that's how I've traditionally named my tables but many development frameworks like Cake and Ruby recommend a naming convention where the identifier would just be 'id'.
jlactose: you might be using a cached stylesheet. I updated a little while ago because of that very issue. Force refresh or clear your cache and try again.
Some useful simple alternative to what I used to do! thanks for sharing it Jonathan
Ah ... gotcha ... I haven't had time to jump on the Cake and Ruby band-wagons yet! When I do tho ... I will keep this post in mind ;)
Nice writeup. I should really improve my SQL-knowledge, I only know the basics.