Multiple Database Tables

Databases
1

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:


$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

could you do a SHOW CREATE TABLE for both tables please

3

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

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

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

i’m sorry, i don’t understand what you’re doing at all

7

I really wish I could just say “SELECT * FROM team, users” and it would just have all of the information available.

8

since those two tables aren’t related, then what you might be thinking of is a UNION query

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

9 

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