join tables?

[phpsycho]

I been messing around with the code a little bit. I got it to display data from both tables. Only problem is, table "wall" only has one row of data and its showing it about 10 times. Then the second tables data is displaying but I want both tables data displayed as one table. I want it so things are ordered by time/date. so there will be say feed data, feed data, wall data, feed data. Thats how I want it to work, is that possible?

 

<?php
include ('includes/db.php');
include ('includes/functions.php');
include ('includes/global.php');
session_start();


echo '<table width="100%">';
$statsql="SELECT wall.by AS wby, wall.date AS wdate, wall.time AS wtime, wall.message AS wmessage, feed.pageid, feed.by, feed.type FROM `wall`, `feed` LIMIT 0,10";
$statres=mysql_query($statsql) or die(mysql_error());

while($statrow=mysql_fetch_assoc($statres)){
	$message=clean_up($statrow[wmessage]);
	$date=clean_up($statrow[wdate]);
	$time=clean_up($statrow[wtime]);
	$by=clean_up($statrow[wby]);


$usersql="SELECT * from `users` WHERE `id`='$by'";
$useres=mysql_query($usersql) or die(mysql_error());

while($row=mysql_fetch_assoc($useres)){
	$avatar=clean_up($row[avatar]);
	$first=clean_up($row[first]);

echo "<tr class='content'><td valign='top'>
<img src='pic.php?w=50&h=50&constrain=1&img=photos/$avatar' /><br>$first"; } echo "</td><td valign='top'>
$message</td></tr>";


$pageid=clean_up($statrow[pageid]);
$type=clean_up($statrow[type]);
$by=clean_up($statrow[by]);
include("../includes/activity_info.php");
}

echo "</table>";
?>

[viviosoft]

You'll want to use UNION:

 

SELECT wall.by AS wby, wall.date AS wdate, wall.time AS wtime, wall.message AS wmessage FROM wall 
UNION
SELECT feed.pageid, feed.by, feed.type FROM feed
ORDER BY wdate ASC

 

[phpsycho]

I tried that and I got Unknown column 'feed.pageid' in 'field list'

then I tried changing your query to:

SELECT wall.by AS wby, wall.date AS wdate, wall.time AS wtime, wall.message AS wmessage FROM wall

UNION

SELECT feed.pageid, feed.by, feed.type FROM feed

ORDER BY wdate ASC

 

because you had it fetching from wall again instead of feed. was I right in changing that?

 

I got this error after changing that: The used SELECT statements have a different number of columns

any idea what that is all about?

[viviosoft]

Yes, you are correct in changing that.  just define your selection using the *

 

SELECT * FROM wall 
UNION
SELECT * FROM feed
ORDER BY date ASC

[phpsycho]

huh I got the same error message again,

The used SELECT statements have a different number of columns

[viviosoft]

Are there relationships between the two tables?  Meaning are the unique ID's that relate to each table you are querying?

[phpsycho]

yes there is:

 

date

time

id

by

[viviosoft]

So the ID for each table is related to each other?  So the feed id relates to the wall id or the wall id relates to the feed id?  We aren't talking about Auto Increment.  You should have a related identifier?  Need to know so we can go in a different direction. 

[phpsycho]

hmm no? sorry a little confused. the field "id" is in both tables. there is no relation though. just two different tables that I want to combine so data streams from both.

[viviosoft]

Are the two related?  Meaning, is your wall table related to the feed table?  I would have to think they are being that you want to combine them.  You should add another column in your feed table and call it something like wall_id.  This is the relationship to your wall table.  So when you add data to your feed table you will add the related wall id to the feed table. Understand? 

[phpsycho]

Yeah I understand a little better now. But I don't think I should do that... I mean the feed table is for latest activity and wall is for comments. I just wanted to combine them, kinda like facebook ya know?

[viviosoft]

I'm confused now... lol.  Why not just do two separate SELECT queries?  I believe you said you tried this before?  I believe the reason you are getting the results looping thru ten times is because you have a users select array within your feed / wall array query.  Try removing the users query and see what the output might look like.

 

 

[phpsycho]

lol sorry so aint I. I want to combine the querys because I want wall data to be mixed with feed data. I don't want all wall data and then feed data under it. I want it all together and organized by time/date.

 

and I just tried that.. still got about 10 or 13 copies of one row

[viviosoft]

Okay, so using the UNION method.  NULL the columns in the second table "feeds" so that both have the same number of columns.  column4 is a filler and is intended to match the missing columns.  This should do what you are after.

 

SELECT wall, date, time, message FROM wall 
UNION
SELECT pageid, by, type, column4 AS null  FROM feed
ORDER BY wdate ASC