The search function I am making requires the number of results found to be updated on screen once a new search option is chosen.
To do this search I need to make the main result query (one for county and one for radius search) from other query parts stored as variables e.g. $county_query_part and $has_photo_query_part shown below:
SELECT member_age, postcode, member_id
FROM members
WHERE member_age > $age_1
AND member_age < $age_2
$county_query_part
AND sex <> (SELECT sex FROM members WHERE member_id = $member_id)
AND member_id <> $member_id
$has_photo_query_part
ORDER BY $sort_by ASC
and
SELECT p.Pcode, p.Grid_N, p.Grid_E, in_radius.Pcode, in_radius.Grid_N, in_radius.Grid_E, m.postcode, m.sex, m.member_age
FROM members m
INNER JOIN postcodes p
ON m.postcode = p.Pcode
INNER JOIN postcodes in_radius
ON ROUND(SQRT(((p.Grid_N - in_radius.Grid_N)*(p.Grid_N - in_radius.Grid_N))+((p.Grid_E - in_radius.Grid_E)*(p.Grid_E - in_radius.Grid_E))) / 1000.2) < $distance
INNER JOIN members mm
ON in_radius.Pcode = mm.postcode
WHERE mm.member_id = $member_id
AND m.sex <> (SELECT sex FROM members WHERE member_id = $member_id)
AND m.member_age > $age_1
AND m.member_age < $age_2
$has_photo_query_part
This is what I am using now:
$has_photo_query_part = "AND (
SELECT COUNT(member_id)
FROM member_images
WHERE member_id <> $member_id
AND image_name IS NOT NULL
) > 0";
And image table is like this:
image_id member_id image_name caption image_added_date
1 1 me.jpg Hi !! 2011-08-10
2 4 Diane1234.jpg Hello 2011-09-06
Both these queries work (probably not very elegant queries) but I cannot make the $has_photo_query_part work for both of them as I think I need a JOIN and that will not be compatible.
Could someone please suggest the photo query that would work for both or any other ideas.
Cheers anyone
