Jump to content


Photo

XML with mysql & php


  • Please log in to reply
2 replies to this topic

#1 theycallmepj

theycallmepj
  • Members
  • PipPip
  • Member
  • 18 posts
  • LocationMontrose, PA

Posted 27 February 2006 - 09:53 PM

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.
Thanks, Paul Travis
THEYCALLMEPJ.com
MASD.info

#2 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 27 February 2006 - 10:00 PM

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.
follow me on twitter @PHPsycho

#3 spelltwister

spelltwister
  • Members
  • PipPipPip
  • Advanced Member
  • 41 posts

Posted 01 March 2006 - 07:34 PM

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! :-)

$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);

Mike




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users