XML crashes on import from php

[TanTrazz]

Hi all,

I've made a script to import data from a mySQL database into xml. Somethimes it crashes because of characters like '&' etc. Very annoying because ill have to delete the characters myself by hand......

How can i fix it??? here is the code

<?php 
header("Content-type: text/xml"); 

//to create connection to database 
$connection = mysql_connect("localhost","username", "password") 
or die ("could not connect to database"); 

//to select the database here test is the sample database come with mysql 
$db = mysql_select_db("databasename",$connection) 
or die ("Couldn't select database."); 

$rs = mysql_query("select id, company, address, name, postalc, city, country, code from docu_request where behandeld='0'",$connection) 
or die ("invalid query"); 

//count the no. of  columns in the table 
$fcount = mysql_num_fields($rs); 

//you can choose any name for the starting tag 
echo ("<result>"); 
while($row = mysql_fetch_array( $rs ) ) 
{ 
echo ("<tablerow>"); 
for($i=0; $i< $fcount; $i++) 
{ 
$tag = mysql_field_name( $rs, $i ); 
echo ("<$tag>". $row[$i]. "</$tag>"); 
} 
echo ("</tablerow>"); 
} 
echo ("</result>"); 
?>

TnQ TanTrazz

 

[KarveR]

Why don't you just get mysql to dump directly to xml ?

mysql -h host -u user -e "select command etc .." -D database -x > filename

from php,
<?php
$dump='mysql -h host -u user -e "select command etc .." -D database -x > filename';
cmd($dump);

?>

# see notes on cmd exec and their limitations and exploitability ...


K

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

*note - CAPITOL X

kthanks, noticed it as I hit submit.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TanTrazz]

Hi KarveR,

I've tried it but it wont work....

What went wrong?

<?php
$dump='mysql -h localhost -u hollandshielding -e "select id, company, address, name, postalc, city, country, code from docu_request where behandeld='0'" -D hollandshielding -X > worddoc.xml';
cmd($dump);

?>

 

[KarveR]

Things have changed a little since I last used it ..
try shell_exec($dump) or exec($dump)

see the manual page here

If its not your own webserver, you could be limited by safe mode. Sorry, should've asked that first.

Do you get any errors or just a blank page?

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

Additional, mysql -X option is available from later versions of mysql only, its not in 3.23 ut 4 upwards is ok.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

A working one on mysql 4.0.something

<?php
$FILE='SOMEFILE.XML';

$dump='mysql -h localhost -u root -e "Select * from TABLE WHERE STUFF ....." -D database -X > '.$FILE;
shell_exec($dump);
if (is_file($FILE)){
echo "$FILE has size of ".filesize($FILE);
echo "<a href=$FILE>$FILE</a>";
include($FILE);
}
?>

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TanTrazz]

Thanks for the replies...It works now...

but....

it will not show the content of the db but the manual or something....

see:


SOMEFILE.XML has size of 4593SOMEFILE.XMLmysql Ver 11.15 Distrib 3.23.43, for pc-linux-gnu (i686) Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. -B, --batch Print results with a tab as separator, each row on a new line. Doesn't use history file. --character-sets-dir=... Directory where character sets are located. -C, --compress Use compression in server/client protocol. -D, --database=.. Database to use. --default-character-set=... Set the default character set. -e, --execute=... Execute command and quit. (Output like with --batch) -E, --vertical Print the output of a query (rows) vertically. -f, --force Continue even if we get an sql error. -g, --no-named-commands Named commands are disabled. Use \* form only, or use named commands only in the beginning of a line ending with a semicolon ( Since version 10.9 the client now starts with this option ENABLED by default! Disable with '-G'. Long format commands still work from the first line. -G, --enable-named-commands Named commands are enabled. Opposite to -g. -i, --ignore-space Ignore space after function names. -h, --host=... Connect to host. -H, --html Produce HTML output. -L, --skip-line-numbers Don't write line number for errors. --no-pager Disable pager and print to stdout. See interactive help (\h) also. --no-tee Disable outfile. See interactive help (\h) also. -n, --unbuffered Flush buffer after each query. -N, --skip-column-names Don't write column names in results. -O, --set-variable var=option Give a variable an value. --help lists variables. -o, --one-database Only update the default database. This is useful for skipping updates to other database in the update log. --pager[=...] Pager to use to display results. If you don't supply an option the default pager is taken from your ENV variable PAGER (). Valid pagers are less, more, cat [> filename], etc. See interactive help (\h) also. This option does not work in batch mode. -p[password], --password[=...] Password to use when connecting to server If password is not given it's asked from the tty. -P, --port=... Port number to use for connection. -q, --quick Don't cache result, print it row by row. This may slow down the server if the output is suspended. Doesn't use history file. -r, --raw Write fields without conversion. Used with --batch -s, --silent Be more silent. -S --socket=... Socket file to use for connection. -t, --table Output in table format. -T, --debug-info Print some debug info at exit. --tee=... Append everything into outfile. See interactive help (\h) also. Does not work in batch mode. -u, --user=# User for login if not current user. -U, --safe-updates[=#], --i-am-a-dummy[=#] Only allow UPDATE and DELETE that uses keys. -v, --verbose Write more. (-v -v -v gives the table output format) -V, --version Output version information and exit. -w, --wait Wait and retry if connection is down. Default options are read from the following files in the given order: /etc/my.cnf /usr/local/mysql/var/my.cnf ~/.my.cnf The following groups are read: mysql client The following options may be given as the first argument: --print-defaults Print the program argument list and exit --no-defaults Don't read default options from any options file --defaults-file=# Only read default options from the given file # --defaults-extra-file=# Read this file after the global files are read Possible variables for option --set-variable (-O) are: connect_timeout current value: 0 max_allowed_packet current value: 16777216 net_buffer_length current value: 16384 select_limit current value: 1000 max_join_size current value: 1000000




How can i fix it....

<?php


$FILE='SOMEFILE.XML';

$dump='mysql -h localhost -u hollandshielding -e "select id, company, address, name, postalc, city, country, code from docu_request where behandeld=0" -D hollandshielding -X > '.$FILE;
exec($dump);
if (is_file($FILE)){
echo "$FILE has size of ".filesize($FILE);
echo "<a href=$FILE>$FILE</a>";
include($FILE);
}
?>


TnQ TanTrazz

 

[KarveR]

Wrong version (3.23...) try opening a file and writing output top that instead of displaying it, this should cure the parse errors .. or try addslashes() see the manual.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.