[SOLVED] PHP, MySQL and SELECT from multiple tables

[zssz]

Right now, I have an sql structure for the table 'users' that looks sort of like this:

username - varchar

clanid - int

userid - int, primary key

 

Then another table 'comments' that looks sort of like this:

comment - varchar

username - varchar

commentid - int, primary key

 

 

What I want to do is change the 'username' row in 'comments' to be the 'userid' row in 'users'. I know how to convert to this, I just don't know about this next part:

How could I make the 'userid' translate into the username when it's displayed on the website? I know of a way, but it would involve selecting the new 'userid' row, then selecting the 'username' row of the 'users' table where the userid = the new 'userid'. The problem with this is, it would take multiple queries. I would like to be able to shorten something like this into 1 query:

$sql = mysql_query("SELECT userid FROM comments");
while ($row = mysql_fetch_array($sql)) {
$userid = $row['userid'];
$sql2 = mysql_query("SELECT username FROM users WHERE userid=$userid");
}

 

Sorry, I'm not the greatest on terminology. I hope you can understand what I am trying to say.

[xtopolis]

SELECT u.username, c.comment FROM users u
JOIN comments c
USING userid

I think.  (Should output a result set of: username - comment)

[zssz]

I get this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/lezssz/public_html/csystem/test.php on line 14

 

With this code:

$sql = mysql_query("SELECT u.username, c.comment FROM users u JOIN comments c USING userid");

while ($row = mysql_fetch_array($sql)) {
echo $row['username'] . "<br>" . $row['comment'];
}

[xtopolis]

SELECT u.username, c.comment FROM users u

JOIN comments c

USING (userid)

 

try that with the ()

 

If you still have errors, try it in PHPmyadmin or mysql console to see if it even works.  If not, post a dump of your structure, or the errors encountered.

[zssz]

That didn't work. I did some reading up about JOINS from multiple sources, and I only saw them using your way with Left joins, I got it to work finally with:

SELECT u.username, c.comment FROM temp1 u, temp2 c WHERE u.userid=c.userid AND u.userid=1

 

Thanks for your assistance!

[zssz]

Since I forgot to include this in my first post (and other posts on this topic), is there any script execution time or efficiency difference when using this? The friend that told me to look into select from multiple tables was more efficient. I'd just like to be able to know for sure.

[xtopolis]

I'm not sure, perhaps fenway will weigh in on this later.

 

The thing to think about is:  is it significantly faster for your application's context?  Will the 0.002 seconds you save by doing it one way really matter?  Does your site get top 50,000 traffic, etc?  It's all relative.

 

You can also test it in your mysql console/phpmyadmin and see what the time it takes (it tells you) is.., but I don't know off hand.

Also, it's hard for me(us) to help your with "pseudo"structures like posted in your first comment... fyi.

[zssz]

Actually, that was a real structure. I just left out some of the rows that weren't necessary, like time of post, password, etc. Don't know why I left clanid in there, oh well.

 

The system I am working on will allow clans and their members to sign up and find matches with other clans (similar to GameBattles, but on a smaller scale). The part I was working on today was redoing the commenting on profiles/matches, hence why you see temp1 and temp2 being used in my test code. I didn't want to alter or mess up anything in the real tables, so I created copies of the real ones and converted usernames to userid's to be able to "play around" with.

 

And the reason I ask about time/efficiency is about another project of mine which handles tons of information that gets imported into a database (~460k queries an hour according to MySQL) then processes and displays it depending on certain criteria. And I'd like to be able to use id's rather than names for that project as well. So, yea.