storing some values from a php-array into mysaql-db fails

Maze · August 2, 2014

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...?

Ch0cu3r · August 2, 2014

On line 3 localhost, root and rimbaud should all be wrapped in quotes

$db = new mysqli('localhost','root','rimbaud','test'); // use your credentials

The only error remaining will be line 121

$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()) {  //<--- line 121
    $currentTags[] = $tn;
}

IGNORE ~~You will get that error when mysqli_query returns false. Looking at the query I can see an error in the order by clause~~

        ORDER BY tagname = 'name' DESC, tagname";

An ORDER BY clause only takes a collection of column names and an optional direction to sort the column values by (either ASC or DESC). The = 'name' text is most likely causing an error which is causing your query to fail. Because the query has failed $db->query has not returned a result object and so you are getting this error

PHP Fatal error:  Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121

Edited August 3, 2014 by Ch0cu3r

Maze · August 2, 2014

hello good day

many thanks for your help. this is very supportive

!!!

On line 3 localhost, root and rimbaud should all be wrapped in quotes
$db = new mysqli('localhost','root','rimbaud','test'); // use your credentials
The only error remaining will be line 121
$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()) {  //<--- line 121
    $currentTags[] = $tn;
}
You will get that error when mysqli_query returns false. Looking at the query I can see an error in the order by clause
        ORDER BY tagname = 'name' DESC, tagname";
An ORDER BY clause only takes a collection of column names and an optional direction to sort the column values by (either ASC or DESC). The = 'name' text is most likely causing an error which is causing your query to fail. Because the query has failed $db->query has not returned a result object and so you are getting this error
PHP Fatal error:  Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 121

Maze · August 2, 2014

hello - many thanks - how to fix the issues with the issues with the "order by "

ORDER BY tagname = 'name' DESC, tagname";

An ORDER BY clause only takes a collection of column names and an optional direction to sort the column values by (either ASC or DESC). The = 'name' text is most likely causing an error which is causing your query to fail. Because the query has failed $db->query has not returned a result object and so you are getting this error

what do you suggest

Maze · August 2, 2014

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>

CroNiX · August 2, 2014

Did you google the error message? Don't be so quick to just report your error here.

Maze · August 3, 2014

hi there wiill do as you advice.

Barand · August 3, 2014

An ORDER BY clause only takes a collection of column names and an optional direction to sort the column values by (either ASC or DESC).

Sorry Ch0cu3r but that's bullshit. Boolean expressions are perfectly acceptable EG

Price list

mysql> SELECT name, price FROM products
    -> ORDER BY name;
+-----------+--------+
| name      | price  |
+-----------+--------+
| product a |   1.50 |
| product b |   2.00 |
| product c |  50.99 |
| product d |  10.25 |
| product e | 100.00 |
| product f |   8.00 |
| product g |   2.29 |
| product h |   0.49 |
+-----------+--------+

Price list with more expensive items first.

mysql> SELECT name, price FROM products
    -> ORDER BY price > 50 DESC, name ;
+-----------+--------+
| name      | price  |
+-----------+--------+
| product c |  50.99 |
| product e | 100.00 |
| product a |   1.50 |
| product b |   2.00 |
| product d |  10.25 |
| product f |   8.00 |
| product g |   2.29 |
| product h |   0.49 |
+-----------+--------+

Ch0cu3r · August 3, 2014

Sorry Ch0cu3r but that's bullshit. Boolean expressions are perfectly acceptable EG

Ohh... never knew that. Learnt something new there

@Maze ignore my comment regarding the order by clause.

Edited August 3, 2014 by Ch0cu3r

Ch0cu3r · August 3, 2014

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>

What did you change the first line to? I ran your code and I get no such error. Sounds to me you have made other changes to your code which is now causing this error.

Maze · August 3, 2014

hello dear Chocu3r

will do some tests later the weekend.

What did you change the first line to? I ran your code and I get no such error. Sounds to me you have made other changes to your code which is now causing this error.

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

Maze · August 3, 2014

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.

What did you change the first line to? I ran your code and I get no such error. Sounds to me you have made other changes to your code which is now causing this error.

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>

Edited August 3, 2014 by Maze

Maze · August 3, 2014

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

Ch0cu3r · August 3, 2014

You have moved the closing heredoc delimiter on line 62. This is what is causing the syntax error.

  XML;

There should not be any characters (including any whitespace characters such as spaces - except new lines) before XML;

The closing delimiter must be the very first character of the next line after the heredoc statement (the xml code in your case).

Edited August 3, 2014 by Ch0cu3r

Maze · August 3, 2014

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!?

CroNiX · August 3, 2014

He meant get rid of all whitespace before the closing XML in your heredoc.

instead of:

XML; (you have spaces before XML;)

it should be

XML; (no spaces before XML;)

Maze · August 3, 2014

hello thx alot

corrected it and runned again

martin@linux-70ce:~/php> php osm_200.php
PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 122
martin@linux-70ce:~/php>

$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

CroNiX · August 3, 2014

It means

$res = $db->query($sql);

didn't work, so $res is NOT an object so you can't use any methods like fetch_row() on it.

Your thread is getting very long mixed with all sorts of different problems. It's getting very hard to track it all. You'd best post a new thread for each different problem, and show the complete (updated) relevant code for each one.

You will do yourself a HUGE favor by googling the main part of your error messages so you can learn what they mean. When I say "main part" I mean leave things off that are specific to YOUR code. Like google "Call to a member function fetch_row() on a non-object" to learn about that error. If you don't, you will always be clueless as to what's going on and won't ever be able to learn how to troubleshoot your errors or write good code for the future.

Edited August 3, 2014 by CroNiX

Ch0cu3r · August 4, 2014

corrected it and runned again

Quote

martin@linux-70ce:~/php> php osm_200.php

PHP Fatal error: Call to a member function fetch_row() on a non-object in /home/martin/php/osm_200.php on line 122

martin@linux-70ce:~/php>

Sounds like your query is failing, Change line 120 to

  $res = $db->query($sql) or trigger_error("Query: $sql has failed! - {$db->error}", E_USER_ERROR);

Jacques1 · August 4, 2014

Instead of manually checking every query for errors, just turn on error reporting in the MySQLi driver:

<?php

$mysqli_driver = new mysqli_driver();
$mysqli_driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

Maze · August 5, 2014

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>

Barand · August 5, 2014

It tells you that you have a syntax error in your query

$sql = "REPLACE INTO pois ('id','lat','lon') VALUES\n" . join(",\n", $poisdata);

Column names should not be in quotes.

Maze · August 5, 2014

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

Maze · August 5, 2014

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 */;

Sign In

storing some values from a php-array into mysaql-db fails

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information