CODEWITHSUNDEEP
phpmysqlsql
Franck
Franck

Reputation: 41

How select data from TABLE 2 join with table 1

How i can to select the user that login for to show name in php, the table 1 is for the main users, and the table 2 is extra users, relationated with main user by ID and ID_ppl

I need show in php the user name that login. but only it show me the main user, Although I login with extra user, And it must show tha name extra user.

For example, i Login with Juana, and in php show me Andres, the main user; when it must show me Juana name

Table 1 Main users

id |  usuario  | password | token | nivel | estado | 
----------------------------------------------------
1  | Andres    | *****    | e12A1 | 1     | on     |
----------------------------------------------------

Table 2 extra users

id |  usuario  | password | token | id_ppl | nivel | estado |
-------------------------------------------------------------
1  | Juana     | *****    |       | 1      | 2     | on     |
-------------------------------------------------------------
2  | Martin    | *****    |       | 1      | 2     | off    |
-------------------------------------------------------------

Php query

$stmt = $conn->prepare("SELECT T1.id, T1.usuario, T1.token, T1.nivel, T1.estado FROM escolar AS T1 LEFT OUTER JOIN users_extra AS T2 ON T1.id = T2.id_ppl");

    $stmt->bind_param("ss",$usuario, $password); 
     $stmt->execute(); 
     $stmt->store_result(); 
     if($stmt->num_rows > 0){ 
     $stmt->bind_result($id, $usuario, $token, $nivel, $estado);
     $stmt->fetch();

    $user = array(
     'id' => $id,
     'usuario' => $usuario,
     'token' => $token,
     'nivel' => $nivel,
     'estado' => $estado
     );

Upvotes: 1

Views: 75

Answers (2)

Praveen
Praveen

Reputation: 41

Try this out

SELECT T1.id, T2.usuario, T1.usuario, T1.token, T1.nivel, T1.estado FROM escolar AS T1 LEFT OUTER JOIN users_extra AS T2 ON T1.id = T2.id_ppl;

Your'e not selecting any column from T2

Thank you ;)

Upvotes: 0

devlin carnate
devlin carnate

Reputation: 8592

You're only selecting from T1. You need to include the users from T2.

SELECT T1.*, T2.usuario AS extrausers
FROM escolar AS T1 
LEFT OUTER JOIN users_extra AS T2 ON T1.id = T2.id_ppl

Also, since there are multiple extra users in T2 per user in T1, you'll get multiple rows in your results (one row for each extra user). You may want to account for that in your php.

Upvotes: 2

Related Questions