dj-kenpo Posted April 29, 2007 Share Posted April 29, 2007 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! Quote Link to comment Share on other sites More sharing options...
Karl33to Posted April 29, 2007 Share Posted April 29, 2007 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 "; Quote Link to comment Share on other sites More sharing options...
dj-kenpo Posted April 29, 2007 Author Share Posted April 29, 2007 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? Quote Link to comment Share on other sites More sharing options...
dj-kenpo Posted April 30, 2007 Author Share Posted April 30, 2007 $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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.