Maze

hell you all first of all - many many thannks for the answers here a more theoretical question - that comes up to my mind - perhaps this is better adressed in a new thread - but i like to post it here - since you re really mysql-experts - and helped me alot so far.. Relational databases have fixed schemas. The sheme of data that is derived from a request at the planet-file may differ dynamically.what if we need a schema that can adjust to a variable number of tags and adapt to changes in the underlying data. for example - see the dataset below - with its sheme: the second dataset has the following tags / values-combination: <tag k="fax" v="+49 621 1259 779"/> which the first does not have. the third dataset has the following tags / values-combination: <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> ... which all others do not have..and the last dataset has the following tags / values-combination: <tag k="smoking" v="separated"/> ...which all others do not have see below; $xmlstr = <<<XML <data> <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data> XML; Relational databases have fixed schemas. This is one of the limitations of working with them that you have to live with. the sheme of data that is derived from a request at the planet-file may differ dynamically. what if we need a schema that can adjust to a variable number of tags and adapt to changes in the underlying data. is this covered by this sheme? - of hard coded column names as in $fields = array('id','lat','lon','name','amenity','operator','vending'); Dont we need to another sheme to pick up any additional columns added to the pois table $sql = "SHOW COLUMNS FROM pois"; $fields = array(); $res = $db->query($sql); while ($row = $res->fetch_row()) { $fields[] = $row[0];} hope i was able to make clear what the intended aims & goals of this posting are... AGAIN: The sheme of data that is derived from a request at the planet-file may differ dynamically. what if we need a schema that can adjust to a variable number of tags and adapt to changes in the underlying data. Can this be coverd by a hardcode struture - or which one is needet here... to fit most? thanks inadvane for any and all help in your answers matz

hello dear Jacques1, ginerjm, jazzman1 many thanks for the answers - will rread dem more thouroghly later the day. this i-cafe in south-spain (malaga - andalusia ) is so crowded that we cannot discuss php-things i answer later meanwhile many many greetings

hello you all - hello and many many thanks for all your answers at the moment i am in a internet -cafe with a bad bad connection - i will answer more later the day.... for the moment i have the following - in reply to ginerjm i was appliying the above mentioned code via sql-query-frontend in the phpmyadmin - but here the full output - after running the code again via (terminal ) <?php $username = 'root'; $password = 'rimbaud'; $dbh = new PDO('mysql:test_10_name;localhost;charset=utf8', $root, $rimbaud); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql= "CREATE TABLE `pois` ( `id` bigint(20) unsigned NOT NULL, `lat` float(10,7) NOT NULL, `lon` float(10,7) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) )"; $query = $dbh->query($sql); unset($dbh); php?> see the errors martin@linux-70ce:~/php> php php_db_driver.php PHP Notice: Undefined variable: root in /home/martin/php/php_db_driver.php on line 5 PHP Notice: Undefined variable: rimbaud in /home/martin/php/php_db_driver.php on line 5 PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1045] Access denied for user ''@'localhost' (using password: NO)' in /home/martin/php/php_db_driver.php:5 Stack trace: #0 /home/martin/php/php_db_driver.php(5): PDO->__construct('mysql:test_10_n...', NULL, NULL) #1 {main} thrown in /home/martin/php/php_db_driver.php on line 5 martin@linux-70ce:~/php> hope all was runned correctly - # note - i was in a great hurry love to hear from you greetings above all - this is a very very helpful and supporetive forum - i lovee it!

hello it sounds crazy but if i create a new db - in phpmyadmin (doing manually at the frontend ) - called test_10 and afterwards i run the following code within the sql-query-form of phpmyadmin;: $username = 'root'; $password = 'rimbaud'; $dbh = new PDO('mysql:dbname=test_10;host=localhost;charset=utf8', $root, $rimbaud); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql= "CREATE TABLE `pois` ( `id` bigint(20) unsigned NOT NULL, `lat` float(10,7) NOT NULL, `lon` float(10,7) NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) )"; $stmt = $dbh->query($sql); $stmt = null; then i get back an error in the sql-form-window... of phpmyadmin $username = 'root'; MySQL meldet: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '$username = 'root'' at line 1 well this seems to be crazy - since all the names and credentials are fullly correct... any idea!? i will do some more trials.. since i think that this is not normalllll - on a sidenote: i allways run in so fantastic errors - probalby i do something wrong here... what do you say.... many many rthanks for all your help...

