Reputation: 990
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
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
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
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'
http://sqlfiddle.com/#!9/1a27f/10
Upvotes: 3