Re: [PHP] PHP/MYSQL/XML Conversion
Kelvin Park wrote: I'm trying to convert joined multiple database table to one xml file. Is it more efficient to initially, join multiple (more that 4 tables) together to produce XML file, or convert every table in to XML file and use those XML files to relate data? I'd make the database do all the work. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MYSQL/XML Conversion
certainly it is better to use the database, as it is designed for such a purpose. -nathan On 7/23/07, Kelvin Park <[EMAIL PROTECTED]> wrote: I'm trying to convert joined multiple database table to one xml file. Is it more efficient to initially, join multiple (more that 4 tables) together to produce XML file, or convert every table in to XML file and use those XML files to relate data? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/MYSQL/XML Conversion
I'm trying to convert joined multiple database table to one xml file. Is it more efficient to initially, join multiple (more that 4 tables) together to produce XML file, or convert every table in to XML file and use those XML files to relate data? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL/XML
Shoot, kind of an important error I made in the code I gave you
earlier -- the closing quote for $xml_document should be a single-quote,
not a double-quote as I wrote.
Sorry,
Erik
On Monday, April 1, 2002, at 04:26 PM, Erik Price wrote:
> Still, you want some advice? First, your SQL code won't work as it
> is -- you've forgotten a mysql_fetch_* function to pull the data from
> the $sql_result. So, add to your code:
>
> while ($xml_data_row = mysql_fetch_assoc($sql_result)) {
> $xml_document = '
>
>
>
> ' . $xml_data_row['name'] . '
> ' . $xml_data_row['address'] . '
> ' . $xml_data_row['city'] . '
> ' . $xml_data_row['home'] . '
>
>
>
> ";
>
Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL/XML
On Monday, April 1, 2002, at 02:33 PM, Jay Fitzgerald wrote:
> Can this be done and am I going about it the right way?
Are you familiar with something called "partial decomposition"? I'm not
sure where it came from, but Google suggests that it may have been
conceived by Daniel Appelquist, the author of a book called "XML and
SQL". I read this book a few weeks ago -- it's expensive, and slim on
content (see my mini-review below), but covers a topic that everyone
seems very interested in. I was elated when Google pointed me to this
article on the theory of partial decomposition (by Appelquist) but
dismayed to find that he didn't submit it in time for a conference, and
so it is not actually found at this link:
http://www.idealliance.org/papers/xml2001/papers/html/06-05-02.html
But I'm sure it's the same idea he puts forth in his book.
Partial Decomposition:
The idea is that you store your information in an XML document, and then
take the most-searched information and store it in a database with some
kind of relationship to the document (either store the XML document in
the database or store a URI to the XML document in the database). In
this fashion, you can use the power of SQL to search for your data, but
have the data itself stored in XML to take advantage of XML's features.
The important thing to remember is that the data in the columns should
ONLY be used for searches, and the XML is where the data TRULY lies,
otherwise you can violate data integrity. It is not a good scheme for
data that is regularly changed or updated -- for this you are better off
storing your data in the usual way. Also, some of the methods for
ensuring data integrity are not at this time possible in MySQL (he uses
Oracle and SQL Server for a lot of his examples), though you can ad-lib
it with your application (i.e. in PHP) if you are dilligent and are
careful not to make changes to your database without going through the
proper channels to ensure that data integrity is maintained.
It is no substitute for a true XML database, since it is just that -- a
"partial" decomposition of your data (and thus is only "partially"
searchable), and won't work for every application. But you may wish to
check out Appelquist's book.
(* my mini-review of "XML and SQL" by Daniel Appelquist: Well, there
aren't a lot of pages, the text is huge, and the book still costs US
$40.00. Kind of ridiculous. This is further exacerbated by the fact
that only 80% of the book actually revolves around ideas of using XML
with SQL, and much of the rest of the book focuses on the process of
developing a web application by taking user surveys and
scenario-role-playing etc, or introducing some of the fundamentals of
XML which are available all over the rest of the bookstore. Excellent
topics, which IMHO do not fall under the topic of 'XML and SQL'. The
book also discusses at length some of the XML features of Oracle and SQL
Server, so I kind of skipped over these sections -- I'm a MySQL fan.
Is it worth reading? The middle few chapters, which deal specifically
with partial decomposition, are interesting. But is it worth $40? Not
unless your employer is paying for it, or if you are looking for a
general book on developing web applications. In fact, it should
probably have been called "Developing Web Applications with XML and
SQL", and if it had then this review might be more forgiving.)
To answer your question, I'm not so sure that I would go about it the
way that you have -- it seems that you are echoing an XML file from SQL
information. There's nothing wrong with this, but simply echoing it to
the screen is probably more useless than just echoing the data in some
meaningful format -- such as a paragraph or a list. HOWEVER, if you
were storing the data in a file, or a string to be sent out as a SOAP
or XML-RPC request, then that makes more sense.
Still, you want some advice? First, your SQL code won't work as it
is -- you've forgotten a mysql_fetch_* function to pull the data from
the $sql_result. So, add to your code:
while ($xml_data_row = mysql_fetch_assoc($sql_result)) {
$xml_document = '
' . $xml_data_row['name'] . '
' . $xml_data_row['address'] . '
' . $xml_data_row['city'] . '
' . $xml_data_row['home'] . '
";
echo $xml_document;
}
This saves you a ton of typing and even makes your code look better.
HTH,
Erik
Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL/XML
On Mon, 1 Apr 2002, Jay Fitzgerald wrote:
> I have created a database called friends with four columns:
> Name / Address / City / State
>
> I also made an XML file to coincide with those variables:
>
>
>
> Friend 1
> 999 Foo Avenue
> Foo Town
> FO
>
>
>
>
> The variables in the XML doc above are already in the database
>
>
> BUT - what I want to do - using PHP and MySQL - is something like this:
>
> $connection = mysql_connect("localhost", "foo", "bar") or die ("Could not
> connect to server.");
> $db = mysql_select_db("friends") or die ("Could not select database.");
> $sql = "select * from friends order by name asc";
> $sql_result = mysql_query($sql, $connection) or die ("Could not execute
> query.");
>
> echo "\n";
> echo "\n\n";
>
> echo "\n";
> echo "\t\n";
> echo "\t\t\n";
>
> echo "\t\t\t";
> echo "$name";
> Can this be done and am I going about it the right way?
It's not clear what would be right in your case because you don't state what you'd
like the code to do for you. But anyway, the extraction from the DB is not complete
(neep more PHP code after you get the result handler). From the way that you have your
SQL query, it seems that you have a table with columns name/address/city/state. If XML
is not your only output format, then that's OK. But if this data will see the light of
day only as XML, then you might as well store it as plain text in a single field. Of
couse you won't be able to order by the name on the SQL side this way :-) But you can
parse the entries on the PHP side after you retrieve them and then sort them.
Just my 2c.
cheers,
--thalis
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] PHP/MySQL/XML
On Monday, April 1, 2002, at 02:33 PM, Jay Fitzgerald wrote: > Can this be done and am I going about it the right way? First, it's a bad idea to cross-post between mailing lists, even if they seem like related topics. Second, what is it you're trying to do? Or what does your application do? Erik Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] PHP/MySQL/XML
I have created a database called friends with four columns:
Name / Address / City / State
I also made an XML file to coincide with those variables:
Friend 1
999 Foo Avenue
Foo Town
FO
The variables in the XML doc above are already in the database
BUT - what I want to do - using PHP and MySQL - is something like this:
$connection = mysql_connect("localhost", "foo", "bar") or die ("Could not
connect to server.");
$db = mysql_select_db("friends") or die ("Could not select database.");
$sql = "select * from friends order by name asc";
$sql_result = mysql_query($sql, $connection) or die ("Could not execute
query.");
echo "\n";
echo "\n\n";
echo "\n";
echo "\t\n";
echo "\t\t\n";
echo "\t\t\t";
echo "$name";
echo "\t\t\t\n";
echo "\t\t\t";
echo "$address";
echo "\t\t\t\n";
echo "\t\t\t";
echo "$city";
echo "\t\t\t\n"
echo "\t\t\t";
echo "$home";
echo "\t\t\t\n";
echo "\t\t";
echo "\t";
echo "";
?>
Can this be done and am I going about it the right way?
Should you have any questions, comments or concerns, feel free to call me
at 318-338-2034.
Thank you for your time,
Jay Fitzgerald, Design Director - CSBW-A, CPW-A, CWD-A, CEMS-A
==
Bayou Internet..(888)
30-BAYOUhttp://www.bayou.com
Mississippi Internet...(800)
MISSISSIPPI...http://www.mississippi.net
Vicksburg Online..(800)
MISSISSIPPIhttp://www.vicksburg.com
==
Tel: (318) 338-2034ICQ: 38823829 Fax:
(318) 323-5053
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
