Hello dear db-experts - good day.

note; i run linux opensuse 13.1

the final goal is to get stored some xml-files in a mysql-database.
thats what i am looking for. - the xml-files are derived from a osm-request - at a OpenSteetpmap-api. and thats why i am here. Guess that you are good php and mysql-experts

well i play around with some openstreetmap-requests - and want to store the results in a Mysql-db.

see the tags:


Code:
id     lat     lon     name     amenity     operator     vending
see the table 'pois' that i want to create - and subsequently create some columns
Code:
see the SQL-Script

CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE hans;
 
CREATE TABLE `pois` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `lat` FLOAT(10,7) NOT NULL,
  `lon` FLOAT(10,7) NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `amenity` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `operator` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `vending` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

first of all i thought that i can do the db creation with some hard coded methods: but instead of hard-coding the required column names as in


$fields = array('id','lat','lon','name','amenity','operator' ,'vending');


we could use this to pick up any additional columns added to your pois table

Code:
$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
    $fields[] = $row[0];
}
btw:



see the data
Code:
    <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>


with the following fields in the db:


Code:
    CREATE DATABASE `db123` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
    USE hans;
     
    CREATE TABLE `pois` (
      `id` BIGINT(20) UNSIGNED NOT NULL,
      `lat` FLOAT(10,7) NOT NULL,
      `lon` FLOAT(10,7) NOT NULL,
      `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
      `amenity` VARCHAR(255) COLLATE utf8_bin NOT NULL,
      `operator` VARCHAR(255) COLLATE utf8_bin NOT NULL,
      `vending` VARCHAR(255) COLLATE utf8_bin NOT NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
see the dataset; - which is stored in the file mysql.txt
the dataset - it is gathered from the request on the overpass-api which

resides here overpass turbo



you see a request on the left part of the screen

note: to get the output - just press the button in the top-menu called "Ausführen"

after this you press the button called "DATEN" on the top-right -
just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen.

note - it has got various ids - that means that the osm-file does not give back constantly all the tags...

the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!?

see the output here:


Code:
    <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>
well you see that i have some questions

the second one is regarding the variations in the mysql.txt - file
- i.e. the different number of tags.; How to make the script robust so that
it is able to work with this - and does not stopt to work....!?


well i hope i was able to make clear what i want to achieve:



can i use the code snippet to create the db table -
[CODE]
we could use this to pick up any additional columns added to your pois table
Code:
$sql = "SHOW COLUMNS FROM pois";
$fields = array();
$res = $db->query($sql);
while ($row = $res->fetch_row()) {
    $fields[] = $row[0];
}
note: i need to have a robust way - where i can cope with xmlfiles that are

full with additionals tags /(that make it necessary to have more collumns added.

I look forward to hear from you

many many greetings

well at this point it is pretty clear: what is better
hardcoding a db-creation process or not: what is more flexible?