PHP XML

[J Sharman]

I am fairly familiar with PHP and MYSQL and I know a little about XML.

I would like to know if it is possible to do queries on XML similar to the
way that you do queries on MYSQL and then use the results.

An example of a simple PHP/MYSQL setup.

<?php
$user="username ";
$host="sqlserve r";
$password="pass word";
$database="data base";
$connection = mysql_connect($ host,$user,$pas sword)
or die ("Couldn't connect to the server");
$db = mysql_select_db ($database,$con nection)
or die ("Couldn't connect to the server");

$query = "SELECT * FROM database WHERE(Criteria) ";
$result = mysql_query($qu ery)
or die ("Couldn't execute query.");

while ($row = mysql_fetch_arr ay($result))
{
extract($row);
echo"Set of instructions to carry out for each row in the
result";
}

?>



The question is, is it possible to do similar things with PHP/XML?
Lets say that I have an XML file located at a certain URL and I want to run
a query on it and use the results.
1. Is it possible?
2. What is the easiest way to do it?
3. How would I translate the above code to work with XML?
4. Is ther resource on the web on how to structure the queries?


Any help is much appreciated.

Rgds

Joe

[kafooey]

Re: PHP XML

On Thu, 13 Nov 2003 15:07:34 -0000, "J Sharman" <joe@joesharman .co.uk>
wrote:
[color=blue]
>I am fairly familiar with PHP and MYSQL and I know a little about XML.
>
>I would like to know if it is possible to do queries on XML similar to the
>way that you do queries on MYSQL and then use the results.[/color]

From what I have read, PHP5 will be able to do this, but I'm not sure
about PHP4.x

FWIW, have you thought about storing the XML in the MySQL database and
searching the XML? - MySQL does let you put regular expressions in the
queries :)

We have a couple of projects at work where we have considered putting
an entire record worth of data in XML and bunging it in a text
field...



kafooey
- kafooey@nospam. yahoo.co.uk
- http://www.pluggedout.com/blog

[André Næss]

Re: PHP XML

J Sharman:
[color=blue]
> I am fairly familiar with PHP and MYSQL and I know a little about XML.
>
> I would like to know if it is possible to do queries on XML similar to the
> way that you do queries on MYSQL and then use the results.[/color]

XQuery is a work in progress: http://www.w3.org/XML/Query

Personally I think this is a terrible idea. History has shown that
hierarchical/network databases are bad.

André Næss

[J Sharman]

Re: PHP XML

I already import xml into xls and then create a csv and load into mysql, but
the reason i want to do it direct from an xml url is so that I don't have to
keep updating, the xml url is updated by others so it would save me a
massive job.

Rgds

Joe

"kafooey" <kafooey@nospam .yahoo.co.uk> wrote in message
news:j9m7rvshtp falu1gs3g9q48t9 b8mobmqob@4ax.c om...[color=blue]
> On Thu, 13 Nov 2003 15:07:34 -0000, "J Sharman" <joe@joesharman .co.uk>
> wrote:
>[color=green]
> >I am fairly familiar with PHP and MYSQL and I know a little about XML.
> >
> >I would like to know if it is possible to do queries on XML similar to[/color][/color]
the[color=blue][color=green]
> >way that you do queries on MYSQL and then use the results.[/color]
>
> From what I have read, PHP5 will be able to do this, but I'm not sure
> about PHP4.x
>
> FWIW, have you thought about storing the XML in the MySQL database and
> searching the XML? - MySQL does let you put regular expressions in the
> queries :)
>
> We have a couple of projects at work where we have considered putting
> an entire record worth of data in XML and bunging it in a text
> field...
>
>
>
> kafooey
> - kafooey@nospam. yahoo.co.uk
> - http://www.pluggedout.com/blog[/color]

[André Næss]

Re: PHP XML

kafooey:
[color=blue]
> We have a couple of projects at work where we have considered putting
> an entire record worth of data in XML and bunging it in a text
> field...[/color]

Was that the absolutely most inefficient and unreliable way you could come
up with, or can you actually do worse?

André Næss

[kafooey]

Re: PHP XML

On Thu, 13 Nov 2003 22:53:46 +0000, André Næss
<andrena.spamre allysucks@ifi.u io.no> wrote:
[color=blue]
>kafooey:
>[color=green]
>> We have a couple of projects at work where we have considered putting
>> an entire record worth of data in XML and bunging it in a text
>> field...[/color]
>
>Was that the absolutely most inefficient and unreliable way you could come
>up with, or can you actually do worse?[/color]

It was just an idea to do with keyword searching where the table
structure may not always be the same.

In the end we chose to go the "textbook" route of building the SQL
completely on the fly from analysis of the content of tables that had
factored in the creation of the tables we wanted to search...

Took a lot longer to design and write, but as you say - much more
efficient.


kafooey
- kafooey@nospam. yahoo.co.uk
- http://www.pluggedout.com/blog

[Terence]

Re: PHP XML

