PHP and MySQL queries...

[SkillBuzz]

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.

[seanlim]

Hi there, welcome to our forums!

 

You can, indeed, use a JOIN. Since you are trying to combine values from two different rows into a single row, you will have to (INNER) JOIN wp_bp_xprofile_data with itself ON the column user_id.

 

Also, to prevent the postcode field from being "joined" to the other field_ids (other than the username field which you are interested in), you will have to specify which field_id (in this case1) you want it to join to.

 

Your SQL portion will look something like that (untested):

    FROM postcodes a, postcodes b, 
        wp_bp_xprofile_data c 
        JOIN  wp_bp_xprofile_data d 
        ON c.user_id=d.user_id AND d.field_id=1
    WHERE a.outcode = '"$postcode"' AND b.outcode = c.value

[SkillBuzz]

Great stuff!!

 

this query works with no errors:

 

// database query to find postcodes withinh x ($area) distance from submitted postcode
    $query1 = "SELECT d.value, c.value, d.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 c JOIN  wp_bp_xprofile_data d 
        ON c.user_id=d.user_id AND d.field_id=1 
    WHERE a.outcode = '".$postcode."' AND b.outcode = c.value
    HAVING (distance < '".$area."')
    ORDER BY distance asc ";

 

now a last noobie questtion.. How to i define the 2 separate values?

 

this is what i have:

 

// display results
while ($list = mysql_fetch_array($result1)) {
        $user_postcode = $list['value'];
$distance = $list['distance'];
$distance = round($distance);

 

and

echo "<td colspan=\"3\" align=\"left\"><strong>$user_postcode</strong></td>";

  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=\"http://maps.google.co.uk/?q=$postcode+$postcode2+to+$user_postcode\" title=\"Get directions\" target=\"_blank\">Directions using Google Maps</a></td>";

  echo "<tr><td align=\"left\"> </td><tr>"; 

echo "</tr>";

 

However, it is showing me the PostCode twice (i need to pluck out the other 'value'

 

Thanks again for the help! =)

 

 

 

 

[SkillBuzz]

Ok, seems like that query does return 2 values, but the values are the same.

 

echo "'$result1'"

 

shows this: 'Resource id #4''Resource id #4'

 

I think my brain is bleeding, but it's so close.....

 

Would a fresh pair of eyes tell me what i'm missing? =)

[SkillBuzz]

this:

 

SELECT d.value, c.value, d.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 c 
JOIN  wp_bp_xprofile_data d          
ON c.user_id=d.user_id AND d.field_id=1      
WHERE a.outcode = '".$postcode."' 
AND b.outcode = c.value

 

($postcode variable entered as a string, of course)

 

when executed on mysql, returns exactly the rows i want, the way i want them.. Now i just need to figure out how to output it..

 

Progress!!

[SkillBuzz]

in short, how do i define the *other* 'value' to a variable?

 

i have the:

 

// display results
while ($list = mysql_fetch_array($result1)) {
    $user_postcode = $list['value'];
$distance = $list['distance'];
$distance = round($distance);

 

 

 

[seanlim]

use the AS keyword!

 

SELECT d.value AS username, c.value AS postcode, d.user_id,

 

you can then use mysql_fetch_assoc to fetch $list['username'] and $list['postcode']

[SkillBuzz]

You, sir, are a lifesaver. Many thanx =)