CODEWITHSUNDEEP
phpmysqlsql
tarun14110
tarun14110

Reputation: 990

Join tables using foreign key

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 |
+-----------+------+--------------+-------+--------------+--------------------------+--------------------+---------------+-----------+---------+--------------+

Upvotes: 5

Views: 185

Answers (3)

Chetan Ameta
Chetan Ameta

Reputation: 7896

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.

Upvotes: 3

WorksOnMyLocal
WorksOnMyLocal

Reputation: 1689

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

Just replace your code with the above, Hope this works for you.

Upvotes: 2

Utsav
Utsav

Reputation: 8143

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

Upvotes: 3

Related Questions