Indexing mysql result set with PHP

PHP
1

I am wanting to index a mysql result set to use outside of the while loop.
I have a simplistic script set up that runs the standard while loop on the result set. That works fine.
I want to get at each row of specific columns in order to put that data into an indexed array, for example

$lat['lat'][0], $lng['lng'][0]

That way I can index through the results outside the loop. In particular, to be used for mapping coordinates.

So, I have a sql table named address with columns named latitude and longitude which contain respective coordinates.
I have this code which will echo out each row inside the loop


        $query = "SELECT latitude, longitude ";
	$query .= "FROM address";
	$results = mysqli_query($dbc,$query);
	
	while($r=mysqli_fetch_object($results)){
		 echo "<pre>";
		 echo $r->latitude;
		 echo "<br>";
		 echo $r->longitude; 
	}

However, I need to have access to each row of latitude and longitude for the code below:

<?php if (!empty( $latlong['lat'][0] ) && !empty( $latlong['long'][0] ) )  { ?>
	places.push(new google.maps.LatLng( <?php echo $latlong['lat'][0] ?>, <?php echo $latlong['long'][0] ?> ) );
	<?php } ?>
<?php if (!empty( $latlong['lat'][1] ) && !empty( $latlong['long'][1] ) )  { ?>
	places.push(new google.maps.LatLng( <?php echo $latlong['lat'][1] ?>, <?php echo $latlong['long'][1] ?> ) );
	<?php } ?>
	
	<?php if (!empty( $latlong['lat'][2] ) && !empty( $latlong['long'][2] ) )  { ?>
	places.push(new google.maps.LatLng( <?php echo $latlong['lat'][2] ?>, <?php echo $latlong['long'][2] ?> ) );
	<?php } ?> 

        and so on...

I asked a similar question earlier, but this one is more specific. It seemed best to start a new thread.

[edit]reference
Iterating over multiple value fields for a single column[/edit]

2

Hi,

There’s actually an easier way to take the coordinates from the DB in PHP and add them to a Google map via JS without using all those IF statements.

You can use the mysqli_fetch_all function to get all the results as an associative array:


$query = "SELECT latitude, longitude FROM address";
$results = mysqli_query($dbc, $query);

$mapCoords = mysqli_fetch_all($results, MYSQLI_ASSOC);

You can then use the [fphp]json_encode[/fphp] function to convert the array into JS, which you can then loop over and add the LatLng objects:

// Output PHP directly into JS script
var coords = <?php echo json_encode($mapCoords); ?>;
for (var i = 0; i < coords.length; i++) {
    places.push(new google.maps.LatLng(coords[i].latitude, coords[i].longitude);
}
3

Thanks, I think that should work really nicely. However, I’m not able to get the markers to show. It seems my PHP version is to low to use mysqli_fetch_all. I’ve adapted the code and except for the markers not showing up, it is working.


<?php
	// Connection
	try {
		$db = new PDO("mysql:host=;dbname=;port=","","");
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$db->exec("SET NAMES 'utf8'");
	} catch (Exception $e) { // $e catches data sent by Exception
		echo "Not connected";	
		exit;
	}
	
	// Query
	try {
		$results = $db->query("SELECT * FROM address");
		if($count = $results->rowCount()) {
			
			while($row = $results->fetchAll(PDO::FETCH_ASSOC)) {
				
				$data[] = $row;
				
			}
			
		}
		
	} catch (Exception $e) {
		echo "no query happedend";
		exit;
	} 
	
	
		
?>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
html {
	height: 100%
}
body {
	height: 100%;
	margin: 0;
	padding: 0
}
#map-canvas {
	margin:40px 0;
	height: 100%
}
</style>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script src="https://maps.googleapis.com/maps/api/js?key=-G4M&sensor=false"></script>
<script>
	(function () {
		
	window.onload = function() {
	
	<?php foreach ($data as $marker) { ?>
			
	
	var options = {
		
		zoom: 11,
		center: new google.maps.LatLng(<?php echo $marker[0]['latitude'] ?>, <?php echo $marker[0]['longitude'] ?>),
		mapTypeId: 'roadmap'
			
	};
	
	var map = new google.maps.Map(document.getElementById('map-canvas'), options);
	
	
	
	var coords = <?php echo json_encode($data); ?>;
	
	for (var i = 0; i < coords.length; i++) {
    	places.push(new google.maps.LatLng(coords[i].latitude, coords[i].longitude));
		
			var marker = new google.maps.Marker({		
				position: places[i],
				map: map
						
			});
		}
	<?php } ?>
	}
	})();
</script>
</head>
<body>
<div id="map-canvas" style="height:400px;top:30px;"></div>
<form method="get" action="<?php echo $_SERVER['PHP_SELF'] ?>">
     <label for="usersearch">Find Yardsale:</label>
     <br />
     <input type="text" id="usersearch" name="usersearch" />
     <br />
     <input type="submit" name="submit" value="Find" />
</form>
</body>
</html>

I’m really liking the use of json_decode. That would not have occurred to me.
Thanks again

4

And if you change query fields as so, does it work?

$results = $db->query("SELECT latitude, longitude FROM address");
5

Hi,

You’ve got a couple of small issues with your code that are stopping it from working properly:

Calling fetchAll returns all rows in the result set, so you don’t need to call it in a while loop. Instead, you can just do this:


try {
	$results = $db->query("SELECT latitude, longitude FROM address");
	$data = $results->fetchAll(PDO::FETCH_ASSOC);
}

You don’t need to assign the return value to an array as fetchAll already returns an array. Unfortunately, the rowCount function doesn’t work with SELECT queries, so you have to count the results some other way, but fortunately fetchAll will just return an empty array if no results were found, which is easy to check for.

You’re still looping over the markers in PHP, which is no longer necessary as we’re doing the looping in JS. Try this:

(function () {
    
    var coords = <?php echo json_encode($data); ?>,
        options = {
            zoom: 11,
            center: new google.maps.LatLng(coords[0]['latitude'], coords[0]['longitude']),
            mapTypeId: 'roadmap'    
        },
        map = new google.maps.Map(document.getElementById('map-canvas'), options);

    for (var i = 0; i < coords.length; i++) {
        var latLng = new google.maps.LatLng(coords[i].latitude, coords[i].longitude),
            marker = new google.maps.Marker({        
                position: latLng,
                map: map    
            });
        places.push(latLng);
    }

})();

Note that you don’t actually need to wrap your JS in a window.onload function. Just place the script block right before the closing </body> tag and the JS will only run once the page has loaded.

6

Ahhh, that works beautifully and is succinctly coded to boot. I’m very appreciative of the tips -thank you. Oh, and obviously I meant to say liking json_encode.
For others that may look at this, please note to declare the places var before the for loop.
Thanks again fretburner!