...

View Full Version : mySQL Query Browser export to xml



erdubya
04-21-2009, 07:44 AM
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?

oesxyl
04-21-2009, 10:48 AM
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

erdubya
04-21-2009, 03:13 PM
I have no idea how to use xslt. How would I change this with xslt?

CFMaBiSmAd
04-21-2009, 03:26 PM
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.

oesxyl
04-21-2009, 03:29 PM
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

erdubya
04-21-2009, 03:45 PM
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.

erdubya
04-21-2009, 03:46 PM
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.

CFMaBiSmAd
04-21-2009, 03:54 PM
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.

erdubya
04-21-2009, 04:02 PM
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.



Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 35 bytes) in ...metabase/metabase_mysql.php on line 224



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

CFMaBiSmAd
04-21-2009, 04:13 PM
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.)

oesxyl
04-21-2009, 05:22 PM
try this:

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

erdubya
04-21-2009, 06:15 PM
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.



<?
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($xmlFile, null);

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

CFMaBiSmAd
04-21-2009, 06:23 PM
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.

erdubya
04-21-2009, 06:28 PM
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));
}

oesxyl
04-21-2009, 06:33 PM
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

CFMaBiSmAd
04-21-2009, 06:40 PM
Did you look at the code he used? It is writing the contents to the file as it creates it. The error is occurring in the database class.

It will take seeing the whole db class to see how the posted code is reached and used by the class. The problem is likely happening when the query is being executed, but it could as easily be while the data is being iterated over.

erdubya
04-21-2009, 07:14 PM
Ok thanks alot everybody. I figured I was borrowing/piggybacking another connection string from different software on my server. Used my own db connection string and it works... well sort of.

NOW, I'm having problems using mysql_fetch_array with elements designated as CDATA. I think this deserves another post so I'll create one as to keep things orderly.

Update:
Surprisingly it works... guess I needed to clear my cache. Thanks!

oesxyl
04-21-2009, 07:18 PM
Did you look at the code he used? It is writing the contents to the file as it creates it.
no, $xmlPacket is the result of concatenation of what the query fetch from database, so will have around 7M, as op said, of text. This could cause the problem.


The error is occurring in the database class.
I agree that you could be right and the problem could come from database class but I wiould first be sure that this part is ok and then search further. If OP use the database class in other modules then fixing the problem to work here could mess things in another part.


It will take seeing the whole db class to see how the posted code is reached and used by the class. The problem is likely happening when the query is being executed, but it could as easily be while the data is being iterated over.
it is possible, but is a assumption, that database class store into a variable(therefor in memory) the results fetched from mysql.

best regards

CFMaBiSmAd
04-21-2009, 07:38 PM
Too bad you did not look at the post he made or the error or the part of the class code he posted. The latest code is using a variable $xmlData now and it is writing each line inside of the loop to the file.

oesxyl
04-21-2009, 08:52 PM
Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 35 bytes) in ...metabase/metabase_mysql.php on line 224
the line where error appear is random, depend on the moment where "out of memory" occure. Could be this or, in another data context, in another place.




while($row = $db->row())
{
$xmlPacket .=
" <song>
<id>$row[id]</id>
<artist><![CDATA[$row[artist]]]></artist>
......
<mood><![CDATA[$row[mood]]]></mood>
<count_requested>$row[count_requested]</count_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);


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).
$xmlPacket is concatenate inside the while loop and write once at the end, that means it have "almost 7M".
I don't know but I think you said same thing in one of your post when you suggest to don't keep all data in memory.


Too bad you did not look at the post he made or the error or the part of the class code he posted. The latest code is using a variable $xmlData now and it is writing each line inside of the loop to the file.
I was looking but probably I don't understand what you want to say. Maybe will be more clear for me if you give details.

Sorry, I think I see it, you said about post #12
best regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum