Folie 1

1
Technische Universität Berlin
SVG with PHP and aMySQL Database
Andreas Fuls
2
Information Flow

• The information to create a SVG map application
  is often stored in different components
• Content of text and geometry in a database.
• Style in a CSS file.
• Layout in SVG.
• Interactivity in a Java script.

3
MySQL

• MySQL is
• a free database system
• available for many platforms (Windows, Linux,
  Mac, Solaris)
• easy to use with PhpMyAdmin (user, databases,
  settings)
• Documentation http//dev.mysql.com/doc/
• MySQL 5.1 Reference Manual http//dev.mysql.com/d
  oc/refman/5.1/en/
• support of WKT-geometry (2D)
  http//dev.mysql.com/doc/refman/5.1/en/gis-class-g
  eometry.html

4
Spatial Columns in MySQL

• GeomFromText() creates geometry valuesINSERT
  INTO geom VALUES (GeomFromText('POINT(1 1)'))
• SET _at_g 'LINESTRING(0 0,1 1,2 2)'INSERT INTO
  geom VALUES (LineStringFromText(_at_g))
• The AsText() function converts a geometry from
  internal format into a WKT string SELECT
  AsText(geometry) FROM geom

5
MySQL Database Administration

• Example of MySQL 3.23.55 at
• http//mca.bv.tu-berlin.de/dbadm/phpMyAdmin/ ,
  or
• http//130.149.168.136/dbadm/phpMyAdmin/
• Database country
• User web
• Password carto07
• Tables countrygeom, countrynames

6
PHP

• PHP is a widely-used general-purpose scripting
  language that is especially suited for Web
  development and can be embedded into HTML.
• server-side scripting
• generate dynamic page content
• send and receive cookies
• Support for a wide range of databases Adabas D,
  dBase, Empress, FilePro (read-only), Hyperwave,
  IBM DB2, Informix, Ingres, InterBase, FrontBase,
  mSQL, Direct MS-SQL, MySQL, ODBC, Oracle (OCI7
  and OCI8), Ovrimos, PostgreSQL, SQLite, Solid,
  Sybase, Velocis, Unix dbm.
• Manual http//de2.php.net/manual/en/index.php

7
PHP Syntax

• http//www.igg.tu-berlin.de/ltusergt/step01.php
• lt?php // The next line prints Hello World! on
  screen echo Hello World!?gt
• Exercise
• Extent the output of desire.php to more then one
  item.

8
PHP MySQL (1)

• http//www.php.net/manual/en/book.mysql.php
• mysql_connect Open a connection to a MySQL
  Server
• mysql_select_db Select a MySQL database
• mysql_query Send a MySQL query
• mysql_num_rows Get number of rows in result
• mysql_result Get result data
• mysql_close Close MySQL connection

9
PHP MySQL (2)

• http//www.php.net/manual/en/book.mysql.php
• mysql_pconnect Open a persistent connection to
  a MySQL server
• mysql_select_db Select a MySQL database
• mysql_query Send a MySQL query
• mysql_fetch_array Fetch a result row as an
  associative array, a numeric array, or both
• mysql_close Close MySQL connection

10
Which Browser?

• Status of native SVG support
• Mozilla Firefox
• https//developer.mozilla.org/en/SVG_in_Firefox
• http//www.mozilla.org/projects/svg/status.html
• Opera
• http//www.opera.com/docs/specs/svg/

11
Exercise

• Write a HTML-menu in PHP to select one of the
  country names listed in the database country in
  table countrynames and transfer the selected
  country name and a language to the file
  mysql_svg_php.php using the method POST.
• Components of the program
• Connection to database country
• Get list of all country names
• Create a select box with all country names
• Display a header and give instructions to the
  user
• Transfer selected country code to
  mysql_svg_php.php using the method POST
• Disconnection to database country

12
Literature

• PHP Manual
• http//www.php.net/manual/en/index.php
• MySQL Manual
• http//dev.mysql.com/doc/refman/5.1/en/index.html
• Gregor Kuhlmann and Friedrich MüllmerstadtMySQL
  mit PHP Der Schlüssel zu Datenbank-Design und
  Programmierung, Rowohlt Taschenbuch Verlag ,
  Reinbek bei Hamburg , 2003.
• W. Jason Gilmore and Bob BrylaBeginning PHP and
  Oracle from novice to professional. learn how
  to build dynamic, database-driven web sites with
  the popular PHP language and powerful Oracle
  database , Books for professionals by
  professionals The expert's voice in web
  development , Apress Berkeley, Calif. , 2007.