Join tables using a foreign key

Question

I get institutes within 10 km in $ result. But I want institutions with a course name to have a GATE. How can i do this? The institutes of course_records have an institute of a foreign institute. I cannot join these tables. Any help would 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 | [e-mail] | 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:

 $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 fields from both tables as a selection field using an alias of the cr.subject table type for the course topic from the course_records table.

Answer 2

I am not sure how you calculate the distance, and I will not go into details. Please double-check your formula. Assuming you fix it, try something like this. Here latitude and longitude hardcoded, but you can change it the way you want for php . Also, the distance I'm 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' 

Open the SQLFiddle demo here

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

Answer 3

 $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 the code above. Hope this works for you.