How to make a select based on data from another table

Asked

Viewed 55 times

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 picks users based on sex and interest in a radius of 25 kilometers.

What I need to do: I have to make the query not catch users I’ve given like accessing the table app_likes, 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

1 answer

3


To avoid catching users who have already given like would have to make a LEFT JOIN with the table app_likes:

$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 will have to remove the WHERE of your variable $query and place a parenthesis where you use the OR, otherwise will mix the results.

Note: I didn’t put requests in the condition AND al.user_a_id = ".$app->session->user_data[0]['user_id']." because I don’t know what kind of data it is, but if it is string or a guy UNIQUEIDENTIFIER should place.

  • 1

    I updated the question and put where the $query and $array variables come from

  • 1

    Edited response to consider variable content $query.

  • John, thank you for your help. But I don’t think you got it right. Thus, if user_b_id is equal to the id of the record that came from the query, it would not be included in the result.

  • 1

    Said the camp user_a_id was the field that should link to the table app_likes, and if so, connecting the two tables with the LEFT JOIN and saying that we do not want the records of users who have already given like (have a record in the table app_likes), then I guess that’s what I was asking, right?

  • Come on. I’ll try to explain using algorithm. [Select 4 users in a radius of 25 kilometers, also check if any user has been returned where I have given like using the app_likes table, whereas user_a_id is my id and user_b_id is the id of other users, if it is found do not return these results]

  • I simply want the query to return users with the requirements defined in WHERE and the query to delete users I have already given like, using the app_like table. Whereas user_a_id is my id and user_b_id is the id of the user I gave like to.

  • 1

    Okay, it’s tough. What’s being returned on query initial?

  • The $query variable sends the user preferences for the query.

  • If the user is bisexual, have men and women selected who are interested in his gender. If the user is straight, select the opposite sex that is interested in his gender. This is how the $query variable works. The $array variable contains the data of these preferences.

  • Consider that $app->Session->user_data[0] contains the user data that is logged in

  • 1

    I think I’ve got the set! I edited the answer to JOIN capture the results in which the user represented by user_b_id has been Likes and at the same time that Likes come from the user who is logged in, then in the clause WHERE we say that all these results should be removed from the query.

  • Man, you are awesome! It worked perfectly.

Show 7 more comments

Browser other questions tagged php mysql sql

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