Using PHP and MySQL to create KML and <folder>

[Eddie LaRue]

I have used the article "Using PHP and MySQL to create KML" to build a
km for my company. I originally used the Spreadsheet Mapper from
Google outreach to do my first version, but have outgrown it. I have
switched everything over to this code, but I am having a problem
sorting the placemarks into folders in the My Places pane. I wrapped
the code in the folder attribute, but it just outputs each placemark
in its own folder, rather then all the similar placemarks under one
folder.

Is there a way of doing this.

Here is my code:

<?php
require('phpsqlajax_dbinfo.php');

// Opens a connection to a MySQL server.
$connection = mysql_connect ($server, $username, $password);
if (!$connection)
{
die('Not connected : ' . mysql_error());
}

// Sets the active MySQL database.
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected)
{
die ('Can\'t use db : ' . mysql_error());
}

// Selects all the rows in the markers table.
$query = 'SELECT * FROM markers WHERE 1';
$result = mysql_query($query);
if (!$result)
{
die('Invalid query: ' . mysql_error());
}

// Creates an array of strings to hold the lines of the KML file.
$kml = array('<?xml version="1.0" encoding="UTF-8"?>');
$kml[] = '<kml xmlns="http://earth.google.com/kml/2.1">';
$kml[] = ' <Document>';
$kml[] = ' <Style id="1Style">';
$kml[] = ' <IconStyle id="1Icon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://www.google.com/intl/en_us/mapfiles/ms/icons/
purple-dot.png
</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';
$kml[] = ' <Style id="2Style">';
$kml[] = ' <IconStyle id="2Icon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://www.google.com/intl/en_us/mapfiles/ms/icons/
red-dot.png
</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';
$kml[] = ' <Style id="3Style">';
$kml[] = ' <IconStyle id="3Icon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://www.google.com/intl/en_us/mapfiles/ms/icons/
yellow-dot.png
</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';
$kml[] = ' <Style id="4Style">';
$kml[] = ' <IconStyle id="4Icon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://www.google.com/intl/en_us/mapfiles/ms/icons/
green-dot.png
</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';
$kml[] = ' <Style id="5Style">';
$kml[] = ' <IconStyle id="5Icon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://www.google.com/intl/en_us/mapfiles/ms/icons/
blue-dot.png
</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';
$kml[] = ' <Style id="6Style">';
$kml[] = ' <IconStyle id="6Icon">';
$kml[] = ' <Icon>';
$kml[] = ' <href>http://www.google.com/intl/en_us/mapfiles/ms/icons/
orange-dot.png
</href>';
$kml[] = ' </Icon>';
$kml[] = ' </IconStyle>';
$kml[] = ' </Style>';

