creating tables in php leads to errors in phpmyadmin....

Maze · August 6, 2014

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

achieve2lead · August 6, 2014

You aren't using semicolons

Jacques1 · August 6, 2014

You cannot execute multiple queries at once. Well, some database extensions do provide methods for this, but you should never use them due to the huge security risk (if there's any SQL injection vulnerability, the attacker is now able to write custom queries and not just manipulate existing ones).

Maze · August 6, 2014

hello jaques

many thanks for the qiuck answer.

this is very helpful

jazzman1 · August 6, 2014

Try to add a semicolon (";") at the end of any sql statement telling a parser where the end of that statement is.

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`)
)

Edited August 6, 2014 by jazzman1

Jacques1 · August 6, 2014

Like I said, you can't have multiple statements. The only workaround would be the multi_query() method in the MySQLi extension, but see the comment about security.

Maze · August 6, 2014

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

Edited August 6, 2014 by Maze

jazzman1 · August 6, 2014

Have you read my reply above?

Example in php using a pdo driver:

<?php

    $username = 'user';
     
    $password = 'pass';
     
    $dbh = new PDO('mysql:dbname=db_name;host=db_address;charset=utf8', $username, $password);
    
    $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);

Edited August 6, 2014 by jazzman1

Maze · August 6, 2014

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>

Edited August 6, 2014 by Maze

Maze · August 6, 2014

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

Jacques1 · August 6, 2014

@ jazzman:

He uses MySQLi, not PDO. And the only reason why your code “works” is because your configuration is broken. You haven't turned off emulated prepared statements, which means that all prepared statements are replaced with client-side escaping. This is a major security risk and can lead to SQL injection vulnerabilities.

@ Maze:

Backticks are not only superfluos. They're downright harmful, because they can lead to very, very ugly bugs. Let's say you want to use the qualified identifier pois.id in one of your queries, but instead of `pois`.`id`, you accidentally write `pois.id`. Due to the backticks, this is still a syntactically valid identifier, so MySQL will complain about a missing column instead of poiting out the actual mistake. Even worse, the error message looks like the column pois.id is missing (which of course isn't the case). I've seen people spend hours on this.

A DEFAULT NULL declaration is not needed, because MySQL does this automatically if NULL is allowed and no explicit default value is given. You can use this for clarity, though.

I don't know what you mean by “i have specified NOT NULL then set the default to null ('')”. The empty string is not the same as NULL. Those are two entirely different things.

Last but not least, you should remove the length arguments of the integer types. For example, BIGINT(20) should simply be BIGINT. The argument specifies the padding length which you almost certainly won't ever need.

ginerjm · August 6, 2014

Something is wrong with the code you are showing. Your assignment of $username is NOT in your query statement so what you are showing makes no sense since it can not be an sql error. We need to see the code before the $username= line to see how the interpreter is seeing it. For some reason it thinks that line is part of a query, so I think you have some previous code that has not been entered correctly.

jazzman1 · August 6, 2014

He uses MySQLi, not PDO. And the only reason why your code “works” is because your configuration is broken. You haven't turned off emulated prepared statements, which means that all prepared statements are replaced with client-side escaping. This is a major security risk and can lead to SQL injection vulnerabilities.

@Jaques1,

how could I know that he uses a MySQLi driver? Why and most important how can I use a prepared statement in the script above b/s I don't see what values need to be prepared from db server

Maze · August 6, 2014

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

Something is wrong with the code you are showing. Your assignment of $username is NOT in your query statement so what you are showing makes no sense since it can not be an sql error. We need to see the code before the $username= line to see how the interpreter is seeing it. For some reason it thinks that line is part of a query, so I think you have some previous code that has not been entered correctly.

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!

ginerjm · August 6, 2014

So now you have some error messages to be resolved.

Jacques1 · August 6, 2014

No, the code is simply wrong, and switching from MySQLi to PDO for a single script also doesn't make a lot of sense. I'm not sure why people immediately adopt anything somebody posts into a forum.

Why and most important how can I use a prepared statement in the script above b/s I don't see what values need to be prepared from db server

It doesn't matter whether or not you used a prepared statement in this specific piece of code. The point is that the PDO configuration is broken, and this affects the entire application or at least the whole script.

Also note that the query() method does in fact create a prepared statement internally.

jazzman1 · August 6, 2014

Also note that the query() method does in fact create a prepared statement internally.

I was thinking that the query method works similar like an exec() one, however, thanks for that and I don't also say that my script is bad and broken regarding OP wishes

at the moment i am in a internet -cafe with a bad bad connection - i will answer more later the day....

@Maze, stay there and enjoy your coffee. We are all freaks here :happy-04:

dalecosp · August 6, 2014

phpMyAdmin is a reasonably good MySQL client, and it supports multiple queries in the "query" frame/tab; they must be separated with a semi-colon just as they should be in any MySQL client. +1 for JazzMan1.

mysql> select * from products
    -> select * from product_options;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that                                                                                                                                                              corresponds to your MySQL server version for the right syntax to use near 'selec                                                                                                                                                             t * from product_options' at line 2
mysql> select * from products; select * from product_options;
+----+-------+
| id | name  |
+----+-------+
|  1 | shirt |
|  2 | shoes |
|  3 | socks |
+----+-------+
3 rows in set (0.16 sec)
 
+----+------------+-------+--------+
| id | product_id | name  | value  |
+----+------------+-------+--------+
|  1 |          1 | color | red    |
|  2 |          1 | color | blue   |
|  3 |          1 | color | green  |
|  4 |          3 | color | black  |
|  5 |          3 | color | white  |
|  6 |          2 | color | black  |
|  7 |          2 | color | purple |
+----+------------+-------+--------+
7 rows in set (0.08 sec)

Edited August 6, 2014 by dalecosp

Maze · August 6, 2014

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

dalecosp · August 6, 2014

hello

and afterwards i run the following code within the sql-query-form of phpmyadmin;:

You cannot run *PHP* code in phpMyAdmin ... ;-)

CroNiX · August 6, 2014

As stated MANY times already, only execute 1 query at a time.

$query1 = $dbh->query($sql_to_create_pois_table);

$query2 = $dbh->query($sql_to_create_pois_tag_table);

You'd save yourself a lot of time if you thoroughly read the answers that people are taking the time to write you.

jazzman1 · August 6, 2014

As stated MANY times already, only execute 1 query at a time.

You need to be more specific on what exatly do you mean? According the OP's question I do think, that he wants to run multiple statements (queries) in one query string, so MySQL server allows to do this. About security...that's a different story.

Edited August 6, 2014 by jazzman1

ginerjm · August 6, 2014

People Have been very specific. From the get-go!

OP - Run one query at a time to make sure you are doing it right. Simple as that! When you are confident in your skills, take the next step and run multiples. Simple as that.

From the sidelines I've seen everyone say the same stuff and you are not listening at all!

Maze · August 7, 2014

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

Edited August 7, 2014 by Maze

Jacques1 · August 7, 2014

The structure of the XML data is fixed, at least in the examples you've posted. Sure, not all items have all data (sometimes the address isn't given, sometimes there's no fax number etc.). But that doesn't mean the structure is different. It means the values are missing, which is exactly what the SQL NULL is for. For example, if we don't have the address, then we simply use NULL in all address fields.

The question is what you want to do: Do you just want to collect random data from OpenStreetMap? Or do you actually want to give the fields a meaning and use them in your application? In the latter case, you need a fixed schema anyway.

Sign In

creating tables in php leads to errors in phpmyadmin....

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

Link to comment

Share on other sites

Join the conversation

Important Information