phpsycho Posted April 3, 2011 Share Posted April 3, 2011 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! Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/ Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196410 Share on other sites More sharing options...
Pikachu2000 Posted April 4, 2011 Share Posted April 4, 2011 What error is the query returning? Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196413 Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196414 Share on other sites More sharing options...
Pikachu2000 Posted April 4, 2011 Share Posted April 4, 2011 Remove 'WHERE' and see if it runs without errors. Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196424 Share on other sites More sharing options...
jcbones Posted April 4, 2011 Share Posted April 4, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196426 Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196436 Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 bump Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196603 Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196634 Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 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>"; ?> Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196682 Share on other sites More sharing options...
phpsycho Posted April 4, 2011 Author Share Posted April 4, 2011 Sorry to be a bother, but I think I should have this in the mysql board. Could someone move this topic there? Thanks Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196840 Share on other sites More sharing options...
jcbones Posted April 5, 2011 Share Posted April 5, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1196963 Share on other sites More sharing options...
phpsycho Posted April 5, 2011 Author Share Posted April 5, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1197487 Share on other sites More sharing options...
phpsycho Posted April 6, 2011 Author Share Posted April 6, 2011 bump Quote Link to comment https://forums.phpfreaks.com/topic/232603-combining-two-tables/#findComment-1197729 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.