Files used

Right-click, save ...

• mysql_svg_php.shtml
• mysql_svg_php.psvg.txt (remove txt-extension and set DB-connections after download)
• mysql_countrynames.sql
• mysql_countrygeom.sql

Technical Details

Please refer to Serverside SVG generation with PHP to see how you may get SVG content from your PHP server. PHP is also very useful to quickly connect to databases and get dynamic content in return. We use MySQL as a freely available database that integrates well within server environment. See www.mysql.org for full documentation.

Following example uses very simple commands but may seem confusing as many elements nest into others. Here what the file does in a few steps: PHP generates a SVG header, connects to the MySQL database and (depending on query stings on the URL) delivers randomly or not geometry and names for some European country. The SVG viewer renders this graphical content. JavaScript re-centres the viewport and sets font-sizes and stroke-widths.

We first show the complete code and describe it afterwards. You may also have a look at the DB table description and download SQL-files from there. PHP-Code is colored in red, JavaScript in green, SQL-statements in mauve while regular SVG is in blue.

001 <?
002   header("Content-type: image/svg+xml");
003   print('<?xml version="1.0" encoding="iso-8859-1"?>');
004   $svgwidth=800;
005   $svgheight=500;
006 ?>
007 
008 <!DOCTYPE svg PUBLIC "-//W3C//DTD SVG 20010904//EN"
009        "https://www.w3.org/TR/2001/REC-SVG-20010904/DTD/svg10.dtd">
010   <svg id="mainmap" xml:space="preserve" 
                          style="shape-rendering:geometricPrecision;