hello dear Jazzman1 - and hello dear Jaques1 at jazzman 1 aahhh thanks alot for the answer... at the moment i am in a great hurry... i will read it more thoroughly later the day... i have to leave house in ten minutes but i want to tell you that i read it and will try out all -and come back and report all many many thanks for all you did! Your help is appreciated. a update many thanks for any and all help. btw: what is aimed is to create the following tables and insert the data - derived from a request at the planet-file of openstreetmap: perhpaps we can debug even more.... the above mentioned parts are the database-creating ones.... see below - what is aimed in the end.... LOVE TO HEAR FROM YOU..many thanks for any and allhelp <?php $mysqli_driver = new mysqli_driver(); $mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; $db = new mysqli('localhost','root','rimbaud','test_2'); // use your credentials $xmlstr = <<<XML <data> <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data> XML; $fields = array('id','name','lat','lon'); $xml = simplexml_load_string($xmlstr); // // PROCESS XML RECORDS // $poisdata = array(); $tagdata = array(); foreach ($xml->node as $node) { $nodedata = array_fill_keys($fields,''); $nodedata['id'] = intval($node['id']); $nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0; $nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0; $poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata); foreach ($node->tag as $tag) { $k = (string)$tag['k']; $v = (string)$tag['v']; $tagdata[] = sprintf("(%d, '%s', '%s')" , $nodedata['id'] , $db->real_escape_string($k) , $db->real_escape_string($v)); } } // // STORE THE DATA ' // $sql = "REPLACE INTO pois (id,lat,lon) VALUES\n" . join(",\n", $poisdata); $db->query($sql); $sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata); $db->query($sql); // // DISPLAY THE DATA // $currentTags = array(); $sql = "SELECT DISTINCT tagname FROM pois_tag ORDER BY tagname = 'name' DESC, tagname"; $res = $db->query($sql); while (list($tn) = $res->fetch_row()) { $currentTags[] = $tn; } $thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>" . join('</th><th>', $currentTags) . "</th></tr>\n"; $currid = $currlat = $currlon = 0; $sql = "SELECT p.id, lat, lon, tagname, tagvalue FROM pois p LEFT JOIN pois_tag t ON t.poisid = p.id ORDER BY p.id"; $res = $db->query($sql); $tdata = ''; while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) { if ($currid != $id) { if ($currid) { $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; } $currid = $id; $currlat = $lat; $currlon = $lon; $poisrow = array_fill_keys($currentTags,''); } $poisrow[$t] = $v; } $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; ?> <html> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Tags</title> <meta name="author" content="Barand"> <meta name="creation-date" content="06/04/2014"> <style type="text/css"> body, td, th { font-family: arial, sans-serif; font-size: 10pt; } table { border-collapse: collapse; } th { background-color: #369; color: white; padding: 5px 2px; } td { background-color: #EEE; padding: 2px; } </style> </head> <body> <table border='1'> <?php echo $thead, $tdata; ?> </table> </body> </html>

hello dear Jazzman hello dear Jaques1 again very very helpful your answer. Youre very supportive and all your postings are a valuable asset of knowledge. Ready to apply in my work. Many thanks!! Well i think i shoulde get rid of the back tics. They are only needed when table or column names conflict with reserved words and that is bad programming practice. In other words we don't need them. As to another specific problem, i have specified NOT NULL then set the default to null (''). this might be a great issue - isnt it: Question: how to set this correct... should i leave the expression " DEFAULT NULL "!´? CREATE TABLE `pois` ( `id` bigint(20) unsigned NOT NULL, `lat` float(10,7) NOT NULL, `lon` float(10,7) NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) ) can you give me a hint how to do this right!? many thanks for any and all help. greeetings

hello jaques many thanks for the qiuck answer. this is very helpful

