SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Database Tables

    I need to grab information from multiple tables and it doesnt seem to work. I need to grab other info from a table named users and I named all of the fields a different name as well so they would not overwrite each other. Here is the code where I am calling it:

    PHP Code:
    $sql="SELECT * FROM team WHERE staff_user='$myusername' and staff_pass='$mypassword'";

    $result=mysql_query($sql);

    $count=mysql_num_rows($result);

    if(
    $count==1){

    session_register("myusername");

    while(
    $row mysql_fetch_assoc($result))

        {

            
    $_SESSION['staff_first_name']=$row['staff_first_name'];

            
    $_SESSION['staff_last_name']=$row['staff_last_name'];

            
    $_SESSION['staff_user']=$row['staff_user'];

            
    $_SESSION['staff_p_extranet']=$row['staff_p_extranet'];

            
    $_SESSION['staff_p_mail']=$row['staff_p_mail'];

            
    $_SESSION['staff_p_client_list']=$row['staff_p_client_list'];

        } 
    Any idea? I tried to do a SELECT * from team, users but it gave me an error.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE for both tables please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    team:
    Field Type Null Default Comments
    staff_id int(11) No
    staff_first_name varchar(255) No
    staff_last_name varchar(255) No
    staff_user varchar(255) No
    staff_pass varchar(255) No
    staff_p_extranet int(11) No
    staff_p_mail int(11) No
    staff_p_client_list int(11) No

    users
    Field Type Null Default Comments
    id int(255) No
    user varchar(255) No
    pass varchar(255) No
    folder varchar(255) No
    company varchar(255) No
    reseller varchar(255) No
    reseller_full varchar(255) No
    nav varchar(255) No

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    How are these tables related, if at all?

    I also don't see where you try to use the users table in your code anywhere?

    session_register() is deprecated, don't use it, and make sure you call session_start() before you put anything in $_SESSION.

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is that the tables are not related at all. Basically you login by your company name and password which is stored in the team table. Then there are links to all of the client accounts (which are pulling information from a separate database table named users). Once you click on the link you lose all the session variables but I was hoping that I could call them. The problem is that you have to be logged in to view the information and by that I mean logged into a user account and not the company account. I was hoping to fix this by having the company login access both tables so it could pull any of the information. It seems more complex than that though. I am using session_start() for both of the login sections. Is there a simple way to do this?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by codeguyz View Post
    Is there a simple way to do this?
    i'm sorry, i don't understand what you're doing at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really wish I could just say "SELECT * FROM team, users" and it would just have all of the information available.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by codeguyz View Post
    I really wish I could just say "SELECT * FROM team, users" and it would just have all of the information available.
    since those two tables aren't related, then what you might be thinking of is a UNION query
    Code:
    SELECT 'team'           AS source_table
         , staff_id         AS id
         , staff_first_name AS first_name
         , ...
      FROM team
    UNION ALL
    SELECT 'users'          AS source_table
         , id               AS id
         , NULL             AS first_name
         , ...
      FROM users
    the thing you need to be careful of here is that each SELECT must have the same number of columns, the column datatypes should be compatible, and you can use NULL placeholders when one of the tables is missing a corresponding column that the other has
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,184
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    PHP Code:
    $sql="SELECT team.* FROM team INNER JOIN users ON team.staff_user = users.user WHERE team.staff_user='$myusername' and team.staff_pass='$mypassword'";

    $result=mysql_query($sql);
    $count=mysql_num_rows($result);
    if(
    $count==1){
    session_register("myusername");
    while(
    $row mysql_fetch_assoc($result))
        {
            
    $_SESSION['staff_first_name']=$row['staff_first_name'];
            
    $_SESSION['staff_last_name']=$row['staff_last_name'];
            
    $_SESSION['staff_user']=$row['staff_user'];
            
    $_SESSION['staff_p_extranet']=$row['staff_p_extranet'];
            
    $_SESSION['staff_p_mail']=$row['staff_p_mail'];
            
    $_SESSION['staff_p_client_list']=$row['staff_p_client_list']
        } 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •