PHP - Moved: Order By Big_int Dpesnt Work...

Any idea why I cant get the order by to work?

$res =  mysql_query ("SELECT COUNT(*), LEFT(`comment`, 3) AS truncated from comments where navn='$rows[navn]' AND adresse='$rows[adresse]' ORDER BY time desc" ) or die(mysql_error());

    Hello dear php-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:

id     lat     lon     name     amenity     operator     vending

see the table 'pois' that i want to create - and subsequently create some columns

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

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

btw:



    see the 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>
     

     

     
     
     
      with the following fields in the db:
     
 

    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;

    EDITED BY MODERATOR - DB CREDENTIALS REMOVED

        

       
    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 http://www.overpass-turbo.eu

    cf. http://overpass-turb...Pg&c=BNJBWRBb1P


    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 he

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

we 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];
}

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
 

Hello dear friends, hello community,

i want to parse a site http://www.aktive-buergerschaft.de/buergerstiftungsfinder

Well therefore i have a true beginner-question regarding Array in order to work with the data: beginner question

The results should be stored in a MySQL-Database: well you see. i have a Curl-Approach: and i want to work with the parsed data: and in this case i want to give over the data into an array (if viewing the output in web browser, do view-source to see structured array).

Here the musings that led to the code:

Depending on num dimensions, we could do, foreach($result as $k => $v) {echo $k." ".$v}
to view and work with data (adding sub foreach loops accordingly; i.e. if $v is itself an array)

see the results: What do you think:

Code: [Select]
$ch = curl_init("http://www.aktive-buergerschaft.de/buergerstiftungsfinder");
curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 
$buffer = curl_exec($ch);
curl_close($ch);
$result = explode(",", $buffer);
foreach($result as $k => $v) {echo $k." ".$v}
if $v is itself an array)
   // print_r($result);


look forward to hear  from you!!

db1

This topic has been moved to Miscellaneous.

http://www.phpfreaks.com/forums/index.php?topic=321790.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=318036.0