daniel_lee_hill Posted November 25, 2009 Share Posted November 25, 2009 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"; }; ?> Quote Link to comment Share on other sites More sharing options...
Goldeneye Posted November 26, 2009 Share Posted November 26, 2009 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. Quote Link to comment Share on other sites More sharing options...
daniel_lee_hill Posted November 26, 2009 Author Share Posted November 26, 2009 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"); Quote Link to comment Share on other sites More sharing options...
daniel_lee_hill Posted November 26, 2009 Author Share Posted November 26, 2009 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). Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.