Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
XML Connection to MySQL
- Thread starter raymundo
- Start date
XML is not a server-side scripting method so much as a data encapsulation format. There still needs to be a mechanism to query the database, and format the output for XML. The two best choices for this are probably PHP or Perl. Each of these scripting languages has MySQL connectivity, and has XML parsing capability.
Thus instead of having MySQL--to--XML, it is MySQL--to--PHP/Perl--to--XML.
Thus instead of having MySQL--to--XML, it is MySQL--to--PHP/Perl--to--XML.
- Thread starter
- #3
- Thread starter
- #5
You might be making this more complicated than you need. How about describing your situation to me and I'll see if I can't save you some time.
Is the XML document meant to be viewed in a browser?
Will there need to be any INPUT from XML into the database, or simply output from the database to XML?(that will make it a lot easier)
Is the XML document meant to be viewed in a browser?
Will there need to be any INPUT from XML into the database, or simply output from the database to XML?(that will make it a lot easier)
- Thread starter
- #7
For now we will only be producing a static report that will display information from a database in xml. Not sending information to the database.
Specifically, an employees benefits statement that he can login to and view. No imput from the employee other than a login and pasword for his/her information.
Specifically, an employees benefits statement that he can login to and view. No imput from the employee other than a login and pasword for his/her information.
It's really quite simple:
1. MySQL database connection
$db = mysql_connect("hostname", "user", "password"
; //(substitute with your database hostname, username, and password, keeping quotes)
$query = "SELECT * FROM benefits WHERE employee_id=$employee_id";
$result = mysql_db_query("databasename", $query) OR die(mysql_error());
2. Since you are only calling one record, there is no need to loop the rowsource array
$r = mysql_fetch_array($result)
$field1 = ($r["field1"]);
$field2 = ($r["field2"]);
$field3 = ($r["field3"]);
3. 'Sprinkle' the PHP values inside the XML tags
<field1><? echo $field1 ?></field1>
<field2><? echo $field2 ?></field2>
<field3><? echo $field3 ?></field3>
(You get the idea. If you are displaying more than one record, you enclose Part2 and Part3 inside a while loop for the rowsource array $r)
1. MySQL database connection
$db = mysql_connect("hostname", "user", "password"
; //(substitute with your database hostname, username, and password, keeping quotes)$query = "SELECT * FROM benefits WHERE employee_id=$employee_id";
$result = mysql_db_query("databasename", $query) OR die(mysql_error());
2. Since you are only calling one record, there is no need to loop the rowsource array
$r = mysql_fetch_array($result)
$field1 = ($r["field1"]);
$field2 = ($r["field2"]);
$field3 = ($r["field3"]);
3. 'Sprinkle' the PHP values inside the XML tags
<field1><? echo $field1 ?></field1>
<field2><? echo $field2 ?></field2>
<field3><? echo $field3 ?></field3>
(You get the idea. If you are displaying more than one record, you enclose Part2 and Part3 inside a while loop for the rowsource array $r)
A bit of clarification here. This is not really PHP parsing XML. This is simply PHP parsing a web page to place dynamic data inside. The code above would have to reside inside a file with extension '.php', unless you change your webserver settings so that '.xml' extensions are also parsed for PHP code.
To do more complicated things with XML, though, you should read the documentation about PHP parsing XML directly.
I'm still unsure why you need XML at all to accomplish a simple task like this. Couldn't you just output a web page?
To do more complicated things with XML, though, you should read the documentation about PHP parsing XML directly.
I'm still unsure why you need XML at all to accomplish a simple task like this. Couldn't you just output a web page?
- Thread starter
- #10
XML is a LOT of HYPE! It, like Java, is being touted as a panacea, and is being claimed to be great for things that it will NOT work well with.
I DID write a metadata extraction program that took a complicated denormalized database representing conversion metadata, and outputted this as a normallized XML file. THAT made sense because there was ONE file that could be input to any program capable of reading XML, and loading it in the desired format. Delimitted files would have been complicated(or many would have been needed), and fixed files would be GIGANTIC.
Some companies provide content data as XML so that the normalized data can be extracted and formatted as desired by third parties. THAT makes sense.
DMOZs use doesn't make sense, because it is a simple structure, and the overhead means the XML format is actually 30% OVER what delimited would be!(but parsing is complicated through XML)
The USE for employee info is probably not a great idea. WHY? Because probably NO third parties will use it, and the employee will likely not need XML data. XML display in a browser requires an XSL sheet, AND an XML capable browser(I believe only I.E 5.0 at this point supports it). OH YEAH! You need to use the tabs with no more than ONE value per(XML allows a lot of values, but XSL on IE 5.0 has bugs that cause it to FAIL!)! That makes it even MORE inefficient. Further, client side processing, and possibly server side, will be more CPU intensive!
In short. If you don't need XML for 3rd party usage, and you don't save space in storage/conversion, and it doesn't facilitate needed flat file storage, WHY USE IT?
BTW I first thought about using XML for my metadata program because a customer kept mentioning it, and claimed to be able to use it. When I gave it to them, they said that NONE of their programs were currently XML compatible! I kept it only for the future.
Steve
I DID write a metadata extraction program that took a complicated denormalized database representing conversion metadata, and outputted this as a normallized XML file. THAT made sense because there was ONE file that could be input to any program capable of reading XML, and loading it in the desired format. Delimitted files would have been complicated(or many would have been needed), and fixed files would be GIGANTIC.
Some companies provide content data as XML so that the normalized data can be extracted and formatted as desired by third parties. THAT makes sense.
DMOZs use doesn't make sense, because it is a simple structure, and the overhead means the XML format is actually 30% OVER what delimited would be!(but parsing is complicated through XML)
The USE for employee info is probably not a great idea. WHY? Because probably NO third parties will use it, and the employee will likely not need XML data. XML display in a browser requires an XSL sheet, AND an XML capable browser(I believe only I.E 5.0 at this point supports it). OH YEAH! You need to use the tabs with no more than ONE value per(XML allows a lot of values, but XSL on IE 5.0 has bugs that cause it to FAIL!)! That makes it even MORE inefficient. Further, client side processing, and possibly server side, will be more CPU intensive!
In short. If you don't need XML for 3rd party usage, and you don't save space in storage/conversion, and it doesn't facilitate needed flat file storage, WHY USE IT?
BTW I first thought about using XML for my metadata program because a customer kept mentioning it, and claimed to be able to use it. When I gave it to them, they said that NONE of their programs were currently XML compatible! I kept it only for the future.
Steve
- Thread starter
- #12
- Status
