Overcoming repeated column names getting overwritten in MySQL

[eluser]Nanodeath[/eluser]
Hi all,

Quick explanation -- I'm working on making a system so that we CI enthusiasts can distribute our code to each other quickly and easily and whatnot and developing some interesting libraries and helpers in the mean time. At present, I'm working on making an ORM database thing, but am having some trouble with MySQL syntax (lolz). In my defense, I'm not sure if it's actually possible what exactly I'm going for.

In any case, to go with the old Blog cliche, suppose you have some posts and some related comments. Each post has "many" comments, and each comment has "one" post. Already, you can go

Code:

$Comments = $this->ciorm->getComments()->andPosts();

The andPosts is a hint to the library to perform a join to get the post data. Only problem is there is no guarantee that both Comments and Posts will have mutually exclusive fields. If there's a datesubmitted field or something that appears in both tables, you'll know that you only see the latest datesubmitted field.

That is to say, if you write

Code:

$query = mysql_query("SELECT comments.*, posts.* FROM comments JOIN posts ON posts.id = comments.post_id");
foreach($row = mysql_fetch_assoc($query))[
  var_dump($row);
}

then you'll only get one of the datesubmitted fields. Anyone know a way around this? Ideally there'd be a way to rename all the comments.* fields from 'subject', 'text', 'datesubmitted' to 'comments.subject', 'comments.text', and 'comments.datesubmitted'. Unfortunately comments.* AS blah doesn't really do anything, and I'd still like to say * instead of listing out specific fields (and I want to use only one query!).

Thanks!

[eluser]Glen Swinfield[/eluser]
If you want to use * you could use:

Code:

"SELECT comments.*, comments.datesubmitted as comment_datesubmitted, posts.* FROM comments JOIN posts ON posts.id = comments.post_id"

That way you have 1 extra field 'comment_datesubmitted' for the comments table, and 'datesubmitted' still applies to the posts table.

[eluser]Nanodeath[/eluser]
Hmm, well, that sort of defeats the point of being able to use the *, because then the developer has to specify the datesubmitted field somewhere. I think I'll just make it so that in the config file one has to explicitly specify all the fields they'll ever want in each table. That way I can just iterate through that and concatenate the table name on the front...no more *, but I guess it can't be helped.