PHP - Moved: Mysql Order By Two Different Fields

This topic has been moved to Third Party PHP Scripts.

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

    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
 

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to Miscellaneous.

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

Lets say I have this query:

$sql = mysql_query("SELECT * FROM Member ORDER BY posts DESC LIMIT 10");
while ($get = mysql_fetch_array($sql)){

$row .= $get['id'];
$posts .= $get['posts'];

echo "$row - $posts<br />";

}

Lets say I have two of rows, with posts fields of 84 and 803. When it displays it, it grabs the 84 row first, when I want the bigger number 803 first. Is there a way to fix this?

I'm currently trying to grab data from my database for my chatbox, which when displayed, displays in an ascending order, when I call it to descend, it shows the messages ONCE in descending order, then just repeats but in random order..

Code: [Select]
$sql = "SELECT * FROM `message` WHERE `chat_id` = 1 AND `message_id` > $last ORDER BY `message_id` DESC";
Database:
message_id | name
1                   RSX
2                   RSX
3                   RSX
4                   RSX

Code: [Select]
RSX: 4
RSX: 3
RSX: 2
RSX: 1
RSX: 4
RSX: 3
RSX: 2
RSX: 4
RSX: 3
RSX: 4

It is ordering it correctly, just repeats, I'm using Ajax, could that be the problem, or is it within the PHP code?

Edit:
I read it wrong, it displays as shown under "output".

This topic's house has been seized by the bank and it now lives in MySQL Help until it can get back on its feet.

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

This topic has been moved to JavaScript Help.

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

This topic has been moved to MySQL Help.

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

This topic has been moved to MySQL Help.

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

hello all.
I have a list of records, which each have a comment button, just like any forum does. when I click on the comment button it brings up the current record at the top, and a blank form, to which you enter in information.

When I submit this information, I want it to show up on the main page as a child of the parent record. I want it similar to the example I've shown below:
Code: [Select]
id name         dob           address              email                  username
1  john smith   10/11/1986    124 Peermont Drive   john.smith@yahoo.com   john smith1
  >>  2   Harry        15/12/1985     98 The Roundhay     harry@gmail.com        harry23
    >>>  3  jhk          08/11/1976     65 dfgdfg           gfdfg@ yahoo.com       jhk345
4  john smith   10/11/1986    124 Peermont Drive   john.smith@yahoo.com   john smith1
     >> 5 Harry        15/12/1985     98 The Roundhay     harry@gmail.com        harry23
        >>>> 6  jhk          08/11/1976     65 dfgdfg           gfdfg@ yahoo.com       jhk345

I've been stuck on this for hours, and I haven't got anywhere. Any ideas?

When you make a mysql query the results are always returned with the first being the first entry in the database.  I was wondering if there is a way to make it so that the last entry is returned first and so on?  Thanks for any help. 

ok so i want to order by high to low on the field price but i don't know how to do this.

i can order low to high by using ORDER BY  `Price` ASC

but how can i order high to low?

Hello,

I would like to order by date that contains day and month, like this: 14-3
Is this possible?

Quote

$sql = mysql_query("SELECT * FROM date WHERE online='1'");