Jump to content

sql and xml


xander85
 Share

Recommended Posts

i am very very new to the whole sql and xml thing, i use dreamhost and have got a sql database for my music library, i can export the sql table to a xml file and format it using xsl no problem at all, the problem im having is automating it so i dont have to manuall export it to a xml file, just have it all running live? it must be possible however i do not know how to...any help will be much appreciated...thanx in advance

Link to comment
Share on other sites

Tricky...How exactly do you export it? By a control panel or something? If so, I'm afraid you can't automate the process, as the panel is probably not under your direct control (I mean it's source code... you can't tweak it's code).Maybe if you could install something like Query2XML, you could generate the XML from the DB in PHP and possibly even transform it there while you're at it.If not, you'll have to either use the DB to generate the final output directly, or you'll generate the XML file "manually" with PHP and then do whatever you want with it.

Link to comment
Share on other sites

yeah it uses a phpMyAdmin control panel. i can export the DB manually using the control panel into XML CSV PDF etc... but its a bit of a pain really (bareable but annoying) if there is a way to do it via php (wich i know sfa about) to grab the DB table and create an xml file that would be awesome

Link to comment
Share on other sites

That's just my point. There isn't one. You'll have to generate it yourself with PHP's MySQL extension. Some PEAR extensions like the one above are pre made APIs that do just that for you. If you can't install them, you'll have to create your own.

Link to comment
Share on other sites

Well, you could have a PHP page that creates a new XML file, reads from the mySQL database and writes the relevant data to the file...

Link to comment
Share on other sites

That's just my point. There isn't one. You'll have to generate it yourself with PHP's MySQL extension. Some PEAR extensions like the one above are pre made APIs that do just that for you. If you can't install them, you'll have to create your own.
haha i dont know how to do that, so it looks like im stuck doing it the long way, but its alot quicker than the way i was doing it earliercheers all
Link to comment
Share on other sites

that will work? like i said im a complete n00b, ie i started 3days ago hahahahow do i do it?
What he says IS the long way. And going thru it is not at all easy. I suggest you contact your host, asking them to install Query2XML or SQL2XML. Both are good for the job and have documentation available.If you still want the long way... read the PHP DOM tutorial and don't forget to check out the DOM reference at php.net. A good understand of the MySQL API or better yet- the MySQLi API is also needed. This all requires PHP5. Don't tell me you have 4. You'll only be making things harder then they are already.
Link to comment
Share on other sites

Yes, XML in PHP 4 is really inefficient and irritating. if you do have PHP 4 it will be faster to bug your hosts until they upgrade then to try doing your conversion in the old version :) .But I would say it is not too unfeasible using PHP 5. Good luck! :)

Link to comment
Share on other sites

i appreciate every1's input, i know it must bug ppl when n00bs ask soo many questions, haha but how else does one learn.it all does sound very hard. ive contacted my host, we will see what happens. in the mean time, im gonna probe your minds again in the xml forum :)cheers and thanks again

Link to comment
Share on other sites

  • 2 weeks later...

ok i know this is somthing ive been buggin you all with before. i have upgraded my PHP on the server to PHP5i have downloaded the Query2XML and DB-1.7.12 Packages (it said on the Query2XML page DB was required??)ok i do not know what to do next, how do i install it? is it just a matter of uploading it via FTP or what?i have figured out whay my sql connection/login is .... i think???this is still in regards to loading from a sql data base to an xml document for live updatingi dont really understand the previous posts from boenrobot, i do appreciate your help VERY MUCH!!! but i am very very new to this, with no education in the area what so ever

Link to comment
Share on other sites

