mysql select from 2 tables with the same column names. Not working. getting repe

[dj-kenpo]

I'm having trouble making a query to both tables simultaneously.

I want to grab the rows and sort them, but I'm only getting ONE set of data not the two, so the query is deffinetly wrong. if the second table has 2 rows, all 5 reslts still show, and it simply REPEATS 1,2,1,2,1 (as in row1 etc from table2)

 

the query I wrote is:

$sql="SELECT journal_comments.*, photo_album_comments.* FROM journal_comments, photo_album_comments WHERE journal_comments.User_ID=$User_ID AND journal_comments.comment_approved=0 AND photo_album_comments.User_ID=$User_ID AND photo_album_comments.comment_approved=0 

ORDER BY journal_comments.Timestamp DESC, photo_album_comments.Timestamp DESC LIMIT 5";

 

table structure is:

 

journal_comments.id

journal_comments.rel_id

journal_comments.User_ID

journal_comments.name

journal_comments.comment

journal_comments.url

 

photo_album_comments.id

photo_album_comments.rel_id

photo_album_comments.User_ID

photo_album_comments.name

photo_album_comments.comment

photo_album_comments.url

 

I tried google, but I'm not looking right, I just got results where people needed the 'like' command, and that's not what I need as far as I know.

 

 

output I want.

 

if photo_album_comments has 2 rows

and journal_comments has 3 rows

 

I want all 5 rows, listed out into the $result array, and sorted by timestamp.

 

what simple mess up did I make?

 

Thanks guys!

[Karl33to]

you need to set up aliases for the data in the second table so that you can distinguish between it and the data in the first one

 

$sql="
SELECT journal_comments.*, 
photo_album_comments.id AS photo_id,
photo_album_comments.rel_id AS photo_rel_id,
photo_album_comments.User_ID AS photo_User_ID,
etc
etc
FROM journal_comments, photo_album_comments AS
WHERE journal_comments.User_ID=$User_ID 
AND journal_comments.comment_approved=0 
AND photo_album_comments.User_ID=$User_ID 
AND photo_album_comments.comment_approved=0 
ORDER BY journal_comments.Timestamp DESC,
photo_album_comments.Timestamp DESC 
LIMIT 5
";

[dj-kenpo]

but then the while loop won't work, how do I still list all the data, (sorry, I'm getting the feeling this is a dumb question)

 

while($row3 = mysql_fetch_array($result3))

{

$ID = $row3["ID"];

$rel_id = $row3["rel_id"];

$Name = $row3["Name"];

$Comment = $row3["Comment"];

$Date = Date("F d Y",$row3["Timestamp"]);

 

 

etc....

 

naming them as an aliase changes this, how do I get the data from both and still spit them into my layout?

[dj-kenpo]

$sql="

SELECT journal_comments.ID,

journal_comments.rel_id,

journal_comments.User_ID,

journal_comments.Name,

journal_comments.Email,

journal_comments.Comment,

journal_comments.URL,

journal_comments.Timestamp, 

photo_album_comments.ID AS photo_ID,

photo_album_comments.rel_id AS photo_rel_id,

photo_album_comments.User_ID AS photo_User_ID,

photo_album_comments.Name AS photo_Name,

photo_album_comments.Email AS photo_Email,

photo_album_comments.Comment AS photo_Comment,

photo_album_comments.URL AS photo_URL,

photo_album_comments.Timestamp AS photo_Timestamp

 

 

FROM journal_comments, photo_album_comments

WHERE journal_comments.User_ID=$User_ID

AND journal_comments.comment_approved=0

AND photo_album_comments.User_ID=$User_ID

AND photo_album_comments.comment_approved=0

ORDER BY journal_comments.Timestamp DESC,

photo_album_comments.Timestamp DESC

LIMIT 5

";

 

just caused nothing to show.. no error, just.. nothing... any ideas?

 

there's tons of info in both tables