Problems with 2 select statements!

[andrew_biggart]

What im trying to do is first of all select all of the recent posts made by users and display them and then i want so get the usernames of the people who made the post, once i have got these i want to select all users which are = to $rows post_usersnames and get there profile pic and display it beside the post.

 

Its hard to explain this properly when ur this dumb lol but hopefully u can make some sense of it. Im using the following code to try and do it but im just getting a banc screen, although when i remove the scond select statement the post info dispays correctly.

 

<?php

include("config_blog.php");


// Retrieve data from database 
$sql="SELECT * FROM User_postT ORDER BY Post_id DESC LIMIT 30" ;
$result=mysql_query($sql);

// Start looping rows in mysql database.
while($rows=mysql_fetch_array($result)){

// Retrieve data from database 
$sql2="SELECT * FROM User_infoT WHERE username=$rows Post_username" ;
$result2=mysql_query($sql2);

// Start looping rows in mysql database.
while($rows2=mysql_fetch_array($result2)){

?>
<table class="profileforum">
<tr><td style="width: 55px"><? echo "<img src='../Thumbnail_images/". $rows2['Profile_picture'] . "' style='width:50px; height:50px;' />";?></td>
<td>
<table class="forum_post" cellspacing="0" cellpadding="0" style="width: 345px; height: 52px">
<tr><td class="forum_sub">Posted by <a class="posts1" href="profile.php?username=<? echo $rows['Post_username']; ?>"><? echo $rows['Post_username']; ?></a> on <? echo $rows['Post_date']; ?></td></tr>
<tr><td class="forum_h" valign="top"><a class="posts2" href="view_topic.php?Post_id=<? echo $rows['Post_id']; ?>"><? echo $rows['Post_subject']; ?></a></td></tr>
</table>
</td></tr>	
</table>
<?
// close while loop 
}

// close connection 
mysql_close();
?>

[Mark Baker]

Try reading the MySQL manual section on JOINS

[andrew_biggart]

i just cant get my head around it at all!! is it really that difficult or am i just being retarded?

[Mark Baker]

I'm not an expert at Joins in MySQL... I tend to do most of my database coding with the Oracle database, and have got into the bad habit of using Oracle's proprietary "shortcut" syntax for joins. You're probably better trying to get one of the mySQL experts on the forum to help with the syntax for a join.

 

However:

$sql2="SELECT * FROM User_infoT WHERE username='".$rows['Post_username']."'";

might fix your problem in the interim.

 

The JOIN really is a better solution though. If your "outer" SQL query returns the 30 row limit, then you're executing an additional 30 SQL queries in the "inner" loop, totalling 31 queries. Using a JOIN, this number would be reduced to 1 query. Database access is slow, so the JOIN would significantly improve the speed of your script, and the code logic would be simpler with only a single while loop.

 

[Mark Baker]

The MySQL join would look something like the following:

SELECT Posts.*,
  FROM User_postT AS Posts
  LEFT JOIN User_infoT AS Users ON (Users.username = Posts.Post_username) 
ORDER BY Posts.Post_id DESC 
LIMIT 30