Zymic Webmaster Forums > 2 Mysql Queries, One Used To Make The Other Work ?

Full Version: 2 Mysql Queries, One Used To Make The Other Work ?

Zymic Webmaster Forums > Web Design & Development > Server Side Scripting > PHP

MrTouz

Jan 24 2009, 03:38 PM

Dunno how to explain really well. I am pasting my code (don't laugh) i know it's possible but the way i am doing it.. there must be a simpler way OR a better way...

CODE

                <?php
                    include ('connect.php');
                      $result = mysql_query("SELECT * FROM user_post ORDER BY id DESC LIMIT 5");
                    while($row = mysql_fetch_array($result))
                        $avatar = mysql_query("SELECT * FROM users_info WHERE username=".$row['username']."");
                        $show = mysql_fetch_array($avatar);
                    {
                    echo "<div><img src=\"".$show['avatar']."\" width=\"100\" height=\"100\" style=\"float:left; margin-right:5px;\">".$row['username']." the ".$row['date']." <a href=\"#\">Read More...</a></div>";
                    }
                    mysql_close($con);
                ?>

Basically what i am trying to do, is my user posted a message, the first QUERY shows the last 5 posts by users. In my USER_POST table there is the post, the id, the username, the date.
What i want is to display the username, the date and the avatar of the user that posted the message. To do that i need to retrieve the info from the USERS_INFO table matching the USERNAME and selecting the avatar... that's what the second query is for. (called $avatar).

If i remove the query AVATAR the script works just fine, it displays the five LAST posts from my users and shows me the date and the link... but i want to have there AVATARS next to it, so i made something up and it says :

QUOTE

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /homepages/23/d222196706/htdocs/rabfly/index.php on line 29

Line 29 is the AVATAR fetch array. I am guessing its because this :

CODE

FROM users_info WHERE username=".$row['username']."");

Is not possible ? i mean username=".row['username']."

______________________

My idea was that when the user posts, i create a new row called useravatar, and in a hidden field in my form it echos the users avatar's link. and i just have to retrieve that link from the same query.... but imagine the user changes his avatar ? The script will keep his OLD avatar since its saved in the table user_post and not in users_info so it will not update....

Booooooooo am i going crazy ? Do i make any sense ?

swordz

Jan 24 2009, 07:17 PM

You want table joins.

CODE

SELECT * FROM user_post INNER JOIN users_info ON user_post.username = users_info.username;

I've never used a join, so let me know if this works! I think all fields other than username probably need to be unique, but I'm not sure...

swordz

MrTouz

Jan 24 2009, 07:47 PM

That's the very first time i see this. Very interesting.

Well, i am about to use it right now and i will let you know.

Thank you.

Alex

Jan 24 2009, 08:03 PM

In addition to using a join, it's worth noting your braces seem to be in the wrong place. That loop would only work for one member (the last one returned).

MrTouz

Jan 24 2009, 08:23 PM

It works perfectly. Exactly what i wanted to do. Amazing.

Im getting closer to opening my test site. Very happy, thanks to you.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.