Jump to content

Using two tables at once


Yesideez

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 :'(
Link to comment
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]
Link to comment
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.
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.