Retrieving from two tables and putting it into one loop

[Fenhopi]

So here's what I got:

$getwall2 = "SELECT comments.comment, wallposts.message FROM comments, wallposts where comments.byuser='$username' and wallposts.person='$username' ORDER BY dtime DESC LIMIT 0, 10";
			$connectgetwall2 = $database->query($getwall2);
			$wall2 = mysql_fetch_array($connectgetwall2);

			echo "<table><tr><td>";
			echo "<br><h2><img src=\"view.php?user=$user\"  width=55 height=40> ";
			echo "<a href=\"userinfo.php?user=$user\"> $user</a> " .$wall2;
			echo "</h2></tr></td>";
			echo "<tr><td>";

 

I still can't get it working, I can't figure out how to retrieve from two different tables, put it into one loop and have it seperate comments from wallposts into different tables ordering by when they were posted. If you need an example for what I'm looking for, think of how they have wallposts and status updates posted on your wall on facebook.

 

Help would be highly appreciated!

[hoogie]

What happens when you run it?  Does it return anything?

[Fenhopi]

Nope. I'm asking for someone who understand what I want, to give me an idea how to more cleverly write it.

[hoogie]

You won't be able to do this in one query.  The query you currently have will either return duplicate records for all comments and wall posts (assuming there's at least one in each table), or nothing at all (if one of the tables is empty).

 

What I would do is have two queries, one for the comments section and one for the wallposts section.  Put the records from both of those queries into an array.  Include the dtime field so that you can then sort the array by time.

 

Then loop the results into your tables.

[Fenhopi]

Hm, could you please give an example what you mean?

[hoogie]

I think I'm misunderstanding something - I thought you wanted them all to be in one table, sorted by time.  Do you also want there to be some sort of visual cue that shows if they were a comment or a wallpost?  I didn't see anything like that in your original code.

 

If that's what you want, you could add another field to the array and set the value to "comment" for all the records from the comment table, and "wallpost" for all the records from the wallpost table, or something like that.  Is that what you were thinking of?

[Fenhopi]

I apologize for being vague, allow me to try again, I'll use facebook as an example seeing as it's easy to relate to:

 

I have one table which holds status updates(time when posted, by who, message). I also have one that holds wallposts(Time when posted, by who, to who, message). What I want is to have both the status updates and the wallposts to appear on my wall in the chronological order they were posted in. Is this possible?

 

 

[hoogie]

Ok, that's what I thought.  First query the comments table for the comment and dtime.  Put that into an array.  Then query the wallposts table for the wallpost and dtime and put that into the same array.  Then you can sort that array according to dtime, and viola, you have an array with all of the comments and wallposts sorted by time.  Then you can loop that array and make it look any way you want.

 

Sorry I don't have time to write out the code right now.  Hopefully that description makes sense.

 

 

[Fenhopi]

Yeah, it totally makes sense. Thanks for taking the time.

 

Here's what I got:

 

$Wallpostquery = "SELECT byuser, message, dtime FROM wallposts WHERE person='$user'";
$Statusquery = "SELECT status, dtime FROM statuses WHERE byuser='$user'";

$showonwall = array($wallpostquery, $statusquery);

 

I'm a bit rusty on arrays. Like how I'd put it in a while, sort by time and separate them. And will each comment/wallpost keep it's own date posted still? Like I said I'm a bit rusty. Thank you for helping me out.