PHP,MySQL, and Google Maps

I've been playing around with the Google Map API, but when I started playing around with retrieving points to map from a MySQL database, I guess I'm in over my head. For one thing, the Google Maps tutorial, http://www.map-server.com/googlemaps/tutorial.html uses PHP, and I've never done a project in PHP. The tutorial uses mysql_connect, while my PHP text "PHP and MySQL Web Development" uses the improved call mysqli_connect, available with PHP 5. Does DH's PHP 4.3.10 offer the improved calls?

I'm not getting any DB connect errors but I'm also getting no map. (See http://ykfp.org/googlemaps/dbmap1.htm) What are some good tricks in PHP to see if the connection is made and the arrays contain the correct stuff?

My code currently looks like this:
<html>
<head>
<title>Yakama Fisheries Facilities in the Yakima Basin</title>
<script src="http://maps.google.com/maps?file=api&v=1&key=********" type="text/javascript"></script>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><style type="text/css">

</style></head>
<body>
<p><strong>Yakama Fisheries Facilities in the Yakima Basin</strong></p>
<div id="map" style="width: 800px; height: 600px"></div>
<script type="text/javascript">
//<![CDATA[

var map = new GMap(document.getElementById("map"));
map.addControl(new GLargeMapControl());
map.addControl(new GMapTypeControl());
map.centerAndZoom(new GPoint(-120.583735, 46.633379), 6);

// Creates a marker whose info window displays the given number
function createMarker(point, number)
{
var marker = new GMarker(point);
// Show this markers index in the info window when it is clicked
var html = number;
GEvent.addListener(marker, "click", function() {marker.openInfoWindowHtml(html);});
return marker;
};
<?php
$link = mysql_connect('mysql.ykfp.org', 'selectonly', 'satus2')
or die("Could not connect: " . mysql_error());
mysql_selectdb('db28989a',$link) or die ("Can't use db28989a : " . mysql_error());

$result = mysql_query("SELECT * FROM Export_Output",$link);
if (!$result)
{
echo "no results ";
}
while($row = mysql_fetch_array($result))
{
echo "var point = new GPoint(" . $row['long'] . "," . $row['lat'] . ");n";
echo "var marker = createMarker(point, '" . $row['project_na'] . "');n";
echo "map.addOverlay(marker);n";
echo "n";
}

mysql_close($link);
?>

//]]>
</script>
</body>
</html>

I think this answers my question about the availability of mysqli_ procedures on DH. I had Dreamweaver MX 2004 generate some PHP code that was able to retrieve records from my MySQL table and write it to an HTML table. I hacked the code slightly to replace mysql_connect with mysqli_connect. This fails with a "Fatal error: Call to undefined function: mysqli_connect() in /home/.edison/ykfpdata/ykfp.org/googlemaps/table2i.php on line 9". Therefore it appears the mysqli_ procedures are not available on DH. Also, Dreamweaver fails to color the "i" procedures blue in its code window, so it doesn't look like mysqli is support there either.

My non working test code:

<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_ynsites = "mysql.ykfp.org";
$database_ynsites = "db28989a";
$username_ynsites = "selectonly";
$password_ynsites = "satus2";
$ynsites = mysqli_connect($hostname_ynsites, $username_ynsites, $password_ynsites, $database_ynsites) or trigger_error(mysql_error(),E_USER_ERROR);

$maxRows_Recordset1 = 10;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;

// mysql_select_db($database_ynsites, $ynsites);
$query_Recordset1 = "SELECT * FROM ynsites";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $ynsites) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>

<table border="1">
<tr>
<td>SITES_ID</td>
<td>X_COORD</td>
<td>Y_COORD</td>
<td>PROJECT_NA</td>
<td>DESCRIP</td>
<td>LATITUDE</td>
<td>LONGITUDE</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Recordset1['SITES_ID']; ?></td>
<td><?php echo $row_Recordset1['X_COORD']; ?></td>
<td><?php echo $row_Recordset1['Y_COORD']; ?></td>
<td><?php echo $row_Recordset1['PROJECT_NA']; ?></td>
<td><?php echo $row_Recordset1['DESCRIP']; ?></td>
<td><?php echo $row_Recordset1['LATITUDE']; ?></td>
<td><?php echo $row_Recordset1['LONGITUDE']; ?></td>
</tr>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

In reply to:
Does DH's PHP 4.3.10 offer the improved calls?

No, you have to use PHP 5 in order to use the mysqli-functions. You can upgrade to PHP 5 from the Control Panel.

"What are some good tricks in PHP to see if the connection is made and the arrays contain the correct stuff?"

I discovered some good stuff using my browser's View Page Source window. When I looked at the source window of http://ykfp.org/googlemaps/dbmap1.htm , I could see that the php stuff wasn't getting executed. I changed the file extension to php, and Page Source showed me the the mysql_fetch_array loop was working one time for each of the 15 records in my database table, but I was getting nulls sent to my Gpoint and markers:

var point = new GPoint(,);
var marker = createMarker(point, '');
map.addOverlay(marker);

I was puzzled until someone pointed out that in my working Dreamweaver generated code, my table item names were capitalized, but not in my Google Maps code. I hadn't realized that the column names got capitalized by the way I imported the dbf file into MySQL (Navicat). Once I capitalized the item names, the script works: http://ykfp.org/googlemaps/dbmap1.php

This technique looks like a easy way to go from a hand held GPS file to online map.

Should I do that, swich to PHP5, considering this guy seems to be having problems: http://discussion.dreamhost.com/showflat.pl?Cat=&Board=forum_programming&Number=32504&page=0&view=collapsed&sb=5&o=14&part=

Does mysqli run that much better?

Edited by majorgeek on 10/31/05 02:50 PM (server time).

I think many DreamHost customers have switched to PHP 5 without any problems. Most offen it shouldn't cause you any problems.

I haven't tried out mysqli, so I don't know if it runs any better.