Selecting and sorting data from multiple tables

[3raser]

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
	}
?>

[Barand]

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

[3raser]

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());

[thara]

what happen with your updated query?

[3raser]

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. :/

[Barand]

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());