011        text-rendering:geometricPrecision; image-rendering:optimizeQuality"
012        width="<?=$svgwidth;?>px" height="<?=$svgheight;?>px" 
013        viewBox="-6148 0 11425 8275" onload='init(evt)' overflow='visible'>
014 
015 <script>
016   <![CDATA[
017     function init(evt){
018       var bbox=document.getElementById('countrygroup').getBBox();
019       var viewboxvals=bbox.x+' '+bbox.y+' '+bbox.width+' '+bbox.height;
020       document.getElementById('mainmap').setAttributeNS(null,'viewBox',viewboxvals);
021       var myviewboxx;
022       var myviewboxy;
023       var myscale;
          // viewbox narrower than viewport
024       if ((bbox.width/bbox.height) >= (<?=$svgwidth;?>/<?=$svgheight;?>)) {
025         myscale = <?=$svgwidth;?>/bbox.width;              
026         myviewboxx = bbox.x;
027         myviewboxy = bbox.y+(bbox.height/2)+
                         ((bbox.width*<?=$svgheight;?> )/(2*<?=$svgwidth;?>));
028       }
029       else { // viewbox straiter as viewport
030         myscale = <?=$svgheight;?>/bbox.height;
031         myviewboxx = bbox.x+(bbox.width/2)-
                         ((bbox.height*<?=$svgwidth;?>)/(2*<?=$svgheight;?>));
032         myviewboxy = bbox.y+bbox.height;
033       }
034       var tldtext=document.getElementById('tldbg');
035       tldtext.setAttributeNS(null,'x',myviewportx+10/myscale);
036       tldtext.setAttributeNS(null,'y',myviewporty-10/myscale);
037       tldtext.setAttributeNS(null,'font-size',(30/myscale)+'px');
038       document.getElementById('countrypath').setAttributeNS(null,'stroke-width",3/myscale);
039       var tbox=tldtext.getBBox();
040       var tldbg=document.getElementById('tldbg');
041       tldbg.setAttributeNS(null,'x',tbox.x-3/myscale);
042       tldbg.setAttributeNS(null,'y',tbox.y-3/myscale);
043       tldbg.setAttributeNS(null,'width',tbox.width+3/myscale);
044       tldbg.setAttributeNS(null,'height',tbox.height+3/myscale);
045     }
046   ]]>
047 </script>     
048 
049 <desc>Random or querystring country boundary generator based on svg,
                          mysql and javascript</desc>
050 <defs>
051   <style type="text/css">
052     <![CDATA[
053       .countrypoly {stroke:#aaa;stroke-width:0;fill:#009240;fill-opacity:0.15}
054       .bg {fill:#fff;fill-opacity:0.5}
055     ]]>
056   </style>
057 </defs>
058 
059 <?
060   function querydb($selcols,$equa) {
061     $host = "your_server.domain.tld";
062     $user = "your_user";
063     $pword = "your_password";
064     $database = "your_database";
065     $serveradmin = "admin@yourdb.tld";
066     @mysql_pconnect($host, $user, $pword) or die 
                          ("Connection failed, contact $serveradmin !");
067     @mysql_select_db($database) or die 
                          ("Could not select $database, contact $serveradmin !");
068     $presult = mysql_query("SELECT $selcols
                   FROM countrynames,countrygeom WHERE $equa");
069     return mysql_fetch_array($presult);
070  }
071  $country = $_GET["country"]; // NECESSARY SINCE PHP-UPGRADE
072  $lang = $_GET["lang"]; // NECESSARY SINCE PHP-UPGRADE
073   if(!$lang){
074     $lang="en";
075   }
076   $langtext="cname".$lang;
077 
078   if(!$country) {  // if random
079     $temparray = querydb("countrynames.tld,countrynames.$langtext,countrygeom.geom",
080                 "countrynames.tld=countrygeom.tld ORDER BY RAND() LIMIT 1");
081     $mytext = "TLD .$temparray[tld] ($temparray[$langtext])";
082     $mygeom = $temparray[geom];
083   }
084   else {  // if a querystring exists
085     $temparray = querydb("countrygeom.tld,countrygeom.geom",
086                          "countrygeom.tld LIKE '%$country%' LIMIT 1");
087     $mytld = $temparray[tld];
088     $mygeom = $temparray[geom];
089     $temparray = querydb("countrynames.$langtext",
                       "countrynames.tld LIKE '%$mytld%'");
090     $myname = $temparray[$langtext];
091     if($myname==""){
092       $mytext = "TLD .$mytld (no database record for country name)";
093     } else {
094       $mytext = "TLD .$mytld ($myname)";  
095     }
096   }
097   if($mygeom==""){
098     $mygeom="M0,0h300v-5h-300z";
099     $mytext = "(no database record for .$country TLD)";
100   }
101 ?>
102 
103  <g id="countrygroup">
105    <path id="countrypath" class="countrypoly" d="<?=$mygeom;?>"/>
106  </g>
107  <rect id="tldbg" class="bg" x="0" y="0" width="0" height="0" /> 
108  <text id="tldtext" x="0" y="0" style="font-size:0;"><?=$mytext;?></text>
109 </svg>

To access the database you need to know what is in the database tables. See below for the two used tables. If you want to build those tables with content you may use countrynames.sql for the bilingual names and countrygeom.sql for geometry.

mysql> show columns from countrygeom;
+-------+-----------------+------+-----+---------+----------------+
| Field | Type            | Null | Key | Default | Extra          |
+-------+-----------------+------+-----+---------+----------------+
| cid   | int(8) unsigned |      | PRI | NULL    | auto_increment |
| tld   | char(2)         |      |     |         |                |
| geom  | text            | YES  |     | NULL    |                |
+-------+-----------------+------+-----+---------+----------------+

mysql> select * from countrygeom;
+-----+-----+---------------------------------------------------------
| cid | tld | geom                                                
+-----+-----+---------------------------------------------------------
|   1 | yu  | M-1431 6308l-3 -14 9 -8 7 6c-4,6 -6,13 -13,16z -12,1....    
|   2 | ua  | M-2337 6531l4 -22 0 -4 -33 -14 -15 14 -1 29 9 2 29,2....
|  .  |  .  |    .           
|  .  |  .  |    .
|  .  |  .  |    .

mysql> show columns from countrynames;
+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| cid     | int(8) unsigned |      | PRI | NULL    | auto_increment |
| tld     | char(2)         |      |     |         |                |
| cnameen | char(128)       | YES  |     | NULL    |                |
| cnamede | char(128)       | YES  |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+

mysql> select * from countrynames;
+-----+-----+------------------------+-------------------------+
| cid | tld | cnameen                | cnamede                 |
+-----+-----+------------------------+-------------------------+
|   1 | yu  | Yougoslavia            | Jugoslawien             |
|   2 | ua  | Ukraine                | Ukraine                 |
|   3 | uk  | United Kingdom         | Gross Britannien        |
|   4 | tr  | Turkey                 | Turkei                  |
|   5 | sm  | San Marino             | San Marino              |
|   6 | sk  | Slovakia               | Slowakei                |
|   7 | si  | Slovenia               | Slowenien               |
|  .  |  .  |    .                   |   .                     |
|  .  |  .  |    .                   |   .                     |
|  .  |  .  |    .                   |   .                     |

As shown the code contains SVG, SQL and PHP, their functions and statements are not invoked in a linear order from the top to the end of the above given code. To keep things clear we will describe it anyhow in a linear way.

The first 6 lines send content-type, xml-header and initialise variables. We heave to print the xml-header using PHP, because the xml-header conflicts with the tags responsible for distinguishing SVG and PHP.

Lines 8 to 13 show the stand doctype-declaration, the svg root-element and a description-tag. Note that we use php-variables to replace the width and height-section. PHP variables - similar to perl - start with a dollar-sign. We also start the following JavaScript function.

Lines 15 to 46 show the code of a JavaScript function that is called when the static part of the PSVG is fully loaded. Its function is to re-centre the viewBox to the displayed country polygon. Indeed the counties stored into the DB are only polygons from an entire Europe map, so those polygons have their specific position and size.

Important functions here are:

mydocument.myelement.getBBox() at line 19 that returns the children x,y, width and height. Those are reused to set new viewBox variables in line 21.

The if-statement in lines 25 to 34 is necessary to calculate the viewport values in the main coordinate system. This is needed to correctly place and set font sizes of the country name in line 35 to 37.

Line 38 applies a scale-independent outline width to the country polygon.

Line 39 gets the extent of the country name text to place a box behind the words to ease readability when text gets in conflict with the country polygon.

Lines 49 to 56 contain styles definitions.

At line 58 starts the PHP statements that contain the db-queries. The results of these queries are used in the SVG block at line 100 to 105. The PHP block uses if-statements because the PSVG file may be called stand alone and accepts URL encoded variables like mysql_svg_php.psvg?lang=de&country=ie. If those variables are not provided the function sets default to English (line 70) and performs a random query in the database (line 76) instead of the specific country query at line 82.

The random query is a SQL-statement that randomly sorts the country table but returns only one row. This query performs a join of the tables countrynames and countrygeom to get the geometry and the full country name. This join is defined at the query function line 67 when both tables are called after the FROM statement. To select a specific column from a specific table statements like mytablename.mycolumnname are used during column selection (see lines 76 or 82).

The specific county query needs one more SQL query at line 86 to get the full county name. This is because the two tables have different lengths: the polygons are also available for some countries in Asia and Africa, but the full names aren't. The random query only operates in the European countries (thanks to the tables join), but the specific county query works for all countries.

The query function returns an array called $temparray. More precisely it is a hash (named array) like in perl. That's why the values are accessed like in the lines 84 to 85.

Lines 88 to 96 handle missing values for geometry or names.