Return Results From A Mysql Database Dependent On Town Entered

[ianhaney]

Hi

 

I am bit stuck, I want to return results from a mysql database when a user enters their town or postcode in for example below

 

they enter in their town or post code and a list of area managers is displayed closest to their town or postcode along with their contact number

 

Is that possible

 

Thank you

 

Ian

[Muddy_Funster]

it is indead possible. What's got you stuck? the PHP or the SQL?

[ianhaney]

Hi Muddy_Funster thank you for the reply

 

I think it will be more the PHP that has me stuck as entering the data should be fairly easy to do, am I right in doing something like the following in the mysql database

 

name

contactnumber

 

the page I want to display the results will look like the following

 

Area Manager: name or manager

Contact Number: contact phone number

[Muddy_Funster]

Well what you're asking about is a bit more complicated than that, hidden in the use of a single word - "closest". Do you know how you are going to establish "closest" from only being given a town or postcode?

[ianhaney]

not sure yet really maybe in the database I could have another column called town and then when a user enters their town, the town matching that of the user puts in will be displayed or the closest to the town that of the user puts in

[drewdan]

You could do it by postal area. So for example, if I enter "NP10 0AS", you query could search for people within "NP10", or even wider to "NP"

[ianhaney]

Hi Drewdan

 

Yeah that sounds like what I need

 

Trouble is not 100% on how to do it, would you be able to give me a head start and see if I can work it out

 

Sorry to ask

[Muddy_Funster]

you guys have missed my point here. How - exactly - is the database going to know anything at all about geographical proximity? all the database stores is 1's and 0's. what information is it going to have to say placeA is cloaser to placeX than placeH is?

[ianhaney]

Ahh ok got ya now Muddy sorry

 

Hmm gonna be difficult to do isn't it?

[ianhaney]

I do have a php script for a estate agent website that returns results dependent on town, how many rooms etc

 

Could I use that somehow and obviously do away with the number of rooms etc and just leave in town and add a column in the database called name

[drewdan]

Yes sorry, my idea is based purely on postal areas, if wont give you the closest person to you, it will just provide results in that postal area, in a sense imitating the functionality of closest too. The problem is, if there is no one in that postal area it will return nothing instead of searching the next postal area.

 

You would need to provide further information, co-ordinates and the lark and a way of searching co-ordinates within a radius of 10 miles etc. Sounds like a big job!

[drewdan]

This might explain what you would need to do better:

http://stackoverflow.com/questions/2296087/using-php-and-google-maps-api-to-work-out-distance-between-2-post-codes-uk

[ianhaney]

It is a big job but think your postal code way could work as your saying it finds results in that postal area

[drewdan]

In fact this might explain it better still:

http://www.sloomedia.com/php_postcode_script.php

[ianhaney]

That looks close to what I need but just unsure how to convert it to what I need but will have a go

[drewdan]

You basically want to use the function the developer is showing you called:

 

calc_postcode_seperation($pcodeA,$pcodeB)

 

You will then need to compare the postcode the user entered with all of your entries in your database, using a loop, then use an if statement to see if they are located less than 10km or whatever from the postcode entered by your user andif they are, add the details to an array and then output the data collected in the array onto your page at the end.

 

The developer provides you with an sql file with all of the postcode details. I am assuming you are located in the UK?

[ianhaney]

Yeah that's it easier said than done I think, Yeah I am based in the UK

[ianhaney]

I have so far managed to get results displayed from the database but the phone number has a 0 at the start in the database but in the php page it is missing the 0

 

Any ideas

[ianhaney]

I have got the 0 displaying now

 

How do I get results to display what the users chooses for example I have a input text field and typed a town in but is still displaying all the records from the database

 

The coding I have is below

 

<html>  
<head><title>Page Results</title></head>  
<body>  
<?php  
$database="";  
mysql_connect ("", "", "");  
@mysql_select_db($database) or die( "Unable to select database");  
$result = mysql_query( "SELECT * FROM managers" )  
or die("SELECT Error: ".mysql_error());  
$num_rows = mysql_num_rows($result);  
print "There are $num_rows records.<P>";  
print "<table width=400 border=1>\n";  
while ($get_info = mysql_fetch_row($result)){  
print "<tr>\n";  
foreach ($get_info as $field)  
print "\t<td><font face=arial size=1/>$field</font></td>\n";  
print "</tr>\n";  
}  
print "</table>\n";  
?>
<form action="viewall.php">
Town
<input type="text">
<input type="submit" name="send" value="Search">
</form>
</body>  
</html>

[Muddy_Funster]

first up - have you thought about using the google maps API? this would work for your ranging search.

second - what you are looking for is number_format()

[Muddy_Funster]

you would need to use the input to create a WHERE clause in the SQL query. This meens that you must now know about sanitization to make the user input safe

[ianhaney]

Hmm gonna be tricky this as not sure on how to use the input to create a where clause in the sql query

 

are there any good tutorials I could look at regarding the where clause

[Muddy_Funster]

see how you get on with this : http://www.tizag.com/mysqlTutorial/mysqlwhere.php

[ianhaney]

I looked at the where link you gave and put this in my coding

 

WHERE town LIKE 'B%'

 

That returns all the town's beginning with B but still not sure on how to get the results displayed from what the user types in

[ianhaney]

Could it be to do with my form

 

<form action="viewall.php">
Town
<input type="text">
<input type="submit" name="search" value="Search">
</form>