combining two tables

[phpsycho]

I am trying to combine two tables into one query. This is what I tried but it wont work:

 

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


echo '<table width="100%">';
$statsql="SELECT * from `wall`,`feed` WHERE ORDER BY `id` DESC LIMIT 0,10";
$statres=mysql_query($statsql) or die(mysql_error());

while($statrow=mysql_fetch_array($statres)){
	$vmessage=clean_up($statrow[message]);
	$date=clean_up($statrow[date]);
	$time=clean_up($statrow[time]);
	$by=clean_up($statrow[by]);
	$type=clean_up($statrow[type]);


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

while($row=mysql_fetch_array($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'>
$vmessage</td></tr>";
}else{
echo "$type<br>";
}


}

echo "</table>";
?>

 

Could someone help me out? Thanks!

[phpsycho]

I think I am a little confused.. what I am needing is data from two tables but I want to use one query. Is that possible?

[Pikachu2000]

What error is the query returning?

[phpsycho]

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY `id` DESC LIMIT 0,10' at line 1

[Pikachu2000]

Remove 'WHERE' and see if it runs without errors.

[jcbones]

You have an empty WHERE clause.  Which just needs to be removed.  What you do need is some table comparisons as well as identifying the returned result set.

 

SELECT * from `wall`,`feed` WHERE ORDER BY `id` DESC LIMIT 0,10

//should be.

SELECT `wall`.*, `feed`.* from `wall` JOIN `feed` ON `wall`.`id` = `feed`.`id` ORDER BY `wall`.`id` DESC LIMIT 0,10 //make sure the comparison in the ON clause is correct.

[phpsycho]

Oh oops, must have left that WHERE after editing.

 

So I got this now

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


echo '<table width="100%">';
$statsql="SELECT `wall`.*, `feed`.* from `wall` JOIN `feed` ON `wall`.`id` = `feed`.`id` ORDER BY `wall`.`id` DESC LIMIT 0,10";
$statres=mysql_query($statsql) or die(mysql_error());

while($statrow=mysql_fetch_array($statres)){
	$message=clean_up($statrow[message]);
	$date=clean_up($statrow[date]);
	$time=clean_up($statrow[time]);
	$by=clean_up($statrow[by]);


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

while($row=mysql_fetch_array($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>";
}

echo "</table>";
?>

 

So how do I pull information from feed or wall? Right now it spits out a blank page.

[phpsycho]

bump 

[phpsycho]

okay I think I have this right, in a way.

 

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


echo '<table width="100%">';
$statsql="SELECT feed.id AS fid, wall.id AS wid, feed.type AS ftype, feed.by AS fby, feed.pageid AS fpageid from `feed`, `wall` ORDER BY `wall`.`id` DESC LIMIT 0,10";
$statres=mysql_query($statsql) or die(mysql_error());

while($statrow=mysql_fetch_array($statres)){
	$message=clean_up($statrow[message]);
	$date=clean_up($statrow[date]);
	$time=clean_up($statrow[time]);
	$by=clean_up($statrow[by]);


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

while($row=mysql_fetch_array($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>";
}


$fid=clean_up($statrow[fid]);
$pageid=clean_up($statrow[fpageid]);
$type=clean_up($statrow[ftype]);
$by=clean_up($statrow[fby]);
include("../includes/activity_info.php");


echo "</table>";
?>

 

Thats what I have now, am I doing that right? The whole AS thing?

the activity info include is where it displays the feed table. Right now I am getting a blank page still. Did I do that right?

[phpsycho]

Sorry I keep posting.. 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.

 

this is the code that I have now after messing around with it

 

<?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>";
?>

[phpsycho]

Sorry to be a bother, but I think I should have this in the mysql board. Could someone move this topic there? Thanks

[jcbones]

You should try something like:

<?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 `feed` JOIN (`wall` LEFT JOIN `users` ON `users`.`id` = `wall`.`by`) ORDER BY wdate ";
$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']);
	$avatar=clean_up($row['avatar']);
	$first=clean_up($row['first']);

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

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

echo "</table>";
?>

 

I think you should make sure you Normalize your database.  Take the time to go through

, that would get you well on your way to understand the importance of making sure your data can be accessed in a Normal form.

[phpsycho]

That works but not totally what I want.. what I want is for the data to be mixed together, organized by date and time. is that possible?

[phpsycho]

bump