Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts

    mySQL Query Browser export to xml

    When exporting a resultset to xml, the system defaults to

    <ROOT>
    <row>
    <field name="firstname">john</field>
    </row>
    </ROOT>


    Is there a way to change the exported elements to something like:
    <contactinformation>
    <contact>
    <firstname>john</firstname>
    </contact>
    </contactinformation>

    ?

    If not mySQL QueryBrowser, is there any good alternatives out there that will let me accomplish this?
    Last edited by erdubya; 04-21-2009 at 07:56 AM. Reason: clarification

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by erdubya View Post
    When exporting a resultset to xml, the system defaults to

    <ROOT>
    <row>
    <field name="firstname">john</field>
    </row>
    </ROOT>


    Is there a way to change the exported elements to something like:
    <contactinformation>
    <contact>
    <firstname>john</firstname>
    </contact>
    </contactinformation>

    ?

    If not mySQL QueryBrowser, is there any good alternatives out there that will let me accomplish this?
    you can use xslt to transform but this is a xml/xslt problem.

    best regards

  • #3
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I have no idea how to use xslt. How would I change this with xslt?

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,109
    Thanks
    2
    Thanked 326 Times in 318 Posts
    You are expecting a database management tool to do something that is specific to your application. Only you and your application know that the data should be output in the format that you listed. You need to write application code (using php for example) that formats and presents the data the way that you want.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by erdubya View Post
    I have no idea how to use xslt. How would I change this with xslt?
    Since you want to have data as xml I assume that you know how to process this with xslt but in that case I'm not so sure if is a good idea to use xslt.
    If you decide to go on this route probably is a better idea to ask a moderator to move the thread in the xml/xslt forum and to post a bigger part of you xml starting with the root node.
    Is a simple task for somebody who know at least basics about this but can be a long thread for a beginer.
    Why do you need data as xml?

    best regards

  • #6
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    You are expecting a database management tool to do something that is specific to your application. Only you and your application know that the data should be output in the format that you listed. You need to write application code (using php for example) that formats and presents the data the way that you want.
    I have a database table of over 6000 records that needs to be exported as a xml file. my web server errors out (memory problems) when I try to have php create the xml file. If I put a limit on the select statement of 1000 records it works fine... anything over that and I get problems (my webserver simply cannot handle it). That's why I need a software solution to this problem.

  • #7
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oesxyl View Post
    Since you want to have data as xml I assume that you know how to process this with xslt but in that case I'm not so sure if is a good idea to use xslt.
    If you decide to go on this route probably is a better idea to ask a moderator to move the thread in the xml/xslt forum and to post a bigger part of you xml starting with the root node.
    Is a simple task for somebody who know at least basics about this but can be a long thread for a beginer.
    Why do you need data as xml?

    best regards
    i tried all other formats, xml works best for what i'm doing. You're right, maybe this should be moved to the appropriate forum.

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,109
    Thanks
    2
    Thanked 326 Times in 318 Posts
    It's likely your php memory/error problem can be easily solved. Post the actual error and the php code you were using. Also post the whole xml tag structure you are trying to create.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #9
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    It's likely your php memory/error problem can be easily solved. Post the actual error and the php code you were using. Also post the whole xml tag structure you are trying to create.
    Code:
    Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 35 bytes) in ...metabase/metabase_mysql.php on line 224

    PHP Code:
    <?
    require_once('config.php'); 

    $query "SELECT
    ID, artist, album, title, date_added, picture, duration, albumyear, buycd, website, info,
    genre, mood, bpm, date_played, date_artist_played, date_album_played,
    weight, count_played, count_requested, last_requested, count_performances
    from songlist WHERE songtype='S'
    ORDER BY ID ASC"
    ;


    $db->open($query);
    //Prepare XML file
    if ($query) {
        
    $xmlFile "musicLibrary.xml";
        
    $fh fopen($xmlFile'w') or die("can't open file");

        
    $xmlPacket "<?xml version='1.0' encoding='UTF-8'?>\n";
        
    //$xmlPacket .= "<!DOCTYPE Server SYSTEM 'opt/pdos/etc/pdoslrd.dtd'>\n";
        
    $xmlPacket .="<songRecords>\n";
        
        
    $count 1
        while(
    $row $db->row()) 
            { 
                
    $xmlPacket .=
    "    <song>
            <id>$row[id]</id>
            <artist><![CDATA[$row[artist]]]></artist>
            <album><![CDATA[$row[album]]]></album>
            <title><![CDATA[$row[title]]]></title>
            <date_added>$row[date_added]</date_added>
            <picture><![CDATA[$row[picture]]]></picture>
            <duration>$row[duration]</duration>
            <albumyear>$row[albumyear]</albumyear>
            <buycd><![CDATA[$row[buycd]]]></buycd>
            <website><![CDATA[$row[website]]]></website>
            <genre><![CDATA[$row[genre]]]></genre>
            <mood><![CDATA[$row[mood]]]></mood>
            <bpm>$row[bpm]</bpm>
            <date_played>$row[date_played]</date_played>
            <date_artist_played>$row[date_artist_played]</date_artist_played>
            <date_album_played>$row[date_album_played]</date_album_played>
            <weight>$row[weight]</weight>
            <count_played>$row[count_played]</count_played>
            <count_requested>$row[count_requested]</count_requested>
            <last_requested>$row[last_requested]</last_requested>
            <count_performances>$row[count_performances]</count_performances>
        </song>\n"
    ;
            }
        
    $xmlPacket .= '</songRecords>';
    }
    else {
        
    $xmlPacket "<?xml version='1.0'?><didnotwrite></didnotwrite>";
    }

    // write var to file
    fwrite($fh,$xmlPacket);
    fclose($fh);

    die(
    'Done...');
    ?>
    I run the webserver on whm and cpanel so I have a lot of control. But I don't know how to accomodate for php writing to such a large file (almost 7MB).

    Thanks.
    Last edited by erdubya; 04-21-2009 at 05:51 PM. Reason: further info.

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,109
    Thanks
    2
    Thanked 326 Times in 318 Posts
    That's because you should be writing each line to the file as you create it instead of forming the whole content of the file in a variable (in memory.)
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #11
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    try this:
    PHP Code:
    <?
    require_once('config.php'); 

    $query "SELECT
    ID, artist, album, title, date_added, picture, duration, albumyear, buycd, website, info,
    genre, mood, bpm, date_played, date_artist_played, date_album_played,
    weight, count_played, count_requested, last_requested, count_performances
    from songlist WHERE songtype='S' ORDER BY ID ASC"
    ;

    $db->open($query);
    //Prepare XML file
    if ($query) { // <- this will be always true since $query is a non-empty string
        
    $xmlFile "../drupal_bridge/musicLibrary.xml";
        
    $fh fopen($xmlFile'w') or die("can't open file");

        
    $xmlPacket "<?xml version='1.0' encoding='UTF-8'?>\n";
        
    //$xmlPacket .= "<!DOCTYPE Server SYSTEM 'opt/pdos/etc/pdoslrd.dtd'>\n";
        
    $xmlPacket .="<songRecords>\n";
        
    fwrite($fh,$xmlPacket);

        
    $count 1
        while(
    $row $db->row()) { 
            
    $xmlPacket ="    <song>
            <id>$row[id]</id>
            <artist><![CDATA[$row[artist]]]></artist>
            <album><![CDATA[$row[album]]]></album>
            <title><![CDATA[$row[title]]]></title>
            <date_added>$row[date_added]</date_added>
            <picture><![CDATA[$row[picture]]]></picture>
            <duration>$row[duration]</duration>
            <albumyear>$row[albumyear]</albumyear>
            <buycd><![CDATA[$row[buycd]]]></buycd>
            <website><![CDATA[$row[website]]]></website>
            <genre><![CDATA[$row[genre]]]></genre>
            <mood><![CDATA[$row[mood]]]></mood>
            <bpm>$row[bpm]</bpm>
            <date_played>$row[date_played]</date_played>
            <date_artist_played>$row[date_artist_played]</date_artist_played>
            <date_album_played>$row[date_album_played]</date_album_played>
            <weight>$row[weight]</weight>
            <count_played>$row[count_played]</count_played>
            <count_requested>$row[count_requested]</count_requested>
            <last_requested>$row[last_requested]</last_requested>
            <count_performances>$row[count_performances]</count_performances>
        </song>\n"
    ;
        
    fwrite($fh,$xmlPacket);
            }
        
    $xmlPacket '</songRecords>';
        
    fwrite($fh,$xmlPacket);
    }
    else {
        
    $xmlPacket "<?xml version='1.0'?><didnotwrite></didnotwrite>";
        
    fwrite($fh,$xmlPacket);
    }

    fclose($fh);

    die(
    'Done...');
    ?>
    I replace .= with = and fwrite to the file and this way I avoid to keep a huge $xmlPacket in memory.

    best regards

  • Users who have thanked oesxyl for this post:

    erdubya (04-21-2009)

  • #12
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    It's likely your php memory/error problem can be easily solved. Post the actual error and the php code you were using. Also post the whole xml tag structure you are trying to create.
    Good idea. However, I just tried your suggestion and got the same problem.

    PHP Code:
    <?
    require_once('config.php');

    $query "SELECT
    ID, artist, album, title, date_added, picture, duration, albumyear, buycd, website, info,
    genre, mood, bpm, date_played, date_artist_played, date_album_played,
    weight, count_played, count_requested, last_requested, count_performances
    from songlist WHERE songtype='S'
    ORDER BY ID ASC"
    ;


    $db->open($query);
    //Prepare XML file
    if ($query) {
        
    $xmlFile "../drupal_bridge/musicLibrary.xml";
        
    //clear file and open file for append write mode
        
    $fh fopen($xmlFile'a') or die("can't open file");
        
    file_put_contents($xmlFilenull);

        
    $xmlData "<?xml version='1.0' encoding='UTF-8'?>\n";
        
    $xmlData .="<songRecords>\n";

        
    //write headers to xml
        
    fwrite($fh,$xmlData);

        
    $count 0;
        while(
    $row $db->row())
        {
                
    $xmlData =
    "    <song>
            <id>$row[id]</id>
            <artist><![CDATA[$row[artist]]]></artist>
            <album><![CDATA[$row[album]]]></album>
            <title><![CDATA[$row[title]]]></title>
            <date_added>$row[date_added]</date_added>
            <picture><![CDATA[$row[picture]]]></picture>
            <duration>$row[duration]</duration>
            <albumyear>$row[albumyear]</albumyear>
            <buycd><![CDATA[$row[buycd]]]></buycd>
            <website><![CDATA[$row[website]]]></website>
            <genre><![CDATA[$row[genre]]]></genre>
            <mood><![CDATA[$row[mood]]]></mood>
            <bpm>$row[bpm]</bpm>
            <date_played>$row[date_played]</date_played>
            <date_artist_played>$row[date_artist_played]</date_artist_played>
            <date_album_played>$row[date_album_played]</date_album_played>
            <weight>$row[weight]</weight>
            <count_played>$row[count_played]</count_played>
            <count_requested>$row[count_requested]</count_requested>
            <last_requested>$row[last_requested]</last_requested>
            <count_performances>$row[count_performances]</count_performances>
        </song>\n"
    ;
        
    fwrite($fh,$xmlData);
        
    //sleep(1);
        
    $count ++;
        echo 
    $count."<br>";

        }

        
    $xmlData '</songRecords>';
        
    fwrite($fh,$xmlData);
    }
    else {
        
    $xmlData "<?xml version='1.0'?><didnotwrite></didnotwrite>";
        
    fwrite($fh,$xmlData);
    }

    // write var to file

    fclose($fh);

    die(
    'Done...');
    ?>
    Is there any way to free up memory after writing to the file? Is there something here in the code that i'm doing wrong? I even tried closing the file after every write, still same problem.

  • #13
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,109
    Thanks
    2
    Thanked 326 Times in 318 Posts
    Which line of the posted code does the error message refer to?

    It's also possible that the db class is doubling the amount of memory needed by making an array copy of the actual result set. If the error message is referring to the db class, you will need to post that to get specific help with what it is doing.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    erdubya (04-21-2009)

  • #14
    New Coder
    Join Date
    Sep 2006
    Posts
    66
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    Which line of the posted code does the error message refer to?

    It's also possible that the db class is doubling the amount of memory needed by making an array copy of the actual result set. If the error message is referring to the db class, you will need to post that to get specific help with what it is doing.
    The error is referring to a line from a different file.

    metabase_mysql.php on line 223

    Lines 221 - 228 of the above mentioned file.

    Function FetchResultArray($result,&$array,$row)
    {
    if(!mysql_data_seek($result,$row)
    || !($array=mysql_fetch_row($result)))
    return($this->SetError("Fetch result array",mysql_error($this->connection)));
    $this->highest_fetched_row[$result]=max($this->highest_fetched_row[$result],$row);
    return($this->ConvertResultRow($result,$array));
    }

  • #15
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by erdubya View Post
    Is there any way to free up memory after writing to the file? Is there something here in the code that i'm doing wrong? I even tried closing the file after every write, still same problem.
    did you read my previous replay?

    best regards


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •