Jump to content


Photo

Using two tables at once


  • Please log in to reply
6 replies to this topic

#1 Yesideez

Yesideez
  • Members
  • PipPipPip
  • Advanced Member
  • 2,334 posts
  • LocationDevon, UK

Posted 22 August 2006 - 01:02 PM

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 :'(
Not a pro just an enthusiast :)

if (empty($coffee)) {$coffee=new coffee();}

Please surround any code using the CODE tags - I rarely look at anything without them

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 August 2006 - 01:44 PM

Sounds like you need a JOIN... what is it that you want back?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 Yesideez

Yesideez
  • Members
  • PipPipPip
  • Advanced Member
  • 2,334 posts
  • LocationDevon, UK

Posted 22 August 2006 - 02:53 PM

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:
$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']
}

Not a pro just an enthusiast :)

if (empty($coffee)) {$coffee=new coffee();}

Please surround any code using the CODE tags - I rarely look at anything without them

#4 Yesideez

Yesideez
  • Members
  • PipPipPip
  • Advanced Member
  • 2,334 posts
  • LocationDevon, UK

Posted 22 August 2006 - 04:54 PM

I've been looking around and have come up with this:
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
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.
Not a pro just an enthusiast :)

if (empty($coffee)) {$coffee=new coffee();}

Please surround any code using the CODE tags - I rarely look at anything without them

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 August 2006 - 04:56 PM

That's about right... though you should use proper JOIN syntax.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 Yesideez

Yesideez
  • Members
  • PipPipPip
  • Advanced Member
  • 2,334 posts
  • LocationDevon, UK

Posted 22 August 2006 - 05:03 PM

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.
Not a pro just an enthusiast :)

if (empty($coffee)) {$coffee=new coffee();}

Please surround any code using the CODE tags - I rarely look at anything without them

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 August 2006 - 11:37 PM

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users