MYSQL define certain column in mysql_query array?

Question

When I'm selecting from multiple tables that share column names is there a way I can return both, but define which one I want to select the data from?

For instance: Both tables contain "date" columns, and I want to use both, but I would like to avoid having to rename each column that has duplicate names.

 $query = mysql_query("SELECT * FROM posts, comments"); //(SELECT * is just for example)
 while($row=mysql_fetch_array($query))
 {
     $postDate = $row['date'];  //I would like to be able to do something like:
                                //$postDate = $row['posts']['date']; OR $row['posts.date'];
                                //of course it's all in an array now, jumbled up.
     $commentDate = $row['date']; 
 }

Answer 1

You need to alias the duplicate column names in your query if you want both, eg

SELECT p.date AS postDate, c.date AS commentDate
FROM posts p, comments c

Then use the aliases to retrieve the values

$postDate = $row['postDate'];
$commentDate = $row['commentDate'];

FYI, it's almost never a good idea to SELECT *, especially when multiple tables are involved. You should always try to be specific about the columns added to your SELECT clause

Answer 2

The best way to do this is to specify the fields in the query itself, giving aliases to them:

SELECT posts.date postDate, comments.date commentDate FROM posts, comments;

It's generally frowned upon to use SELECT *. You end up with code that's a little less stable. By specifying the exact fields, and the aliases of those fields, you are less prone to bugs that might arise from changes to the database schema, etc.

Answer 3

Just add aliases...btw, you should never use SELECT * FROM ...

$query = mysql_query("SELECT posts.date as pdate, comments.date as cdate FROM posts, comments");
while($row=mysql_fetch_array($query))
{
    $postDate = $row['pdate'];  
    $commentDate = $row['cdate']; 
}