J Sharman wrote:[color=blue]
> I am fairly familiar with PHP and MYSQL and I know a little about XML.
>
> I would like to know if it is possible to do queries on XML similar to the
> way that you do queries on MYSQL and then use the results.
>
> An example of a simple PHP/MYSQL setup.
>
> <?php
> $user="username ";
> $host="sqlserve r";
> $password="pass word";
> $database="data base";
> $connection = mysql_connect($ host,$user,$pas sword)
> or die ("Couldn't connect to the server");
> $db = mysql_select_db ($database,$con nection)
> or die ("Couldn't connect to the server");
>
> $query = "SELECT * FROM database WHERE(Criteria) ";
> $result = mysql_query($qu ery)
> or die ("Couldn't execute query.");
>
> while ($row = mysql_fetch_arr ay($result))
> {
> extract($row);
> echo"Set of instructions to carry out for each row in the
> result";
> }
>
> ?>
>
>
>
> The question is, is it possible to do similar things with PHP/XML?
> Lets say that I have an XML file located at a certain URL and I want to run
> a query on it and use the results.
> 1. Is it possible?[/color]

YES. Any furthermore, you have choices.
[color=blue]
> 2. What is the easiest way to do it?[/color]

Maybe you will want to decide this for yourself once checking out your
options. I'll list some down the bottom.
[color=blue]
> 3. How would I translate the above code to work with XML?[/color]

//*[(criteria)]

Depending on what it is exactly you're interested in extracting and what
exactly the criteria is for extracting this, the complexity will go up.
That was an XPath query by the way.

[color=blue]
> 4. Is ther resource on the web on how to structure the queries?
>[/color]

For XPath, do the tutorials at zvon.org


OK, stylesheet processors come with XPath built in, but you may not
neccesarily be after a full templating solution, you may just want some
data from the XML document to use in your code. And you seem to be
familiar with XSL so you know how you could use that.

The two main other ways I can think of (before discussing pure XML
databases) is SAX or DOM XPath.

SAX is probably the simplest (hence the name, Simple Api for XML).
Basically you just write a filter function which is targetted at a
particular XML construct. Lets say you want to target an ELEMENT
construct. You then write a handler function that deals with elements.
When you execute the SAX parser, it will call this function everytime it
hits an element as it "scans" down a document. Simply put an "if"
statement in the function which has your criteria, then you can add the
content to some sort of stack (array) when the condition is met.

http://www.php.net/xml

This can be tricky since may probably need to use
xml_set_charact er_data_handler () instead of xml_set_element _handler()
depending on your target info.

DOM is what I always use. So basically you creat a PHP DOM XML object
instance based on your document, you then use an XPath function on that
object to return you a nodeset. You can extract your data from this
nodeset. THis is generally how I do it.

http://www.php.net/manual/en/function.xpath-eval.php

My framework makes this easy. (http://xao-php.sourceforge.net)
$objDom = new DomDoc($xmlData ,XAO_DOC_DATA);
$arrNodes = $objDom->arrNdXPath($cr iteria_xpath_ex pression);
and then of course, for each node you can run get_content() on it.

http://www.php.net/manual/en/function.domnode-get-content.php

OK. The last option is the most ideal. **Pure XML databases** have
XQuery which goes beyond XPath and incorporate more SQL-type
functionality (even though XPath by itself is equal to the task). The
difference is that your query can span any amount of XML documents or
document collections :)
Unfortunately I am out of time so maybe an article on that later. The
best product I have seen so far is Tamino by Software AG but an
unlimited licence is not cheap (think tens of thousands $$$ per CPU). On
the other hand, there are 2 strong open-source options.

http://eXist.sourceforge.net

and http://xml.apache.org/xindice
GO LOOK AT THE DOCUMENTATION. You will find that these databases meet
your needs exactly. Both can be accessed from PHP scripts which
implement XML-RPC. This will be the next feature to go into my framework.

good luck.

PS.
Tamino can backend onto other information systems such as MySQL and you
can assign columns to tags/attributes or whatever in your database
documents. XQueries (and other access methods) then pick up the data in
your database completely transparently. Tamino is "THE KILLER PRODUCT"
in my opinion, but it is like Oracle or IBM in the $$$ department :(
One access method is webdav, which is great for win XP users who can
effectively "mount" in in windows explorer. Of course same applies for
unix, but windows gumbies love it :)

[sandy pittendrigh]

Re: PHP XML

Take a look at Xindice, DBXml and SleepyCat, Ipedo, Xcelon and even
(now) Oracle.

All of the above support complex XPath queries over XML data.
All of the above apply any given XPath iteratively over
all xml data in a collection, rather than applying
the XPath over a singleton file. That (queries over a collection)
means they can be described as "xml databases."

Heirarchical databases are not a good choice for large
data sets, but large is a fast moving target these days.
XML is a particularly good choice for describing data with
an evolving schema (flexible schema is an oxymoron in
the relational world).