XML from mysql

Hi Guys,

What i have done is generate an xml file that feeds into my flash gallery.

code:


<?php
 // Include the database connection
 include("admin/inc/db-connection.php");
 $q = "SELECT * FROM `mftl_categories`";
 $r = mysql_query($q); 
 $file = "gallery.xml";
 $fh = fopen($file, 'w');
 $stringData = "<gallery title=\\"Miracle Lift - Before And After Photos\\" thumbDir=\\"./images/thumbs/\\" imageDir=\\"./images/\\" random=\\"true\\">\
";
 
 // Loop
 while ($a = mysql_fetch_array($r))
 {
 
    $catName = $a['category_name'];
  
    // Select the images...
    $qI = "SELECT * FROM `mftl_images` WHERE `image_category`='$catName'";
    $rI = mysql_query($qI);
    $aI = mysql_fetch_array($rI);
  
    // Local vars
    $imgDate = $aI['date'];
    $imgCate = $aI['image_category'];
    $imgFull = $aI['image_fullsize']; 
    $imgThum = $aI['image_thumbnail']; 
    $imgThDe = $aI['image_thumbnail_description']; 
    $imgDesc = $aI['image_description']; 
  
    //if ($catName == $imgCate)
    //{
    //if (mysql_num_rows($rI) > 0)
    //{
    
    $stringData .= "<category name=\\"$catName\\">\
";  
    
    //} else {
    
      
    $stringData .= "  <image>\
";
	  $stringData .= "   <date>$imgDate</date>\
";
	  $stringData .= "   <title>$imgThDe</title>\
";
	  $stringData .= "   <desc>$imgDesc</desc>\
";
	  $stringData .= "   <thumb>$imgThum</thumb>\
";
	  $stringData .= "   <img>$imgFull</img>\
";  
    $stringData .= "  </image>\
";  
    $stringData .= "</category>\
";
    //}
 
 }
 //fwrite($fh, $stringData);
 
 $stringData .= "</gallery>\
";
 fwrite($fh, $stringData);
 
 fclose($fh);
 
?>

This works (kind of) it generates the xml like:


<gallery title="Miracle Lift - Before And After Photos" thumbDir="./images/thumbs/" imageDir="./images/" random="true"> 
<category name="Before Miracle Lift"> 
  <image> 
   <date>2010-01-02 15:15:54</date> 
   <title>Esther before</title> 
   <desc>stunner!!!</desc> 
   <thumb>thumb-esther_before_(front).jpg</thumb> 
   <img>esther_before_(front).jpg</img> 
  </image> 
</category> 
<category name="After Miracle Lift"> 
  <image> 
   <date>2010-01-02 15:17:48</date> 
   <title>Susan After!!</title> 
   <desc>still stunner!!</desc> 
   <thumb>thumb-sus_4_days_after_(front).jpg</thumb> 
   <img>sus_4_days_after_(front).jpg</img> 
  </image> 
</category> 
</gallery>  

There is actually 3 images in the database 2 under “Before Miracle Lift” and 1 “After Miracle Lift” what i am having trouble with is grouping all the “After images” under the after category and the same with the before ones, the xml should look like:


<gallery title="Miracle Lift - Before And After Photos" thumbDir="./images/thumbs/" imageDir="./images/" random="true"> 
<category name="Before Miracle Lift"> 
  <image> 
   <date>2010-01-02 15:15:54</date> 
   <title>Esther before</title> 
   <desc>stunner!!!</desc> 
   <thumb>thumb-esther_before_(front).jpg</thumb> 
   <img>esther_before_(front).jpg</img> 
  </image> 
  <image> 
   <date>2010-01-02 15:15:54</date> 
   <title>April before</title> 
   <desc>stunner!!!</desc> 
   <thumb>thumb-aprilr_before_(front).jpg</thumb> 
   <img>april_before_(front).jpg</img> 
  </image> 
</category> 
<category name="After Miracle Lift"> 
  <image> 
   <date>2010-01-02 15:17:48</date> 
   <title>Susan After!!</title> 
   <desc>still stunner!!</desc> 
   <thumb>thumb-sus_4_days_after_(front).jpg</thumb> 
   <img>sus_4_days_after_(front).jpg</img> 
  </image> 
</category> 
</gallery>  

All grouped together, i can’t for the life of me think of a way to code in so it works like above any help would be appreciated.

thanks guys

Graham

Use an inner loop. Your query inside the loop produces multiple rows, yet you only fetch one. Use a while loop to fetch them all.

<category>

while
<image>

</image>
endwhile

</category>

ah i see what you mean, my loop inside my loop is only getting 1 result set, i ammended the code like:


