Jump to content

XML with mysql & php


theycallmepj

Recommended Posts

I know this is possible, I would like to know if I could take a php script that would pull data off our mysql database, and turn it into some form of xml. Our website has several news sections on it and we would like to turn them into xml files, for the purpose of letting other websites put our news on their site, or many other programs use xml.

Our news is set up in two database tables, the first table contains all the news which has several fields, one field is used for the "news id" (each news item has its own id number), there is a field that contains the title of the news item, there is a field that contains the body of the news item, there is a field that contains the category of the news item (we have several places on our website that have there own separate news, the categories are numbered), we also have two fields for the date the news item was entered, and the date the news item expires.

The second table contains all the news categorys which has a field for the category number (same number used in the above table), it has a field for a short name for the news category, a field for the long name, and a field for a description.
( Long Name = Montrose Area News
Short Name = mont_news )

What we want to be able to do is make several xml files, one for each news category, and have only current news on them, we don't want old news, that would be determined by the expiration date on each news item.

Do you know of any websites that could help me do this? It would be greatly appreciated if someone here would help a little.
Link to comment
Share on other sites

this is just an issue of transfering the data into a file.

all you will need is to separate the sql queries to the sections you want, parse the returned info and write to a file which you save as .xml.

have a look at the file functions in php. If you need any further help just come back on here.
Link to comment
Share on other sites

Here's some code that I use to make XML files out of sql queries. The only thing to be careful of is that if you don't choose a name different from what's on the server it will add it to the end of the file that's already there (really annoying). I hope this helps! :-)

[code]$data = "<?xml version=\"1.0\"?>\n";  
$data .= "<units>\n";

for($i=0;$i<sizeof($nationArray);$i++){
//Gather all units for each nation this turn
$query_units = mysql_query("select * from units where nation = '$nationArray[$i]' and turn = '$turn' AND game='$game'");

for($j=0;$j<mysql_num_rows($query_units);$j++){
$array_units = mysql_fetch_array($query_units);
$unittype = $array_units['type'];
$location = $array_units['location'];
$number = $array_units['number'];

if($unittype=="general" && $number==0){
  $number = rand(1,10);
}

$data .="\t<".$unittype.">\n";
$data .="\t\t<owner>".$nationArray[$i]."</owner>\n";
$data .="\t\t<location>".$location."</location>\n";
$data .="\t\t<number>".$number."</number>\n";
$data .="\t</".$unittype.">\n";

}

}

$data .="</units>";
$file = "xml_files/".$game."u.xml";    
if (!$file_handle = fopen($file,"a")) { echo "Cannot open file"; }  
if (!fwrite($file_handle, $data)) { echo "Cannot write to file"; }  
fclose($file_handle);[/code]

Mike
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.