what is dificult in creating this tables... CREATE TABLE `pois` ( `id` bigint(20) unsigned NOT NULL, `lat` float(10,7) NOT NULL, `lon` float(10,7) NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) ) i get back the following error ; MySQL meldet: Dokumentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname`' at line 8 many thanks for any and all help

helllo dear allll finally a dataset was created in the db <- note ; i changed the things Braband mentioned - as mentioned - fortunatly a dataset was created in the db. see below q;: i have issues to reproduce this how can i re-do this....? in other workds - under which conditions can i do this again....? q 2-. should i create the db first !? are there any steps to make it even more robust? love to hear from you greetings - phpMyAdmin SQL Dump -- version 4.1.8 -- http://www.phpmyadmin.net -- -- Host: localhost -- Erstellungszeit: 05. Aug 2014 um 22:49 -- Server Version: 5.5.33-MariaDB -- PHP-Version: 5.4.20 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Datenbank: `test` -- -- -------------------------------------------------------- -- -- Tabellenstruktur für Tabelle `pois_tag` -- CREATE TABLE IF NOT EXISTS `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Daten für Tabelle `pois_tag` -- INSERT INTO `pois_tag` (`poisid`, `tagname`, `tagvalue`) VALUES (667927886, 'addr:city', 'Mannheim'), (667927886, 'addr:country', 'DE'), (667927886, 'addr:housenumber', '5'), (667927886, 'addr:postcode', '68161'), (667927886, 'addr:street', 'Collinistraße'), (667927886, 'amenity', 'restaurant'), (667927886, 'name', 'Churrascaria Brasil Tropical'), (667927886, 'phone', '+496211225596'), (667927886, 'wheelchair', 'limited'), (689928440, 'amenity', 'restaurant'), (689928440, 'cuisine', 'greek'), (689928440, 'email', 'epirus70@hotmail.de'), (689928440, 'fax', '0621/4407 762'), (689928440, 'name', 'Epirus'), (689928440, 'opening_hours', 'Mo-Sa 12:00-15:00,18:00-24:00'), (689928440, 'phone', '0621/4407 761'), (689928440, 'smoking', 'separated'), (689928440, 'website', 'http://epirus-ma.blogspot.com/'), (689928440, 'wheelchair', 'no'), (689928445, 'amenity', 'restaurant'), (689928445, 'cuisine', 'italian'), (689928445, 'email', 'gianlucascurti@ristorante-augusta.de'), (689928445, 'name', 'Ristorante Augusta'), (689928445, 'opening_hours', 'Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00'), (689928445, 'phone', '0621 449872'), (689928445, 'website', 'ristorante-augusta.com/'), (689928445, 'wheelchair', 'no'), (2064639440, 'amenity', 'restaurant'), (2064639440, 'cuisine', 'turkish'), (2064639440, 'email', 'info@lynso.de'), (2064639440, 'name', 'Kilim - Café und Bar Restaurant'), (2064639440, 'opening_hours', 'Su-Th 17:00-1:00; Fr, Sa 17:00-3:00'), (2064639440, 'operator', 'Cengiz Kaya'), (2064639440, 'phone', '06 21 - 43 755 371'), (2064639440, 'website', 'http://www.kilim-mannheim.de/'), (2126473801, 'amenity', 'restaurant'), (2126473801, 'cuisine', 'italian'), (2126473801, 'email', 'mannheim1@vapiano.de'), (2126473801, 'fax', '+49 621 1259 779'), (2126473801, 'name', 'Vapiano'), (2126473801, 'opening_hours', 'Su-Th 10:00-24:00; Fr-Sa 10:00-01:00'), (2126473801, 'operator', 'Vapiano'), (2126473801, 'phone', '+49 621 1259 777'), (2126473801, 'website', 'http://www.vapiano.de/newsroom/?store=29'), (2126473801, 'wheelchair', 'yes'); /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

hello dear Barand many many thanks for the quick reply. great to hear from you well i am deeply impressed by this forum and the very very supportive help. i am overwehelmed!!! many many thanks for all you did will try it out and come back and report greetings

hello you both i did what jacques was telling us - and switched on the mysql.-error reporting martin@linux-70ce:~/php> php osm_200.php PHP Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''id','lat','lon') VALUES (2064639440, 0.0000000, 49.4873181), (2126473801, 0.0' at line 1' in /home/martin/php/osm_200.php:100 Stack trace: #0 /home/martin/php/osm_200.php(100): mysqli->query('REPLACE INTO po...') #1 {main} thrown in /home/martin/php/osm_200.php on line 100 martin@linux-70ce:~/php> this gave back the following <?php $mysqli_driver = new mysqli_driver(); $mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; $db = new mysqli('localhost','root','rimbaud','test'); // use your credentials $xmlstr = <<<XML <data> <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data> XML; $fields = array('id','name','lat','lon'); $xml = simplexml_load_string($xmlstr); // // PROCESS XML RECORDS // $poisdata = array(); $tagdata = array(); foreach ($xml->node as $node) { $nodedata = array_fill_keys($fields,''); $nodedata['id'] = intval($node['id']); $nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0; $nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0; $poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata); foreach ($node->tag as $tag) { $k = (string)$tag['k']; $v = (string)$tag['v']; $tagdata[] = sprintf("(%d, '%s', '%s')" , $nodedata['id'] , $db->real_escape_string($k) , $db->real_escape_string($v)); } } // // STORE THE DATA // $sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata); $db->query($sql); $sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata); $db->query($sql); // // DISPLAY THE DATA // $currentTags = array(); $sql = "SELECT DISTINCT tagname FROM pois_tag ORDER BY tagname = 'name' DESC, tagname"; $res = $db->query($sql); while (list($tn) = $res->fetch_row()) { $currentTags[] = $tn; } $thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>" . join('</th><th>', $currentTags) . "</th></tr>\n"; $currid = $currlat = $currlon = 0; $sql = "SELECT p.id, lat, lon, tagname, tagvalue FROM pois p LEFT JOIN pois_tag t ON t.poisid = p.id ORDER BY p.id"; $res = $db->query($sql); $tdata = ''; while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) { if ($currid != $id) { if ($currid) { $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; } $currid = $id; $currlat = $lat; $currlon = $lon; $poisrow = array_fill_keys($currentTags,''); } $poisrow[$t] = $v; } $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; ?> <html> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Tags</title> <meta name="author" content="Barand"> <meta name="creation-date" content="06/04/2014"> <style type="text/css"> body, td, th { font-family: arial, sans-serif; font-size: 10pt; } table { border-collapse: collapse; } th { background-color: #369; color: white; padding: 5px 2px; } td { background-color: #EEE; padding: 2px; } </style> </head> <body> <table border='1'> <?php echo $thead, $tdata; ?> </table> </body> </html>

