Join tables using foreign key

Question

I am getting the institutes within 10km in $result. But I want to get the institutes with course name having GATE. How can I do this? course_records has a foreign key institute_id in institutes. I am not able to join these tables. Any kind of help will be appreciated.

 $result=$conn->query("SELECT *, ( 6371 * acos( cos( radians($user_latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians($user_longitude) ) + sin( radians($user_latitude) ) * sin( radians( latitude ) ) ) ) AS distance FROM institutes  HAVING distance < 10 ORDER BY distance  LIMIT 0 , 10 ");

mysql> select * from institutes;

+--------------+-------------------+---------------------+----------------+----------------------------------+-------------+--------+----------+-----------+
| institute_id | name              | email               | contact_number | address                          | telephone   | tut_id | latitude | longitude |
+--------------+-------------------+---------------------+----------------+----------------------------------+-------------+--------+----------+-----------+
|           23 | Dhananjay Classes | [email protected] | 9999888877     | Palam dabri Road,Mahavir Enclave | 011-1234567 |     11 |  28.5892 |   77.0858 |
|           24 | ffe               | rr@rere             | 323232         |                                  |             |     11 |  28.5667 |   77.2833 |
+--------------+-------------------+---------------------+----------------+----------------------------------+-------------+--------+----------+-----------+

mysql> select * from course_records;

+-----------+------+--------------+-------+--------------+--------------------------+--------------------+---------------+-----------+---------+--------------+
| course_id | name | subject      | fees  | num_students | num_students_per_teacher | month_of_admission | num_of_trials | commision | created | institute_id |
+-----------+------+--------------+-------+--------------+--------------------------+--------------------+---------------+-----------+---------+--------------+
|         1 | GATE | CSE          | 10000 | 110          | 20                       | January            | 3             | yes       | NULL    |           23 |
|         2 | NDA  | all_subjects | 7000  | 50           | 20                       | April              | 3             | yes       | NULL    |           23 |
|         3 | 12th | Math         | 2     | 90           | 20                       |                    | 2             |           | NULL    |           23 |
+-----------+------+--------------+-------+--------------+--------------------------+--------------------+---------------+-----------+---------+--------------+

Answer 1

try it with:

$result=$conn->query("SELECT
i.name as inst_name, cr.name as course_name,
( 6371 * acos( cos( radians($user_latitude) ) * cos( radians( i.latitude ) ) * cos( radians( i.longitude ) - radians($user_longitude) ) + sin( radians($user_latitude) ) * sin( radians( i.latitude ) ) ) ) AS distance
FROM
institutes i
join course_records cr on i.institute_id = cr.institute_id
where cr.name = 'GATE'
HAVING
distance < 10 ORDER BY distance  LIMIT 0 , 10 ");

you can add more field from both table as select field by using alias of table like cr.subject for course subject from course_records table.

Answer 2

I am not sure how are you calculating the distance and I am not going in its detail. Please crosscheck your formula for it. Assuming that you correct it, try something like this. Here the latitude and longitude is hardcoded but you can change it back the way you want for php. Also the distance I am using is 50.

select * from 
(
        SELECT
        i.*, c.name as course_name,
        ( 6371 * acos( cos( radians(28.5892) ) * cos( radians( i.latitude ) ) 
          * cos( radians( i.longitude ) - radians(77.0858) ) + sin( radians(28.5892) ) 
          * sin( radians( i.latitude ) ) ) ) 
        AS distance
        FROM
        institutes i
        inner join course_records c on i.institute_id = c.institute_id
) as dist
where dist.distance <50
and dist.course_name='GATE'

View SQLFiddle demo here

http://sqlfiddle.com/#!9/1a27f/10