from xml to mysql

[Muppy]

with phpmyadmin i export a table of a mysql db in xml file. Now i want
import a xml file in a mysql db.
How can i do??
Thanks
Andrea

[Pedro]

Re: from xml to mysql

Muppy wrote:[color=blue]
>with phpmyadmin i export a table of a mysql db in xml file. Now i want
>import a xml file in a mysql db.
>How can i do??
>Thanks
>Andrea[/color]

I don't know phpmyadmin, but if it has a export to xml, does it not
have a import from xml to go along?

If it doesn't make your own :)


I don't know what your xml data looks like, but it's very
easy to transform

<sqldata>
<record>
<name>Pedro</name>
<eyecolor>brown </eyecolor>
</record>
<record>
<name>Muppy</name>
<eyecolor>blu e</eyecolor>
</record>
<!-- ... ... ... -->
</sqldata>

into

insert into table (name, eyecolor) values ('Pedro', 'brown');
insert into table (name, eyecolor) values ('Muppy', 'blue');

and then mysql_query every one of the insert statments.


Happy Coding :-)


--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.

[Muppy]

Re: from xml to mysql

> I don't know phpmyadmin, but if it has a export to xml, does it not[color=blue]
> have a import from xml to go along?[/color]

no, it doesn't have.
[color=blue]
> If it doesn't make your own :)
>
>
> I don't know what your xml data looks like, but it's very
> easy to transform[/color]

It's not esay for me ;)
Any help will be appreciated
Thanks
Andrea

[Pedro]

Re: from xml to mysql

Muppy wrote:[color=blue]
> It's not esay for me ;)
> Any help will be appreciated[/color]

Ever tried regexps?

Take that data

<?php
$data = "<?xml version=\"1.0\" ?>
<sqldata>
<record><name>P edro</name><eyecolor> brown</eyecolor></record>
<record><name>M uppy</name><eyecolor> blue</eyecolor></record>
</sqldata>";
?>

and get all the records into an array

<?php
preg_match_all( '#<record>(.*)</record>#Us', $data, $records);
?>

then, for each record, get the respective values, and insert into sql
I am treating all values as strings (quoting them with ')
you might want to test the column names and do it differently

<?php
foreach ($records[1] as $record) {
preg_match_all( '#<(.*)>(.*)</\1>#Us', $record, $values);
// $values[1] has the column names
// $values[2] has the values to insert
$sql = "insert into table ("
. implode(', ', $values[1])
. ") values ('"
. implode("', '", $values[2])
. "')";
echo $sql, '<br />'; ### or mysql_query($sq l)
}
?>


Happy Coding :-)


check the regexp page at php.net
** http://www.php.net/manual/en/ref.pcre.php

--
I have a spam filter working.
To mail me include "urkxvq" (with or without the quotes)
in the subject line, or your mail will be ruthlessly discarded.

[Muppy]

Re: from xml to mysql

> Ever tried regexps?

no ;(
Many thanks for the help!
Andrea

[Andy Hassall]

Re: from xml to mysql

On 4 Oct 2003 18:10:28 GMT, Pedro <hexkid@hotpop. com> wrote:
[color=blue]
>Muppy wrote:[color=green]
>> It's not esay for me ;)
>> Any help will be appreciated[/color]
>
>Ever tried regexps?[/color]

You can't parse XML fully with just regexps. Use an XML parser:

http://www.php.net/manual/en/ref.xml.php

--
Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Zurab Davitiani]

Re: from xml to mysql

Muppy wrote on Saturday 04 October 2003 12:24:
[color=blue][color=green]
>> Ever tried regexps?[/color]
>
> no ;(
> Many thanks for the help!
> Andrea[/color]

Either use XML parser in PHP per Andy's suggestion or use ActiveLink PHP XML
Package to manipulate XML (no PHP XML libs required):

http://www.active-link.com/intranet/software.php

http://php-xml.sourceforge.net/

Good luck!

--
Business Web Solutions
ActiveLink, LLC

https://www.active-link.com/intranet/