not sure how to change this query

asa_carter

Hi,

This query works but I need to modify it:

$query_userCoords = "SELECT COUNT(dest.outcode) as density, dest.outcode,
dest.x, dest.y FROM hwz_users AS source, hwz_postcodes AS dest WHERE
source.outcode = dest.outcode GROUP BY dest.outcode ASC";

I have a table called personal_details instead of hwz_users
The field in personal_details is called postcode instead of outcode
Postcode contains the full postocde instead of just the outcode
E.g. PA28 6ST instead of PA28

So I need to say something like 'where the 'outcode' in 'hwz_postcodes' is in the string in 'postocde' in 'personal_details' but not an exact match'.

I thought it might be something like this but this does not work:

$query_userCoords = "SELECT COUNT(dest.outcode) as density, dest.outcode,
dest.x, dest.y FROM personal_details AS source, hwz_postcodes AS dest WHERE
dest.outcode LIKE 'source.postcode%' GROUP BY dest.outcode ASC";

Thanks

bradgrafelman

I think it's that LIKE part that's killing it. I'm no MySQL guru, but perhaps this will work?

$query_userCoords = "SELECT COUNT(dest.outcode) as density, dest.outcode,
dest.x, dest.y FROM personal_details AS source, hwz_postcodes AS dest WHERE
dest.outcode LIKE CONCAT(source.postcode,'%') GROUP BY dest.outcode ASC";

asa_carter

Hi,

Thanks for help but it is still returning 0 rows.

Any other idea's?

tomhath

Try adding the wildcard to the front. And I think you have source and dest mixed up:

source.postcode LIKE '%' + dest.outcode + '%'

asa_carter

That seems to return a mysql error.

I thought another way to do it would be to create a field called outcode in the personal details table and add the first part of the details to it on registration.

I have been trying to find part of a full postcode string in the postcode table.
but 'PA28 6ST' using LIKE %PA28 6ST% but it wont find PA28 as it searching for everything after 6ST.

What function can I use to find the PA28 in "PA28 6ST" where the table contains a field called outcode in the format: PA1, PA2, ..... PA28, PA29?

$postcode="PA286ST";
$test = mysql_query("SELECT outcode from hwz_postcodes");
while($row = mysql_fetch_row($test))
{
  if (strchr($postcode, $row[0]))
    echo $row[0];
}

This returns PA2 and PA28. what do I need to do to get the closest match (PA28)?

Thanks

Asa

asa_carter

Hi,

I got it working this way but it's not 100%:

On new registrations, I created a new field called outcode in the personal_details table. On entering a new registration in the table:

$outcode=strrev($postcode);
$outcode=substr_replace($outcode,"",0,3);
$outcode=strrev($outcode);
$outcode=trim($outcode);

Then for the query:

$query_userCoords = "SELECT COUNT(dest.outcode) as density, dest.outcode,
dest.x, dest.y FROM personal_details AS source, hwz_postcodes AS dest WHERE
source.outcode = dest.outcode GROUP BY dest.outcode ASC";

http://www.argyllnewmedia.co.uk/auction/map.php

Seems to work ok, but if anyone has a better solution please let me know

Thanks