&lt;?php
 // Include the database connection
 include("admin/inc/db-connection.php");
 $q = "SELECT * FROM `mftl_categories`";
 $r = mysql_query($q); 
 $file = "gallery.xml";
 $fh = fopen($file, 'w');
 $stringData = "&lt;gallery title=\\"Miracle Lift - Before And After Photos\\" thumbDir=\\"./images/thumbs/\\" imageDir=\\"./images/\\" random=\\"true\\"&gt;\
";
 
 // Loop
 while ($a = mysql_fetch_array($r))
 {
 
    $catName = $a['category_name'];
    
    // Start the xml file
    $stringData .= "&lt;category name=\\"$catName\\"&gt;\
";  
  
    // Select the images...
    $qI = "SELECT * FROM `mftl_images` WHERE `image_category`='$catName'";
    $rI = mysql_query($qI) or die (mysql_error());
     
    // Start while loop  
    while (mysql_fetch_array($rI))
    {  
    
    // Local vars
    $imgDate = $aI['date'];
    $imgCate = $aI['image_category'];
    $imgFull = $aI['image_fullsize']; 
    $imgThum = $aI['image_thumbnail']; 
    $imgThDe = $aI['image_thumbnail_description']; 
    $imgDesc = $aI['image_description']; 
    
    $stringData .= "  &lt;image&gt;\
";
	  $stringData .= "   &lt;date&gt;$imgDate&lt;/date&gt;\
";
	  $stringData .= "   &lt;title&gt;$imgThDe&lt;/title&gt;\
";
	  $stringData .= "   &lt;desc&gt;$imgDesc&lt;/desc&gt;\
";
	  $stringData .= "   &lt;thumb&gt;$imgThum&lt;/thumb&gt;\
";
	  $stringData .= "   &lt;img&gt;$imgFull&lt;/img&gt;\
";  
    $stringData .= "  &lt;/image&gt;\
"; 
    }
    // End while loop
     
    $stringData .= "&lt;/category&gt;\
";
 
 }
 
 // Write the xml to file
 $stringData .= "&lt;/gallery&gt;\
";
 fwrite($fh, $stringData);
 fclose($fh);
 
?&gt;

This produces:


&lt;gallery title="Miracle Lift - Before And After Photos" thumbDir="./images/thumbs/" imageDir="./images/" random="true"&gt;
&lt;category name="Before Miracle Lift"&gt;
  &lt;image&gt;
   &lt;date&gt;&lt;/date&gt;
   &lt;title&gt;&lt;/title&gt;
   &lt;desc&gt;&lt;/desc&gt;
   &lt;thumb&gt;&lt;/thumb&gt;
   &lt;img&gt;&lt;/img&gt;
  &lt;/image&gt;
  &lt;image&gt;
   &lt;date&gt;&lt;/date&gt;
   &lt;title&gt;&lt;/title&gt;
   &lt;desc&gt;&lt;/desc&gt;
   &lt;thumb&gt;&lt;/thumb&gt;
   &lt;img&gt;&lt;/img&gt;
  &lt;/image&gt;
&lt;/category&gt;
&lt;category name="After Miracle Lift"&gt;
  &lt;image&gt;
   &lt;date&gt;&lt;/date&gt;
   &lt;title&gt;&lt;/title&gt;
   &lt;desc&gt;&lt;/desc&gt;
   &lt;thumb&gt;&lt;/thumb&gt;
   &lt;img&gt;&lt;/img&gt;
  &lt;/image&gt;
&lt;/category&gt;
&lt;/gallery&gt;

This is exacly what i am after, but the values are missing, they were there before i moved some code, it’s probably something i have overlooked lol

thanks mate

Graham

I highly recommend you develop code with error_reporting set high.


//top of scripts
ini_set('display_errors', 1);
error_reporting(E_ALL);

php will complain loudly when you try to use undefined variables(generally typos or silly mistakes)

Thnaks mate as a rule of thuimb ill do that from now on, 99% of the time it’s something really simple with me lol

cheers

Grahanm

Have you sorted the problem then?

If not, look at your while loop - it should read:

while ($aI = mysql_fetch_array($rI)){...

If you go into further XML development, I’d recommend using an XML library to generate your XML. It will certainly help when it comes to things like escaping your names (e.g. what if $CatName had a double-quote or two inside? that would invalidate your XML) and it also prevents silly mistakes caused by a lack of either sleep or caffeine :stuck_out_tongue:

I think simple solution would be using htmlentities in tag values like:

$xml .= "<some_tag>".htmlentities($row['field'])."</some_tag>".PHP_EOL;

instead of striking head with XML library.
or am i missing something?

I just wrote a response to a very similar topic under my other alias with coding a DOM example etc. I have dealt with a lot of flawed XML feeds, and not even my ones, other companies developers screw them up and the people they work for chase me to tell them what their developers have buggered up. After a few phone calls on days off it gets a tad annoying. It is something worthwhile trying to break test yourself if you are inquisitive, unfortunately a lot of developers cut corners go “it works on my machine using abcde” run off and leave someone else to pick up the pieces.

Sorry if I seem narky but data exchange is a very serious ball game :slight_smile:

Anyway the link is…
http://www.phpbuilder.com/board/showthread.php?p=10938419#post10938419

When dealing with XML you want a halt on unknowns and log fatals on your end not only on the persons reader

For example
€(Euro) will not get translated and cause all readers in Latin 1 mode to fail. Windows uses 1252, anything copied from word will also possibly fail as it replaces some Latin 1 stuff with prettier Windows-1252 stuff. It is a minefield, especially in Latin 1 and not utf-8. As far as I am concerned every Latin 1 install is a problem waiting to happen( it misses a few characters here and there in Western Europe as well just to make it fun hence ISO-8859-15 and ISO-8859-14( just for those difficult welsh ) ).

If you find yourself hacking around something in XML you really are doing it wrong and it can just cascade( html entities is a hack, it does not know right from wrong and will just carry on ). People write iconv/ xml libraries etc for a very good reason as it is stuff that can make most people cry when it starts getting dirty. It is not simple but wrappers can be built around the dom/iconv etc to make it simple and relatively pain free, just takes a bit more brains now.

The joy of UTF-8 in browsers is they will translate from whatever source encoding is pasted into UTF-8 silently saving a whole load of problems( such a people copying from word documents).

Anyway enough XML stuff for tonight. I think 4 hours is enough and I’ll end up cranky tomorrow still thinking about it. I hurt, character encodings are like a big swallowing hole :slight_smile: