How to do a select based on data from another table

Question

How to do a select based on data from another table

Navigation

#1 by (3 votes)

4

I have to make the following adaptation in a query:

Table app_usuários : id, nome, sobrenome, hash, sexo, interesse, latitude e longitude .

Table app_likes : id, user_a_id, user_b_id

Briefly, I have a query that takes users based on gender and interest within a 25-kilometer radius.

What I need to do: I have to do the query not get users I already gave like accessing the app_likes table, I know I have to do this with inner join , but how? Consider that user_a_id would be the id of who gave like.

My query:

$teste  = $app->database->query("SELECT user_id, user_firstname, latitude, longitude,
(6371 * acos(
 cos( radians(".$app->session->user_data[0]['latitude'].") )
 * cos( radians( latitude ) )
 * cos( radians( longitude ) - radians(".$app->session->user_data[0]['longitude'].") )
 + sin( radians(".$app->session->user_data[0]['latitude'].") )
 * sin( radians( latitude ) ) 
 )
) AS distancia
FROM app_users ".$query."
HAVING distancia < 25
ORDER BY distancia ASC
LIMIT 4;", $array);

Where the $ query and $ array variables come from:

if($app->session->user_data[0]['user_interest'] == "m" || $app->session->user_data[0]['user_interest'] == "f"){
  $query = "WHERE user_gender = :ug AND (user_interest = :uin OR user_interest = :reserved) not in (".$app->session->user_data[0]['user_id'].")";
  $array = array("ug"=>$app->session->user_data[0]['user_interest'], "uin"=>$app->session->user_data[0]['user_gender'],"reserved"=>"fm");
} else if($app->session->user_data[0]['user_interest'] == "fm"){
  $query = "WHERE user_interest = :uin OR user_interest = :reserved not in (".$app->session->user_data[0]['user_id'].")";
  $array = array("uin"=>$app->session->user_data[0]['user_gender'],"reserved"=>"fm");
}

php mysql sql

asked by anonymous 28.08.2018 / 12:04

1 answer

Create an extension for visual studio that dynamically adds classes How do I correctly access elements of a dynamic array via pointer?

score 3 · Accepted Answer

In order not to catch the users who already gave like would have to make a LEFT JOIN with the app_likes table:

$teste  = $app->database->query("   SELECT      user_id
                                            ,   user_firstname
                                            ,   latitude
                                            ,   longitude
                                            ,   (6371 * acos(       cos(radians(".$app->session->user_data[0]['latitude'].")) 
                                                                *   cos(radians(latitude)) 
                                                                *   cos(radians(longitude) - radians(".$app->session->user_data[0]['longitude'].")) 
                                                                +   sin(radians(".$app->session->user_data[0]['latitude'].")) 
                                                                *   sin(radians(latitude)) 
                                                            )
                                                ) AS distancia
                                    FROM        app_users au
                                    LEFT JOIN   app_likes al ON al.user_b_id = au.id 
                                                            AND al.user_a_id = ".$app->session->user_data[0]['user_id']."
                                    WHERE       al.id IS NULL
                                            AND ".$query."
                                    HAVING      distancia < 25
                                    ORDER BY    distancia ASC
                                    LIMIT 4;", $array);

You'll need to remove% w / o% of your% w / _% variable and place parentheses where you use% w /%, otherwise you'll mix the results.

Note : I did not put quotes in the WHERE condition because I do not know what kind of data it is, but if it is $query or a%