Hello, in short, here is my problem:
There are 2 tables:
a table called postcodes, which contains UK postcodes
and a table called wp_bp_xprofile_data (i want to integrate this search with the Buddypress plugin's tables) which contains the user data.
I tried searching this, but to be honest, i didn't quite know how to go about it..
Here is what i have:
$query1 = "SELECT value, user_id, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
FROM postcodes a, postcodes b, wp_bp_xprofile_data
WHERE a.outcode = '"$postcode"' AND b.outcode = wp_bp_xprofile_data.value
HAVING (distance < '"$area"')
ORDER BY distance asc ";
$result1=mysql_query($query1) or die(mysql_error());
echo "<p style=\"font-size:10px;line-height:14px;color:#888;\">Straight line distances shown.<br>";
echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" ><tr>";
// display results
while ($list = mysql_fetch_array($result1)) {
$user_postcode = $list['value'];
$distance = $list['distance'];
$distance = round($distance);
echo "<td align=\"right\">$distance miles away</td></tr>";
echo "<tr><td colspan=\"3\" align=\"left\" width=\"130px\">$user_postcode </td>";
echo "<td align=\"right\"><a href=\"blahblah\">Directions using Google Maps</a></td>";
echo "<tr><td align=\"left\"> </td><tr>";
echo "</tr>";
}
echo "</table>";
id field_id user_id value
1 1 1 admin
8 3 3 NW10
6 1 3 Test User 1
9 4 3 IT, Web Development
11 1 4 Test User 2
12 3 4 HA5
13 4 4 Test, It, Some Work
outcode x y latitude longitude
B10 392900 804900 57 -2
AB11 394500 805300 57 -2
AB12 393300 801100 57 -2
AB13 385600 801900 57 -2
AB14 383600 801100 57 -2
Here is an example of what i need:
field_id "3" corresponds to a postcode. My script so far, can search a postcode and return results with the postcodes sorted by distance. What i'm trying to do, is also get the username on the result (i.e the 'value' column again..).
in short, im trying to search a postcode, find it on wp_bp_xprofile_data, associate it with the correspondent user, and return both. Someone told me i need to INNER JOIN, but im lost..
The end result im looking for is if i type the search: "NW9 1AA"
It returns:
Test User 1
NW10
Test User 2
HA5
(the code sorts them by distance. So far i can get it to return the postcode, but cannot associate the relevant name.
Can anyone help me with this?
I hope this is in the right forum.
Thanks for your attention.