3raser Posted June 3, 2012 Share Posted June 3, 2012 I've been reading guides that talk about selecting data from multiple tables. I've got a pretty good idea of how these work, but I've been trying something sort of unorthodox in terms of what the tutorial goes over. What I'm attempting: I want to select twenty of the most recent posts and threads created by the user from one query. I then want to get that data together, and sort all of them by date. I also have a question regarding when I actually echo out the content: Do I have to include the 'tablename.columnname', or can I just do 'columnname'. Code: <?php $query = mysql_query("SELECT threads.title,threads.date,posts.title,posts.date FROM threads,posts WHERE threads.username = '$username' AND threads.posts = '$username' ORDER BY threads.date,posts.date LIMIT 20") or die(mysql_error()); while($row = mysql_fetch_assoc($query)) { ?> <tr> <td></td> <td><a href="#">Test</a></td> <td><a href="#">General</a></td> <td><?php $row['title']; ?></td> <td><a href="#">Show</a></td> </tr> <?php } ?> Link to comment https://forums.phpfreaks.com/topic/263605-selecting-and-sorting-data-from-multiple-tables/ Share on other sites More sharing options...
Barand Posted June 4, 2012 Share Posted June 4, 2012 from your query you have 2 tables with these columns threads posts ============= =========== title title date date username posts You are attempting a join but there does not seem to be a key in the posts table on which to make the join with threads table Link to comment https://forums.phpfreaks.com/topic/263605-selecting-and-sorting-data-from-multiple-tables/#findComment-1350967 Share on other sites More sharing options...
3raser Posted June 4, 2012 Author Share Posted June 4, 2012 from your query you have 2 tables with these columns threads posts ============= =========== title title date date username posts You are attempting a join but there does not seem to be a key in the posts table on which to make the join with threads table Can you clarify a bit more? Updated query: $query = mysql_query("SELECT threads.title,threads.date,posts.date,posts.id FROM threads,posts WHERE threads.username = '$username' AND posts.username = '$username' ORDER BY threads.date,posts.date LIMIT 20") or die(mysql_error()); Link to comment https://forums.phpfreaks.com/topic/263605-selecting-and-sorting-data-from-multiple-tables/#findComment-1350996 Share on other sites More sharing options...
thara Posted June 4, 2012 Share Posted June 4, 2012 what happen with your updated query? Link to comment https://forums.phpfreaks.com/topic/263605-selecting-and-sorting-data-from-multiple-tables/#findComment-1351003 Share on other sites More sharing options...
3raser Posted June 4, 2012 Author Share Posted June 4, 2012 what happen with your updated query? I just fixed a few things (such as there being a posts.titles when that column didn't even exist). I'm starting to think the join command isn't built for what I'm trying to accomplish. In no way can I use a key here. :/ Link to comment https://forums.phpfreaks.com/topic/263605-selecting-and-sorting-data-from-multiple-tables/#findComment-1351010 Share on other sites More sharing options...
Barand Posted June 4, 2012 Share Posted June 4, 2012 Looks like you may need a union $sql = "SELECT t.date, t.title, null as postid FROM title t WHERE t.username = '$username' UNION SELECT p.date, null as title, p.id as postid FROM posts p WHERE p.username = '$username' ORDER BY date DESC LIMIT 20"; $query = mysql_query() or die(mysql_error()); Link to comment https://forums.phpfreaks.com/topic/263605-selecting-and-sorting-data-from-multiple-tables/#findComment-1351052 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.