Select same column name from two tables

Question

I have this query:

$result3 = mysql_query("SELECT posts.id, posts.date, posts.title, comments.post, comments.id, comments.date FROM posts, comments WHERE posts.id = comments.post")       
or die(mysql_error());  

while($row2 = mysql_fetch_array( $result3 )) {
    echo $row2['title'];
}

The problem is with the posts.id , posts.date and comments.id , comments.date . How can I get out id, date for both tables $row2['....]; I tried $row2['posts.id']; but it didn't work!

mysql_fetch_array also returns the id's of the fields. See my answer. — transilvlad, Aug 29 '12 at 11:54
You have 3 types of fetch: array (name and id), assoc (name), row (id). The id's are in the order of your SELECT. If you select * they come in the order of the table. — transilvlad, Aug 29 '12 at 11:56

score 8 · Accepted Answer · edited May 23 '17 at 11:53

Name the column in your query (this is called an column alias) like this:

SELECT 
    posts.id as postsID, 
    posts.date, 
    posts.title, 
    comments.post, 
    comments.id as CommentsID, 
    comments.date 
FROM 
    jaut_posts, 
    f1_comments 
WHERE 
    jaut_posts.id = f1_comments.post

Then you can use:

echo $row2['postsID'];
echo $row2['commentsID'];

Edit:

You may also benefit from this question I wrote and answered which discusses many common SQL queries and requests.

score 2 · Answer 2 · answered Aug 29 '12 at 11:49

2

Use the as in your query, some thing like

select post.id as PostId, comment.id as CommentId

and then :

row["PostId"]
row["CommentId"]

answered Aug 29 '12 at 11:49

SidAhmed

2,332
2
25
46

score 1 · Answer 3 · answered Aug 29 '12 at 11:49

1

while($row2 = mysql_fetch_array( $result3 )) {
  $post_id = $row2[0];
  $posts_date = $row2[1];
  $posts_title = $row2[2];
  $comments_post = $row2[3];
  $comments_id = $row2[4];
  $comments_date = $row2[5];
}

answered Aug 29 '12 at 11:49

transilvlad

13,974
13
45
80

2

This works, but do you think that this is the most intuitive way of doing this? – Fluffeh Aug 29 '12 at 11:51
It's the simplest. This is a basic question so it is best to give a basic answer. – transilvlad Aug 29 '12 at 11:58
Perhaps it is just me, but I always found that named fields were *much* easier to use rather than a numeric array in the order that the columns are specified in the `select` statement. They also won't cause problems if the SQL is changed as long as they are still selected. – Fluffeh Aug 29 '12 at 12:04

score 1 · Answer 4 · edited Aug 29 '12 at 11:54

1

change

SELECT posts.id, posts.date, posts.title, comments.post, comments.id, comments.date

into

SELECT posts.id AS postsid, posts.date, posts.title, comments.post, comments.id AS commentsid, comments.date

then you can use $row2['postsid']; and $row2['commentsid'];

edited Aug 29 '12 at 11:54

Fluffeh

33,228
16
67
80

answered Aug 29 '12 at 11:50

Ossie7

360
3
8

score 0 · Answer 5 · answered Aug 29 '12 at 11:49

0

Create alias:

$sql = 'SELECT posts.id AS post_id, comments.id AS comment_id ...';

$row = mysql_fetch_assoc($sql);

echo $row['post_id'];
echo $row['comment_id'];

or, alternatively, you can access it by indexes (since you're using mysql_fetch_array anyway)

echo $row[0]; // posts.id

answered Aug 29 '12 at 11:49

German Rumm

5,782
1
25
30

@tntu did that, what are you trying to say? – German Rumm Aug 29 '12 at 11:53

Select same column name from two tables

5 Answers5