1. Google Map’s PHP API COMP 2920 Assignment 3 By Azzam Labeeb Mingang Zhu June 19, 2012

2. Overview • Introduction • The Application Tasks • Prototype Screenshots • The Steps to Build the Application • Some Important PHP Codes • Some Requirements for the Application • The Problems that We Encountered • The Stuff that We Learned • References

3. Introduction The Public Schools in City of Vancouver In this assignment, we build a Google Map API application that helps Vancouver’s users to look up the nearest public schools in city of Vancouver, add schools on Google Map, and display all the Public Schools in Vancouver.

4. The Application Tasks This application allows users to: • Search the nearest schools in city of Vancouver • Add the new schools on the map • Display all the schools in city of Vancouver

5. Find a school prototype

6. Add schools prototype

7. Display schools prototype

8. The Steps to Build the Application • Setting up my SQL database connection • Creating a table in a mySQL database • Populating the table • Outputting XML with PHP • Creating a map

9. 1. Setting up my SQL database connection • It is a good idea to put our database connection information in a separate file. That keeps our database away from sharing and strangers. • The tutorial of Google Map API suggests to create the database connection file in this way (phpsqlsearch_dbinfo.php): <? $username="a*******_root"; $password=“******"; $database="a*******_schools"; $webhost="mysql16.000webhost.com"; ?>

10. 2. Creating a table in a mySQL database • The second step is to create the (markers) table in mySQL that stores the data of schools inVancouver. CREATE TABLE `markers` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR( 60 ) NOT NULL , `address` VARCHAR( 80 ) NOT NULL , `lat` FLOAT( 10, 6 ) NOT NULL , `lng` FLOAT( 10, 6 ) NOT NULL )

11. 3. Populating the table • We used http://data.vancouver.ca to download schools.csv file that contains the data of schools in Vancouver. • We used http://www.withdata.com/data-convert/csv-to-sql.php to convert our file (schools.csv) to SQL format to populate it in mySQL.

12. 4. Outputting XML with PHP To make sure that the XML output works, we use a sample query parameters to the URL : (e.g. ?lat=49&lng=-123&radius=20)

13. Some Important PHP Codes • The Google tutorial uses the function parseToXML to output XML. function parseToXML($htmlStr) { $xmlStr=str_replace('<','&lt;',$htmlStr); $xmlStr=str_replace('>','&gt;',$xmlStr); $xmlStr=str_replace('"','&quot;',$xmlStr); $xmlStr=str_replace("'",'&apos;',$xmlStr); $xmlStr=str_replace("&",'&amp;',$xmlStr); return $xmlStr; }

14. Some Important PHP Codes (continued) • The tutorial uses this code below that allows us to insert new row with user data. $query = sprintf("INSERT INTO markers " . " (id, name, address, lat, lng ) " . " VALUES (NULL, '%s', '%s', '%s', '%s');", mysql_real_escape_string($name), mysql_real_escape_string($address), mysql_real_escape_string($lat), mysql_real_escape_string($lng)); $result = mysql_query($query);

15. Some Requirements for the Application • To make our Google map API application works, we need to get a key and insert it in the javascript tag. <script src="http://maps.google.com/maps?file=api&v=2&key=AIzaSyDR1N9gFsxj5J5jRf3b3Jdul62bR0lGRgU" type="text/javascript"> </script> • We used www.data.vancouver.ca to get the data of the schools in Vancouver.

16. The Problems that We Encountered • At the beginning, we couldn’t get XML output because the tutorial uses domxml_new_doc(“") command (older version of PHP), so we used DOMDocument(“") instead (new version). • When we run the application on Internet Explorer, we can’t get the added locations on the map. So we used Mozilla Firefox instead.

17. The Stuff that We Learned • We chose Google Map API application because it is interesting and beneficial. • We learned how to build the application from mySQL database, PHP, XML, and javascript. • mySQL used to create a table and store data. • JavaScript used to interact with Google Maps API • PHP used to interact with database

18. References • Creating a Store Locator with PHP, MySQL & Google Maps Google Geo APIs Team, January 2008.https://developers.google.com/maps/articles/phpsqlsearch • From Info Windows to a Database: Saving User-Added Form Data Google Maps API Team, September 2007. https://developers.google.com/maps/articles/phpsqlinfo • Using PHP/MySQL with Google Maps Google Geo Team, With contributions from Lary Stucker, Maps API Developer, April 2007. https://developers.google.com/maps/articles/phpsqlajax