Jump to content


Photo

Need Help With Two Tables


  • Please log in to reply
4 replies to this topic

#1 Avalanche

Avalanche
  • Members
  • PipPipPip
  • Advanced Member
  • 73 posts

Posted 12 September 2003 - 09:08 PM

Okay, I am making a membership system, and there are two tables for the members. One is tbl_users (has username, password, email, and rank), and the other is tbl_userinfo (has 10+ columns of other information). So, I split it up into two different tables so it wouldn\'t get to be a ginormous table.

The rank that I mentioned in tbl_users can be admin, user, or unset. \"Unset\" means that the account hasn\'t been verified yet, and it doesn\'t let you log in if your account is \"unset.\"

Now, on the page where it lists the users (but doesn\'t have the whole profile) it uses information only from the tbl_userinfo, and doesn\'t draw anything from tbl_users. However, I need it to only select users information from tbl_userinfo where their rank in tbl_users is not \"unset.\"

I\'m not sure exactly how I could do this with my current table setup. Would I just have to move the rank over to tbl_userinfo?

I you are able to understand what I mean. Thanks.

PS: I have an id_key in the tbl_users, and a parent_id_key in the tbl_userinfo that are identical for each account.
DEFINE("YOU","Spending Too Much Time On The Computer");

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 12 September 2003 - 09:49 PM

hmm. can we get some visuals? and the table structures?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 12 September 2003 - 11:46 PM

Okay, I am making a membership system, and there are two tables for the members. One is tbl_users (has username, password, email, and rank), and the other is tbl_userinfo (has 10+ columns of other information). So, I split it up into two different tables so it wouldn\'t get to be a ginormous table.

The rank that I mentioned in tbl_users can be admin, user, or unset. \\\"Unset\\\" means that the account hasn\'t been verified yet, and it doesn\'t let you log in if your account is \\\"unset.\\\"

Now, on the page where it lists the users (but doesn\'t have the whole profile) it uses information only from the tbl_userinfo, and doesn\'t draw anything from tbl_users. However, I need it to only select users information from tbl_userinfo where their rank in tbl_users is not \\\"unset.\\\"

I\'m not sure exactly how I could do this with my current table setup. Would I just have to move the rank over to tbl_userinfo?

I you are able to understand what I mean. Thanks.

PS: I have an id_key in the tbl_users, and a parent_id_key in the tbl_userinfo that are identical for each account.


You simply need to join the tables together. In SQL you are always returned a \"set\" ie. a table. Joining two or more tables together creates what you could think of as a temporary table. If I understand you correctly you have two tables that you can join by id_key and parent_id_key.


So something like:

SELECT * FROM
table a, table b
WHERE a.id_key = b.parent_id_key AND
rank <> \'unset\'

#4 Avalanche

Avalanche
  • Members
  • PipPipPip
  • Advanced Member
  • 73 posts

Posted 13 September 2003 - 02:52 AM

So in my case it would be like:

[php:1:ed34137c45]SELECT * FROM
tbl_users, tbl_userinfo
WHERE tbl_users.id_key = tbl_userinfo.parent_id_key AND
rank!=\'unset\'[/php:1:ed34137c45]

Also, would I have to put \"AND tbl_users.rank ...\"?

Thanks a bunch!
DEFINE("YOU","Spending Too Much Time On The Computer");

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 13 September 2003 - 07:37 AM

Also, would I have to put \\\"AND tbl_users.rank ...\\\"?


You shouldn\'t have any fields in both tables except the id (so you can join them)

If you don\'t want password or email selected , you can be more selective by

[php:1:81b203b108]<?php
SELECT tbl_userinfo.*, tbl_users.username, tbl_users.rank FROM
tbl_users, tbl_userinfo
WHERE tbl_users.id_key = tbl_userinfo.parent_id_key AND
rank!=\'unset\'
?>[/php:1:81b203b108]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users