Oh boy... without a PEAR installer, it would be quite some trickery.First stop, I suggest using MDB2 instead of DB, mostly because DB is superseded and they reccomend MDB2 instead (honestly said, I didn't know that until today).Extract the following files from Query2XML so that they follow this data structure:

/XML/Query2XML/Callback.php/XML/Query2XML/ISO9075Mapper.php/XML/Query2XML.php

And extract the following files from MDB2 so that they follow this structure:

/MDB2.php/MDB2/LOB.php/MDB2/Iterator.php/MDB2/Extended.php/MDB2/Date.php/MDB2/mysql.php/MDB2/Datatype/Common.php/MDB2/Datatype/mysql.php/MDB2/Function/Common.php/MDB2/Function/mysql.php/MDB2/Manager/Common.php/MDB2/Manager/mysql.php/MDB2/Native/Common.php/MDB2/Native/mysql.php/MDB2/Reverse/Common.php/MDB2/Reverse/mysql.php

I'm not sure if the mysql.php files (a.k.a. drivers) are required. I think they were needed only for the MySQL only features, if you use any. Try without them. If you need them... I think you'll need to use another server, when you DO have access to the PEAR installer and install the drivers with the command

pear install MDB2#mysql

and then copy the mysql.php files to the server, arranging them in the manner above.After you install that, MDB2's excellent documentation will guide you thru.

Link to comment
Share on other sites

I'm not sure if it's a must.The PEAR installer is an executable file in PHP's directory (called pear.bat in Windows) that lets you install extensions written in PHP. It has certain built-in features (a sort of "framework") which is why there's a need for an executable to begin with. Also, this executable eases the things as you can just type in a few words and be over with it.Do you have PHP on your own computer? On your own server? And do you use Windows on that machine? If so, go to Start > Run and type

cmd

in the text box. A new (black) window should open. Now, go to PHP's folder.If you've worked with DOS, you should know how. If not... first type the drive you installed PHP on. Then type

cd "path/to/the/folder/in/which/php/is/installed"

For example, if PHP is installed in

D:\PHP\

type

d:

and then

cd php

Once in that folder, type

go-pear

to start the go-pear.bat file. The file will ask you some stuff. Just press ENTER all the way if you're not sure.Once that's over with, you'll see a new executable file - pear.bat. That's the so called "PEAR installer". It makes it easy to install extensions made for it. It could actually directly download files from pear.php.net and install them right away. While you're still in the command promt (the black window) and in PHP's folder, type

pear install mdb2#mysql

and you'll see how MDB2 and the MySQL driver are downloaded and installed. Once that is completed, you can use it, as described in the documentation.Since hosts don't allow you to use that executable, you're left with manually extracting the files in order.

Link to comment
Share on other sites

ok i extracted the files into the folders like you saidthe dir is like this

x:/acousticmushroom.com/library/script....../script/MDB2.php.../script/MDB2/Date.php.../script/MDB2/Extended.php.../script/MDB2/Iterator.php.../script/MDB2/LOG.php.../script/MDB2/Datatype/Common.php.../script/MDB2/Function/Common.php.../script/MDB2/Manager/Common.php.../script/MDB2/Native/Common.php.../script/MDB2/Reverse/Common.php.../script/XML/Query2XML.php.../script/XML/Query2XML/Callback.php.../script/XML/Query2XML/ISO907Mapper.php

the mysql.php files were not in the packagenow i tried to follow the pear install thing but got totally losttried the thing in cmd, but it didnt work? there is a file from the package downloaded from the PEAR site called INSTALL could it be that?i am now more lost than i was before haha

Link to comment
Share on other sites

After some searching on the PEAR site for "MDB2 driver" I think the drivers are just downloaded separatly. You can see the MySQL drivers as well as the MySQLi drivers from there.

Link to comment
Share on other sites

ok i downloaded the Drivers and put them in the directories like you had listed previously.I do not have PHP installed on the PC, so i gues i can't us PEAR installer.I upgraded my domain to PHP 5do i now have all the components? how do i get it to work?once again you have been an absolute champ in your assistance, even tho it hasnt gone far yet, i bet it will :) cheers

Link to comment
Share on other sites

OK. Let's review:PHP5 - checkMDB2 - checkQuery2XML - checkproper folder structure - almost checkThe way I see it, you're keeping your scripts in a "scripts" folder. You should either put them in the document root, or somehow adjust the include_path variable of php.ini to include that folder too. The later could be troublesome to do.Also, it seems you may need the PEAR core after all. MDB2 depends on it (and when I looked at the source file, it seems it actually requires it). You'll notice the pear.bat in the package too. Unless you adjust your include_path, you'll also have to place that into the document root.So, if you want to keep all that in the scripts folder, your PHP file would probably looks something like this, provided your host is on UNIX/Linux:

<?phpini_set('include_path',ini_get('include_path') . ';' . getcwd() . '/scripts');include 'XML/Query2XML.php';//code for converting SQL2XML?>

P.S. If you could somehow easily install and use the PEAR installer on the remote machine, you'll have been done by now. A simple

pear install XML_Query2XML

would have installed the package as well as all packages it depends on.

Link to comment
Share on other sites

ok i have the pear core packaged in the root directory, ill take that option as it will be the easiest.... i guess lolwhat do i need from PEAR core?package contains alot of php files but no *.batim guessing once its all set up correct its just a matter or creating a php file to load the xml and then use xsl(t) to transform it? similar to what i have already, just automoated from the mySQL server?

Link to comment
Share on other sites

Well, I think you need the PEAR.php file only. Just in case, also copy the whole PEAR and OS folders as they are (in the document root). The PEAR installer is in the scripts folder, but you won't be able to use it unfortunatly.If you want, you could keep all of those extensions in another folder. It would be more neat that way.Something like what I wrote above is the only extra thing that needs to be done.

Link to comment
Share on other sites

ok sorry if i seem a little n00bish here (wich i am) i have all the relevant files in the doc root, what do i do now? how do i get it to connect to the sql server? is there a line in the query2xml file(s) i have to edit to connect? can i load it using multiple xsl styles as its essentially xml i want to display???

Link to comment
Share on other sites

The Query2XML site contains an official tutorial including API documentation to which you can refer to for details. Judging from what's written there, the follow code should probably be what you need, with some adjustments of course:

<?phprequire_once '/XML/Query2XML.php';require_once 'MDB2.php';/* Replace "user:pass" with your actual database username and password and replace "DB" with the actual database you'll be quering */$query2xml = XML_Query2XML::factory(MDB2::factory('mysql://user:pass@localhost/DB'));/* Replace the value of the following variable with the actual DB query you want or leave it like that to map the whole DB */$query = false;/////////////////////////////////////////The following array is where you forge the output XML file. Adjust those options depending on your DB layout//The sample here is directly copied from the documentation///////////////////////////////////////$options = array(		'rootTag' => 'music_library',		'rowTag' => 'artist',		'idColumn' => 'artistid',		'elements' => array(			'artistid',			'name',			'birth_year',			'birth_place',			'genre',			'albums' => array(			  'rootTag' => 'albums',			  'rowTag' => 'album',			  'idColumn' => 'albumid',			  'elements' => array('albumid', 'title', 'published_year')			)		)$xml = $query2xml->getXML($query, $options);/* This is the time to make certain adjustments to the output XML if you want to.If you have the XSL extension enabled (search php.net for "XSL"), you could perform the XSLT transformation on the server.If not, you might want to uncomment the following line to add a processing instruction in the XML, so that the XSLT transformation could be done on the client side (Changing "test.xsl" to the actual XSLT file you're using of course). *///$xml->appendChild($xml->createProcessingInstruction('xml-stylesheet', 'href="test.xsl" type="text/xsl"'));//Serve the content as an XML document. You might want to remove this if you use the XSL extension on the server to generate HTML outputheader('Content-Type: application/xml');//Display the result to the clientecho $xml->saveXML();?>

Read carefully the comments I've lefted as well as the documentation to understand the $options array better.

Link to comment
Share on other sites

ok cool thats awsome, just on the options array thing, ive skimmed thru the tute and i think i have it ritesay the xml is

</library>    <music>        <artist>streophonics</artist>        <album>pull the pin</album>        <source>esk</source>        <kind>mp3 audio</kind>        <Added>2005-08-25</Added>    </music>  <!-- Table stats -->    <stats>        <artists>1663</artists>        <albums>1,837</albums>        <songs>23,774</songs>        <updated>2007-08-25</updated>        <size>128.51 Gigabytes</size>        <playback>64 days, 20 hours, 52 minutes & 21 seconds</playback>        <mp3>1,268</mp3>        <aac>420</aac>        <lossless>0</lossless>    </stats></library>

would the PHP options array look like this?

$options = array(        'rootTag' => 'library',        'rowTag' => 'music',        /*'idColumn' => 'artistid',*/        'elements' => array(            'artist',            'album',            'source',            'kind',            'Added',            'stats' => array(              'rootTag' => 'library',              'rowTag' => 'stats',              /*'idColumn' => 'artistid',*/              'elements' => array('artists', 'albums', 'songs', 'updated', 'size', 'playback', 'mp3', 'aac', 'lossless')            )        )

the idColumn is 'commented' as i do not have an ID column or primary key so to speak on any of the tables in the DB - libraryor is it just a default value of 1,2,3,4 etc for each row, its all uploading via ftp now so i hope it works!!

Link to comment
Share on other sites

You need one more closing bracket right at the end of the $options array, you have four layers of arrays but only three closing brackets at the end.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...