Jump to content

Archived

This topic is now archived and is closed to further replies.

Yesideez

Using two tables at once

Recommended Posts

I've got one table (called "users") which has these two fields:
userid: INT UNSIGNED
username: VARCHAR(20)

I've also got a table called "inbox" which contains these fields:
inboxid: INT UNSIGNED
ownerid: INT UNSIGNED
fromid: INT UNSIGNED

In the "inbox" table the last two fields link back to the the "users" table. I'm storing just the user's ID numbers in the "inbox" table and wish to use the "users" table to get their usernames.

Please would someone be kind enough to show me how to link these together in one SELECT query because at the moment I have a for() loop going through the inbox pulling out all the ownerid's messages then I have another SELECT query pulling out the username from the "users" table for the "fromid" for each piece of data retrieved.

I spent a while trying to do this using the MySQL manual but its proving too much :'(

Share this post


Link to post
Share on other sites
Sounds like you need a JOIN... what is it that you want back?

Share this post


Link to post
Share on other sites
I need to get the username from the "users" table.

For the "inbox" table instead of storing the usernames in VARCHAR fields I've opted to use integer fields instead.

This isn't the actual code I'm using as I'm currently at work but this should give an idea:
[code]$query=mysql_query("SELECT * FROM inbox WHERE (`ownerid`='$userid') AND (`read`='0') ORDER BY `dt` DESC");
while ($fetch=mysql_fetch_array($query)) {
  $fromid=$fetch['fromid'];
  $getname=mysql_fetch_array(mysql_query("SELECT username FROM users WHERE `userid`='$fromid'"));
  echo 'Message from '.$getname['username'].' sent at '.$fetch['dt']
}[/code]

Share this post


Link to post
Share on other sites
I've been looking around and have come up with this:
[code]SELECT inbox.*,users.`username` FROM inbox,users WHERE (inbox.`toid`='".$user_id."') AND (inbox.`read`='0') AND (inbox.`deleted`='0') ORDER BY inbox.`inboxid` DESC[/code]
Before I change loads of code to test it I'd really appreciate it if someone could tell me whether this is what I need to do or even if I'm on the right path.

Share this post


Link to post
Share on other sites
That's about right... though you should use proper JOIN syntax.

Share this post


Link to post
Share on other sites
I've been looking at the syntax for the JOIN syntax and really can't get my head around it even by looking at the script to something which is doing something similar in something I help run.

Would I be asking too much if you could write the query for me? At least seeing the query on something I'm creating rather than helping on would help me understand this a whole lot more.

Share this post


Link to post
Share on other sites
[code]
SELECT inbox.*,users.username
FROM inbox
JOIN users ON (inbox.toid='".$user_id."')
WHERE inbox.read='0' AND inbox.deleted='0'
ORDER BY inbox.inboxid DESC
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.