Take data from two tables at the same time, just when you log in

Asked

Viewed 60 times

0

I’m trying to get data from two tables right at the beginning of the session when the user logs in, when I use the

SELECT * FROM usuarios, orders WHERE email = '$login' and senha= '$senha'

I can log in quietly, until then good, more when I modify the code to

$comando="SELECT * FROM usuarios INNER JOIN orders ON usuarios.id_usuario = orders.id_usuario WHERE email.usuarios='$login' and senha.usuarios='$senha'";

It only lets me log in if the user already has some Orders (request). What I am trying to do is as soon as the user logs in, also show the requests he has already made etc, which are data that are in another table. Someone can see my mistake?

php html mysql mysqli

1 answer

1

Ignoring other possible problems. What you probably want to do is LEFT JOIN and not a INNER JOIN.

Try using the LEFT JOIN, instead of INNER JOIN:

SELECT * FROM usuarios LEFT JOIN orders ON usuarios.id_usuario = orders.id_usuario WHERE email.usuarios='$login' and senha.usuarios='$senha'

The LEFT JOIN must obtain all data from usuarios and, if there is any match he will catch the orders. There is this answer with a comparison of all JOIN. Note that you are using email.usuarios, maybe it’s usuarios.email, but I just copied as it is in your question.

  • Personally, I would still prefer to do two distinct queries, in different roles.

  • Now I can’t get some table data as Id_usuario

  • Users who have Orders(requests) I can get the id_user table users, plus those who do not have Orders, it does not return me the id_user

  • That’s because the id_usuario is present in both tables, is ambiguous, so if you try to catch id_usuario it will pick up from any one. You have to specify usuarios.id_usuario (I no longer use PHP, I don’t remember if it was possible) or select with an alias (SELECT *, usuarios.id_usuario as id_usuario FROM ....), this way you force that id_usuario be the usuarios.id_usuario.

  • Then it would be "SELECT usuarios.id_usuario as id_usuario * FROM usuarios LEFT JOIN Orders ON usuarios.id_usuario = Orders.id_usuario WHERE usuarios.email='$login' and usuarios.password='$password'" ?

  • @Edvan, basically. But, the * has always come before (SELECT *, usuarios.id_usuario as id_usuario FROM ...) and not (SELECT usuarios.id_usuario as id_usuario, * FROM ....), or replace with the columns you are actually using (which is preferable).

  • Lord God bless you :)

  • For example if I have 2 requests from the same Id_usuario, have to make it return me all the requests made by the same Id_usuario, or I would have to use a new query with WHILE?

  • Each order will be a line, you will have to access each one using the mysqli_fetch normally. You can make a foreach(mysqli_fetch_array($con, $result) as $row) {
// var_dump($row);
}

  • There is how I do this using the data I already have from the user’s SESSION, or I will have to make a new SQL query

  • Forgive me if I’m being uncomfortable, I’m not very familiar with PHP

Show 6 more comments

Browser other questions tagged php html mysql mysqli

You are not signed in. Login or sign up in order to post.