hello thx alot corrected it and runned again $res = $db->query($sql); $tdata = ''; while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) { - this line is line 122!!!!! if ($currid != $id) { if ($currid) { $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; so what is this

hello you mean that i need to put the ?> into the line 62 - THAT IS at the end of the following conscruct </node> </data> XML; in the below code: </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data> XML; $fields = array('id','name','lat','lon'); $xml = simplexml_load_string($xmlstr); // // PROCESS XML RECORDS // $poisdata = array(); $tagdata = array(); foreach ($xml->node as $node) { $nodedata = array_fill_keys($fields,''); $nodedata['id'] = intval($node['id']); $nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0; $nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0; $poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata); foreach ($node->tag as $tag) { $k = (string)$tag['k']; $v = (string)$tag['v']; $tagdata[] = sprintf("(%d, '%s', '%s')" , $nodedata['id'] , $db->real_escape_string($k) , $db->real_escape_string($v)); } } // // STORE THE DATA // $sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata); $db->query($sql); $sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata); $db->query($sql); // // DISPLAY THE DATA // $currentTags = array(); $sql = "SELECT DISTINCT tagname FROM pois_tag ORDER BY tagname = 'name' DESC, tagname"; $res = $db->query($sql); while (list($tn) = $res->fetch_row()) { $currentTags[] = $tn; } $thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>" . join('</th><th>', $currentTags) . "</th></tr>\n"; $currid = $currlat = $currlon = 0; $sql = "SELECT p.id, lat, lon, tagname, tagvalue FROM pois p LEFT JOIN pois_tag t ON t.poisid = p.id ORDER BY p.id"; $res = $db->query($sql); $tdata = ''; while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) { if ($currid != $id) { if ($currid) { $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; } $currid = $id; $currlat = $lat; $currlon = $lon; $poisrow = array_fill_keys($currentTags,''); } $poisrow[$t] = $v; } $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; ?> <html> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Tags</title> <meta name="author" content="Barand"> <meta name="creation-date" content="06/04/2014"> <style type="text/css"> body, td, th { font-family: arial, sans-serif; font-size: 10pt; } table { border-collapse: collapse; } th { background-color: #369; color: white; padding: 5px 2px; } td { background-color: #EEE; padding: 2px; } </style> </head> <body> <table border='1'> <?php echo $thead, $tdata; ?> </table> </body> </html> by the way: can you say some thing to the following ideas : Barand said: "Instead of hard-coding the required column names as in" $fields = array('id','lat','lon','name','amenity','operator','vending'); you could use this to pick up any additional columns added to your pois table $sql = "SHOW COLUMNS FROM pois"; $fields = array(); $res = $db->query($sql); while ($row = $res->fetch_row()) { $fields[] = $row[0]; } Well that is a pretty important thing - i hope i got it right - the above mentioend ideas and the tiny script can help here... But to understand it better - and to rephrase the problem again - in other words: question: how to make the script robust so that all the cases are covered!? What if we have somethimes a dataset that has completley new tags... can we make the script covering that!?

hi there i runned the code again and here is the result: martin@linux-70ce:~/php> php osm_200.php PHP Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /home/martin/php/osm_200.php on line 76 martin@linux-70ce:~/php> the code is the following: <?php $db = new mysqli('localhost','root','rimbaud','test'); // use your credentials $xmlstr = <<<XML <data> <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data> XML; $fields = array('id','name','lat','lon'); $xml = simplexml_load_string($xmlstr); // // PROCESS XML RECORDS // $poisdata = array(); $tagdata = array(); foreach ($xml->node as $node) { $nodedata = array_fill_keys($fields,''); $nodedata['id'] = intval($node['id']); $nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0; $nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0; $poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata); foreach ($node->tag as $tag) { $k = (string)$tag['k']; $v = (string)$tag['v']; $tagdata[] = sprintf("(%d, '%s', '%s')" , $nodedata['id'] , $db->real_escape_string($k) , $db->real_escape_string($v)); } } // // STORE THE DATA // $sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata); $db->query($sql); $sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata); $db->query($sql); // // DISPLAY THE DATA // $currentTags = array(); $sql = "SELECT DISTINCT tagname FROM pois_tag ORDER BY tagname = 'name' DESC, tagname"; $res = $db->query($sql); while (list($tn) = $res->fetch_row()) { $currentTags[] = $tn; } $thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>" . join('</th><th>', $currentTags) . "</th></tr>\n"; $currid = $currlat = $currlon = 0; $sql = "SELECT p.id, lat, lon, tagname, tagvalue FROM pois p LEFT JOIN pois_tag t ON t.poisid = p.id ORDER BY p.id"; $res = $db->query($sql); $tdata = ''; while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) { if ($currid != $id) { if ($currid) { $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; } $currid = $id; $currlat = $lat; $currlon = $lon; $poisrow = array_fill_keys($currentTags,''); } $poisrow[$t] = $v; } $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; ?> <html> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Tags</title> <meta name="author" content="Barand"> <meta name="creation-date" content="06/04/2014"> <style type="text/css"> body, td, th { font-family: arial, sans-serif; font-size: 10pt; } table { border-collapse: collapse; } th { background-color: #369; color: white; padding: 5px 2px; } td { background-color: #EEE; padding: 2px; } </style> </head> <body> <table border='1'> <?php echo $thead, $tdata; ?> </table> </body> </html> well i have no idea what is going wrong here i will do some further investigations greetings

hello dear Chocu3r hello dear Barand, first of all - many many thanks for the help. @ Barand thx for the basic inspiration with the code. i will do some tests later the weekend. i do the tests later the wekendd comme back and report all the findings first of all - many many thanks for your great support. greetings by the way: what if the set of tags that i get out form the planet file changes what if i get sometimes: a. less tags with values out of the planet-file. b. more tags with values out of the planet - file and http://forums.phpfreaks.com/topic/288907-from-osm-openstreetmap-to-mysql-with-php/?p=1481552 Barand made some great inspirations - when talking bout this ideas: You Barand said: "Instead of hard-coding the required column names as in" $fields = array('id','lat','lon','name','amenity','operator','vending'); you could use this to pick up any additional columns added to your pois table $sql = "SHOW COLUMNS FROM pois"; $fields = array(); $res = $db->query($sql); while ($row = $res->fetch_row()) { $fields[] = $row[0]; } Well that is a pretty important thing - i hope i got it right - the above mentioend ideas and the tiny script can help here... But to understand it better - and to rephrase the problem again - in other words: question: how to make the script robust so that all the cases are covered!? What if we have somethimes a dataset that has completley new tags... can we make the script covering that!? Note: That is a very very important question - since i (guess) that the request at the see some possible results in the pastebin - site: http://pastebin.com/mq5kAHyy or just: a. see a request: http://overpass-turbo.eu/?key=amenity&value=school&template=key-value just press on "ausführen" in the top left b. or run it at http://overpass-api.de/query_form.html hope i was able to express what i am after.... <!-- This query looks for nodes, ways and relations with the given key/value combination. Choose your region and hit the Run button above! --> <osm-script output="json" timeout="25"> <!-- gather results --> <union> <!-- query part for: “amenity=school” --> <query type="node"> <has-kv k="amenity" v="school"/> <bbox-query {{bbox}}/> </query> <query type="way"> <has-kv k="amenity" v="school"/> <bbox-query {{bbox}}/> </query> <query type="relation"> <has-kv k="amenity" v="school"/> <bbox-query {{bbox}}/> </query> </union> <!-- print results --> <print mode="body"/> <recurse type="down"/> <print mode="skeleton" order="quadtile"/> </osm-script> or see the output: </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data>

hello dear Chocu3r will do some tests later the weekend. i do the tests later the wekendd comme back and report all the findings first of all - many many thanks for your great support. greetings

hi there wiill do as you advice.

well after fixing the error in the very first lines - regarding the connection to the db i runned the script again and i got bacik the following issues martin@linux-70ce:~/php> php osm_200.php PHP Parse error: syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /home/martin/php/osm_200.php on line 77 martin@linux-70ce:~/php>

hello - many thanks - how to fix the issues with the issues with the "order by " ORDER BY tagname = 'name' DESC, tagname"; what do you suggest

hello good day many thanks for your help. this is very supportive !!!

i want to store some data that i have gained from a request at the planetfile of openstreetmap: so i have set up a database on my opensuse 13.1 i have created a db called test on the mysql-server on my opensuse: CREATE TABLE `pois` ( `id` bigint(20) unsigned NOT NULL, `lat` float(10,7) NOT NULL, `lon` float(10,7) NOT NULL, PRIMARY KEY (`id`) ) CREATE TABLE `pois_tag` ( `poisid` int(11) NOT NULL DEFAULT '0', `tagname` varchar(45) NOT NULL DEFAULT '', `tagvalue` varchar(255) DEFAULT NULL, PRIMARY KEY (`poisid`,`tagname`) ) Where each tagname/value pair is stored as a row in a separate table with the pois id Processing would be like this <?php $db = new mysqli(localhost,root,rimbaud,'test'); // use your credentials $xmlstr = <<<XML <data> <node id="2064639440" lat="49.4873181" lon="8.4710548"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="turkish"/> <tag k="email" v="info@lynso.de"/> <tag k="name" v="Kilim - Café und Bar Restaurant"/> <tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/> <tag k="operator" v="Cengiz Kaya"/> <tag k="phone" v="06 21 - 43 755 371"/> <tag k="website" v="http://www.kilim-mannheim.de/"/> </node> <node id="2126473801" lat="49.4851170" lon="8.4756295"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="mannheim1@vapiano.de"/> <tag k="fax" v="+49 621 1259 779"/> <tag k="name" v="Vapiano"/> <tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/> <tag k="operator" v="Vapiano"/> <tag k="phone" v="+49 621 1259 777"/> <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/> <tag k="wheelchair" v="yes"/> </node> <node id="667927886" lat="49.4909673" lon="8.4764904"> <tag k="addr:city" v="Mannheim"/> <tag k="addr:country" v="DE"/> <tag k="addr:housenumber" v="5"/> <tag k="addr:postcode" v="68161"/> <tag k="addr:street" v="Collinistraße"/> <tag k="amenity" v="restaurant"/> <tag k="name" v="Churrascaria Brasil Tropical"/> <tag k="phone" v="+496211225596"/> <tag k="wheelchair" v="limited"/> </node> <node id="689928440" lat="49.4798794" lon="8.4853418"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="greek"/> <tag k="email" v="epirus70@hotmail.de"/> <tag k="fax" v="0621/4407 762"/> <tag k="name" v="Epirus"/> <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/> <tag k="phone" v="0621/4407 761"/> <tag k="smoking" v="separated"/> <tag k="website" v="http://epirus-ma.blogspot.com/"/> <tag k="wheelchair" v="no"/> </node> <node id="689928445" lat="49.4799409" lon="8.4851357"> <tag k="amenity" v="restaurant"/> <tag k="cuisine" v="italian"/> <tag k="email" v="gianlucascurti@ristorante-augusta.de"/> <tag k="name" v="Ristorante Augusta"/> <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/> <tag k="phone" v="0621 449872"/> <tag k="website" v="ristorante-augusta.com/"/> <tag k="wheelchair" v="no"/> </node> </data> XML; $fields = array('id','name','lat','lon'); $xml = simplexml_load_string($xmlstr); // // PROCESS XML RECORDS // $poisdata = array(); $tagdata = array(); foreach ($xml->node as $node) { $nodedata = array_fill_keys($fields,''); $nodedata['id'] = intval($node['id']); $nodedata['lat'] = isset($node['lat']) ? floatval($node['lat']) : 0; $nodedata['lon'] = isset($node['lon']) ? floatval($node['lon']) : 0; $poisdata[] = vsprintf("(%d, %10.7f, %10.7f)", $nodedata); foreach ($node->tag as $tag) { $k = (string)$tag['k']; $v = (string)$tag['v']; $tagdata[] = sprintf("(%d, '%s', '%s')" , $nodedata['id'] , $db->real_escape_string($k) , $db->real_escape_string($v)); } } // // STORE THE DATA // $sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata); $db->query($sql); $sql = "REPLACE INTO pois_tag (poisid, tagname, tagvalue) VALUES\n" . join(",\n", $tagdata); $db->query($sql); // // DISPLAY THE DATA // $currentTags = array(); $sql = "SELECT DISTINCT tagname FROM pois_tag ORDER BY tagname = 'name' DESC, tagname"; $res = $db->query($sql); while (list($tn) = $res->fetch_row()) { $currentTags[] = $tn; } $thead = "<tr><th>id</th><th>lat</th><th>lon</th><th>" . join('</th><th>', $currentTags) . "</th></tr>\n"; $currid = $currlat = $currlon = 0; $sql = "SELECT p.id, lat, lon, tagname, tagvalue FROM pois p LEFT JOIN pois_tag t ON t.poisid = p.id ORDER BY p.id"; $res = $db->query($sql); $tdata = ''; while (list($id, $lat, $lon, $t, $v) = $res->fetch_row()) { if ($currid != $id) { if ($currid) { $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; } $currid = $id; $currlat = $lat; $currlon = $lon; $poisrow = array_fill_keys($currentTags,''); } $poisrow[$t] = $v; } $tdata .= "<tr><td>$currid</td><td>$currlat</td><td>$currlon</td><td>" . join('</td><td>', $poisrow) . "</td></tr>\n"; ?> <html> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Tags</title> <meta name="author" content="Barand"> <meta name="creation-date" content="06/04/2014"> <style type="text/css"> body, td, th { font-family: arial, sans-serif; font-size: 10pt; } table { border-collapse: collapse; } th { background-color: #369; color: white; padding: 5px 2px; } td { background-color: #EEE; padding: 2px; } </style> </head> <body> <table border='1'> <?php echo $thead, $tdata; ?> </table> </body> </html> leeeds to the following errors.... PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121 martin@linux-70ce:~/php> php osm_200.php PHP Notice: Use of undefined constant localhost - assumed 'localhost' in /home/martin/php/osm_200.php on line 3 PHP Notice: Use of undefined constant root - assumed 'root' in /home/martin/php/osm_200.php on line 3 PHP Notice: Use of undefined constant rimbaud - assumed 'rimbaud' in /home/martin/php/osm_200.php on line 3 PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121 martin@linux-70ce:~/php> so what goes wrong here...?

sure you can help me here tooo... I am new to PHP's SimpleXML. Below I have added my own answer to the question, how to refine the code to ad more tags. I want to filter the data to get the nodes with special category. Here is sample of the OSM data I want to get the whole schools within an area. The first script runs well - but now I want to refine the search and add more tags. I want to store all into MySQL. So we need to make some XML parsing with PHP: The following is a little OSM Overpass API example with PHP SimpleXM we need to do this here <?php /** * OSM Overpass API with PHP SimpleXML / XPath * * PHP Version: 5.4 - Can be back-ported to 5.3 by using 5.3 Array-Syntax (not PHP 5.4's square brackets) */ // // 1.) Query an OSM Overpass API Endpoint // $query = 'node ["amenity"~".*"] (38.415938460513274,16.06338500976562,39.52205163048525,17.51220703125); out;'; $context = stream_context_create(['http' => [ 'method' => 'POST', 'header' => ['Content-Type: application/x-www-form-urlencoded'], 'content' => 'data=' . urlencode($query), ]]); # please do not stress this service, this example is for demonstration purposes only. $endpoint = 'http://overpass-api.de/api/interpreter'; libxml_set_streams_context($context); $start = microtime(true); $result = simplexml_load_file($endpoint); printf("Query returned %2\$d node(s) and took %1\$.5f seconds.\n\n", microtime(true) - $start, count($result->node)); // // 2.) Work with the XML Result // # get all school nodes with xpath $xpath = '//node[tag[@k = "amenity" and @v = "school"]]'; $schools = $result->xpath($xpath); printf("%d School(s) found:\n", count($schools)); foreach ($schools as $index => $school) { # Get the name of the school (if any), again with xpath list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)']; printf("#%02d: ID:%' -10s [%s,%s] %s\n", $index, $school['id'], $school['lat'], $school['lon'], $name); } ?> i just have had a quick view on the above mentioned site. i try to figure out how to do this - for any and all hints i am thankful The following code lists all schools and tries to obtain their names as well. I have not covered translations yet because my sample data didn't have those, but you can also look for all kind of names including translations and just prefer a specific one): Code: // // 2.) Work with the XML Result // # get all school nodes with xpath $xpath = '//node[tag[@k = "amenity" and @v = "school"]]'; $schools = $result->xpath($xpath); printf("%d School(s) found:\n", count($schools)); foreach ($schools as $index => $school) { # Get the name of the school (if any), again with xpath list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)']; printf("#%02d: ID:%' -10s [%s,%s] %s\n", $index, $school['id'], $school['lat'], $school['lon'], $name); } The key point here are the xpath queries. In the above mentioend example two are used, the first xpath queriy is to get the nodes that have certain tags.I think this is the most interesting one for me: //node[tag[@k = "amenity" and @v = "school"]] This line says: Give us all node elements that have a tag element inside which has the k attribute value "amenity" and the v attribute value "school". This is the condition we have to filter out those nodes that are tagged with amenity school. Further on xpath is used again, now relative to those school nodes to see if there is a name and if so to fetch it: tag[@k = "name"]/@v' This line says: Relative to the current node, give me the v attribute from a tag element that as the k attribute value "name". As you can see, some parts are again similar to the line before. I think you can both adopt them to your needs. Because not all school nodes have a name, a default string is provided for display purposes by adding it to the (then empty) result array: list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)']; ^^^^^^^^^^^^^^^ Provide Default Value So here my results for that code-example: Query returned xxxxx node(s) and took 1.10735 seconds. 2179 School(s) found: the last ones are shown below..... #2151: ID:2688357765 [51.4668941,-0.9731135] New Directrions, North Reading #2152: ID:2702504696 [51.5884265,-0.7829013] Burford School #2153: ID:2702549737 [51.5802201,-0.7653918] Great Marlow School #2154: ID:2706219304 [51.3779317,-0.0895302] ARK Oval Primary Academy #2155: ID:2706219314 [51.3871935,-0.0623001] Ashburton Primary School #2156: ID:2706219320 [51.3210977,-0.1398859] CALAT Smitham Centre #2157: ID:2706219326 [51.3638861,-0.0922032] Elmhurst School #2158: ID:2706219339 [51.4007121,-0.0743710] Harris Academy South Norwood #2159: ID:2706219343 [51.3831662,-0.0405476] Orchard Way Primary School #2160: ID:2706219347 [51.3531047,-0.0959447] Purley Oaks Primary School #2161: ID:2706219348 [51.3428384,-0.0069931] Rowdown Primary School #2162: ID:2706219350 [51.3954917,-0.0732185] South Norwood Primary School #2163: ID:2706219351 [51.3377151,-0.1230482] St David's Preparatory School #2164: ID:2706219353 [51.3993760,-0.1144352] Winterbourne School #2165: ID:2717394621 [51.8706538,0.1480886] Prep #2166: ID:2717394636 [51.8685838,0.1463720] Pre-Prep #2167: ID:2722704201 [51.1398429,-0.0457445] Felbridge Primary School #2168: ID:2723815070 [50.8465429,-0.3030261] Lancing College #2169: ID:2727170814 [51.5780664,-0.0249051] Adult Education Centre #2170: ID:2833253896 [50.9928140,-0.7774996] (unnamed) #2171: ID:2837001831 [51.1783749,-0.7970866] More House School #2172: ID:2865091022 [50.9090614,-0.5565425] Dorset House School #2173: ID:2882477853 [51.6261198,-0.7349665] Bowerdean Primary School #2174: ID:2901434856 [51.6542477,-0.3098923] The Haberdashers' Aske's Girls School #2175: ID:2901434857 [51.6565707,-0.3129822] The Haberdashers' Aske's Boys Schooland now i try to figure out how i can enter more xpath queries at the above mentioned code and get out even more important data - see here Key:contact - OpenStreetMap Wiki contacthone contact:fax contact:website contact:emaili will digg into all documents and come back later the weekend... and report all the findings well - i think that i need to extend the xpath requests within the loop where xpath is used again, now relative to those school nodes to see if there is a name and if so to fetch it: tag[@k = "name"]/@v' tag[@k = " contact:website"]/@v' tag[@k = " contact:email"]/@v' hope you can help me... look forward to hear from you

I am new to PHP's SimpleXML. The original version of this question was derived from here: OSM Data parsing to get the nodes with child I am thankful that hakre offered a great example in the comments that makes a overwhelming starting point for my project. Below I have added my own answer to the question, how to refine the code to ad more tags. I want to filter the data to get the nodes with special category. Here is sample of the OSM data I want to get the whole schools within an area. The first script runs well - but now I want to refine the search and add more tags. I want to store all into MySQL. So we need to make some XML parsing with PHP: The following is a little OSM Overpass API example with PHP SimpleXM <?php /** * OSM Overpass API with PHP SimpleXML / XPath * * PHP Version: 5.4 - Can be back-ported to 5.3 by using 5.3 Array-Syntax (not PHP 5.4's square brackets) */ // // 1.) Query an OSM Overpass API Endpoint // $query = 'node ["addr:postcode"~"RM12"] (51.5557914,0.2118915,51.5673083,0.2369398)->.point; ( node (around.point:100000) ["amenity"~"school"]; way (around.point:100000) ["amenity"~"school"]; ); out;'; $context = stream_context_create(['http' => [ 'method' => 'POST', 'header' => ['Content-Type: application/x-www-form-urlencoded'], 'content' => 'data=' . urlencode($query), ]]); # please do not stress this service, this example is for demonstration purposes only. $endpoint = 'http://overpass-api.de/api/interpreter'; libxml_set_streams_context($context); $start = microtime(true); $result = simplexml_load_file($endpoint); printf("Query returned %2\$d node(s) and took %1\$.5f seconds.\n\n", microtime(true) - $start, count($result->node)); // // 2.) Work with the XML Result // # get all school nodes with xpath $xpath = '//node[tag[@k = "amenity" and @v = "school"]]'; $schools = $result->xpath($xpath); printf("%d School(s) found:\n", count($schools)); foreach ($schools as $index => $school) { # Get the name of the school (if any), again with xpath list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)']; printf("#%02d: ID:%' -10s [%s,%s] %s\n", $index, $school['id'], $school['lat'], $school['lon'], $name); } ?> how to get more out of it.. at least the adress and the website where to add those tags in the request`? in this line: list($name) = $school->xpath('tag[@k = "name"]/@v') + ['(unnamed)']; many thanks for any and all help!!

how can i check if i have PHP SimpleXML on the opensuse is there a command that i can use in terminal

hello dear friends if we want to put a facebook-fanbox on a wordpress-page - so that it looks like this here: http://www.designchemical.com/blog/index.php/frequently-asked-questions/social-media-tabs/ we can go like so: we take the test from the facebook-deeloper site: https://developers.facebook.com/docs/plugins/like-box-for-pages Initialize the JavaScript SDK using this app: books-page... <div id="fb-root"></div> <script>(function(d, s, id) { var js, fjs = d.getElementsByTagName(s)[0]; if (d.getElementById(id)) return; js = d.createElement(s); js.id = id; js.src = "//connect.facebook.net/en_GB/sdk.js#xfbml=1&appId=473241986032774&version=v2.0"; fjs.parentNode.insertBefore(js, fjs); }(document, 'script', 'facebook-jssdk'));</script> Include the JavaScript SDK on your page once, ideally right after the opening <body> tag. or do i need to inclue this <div class="fb-like-box" data-href="https://www.facebook.com/FacebookDevelopers" data-colorscheme="light" data-show-faces="true" data-header="true" data-stream="false" data-show-border="true"></div>` love to hear from you