Help With Substring in Query

[daniel_lee_hill]

Hi All,

Having trouble figuring out how to do this one.

My table lists towns & postcodes.

If someone calls up Sydney per instance, I want it to get the postcode '2000' take the first two digits, and call all towns with the same first two digits.

Here is the code I have so far. I can call up the first 2 digits of SYDNEY but, can't figure out the rest.

 

 

<?php
require_once("common/database-connect.php");
require_once("scripts/functions.php");
require_once("common/variables.php");

$result = mysql_query("SELECT * FROM town
WHERE name='SYDNEY' ");

while($row = mysql_fetch_array($result))
  {
  $post = substr($row['postcode'],0,2);
  echo $post;
    };

$result = mysql_query("SELECT * From town WHERE SUBSTRING(postcode,0,2)='".$post."'" );	

while($row = mysql_fetch_array($result))
  {
echo $row['name'];
echo "</br> /n";
    };

?> 

[Goldeneye]

This part of your second query does not look right to me:

SUBSTRING(postcode,0,2)='".$post."'

 

You're comparing two values (20 = 20) against eachother which doesn't do anything in an SQL-query.

 

instead, use this for your second-query:

$result = mysql_query("SELECT SUBSTRING(postcode,0,2) From town WHERE `postcode`=2000");

 

Keep in my mind, I'm not sure if that'll work, but it's atleast syntactically correct.

[daniel_lee_hill]

Thanks for the response.

 

I'm too sure if I explained what I am trying to do correctly.

 

This Code below get's the selected towns post(area) code and strips it back to two digits.

 

$result = mysql_query("SELECT * FROM town
WHERE name='".$town"' ");

while($row = mysql_fetch_array($result))
  {
  $post = substr($row['postcode'],0,2);
  echo $post;
    };

 

What I want to do is now take any other record that the post(area)code begins with the same two digits.

 

So for a search of Sydney with an area code of 2000,

$post = '20'

 

So now I want to bring up a list of town/area names that have their area code begin with $post (20).

 

Unfortunately the code below is not correct.

 

$result = mysql_query("SELECT SUBSTRING(postcode,0,2) From town WHERE `postcode`=2000");

 

 

[daniel_lee_hill]

Ha, I found the problem.

 

MySql doesn't count from zero i.e. SELECT SUBSTRING(postcode,0,2) should be SELECT SUBSTRING(postcode,1,2).