Geocoding markers by field in database

[erme]

I have just got a google map to display markers as per lat and long fields in an SQL database. Works fine! However, the question is (and I've spent about 3 hours trying to solve this with my limited PHP knowledge) how would I only show the markers as per what county I am viewing?

 

<?php

$host = "";
$user = "";
$db_name= "";
$pass= "";

$conn = mysql_connect($host, $user, $pass) or die(mysql_error());
mysql_select_db($db_name, $conn) or die(mysql_error());


function parseToXML($htmlStr)
{
    $xmlStr=str_replace('<','<',$htmlStr);
    $xmlStr=str_replace('>','>',$xmlStr);
    $xmlStr=str_replace('"','"',$xmlStr);
    $xmlStr=str_replace("'",'&#39;',$xmlStr);
    $xmlStr=str_replace("&",'&',$xmlStr);
    return $xmlStr;
}

$query = "SELECT * FROM locations";
$result = mysql_query($query);
if (!$result)
{
    die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");

echo '<markers>';
while ($row = @mysql_fetch_assoc($result))
{
    if(!empty($row['bg_lat']) or !empty($row['bg_long']))
    {
        echo '<marker ';
        echo 'company="' . parseToXML($row['company']) . '" ';
        echo 'address="' . parseToXML($row['address']) . '" ';
        echo 'tel="' . $row['tel'] . '" ';
        echo 'county="' . $row['county'] . '" ';
        echo 'package="' . $row['package'] . '" ';
        echo 'bg_lat="' . $row['bg_lat'] . '" ';
        echo 'bg_long="' . $row['bg_long'] . '" ';
        echo '/>';
    }
}
echo '</markers>';

?>

 

The table has different counties such as cornwall, devon, somerset etc.

 

 

[JonnoTheDev]

Have you got your locations organised into counties?

i.e

counties

======

countyId

title

 

locations

=======

locationId

countyId

title

longitude

latitude

 

Then you only need to select the records from a specific countyId and display map markers

[erme]

Yep have a field called 'county' and all records have an entry in that field as to where they are.

 

Do you mean something like this?

 

$query = "SELECT * FROM locations WHERE county='whatever'";

 

[thebadbad]

Why don't you try that query?

[erme]

Tried this but didn't work

 

$extrapart = (isset($_GET['county'][0]))?"WHERE County='".mysql_real_escape_string($_GET['county'])."'":"";

 

$query = "SELECT * FROM locations $extrapart";

$result = mysql_query($query);

[thebadbad]

didn't work

 

You want us to guess what went wrong?

[erme]

didn't work .. as in nothing happened. I'm sorry I don't have any more information to give you.

 

 

[thebadbad]

"Nothing happened" could mean several things. Did your page go blank?

 

..

 

I'm not sure if field names are case sensitive, but could be, and you're using both County and county. And have you tried to just run the query with a hard coded county?

[erme]

No the page didn't go blank, just didn't change the tags as per county on the map.

[erme]

Have changed county to lowercase as thats what it is in the database.

 

I hard coded :

$query = "SELECT * FROM locations WHERE county='devon'";

 

and that seemed to work. Any idea why the above code dosent work? 

[thebadbad]

Should work, but be sure that $_GET['county'] is set to a county via the URL.

[JonnoTheDev]

Your database is not normalised!

If your locations table containing towns/cities has its county as a varchar field then this is bad. You are duplicating data.

Again, your database should be laid out as:

 

counties

======

countyId

title

 

locations

=======

locationId

countyId

title

longitude

latitude

 

 

Imagine you noticed that one of your counties has a spelling mistake. You have to update x number of records in your locations table rather than just 1 in the counties table. The tables should be joined via a foreign key in locations.

 

Your sql to get locations by county should look as follows

// countyId 1 = 'Merseyside'

SELECT * FROM locations WHERE countyId='1' ORDER BY title ASC

NOT

SELECT * FROM locations WHERE county='Merseyside' ORDER BY title ASC

 

As you have noticed the second method produces unexpected results when you capitalise the first letter of the county.

I hope this helps in future projects requiring databases.

 

[erme]

Thanks for that neil.johnson. Didn't know you could do that. I will look into that sometime. At the moment I'm still trying to get this geocoding thing to change tags upon county=whatever in the url. 2 weeks ago I didn't know a thing about SQL and could just about write a contact form in PHP .. until I got presented with this! Big learning curve.

[thebadbad]

It's really simple; if it works with a hard coded county, you just have to make sure the variable you're using instead holds the right value. You can check that by printing it to the screen, e.g. with var_dump()

[erme]

$extrapart outputs correctly.

 

Trouble is, im working from location.php and the varabale $extrapart is actually in index.php 

[thebadbad]

So are you including index.php, or how is it set up?

[erme]

index.php is the page where the map is displayed. Included on that page is locations.php.

 

In locations.php i have $query = "SELECT * FROM location $extrapart"; however $extrapart is in index.php.

 

If I put the $extrapart variable in locations.php and echo it in index.php it displays the correct county='blah' however the tags on the map are not selected by county.

[JonnoTheDev]

I reccommend going through some php tutorials on using url parameters and sanitizing them when used in sql, also keeping data persistent through pages using various methods. No offense but sounds like you are a newbie.

[erme]

Thinking about it the $extrapart isn't going to output anything in /includes/locations.php as it is /index.php that's doing the ?county=devon thing.

 

Now I'm really stooped, how would I pull that ipp so that /includes/locations.php can read it?