Select multiple to avoid 2 queries to the database

Question

Select multiple to avoid 2 queries to the database

Navigation

#1 by (2 votes)

0

I have the following select (using PDO):

$usuariosListados = $pdo->prepare("SELECT user_1, user_2 FROM usuarios WHERE user_1 = '".$_SESSION["usuario"]."' AND estatus = 2");
$usuariosListados -> execute();
$fAll = $usuariosListados -> fetchAll(PDO::FETCH_ASSOC);
$rCount= $usuariosListados -> rowCount();


// E o seguinte foreach
if(rCount) {
    foreach($fAll as $itemUser) {
        echo "Usuário amigo: ".$itemUser["user_2"];
    }
} else {
    echo "Nenhum registro encontrado.";
}

However, you would need to make another select, but in the following way:

$usuariosListados_2 = $pdo->prepare("SELECT user_1, user_2 FROM usuarios WHERE user_2 = '".$_SESSION["usuario"]."' AND estatus = 2");

And the foreach would be:

foreach($fAll_2 as $itemUser_2) {
    echo "Usuário amigo: ".$itemUser["user_1"];
}

I would like to know if there is any way to "join" these selects thus avoiding 2 queries to the database. Remembering that, user_1 and user_2 will never be equal, and I need the value that corresponds to the "opposite" of the logged in user (which is $_SESSION["usuario"] ).

php select

asked by anonymous 20.07.2015 / 23:35

1 answer

Do not move the mouse with p: dialog how to view related posts through php tags

score 2 · Accepted Answer

You can make an appointment like this:

"SELECT user_1, user_2 FROM usuarios 
WHERE '" . $_SESSION['usuario'] . "' IN ( user_1, user_2 ) " .
"AND estatus = 2"

This query returns any user that is in user_1 or user_2 that equals $_SESSION['usuario'] .

Explanation

If you are wanting both user_1 where equal $_SESSION['usuario'] and also and user_2 where equal $_SESSION['usuario'] , the query above gives just that - ie:

I query user_1 and user_2 on the users table where $ _SESSION ['user'] is equal to at least one of the two, and also both have the status of 2.

There, your% w / o will contain two (or more) results, one being $usuariosListados->fetchAll(PDO::FETCH_ASSOC) and the other user_1 .