// Iterates through the rows, printing a node for each row.
while ($row = @mysql_fetch_assoc($result))
{

$kml[] = ' <Placemark><name>' . htmlentities($row['id']) . '</
name>';
$kml[] = ' <name>' . htmlentities($row['job']) . '</name>';
$kml[] = ' <Snippet maxLines="0"></Snippet>';
$kml[] = ' <Style>';
$kml[] = ' <BalloonStyle>';
$kml[] = ' <text><![CDATA[
<table width="325" border="0" cellspacing="0" cellpadding="5">
<tr>
<td align="center"><img src="http://www.celltreesinc.com/images/
celltreeslogo.jpg" width="100" /> </td>
</tr>
<tr>
<td align="center"><h2><font color="#00CC99">$[Title]</font></
h2>
<h3><font color="#00CC99">$[Address]</font></h3></td>
</tr>
<tr>
<td align="center"><img src="$[Photo_URL]" width="300" /> </td>
</tr>
<tr>
<td align="center"><h3><font color="#808080">$[Text]</font></h3>
<p><font color="#3399CC"></font></p></td>
</tr>
<tr>
<td align="center"><a href="#">www.celltreesinc.com</a> </td>
</tr>
<tr>
<td align="center"><font color="#999999">&copy;2010 Cell Trees
Inc.</font> </td>
</tr>
<tr>
<td align="center">$[geDirections] </td>
</tr>
</table>
]]></text>';
$kml[] = ' </BalloonStyle>';
$kml[] = ' </Style>';
$kml[] = ' <styleUrl>#' . ($row['color']) . 'Style</styleUrl>';
$kml[] = ' <ExtendedData>';
$kml[] = ' <Data name="Title">';
$kml[] = ' <value>' . htmlentities($row['title']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Address">';
$kml[] = ' <value>' . htmlentities($row['address']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Photo_URL">';
$kml[] = ' <value>' . htmlentities($row['photo']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Text">';
$kml[] = ' <value>' . htmlentities($row['text']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' </ExtendedData>';
$kml[] = ' <Point>';
$kml[] = ' <coordinates>' . $row['lng'] . ',' . $row['lat'] . '</
coordinates>';
$kml[] = ' </Point>';
$kml[] = ' </Placemark>';

}

// End XML file
$kml[] = ' </Document>';
$kml[] = '</kml>';
$kmlOutput = join("\n", $kml);
header('Content-type: application/vnd.google-earth.kml+xml');
echo $kmlOutput;
?>

Thanks for your help.

[Rossko]

> Thanks for your help.

An example of KML output (not a listing) would be more useful

[Eddie LaRue]

Thanks for looking at this. Here is the kml generated from the
folder:

<?xml version="1.0" encoding="UTF-8"?>

<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://
www.google.com/kml/ext/2.2" xmlns:kml="http://www.opengis.net/kml/2.2"
xmlns:atom="http://www.w3.org/2005/Atom">
<Folder id="name">
<name>Broadleaves</name>
<Placemark>
<name>07-085</name>
<Snippet maxLines="0"></Snippet>
<styleUrl>#1Style</styleUrl>
<Style>
<BalloonStyle>

<textColor>ff000000</textColor>
<displayMode>default</displayMode>
</BalloonStyle>
</Style>
<ExtendedData>
<Data name="Title">
<value>Broadleaf Monopole</value>
</Data>
<Data name="Address">
<value>590 Denio Avenue, Gilroy, CA 95020</value>
</Data>
<Data name="Photo_URL">
</Data>
<Data name="Text">
</Data>
</ExtendedData>
<Point>
<coordinates>-121.576736,37.045517,0</coordinates>
</Point>
</Placemark>
</Folder>
</kml>

[Rossko]

> Thanks for looking at this.  Here is the kml generated from the
> folder:

There's only one <Folder>. It contains one <Placemark>. Presumably it
wasn't output by the script you dumped earlier, since that never
creates any <Folder> at all.
What were you expecting to see?

[Eddie LaRue]

I was expecting to see the folder "A", with its placemarks for "A",
then folder "B" with it's placemarks.

What is happening, is that the folder wraps the code, and just pairs
up one folder with one placemark.

while ($row = @mysql_fetch_assoc($result))
{
$kml[] = ' <Folder><name>' . htmlentities($row['name']) . '</name>';

$kml[] = ' <Placemark><name>' . htmlentities($row['id']) . '</
name>';
$kml[] = ' <name>' . htmlentities($row['job']) . '</name>';
$kml[] = ' <Snippet maxLines="0"></Snippet>';

.......
$kml[] = ' </Placemark>';
$kml[] = ' </Folder>';

What I want is it to put all the "Broadleafs" under a Broad leaf
folder, and all the "Palms" under the Palm Tree folder.

[Nymor]

Hi,

As it stands, just looking at your last post, you're opening and
closing a folder within each while loop - and therefore what you're
saying is happening is what you're asking it to do.

To group a set of placemarks within a folder you need to test whether
you need to open a new folder and/or close an existing one on each
loop. There was a similar question a while ago in the Server-Side
Scripting forum which I posted the way I do what you're after - last
post in this thread
http://groups.google.com/group/kml-support-server-side-scripting/browse_thread/thread/28794c8a6dc5ae27#

The original question in that thread was about doing it using the DOM
method but the pseudo code I posted works just as well for how you're
constructing the kml as it all really about how you track what's what
with your folders while looping through your recordset (which has to
have the placemarks sorted by folder)

Hope that helps
Nymor

[Rossko]

>   $kml[] = ' <Folder><name>' . htmlentities($row['name']) . '</name>';

That wasn't in your original code dump, which renders it rather
pointless.

> What I want is it to put all the "Broadleafs" under a Broad leaf
> folder, and all the "Palms" under the Palm Tree folder.

Exploit MySQL to ORDER the results by that category, it will greatly
simplify outputting by category in <Folder>. See any MySQL tutorial.

[Eddie LaRue]

This is what I came up with from your post, but it is not working. Any
suggestions

"name is the folder name" I putt $i=0 in the dbinfo.php

while ($i = 0)
if(($row['name'])!=$row['name'])

$kml[] = ' <Folder><name>' . htmlentities($row['name']) . '</name>';
$kml[] = ' <Placemark><name>' . htmlentities($row['id']) . '</
name>';
$kml[] = ' <name>' . htmlentities($row['job']) . '</name>';
$kml[] = ' <Snippet maxLines="0"></Snippet>';

$kml[] = ' <Style>';
$kml[] = ' <BalloonStyle>';

$kml[] = ' <text><![CDATA[

]]></text>';
$kml[] = ' </BalloonStyle>';
$kml[] = ' </Style>';
$kml[] = ' <styleUrl>#' . ($row['color']) . 'Style</styleUrl>';
$kml[] = ' <ExtendedData>';
$kml[] = ' <Data name="Title">';
$kml[] = ' <value>' . htmlentities($row['title']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Address">';
$kml[] = ' <value>' . htmlentities($row['address']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Photo_URL">';
$kml[] = ' <value>' . htmlentities($row['photo']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Text">';
$kml[] = ' <value>' . htmlentities($row['text']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' </ExtendedData>';
$kml[] = ' <Point>';
$kml[] = ' <coordinates>' . $row['lng'] . ',' . $row['lat'] . '</
coordinates>';
$kml[] = ' </Point>';

$kml[] = ' </Placemark>';
$kml[] = ' </Folder>';

{

if ($i!=0)
$name = $row['name'];
$i++;

}

On Feb 24, 9:21 pm, Nymor wrote:
> Hi,
>
> As it stands, just looking at your last post, you're opening and
> closing a folder within each while loop - and therefore what you're
> saying is happening is what you're asking it to do.
>
> To group a set of placemarks within a folder you need to test whether
> you need to open a new folder and/or close an existing one on each
> loop. There was a similar question a while ago in the Server-Side
> Scripting forum which I posted the way I do what you're after - last

> post in this threadhttp://groups.google.com/group/kml-support-server-side-scripting/brow...

[Rossko]

> but it is not working.

An example of KML output (not a listing) or the error message would be
more useful

[Eddie LaRue]

At this point, it loads in GE with out errors, but there is no output.
I'm sure my while loop is wrong.

[Rossko]

> At this point, it loads in GE with out errors, but there is no output.
> I'm sure my while loop is wrong.

Quite possibly.
For sure the php code as last dumped would put each and every
<Placemark> in its own <Folder>, but that doesn't account for no-
shows.
An example of KML output (not a listing) would be useful.

[Eddie LaRue]

Nymor,

I've spent a week on this, and I still get the folder everytime. I
tried your example, evidently I did not get it to work.:
Here is my while loop:

while ($row = @mysql_fetch_assoc($result))
{
$kml[] = ' <Folder><name>' . htmlentities($row['name']) . '</
name>';

$kml[] = ' <open>1</open>';

if ($row['name']=$row['name'])
{

$kml[] = ' <Placemark><name>' . htmlentities($row['id']) . '</
name>';
$kml[] = ' <name>' . htmlentities($row['job']) . '</name>';
$kml[] = ' <Snippet maxLines="0"></Snippet>';

$kml[] = ' <Style>';
$kml[] = ' <BalloonStyle>';

$kml[] = ' <text><![CDATA[

<table width="325" border="0" cellspacing="0" cellpadding="5">
<tr>
<td align="center"><img src="http://www.celltreesinc.com/

images/celltreeslogo.jpg" width="100" /> </td>

</table>]]></text>';
$kml[] = ' </BalloonStyle>';
$kml[] = ' </Style>';
$kml[] = ' <styleUrl>#' . ($row['color']) . 'Style</styleUrl>';
$kml[] = ' <ExtendedData>';
$kml[] = ' <Data name="Title">';
$kml[] = ' <value>' . htmlentities($row['title']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Address">';
$kml[] = ' <value>' . htmlentities($row['address']) . '</
value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Photo_URL">';
$kml[] = ' <value>' . htmlentities($row['photo']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' <Data name="Text">';
$kml[] = ' <value>' . htmlentities($row['text']) . '</value>';
$kml[] = ' </Data>';
$kml[] = ' </ExtendedData>';
$kml[] = ' <Point>';
$kml[] = ' <coordinates>' . $row['lng'] . ',' . $row['lat'] .
'</coordinates>';
$kml[] = ' </Point>';

$kml[] = ' </Placemark>';

}

$kml[] = ' </Folder>';
}

I could not get your loop to work.

On Feb 24, 9:21 pm, Nymor wrote:

> Hi,
>
> As it stands, just looking at your last post, you're opening and
> closing a folder within each while loop - and therefore what you're
> saying is happening is what you're asking it to do.
>
> To group a set of placemarks within a folder you need to test whether
> you need to open a new folder and/or close an existing one on each
> loop. There was a similar question a while ago in the Server-Side
> Scripting forum which I posted the way I do what you're after - last

> post in this threadhttp://groups.google.com/group/kml-support-server-side-scripting/brow...

[Rossko]

> while ($row = @mysql_fetch_assoc($result))
> {
> $kml[] = ' <Folder><name>' . htmlentities($row['name']) . '</
> name>';

Every time the code goes round the loop, i.e. for each row, the
output
is wrapped in a <Folder>

If you want to put your data into <Folder> by category, you must
first
organise your data by category so that you can process it by
category. None of that happens in your while loop, no point in keep
posting that.

Without having any idea on what you are trying to sort by, here's a
possible skeleton

SELECT stuff FROM table WHERE whatever
ORDER BY some_column

This will get your SQL data into groups

Now you can detect the groups in your php

$temp = $row['some_column'] ; // identify the first group
output the first <Folder> tag

while loop ....
$category = $row['some_column']) ; // read the current
placemark's group
if ($category != $temp) // test if we're in the same group still
output the </Folder> <Folder> tags // because the
group has changed
$temp = $category; // remember the new group
endif
output the placemark
end while
output a </Folder> to finish off
finish off the document

There are other ways, but you get the idea.

None of that is anything to do with using KML
http://www.google.co.uk/search?hl=en&source=hp&q=php+mysql+tutorial&meta=&aq=f&oq=

[Eddie LaRue]

Thanks everybody. I got it working. I used Nymor's example.

> None of that is anything to do with using KMLhttp://www.google.co.uk/search?hl=en&source=hp&q=php+mysql+tutorial&m...

[Nymor]

Glad you managed to get it going :)

For the record mine and Rossko's methods are virtually identical with
the small exception of how/where we open/close the folders and track
where we are. Mine is slightly more wordy as I too had a bit a trouble
getting it going to begin with, and had 3 levels of nesting to contend
with as well, so did it long hand and never got round to condensing
everything afterwards.

The important thing as Rossko pointed out is to sort your placemarks
by folder first and then just ask within each loop whether we're in
the same one or not.

Regards
Nymor

[pfu]

Thank you very much for posting this! That's really what I am looking
for.
I'm a newer and trying to learn to make kml using php and MySQL
through a tutorial posted by Mano Marks, Google Geo Team (http://
code.google.com/apis/kml/articles/phpmysqlkml.html).
I sorted the placemarkers by their type and tried to build a folder
for each type and name it after its type. I tried to do it in Rossko's
methods.
Here is part of script I edit after Mano Marks.

$kml[] = ' <Folder>';
$kml[] = ' <name>' . htmlentities(markers) . '</name>';

$temp = $row['type'] ;
$kml[] = ' <Folder>';

while ($row = @mysql_fetch_assoc($result))
{
$category = $row['type'];
if ($category != $temp){
$kml[] = ' </Folder>';
$kml[] = ' <Folder>';
$kml[] = ' <name>' . htmlentities($category) . '</name>';
$temp = $category;
}
$kml[] = ' <Placemark id="placemark' . $row['id'] . '">';
$kml[] = ' <name>' . htmlentities($row['name']) . '</name>';
$kml[] = ' <description>' . htmlentities($row['address']) . '</
description>';
$kml[] = ' <styleUrl>#' . ($row['type']) .'Style</styleUrl>';

$kml[] = ' <Point>';
$kml[] = ' <coordinates>' . $row['lng'] . ',' . $row['lat'] . '</
coordinates>';
$kml[] = ' </Point>';
$kml[] = ' </Placemark>';
}

$kml[] = ' </Folder>';

$kml[] = ' </Folder>';

// End XML file
$kml[] = ' </Document>';
$kml[] = '</kml>';

It generated a kml file like this:

<?xml version="1.0" encoding="UTF-8"?>

<kml xmlns="http://earth.google.com/kml/2.1">
<Document>
<Style id="restaurantStyle">
<IconStyle id="restuarantIcon">
<Icon>
<href>http://maps.google.com/mapfiles/kml/pal2/icon63.png</href>
</Icon>
</IconStyle>
</Style>
<Style id="barStyle">
<IconStyle id="barIcon">
<Icon>
<href>http://maps.google.com/mapfiles/kml/pal2/icon27.png</href>
</Icon>
</IconStyle>
</Style>
<Folder>
<name>markers</name>

<Folder> ?????????????????????
</
Folder> ?????????????????????
<Folder>
<name>bar</name>
<Placemark id="placemark2">
<name>Buddha Thai &amp; Bar</name>
<description>2222 2nd Ave, Seattle, WA</description>
<styleUrl>#barStyle</styleUrl>
<Point>
<coordinates>-122.344391,47.613590</coordinates>
</Point>
</Placemark>
<Placemark id="placemark5">
<name>Sake House</name>
<description>2230 1st Ave, Seattle, WA</description>
<styleUrl>#barStyle</styleUrl>
<Point>
<coordinates>-122.345673,47.612823</coordinates>
</Point>
</Placemark>
</Folder>
<Folder>
<name>restaurant</name>
<Placemark id="placemark1">
<name>Pan Africa Market</name>
<description>1521 1st Ave, Seattle, WA</description>
<styleUrl>#restaurantStyle</styleUrl>
<Point>
<coordinates>-122.340141,47.608940</coordinates>
</Point>
</Placemark>
<Placemark id="placemark3">
<name>The Melting Pot</name>
<description>14 Mercer St, Seattle, WA</description>
<styleUrl>#restaurantStyle</styleUrl>
<Point>
<coordinates>-122.356445,47.624561</coordinates>
</Point>
</Placemark>
<Placemark id="placemark4">
<name>Ipanema Grill</name>
<description>1225 1st Ave, Seattle, WA</description>
<styleUrl>#restaurantStyle</styleUrl>
<Point>
<coordinates>-122.337654,47.606365</coordinates>
</Point>
</Placemark>
<Placemark id="placemark6">
<name>Crab Pot</name>
<description>1301 Alaskan Way, Seattle, WA</description>
<styleUrl>#restaurantStyle</styleUrl>
<Point>
<coordinates>-122.340363,47.605961</coordinates>
</Point>
</Placemark>
<Placemark id="placemark7">
<name>Piroshky Piroshky</name>
<description>1908 Pike pl, Seattle, WA</description>
<styleUrl>#restaurantStyle</styleUrl>
<Point>
<coordinates>-122.342834,47.610126</coordinates>
</Point>
</Placemark>
</Folder>
</Folder>
</Document>
</kml>

The placemarkers are in their belonging folder with the right name.
But there is a empty folder after the main folder of markers.
I tried to move or change a few lines, but the script I posted above
works best.

Do you have any suggestion about this?

I appreciate your help!

> None of that is anything to do with using KMLhttp://www.google.co.uk/search?hl=en&source=hp&q=php+mysql+tutorial&m...

[Rossko]

> I tried to move or change a few lines, but the script I posted above
> works best.
>
> Do you have any suggestion about this?

Yes, follow the given examples.
Randomly moving important lines outside of the loop is going to change
the behaviour, of course.

[pfu]

Hi, Rossko! Thanks for your reply!
Sorry I didn't make my question clear.

I don't want the empty folder. For my example, I want just two
folders, 'restaurant' and 'bar'.
I get the empty folder all the time no matter how I organize the
lines.

If it's possible, could you point out what's wrong with the scripts?

I tried to delete the empty folder

[Nymor]

> $temp = $row['type'] ;
> $kml[] = ' <Folder>';
>
> while ($row = @mysql_fetch_assoc($result))
> {
>   $category = $row['type'];
>   if ($category != $temp){
>   $kml[] = ' </Folder>';
>   $kml[] = ' <Folder>';
>   $kml[] = ' <name>' . htmlentities($category) . '</name>';
>   $temp = $category;
>   }

Hi,

Looks to me like there's a mistake is the above bit of logic...

Have you fetched/read the 1st row when you assign $temp? If not it
will be blank. You then open a folder and go into your while loop.
When the first row is then read in you set $category = $row['type']
and this will be different from you starting $temp (blank) and
therefore the code closes the folder you created and opens a new one -
hence your empty blank folder at the start.

You need to read the value of 'type' from the first row before you set
your starting $temp - and then loop through the recordset starting at
the 1st row again.

Hope that helps
Nymor

[Nymor]

....also as an aside...

There are 2 ways of opening the first folder for the recordset -
either open it before you start the loop or open it within the loop if
you're on the first record. You're doing it the 1st way but my linked
example above somewhere uses the 2nd (as with nested folders I have
some that didn't contain records so I don't want to create folders
beforehand but within the loop if needed).

Using the method you are you need (as mentioned) to read in your 1st
record's 'type' before creating the folder and also set the name of
the folder afetr you have created it (the one before the loop that is)
as as it stands at the moment the 1st folder won't have a name.

Nymor

[pfu]

Nymor, thank you very much for your replies!

You were totally correct. I re-edited the code. Now the empty folder
is gone. But I got another problem. I missed the first record. I know
little about php. Probably I made some stupid mistake but I couldn't
find it. What's wrong with it?
The code looks like:

$row = mysql_fetch_array($result) or die(mysql_error());

$temp = $row['type'] ;
$kml[] = ' <Folder>';

$kml[] = ' <name>' . htmlentities($temp) . '</name>';

while ($row = @mysql_fetch_assoc($result))
{
$category = $row['type'];
if ($category != $temp){
$kml[] = ' </Folder>';
$kml[] = ' <Folder>';

$temp = $category;
$kml[] = ' <name>' . htmlentities($temp) . '</name>';
}
output the placemark
}
$kml[] = ' </Folder>';

About the way of openning folder within loop, I tested my code, which
opens the first folder before loop, by querying a empty table. It
didn't turn any result. So is it really nessasary for openning folder
within loop? Nevertheless, I tried to follow your example (http://
groups.google.com/group/kml-support-server-side-scripting/
browse_thread/thread/28794c8a6dc5ae27#) . Probably I didn't get your
example well. I didn't find the line for openning a folder for the 1st
group of records if $row[vendor]=$vendor. However, I still wrote one
as below following your example. It's quiet wordy and the results is
the same as the one above.

$row = mysql_fetch_array($result) or die(mysql_error());
$temp = $row['type'] ;
$i=0;

while ($row = @mysql_fetch_assoc($result))
{
$category = $row['type'];

if ($category == $temp){
if ($i==0){
$kml[] = ' <Folder>';
$kml[] = ' <name>' . htmlentities($temp) . '</name>';
}
$i++;
}

else {
$i==1;

$kml[] = ' </Folder>';
$kml[] = ' <Folder>';

$temp = $category;
$kml[] = ' <name>' . htmlentities($temp) . '</name>';
}
output the placemark

}
$kml[] = ' </Folder>';

[Nymor]

Hi,

That's a bit messed up. Here's what the second method should look
like:-

$row = mysql_fetch_array($result) or die(mysql_error());
$temp =

'anything_that_isnt_the_type_of_row_1__so_just_xxx_if_you_want' ;
$i=0;

while ($row = @mysql_fetch_assoc($result))
{
if ($row['type'] != $temp){
if ($i!=0){
$kml[] = ' </Folder>';

}
$temp = $row['type'];
$kml[] = ' <Folder>';
$kml[] = ' <name>' . htmlentities($temp) . '</name>';
}

output the placemark
$i++;
}
if ($i<>0) {
$kml[] = ' </Folder>';
}

and these are the important parts of the logic ...

$temp =
'anything_that_isnt_the_type_of_row_1__so_just_xxx_if_you_want' ;
...this line just sets $temp to anything - it might even work with a
blank value - haven't tried that - but just make sure it ISN'T a value
that would be a valid 'Type'

$i=0;
...just a counter that we increment each time we loop to the next
record - only used as an easy way to know when we are on the first
row.

if ($row['type'] != $temp){
...is the 'Type' different from the previous one or the initial value
we set for $temp - if it is different we need to open a new folder.
However we also need to make sure that we close the previous folder if
it's already open and to that we can say that there will always be a
folder open unless we are on the 1st row - which is what the next bit
does...

if ($i!=0){
$kml[] = ' </Folder>';
}
... if $i!=0 we are not on row 1 so we have to close a folder first.

$temp = $row['type'];
$kml[] = ' <Folder>';
$kml[] = ' <name>' . htmlentities($temp) . '</name>';

...open the new folder.(This will happen whenever the Type changes
including on the 1st Row)

output the placemark
$i++
... increment the row counter $i

if ($i<>0) {
$kml[] = ' </Folder>';
}
...once we've looped through the recordset close the final folder -
but only if we had some records in the recordset to begin with
otherwise we'd have an orphaned </Folder> tag.

I hope that's right and the slightly long winded explanation helps. I
sure there are more efficient ways of doing this but it works.

Regards
Nymor

[pfu]

Nymor, thank you very much!

Your explanation is very explicit!
I got it, eventhough I am stupid.

Since $temp
='anything_that_isnt_the_type_of_row_1__so_just_xxx_if_you_want' , so
we don't need fetch the first row.
By the way, fetching the first row made the first record lost in the
output. I deleted it and now the first record appears in the kml file,
finally!

Have a nice day!

Ping

[Nymor]

No problem Ping - glad you got it working.

ps - it's not stupidity - it's just sometimes it takes a while for the
fog to clear ... I know that from my own experiences ;)

The way I work it, and did so when I needed to do something similar to
yours, is to write it out with pencil and paper long-hand ... write
out your variables and then work through each loop ... what is x set
to, what is the value of y etc ... and it soon becomes clear where you
need extra settings - ie I need to know what row I'm on therefore I
need a counter ($i) etc etc.

Good luck

Reagrds
Nymor