Re: creating an array from a mySQL query


ericv wrote:
I am new to php and mySQL and have a problem..

we have the following code that successfully retreives data from a
mySQL database and then posts it to the Google Maps API which then maps
the latitude (a field in the db) and longitude (yet another field in
the db).

// Retrieve all the data from the table
$result = mysql_query("SELECT * FROM points_of_interest WHERE
(KEY_ID='$KEY')")
or die(mysql_error());
$num_rows = mysql_num_rows($result);
if ($num_rows==0){
echo "<font face='Arial' size='2'><c><b>Point of interest not
found.</b><br />Please try again.<a href='main.php'>Click here</a> to
try another point.</c></font>";
} else{
// store the record of the table into $row
$row = mysql_fetch_array( $result );

The Google API is javascript and we then retrieve the mySQL data for
mapping with the following code (inside of the google javascript):
// retrieve latitude and longitude for mapping
<?php
print "var latitude = $row[LAT];";
print "var longitude = $row[LONG];";
print "var namelong = '$row[NAME_LONG]';";
print "var address = $row[ADDR_NUM];";
print "var stname = '$row[ST_NAMEFULL]';";

I would like to use an array to feed multiple rows of data to the
Google API to map multiple data points (multiple latitude and longitude
values)..

I have looked in the php manuals for how to define an array, but I
don't seem to get a clear idea of how to do it.

Any ideas?

Thanks
Eric V
Gurnee, IL


To take your $result, and put it into an array, you need to feed each
row of
an array through a loop. The best way that I like is to use a while
loop, and put
it into an array. Although it does get complicated attempting to do
multi-dimensional
associative arrays, I would suggest using the constant MYSQL_BOTH as
the type of
array to put it into. In your case, to fetch the array and put it into
an array, it would
look like this.

// Grab all coordinates and put them in an array
while ( $row = mysql_fetch_array( $result, MYSQL_BOTH ) {
$coordinates[] = $row;
}

Using both associative and a numerical array, you can then use a for
loop to cycle through
all coordinates like this. Using the associtive array, you can use
this to define the fields inside
the second dimension of the array.

for ( $i = 0; $i < count($coordinates); $i++) {
print "var longitude = $coordinates['long']";
print "var namelong = $coordinates['name_long']";
print "var address = $coordinates['addr_num']";
print "var stname = $coordinates['st_namefull']";

// Do whatever processing required to map the coordinate to
continue to the next

}

Hope this helps.

Respectfully Submitted,
Huck Finn

.

Relevant Pages