SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: XML from mysql

  1. #1
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    XML from mysql

    Hi Guys,

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

    code:

    PHP 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\">\n";
     
     
    // 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\">\n";  
        
        
    //} else {
        
          
        
    $stringData .= "  <image>\n";
          
    $stringData .= "   <date>$imgDate</date>\n";
          
    $stringData .= "   <title>$imgThDe</title>\n";
          
    $stringData .= "   <desc>$imgDesc</desc>\n";
          
    $stringData .= "   <thumb>$imgThum</thumb>\n";
          
    $stringData .= "   <img>$imgFull</img>\n";  
        
    $stringData .= "  </image>\n";  
        
    $stringData .= "</category>\n";
        
    //}
     
     
    }
     
    //fwrite($fh, $stringData);
     
     
    $stringData .= "</gallery>\n";
     
    fwrite($fh$stringData);
     
     
    fclose($fh);
     
    ?>
    This works (kind of) it generates the xml like:

    PHP Code:
    <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:

    PHP Code:
    <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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>

  3. #3
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah i see what you mean, my loop inside my loop is only getting 1 result set, i ammended the code like:

    PHP 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\">\n";
     
     
    // Loop
     
    while ($a mysql_fetch_array($r))
     {
     
        
    $catName $a['category_name'];
        
        
    // Start the xml file
        
    $stringData .= "<category name=\"$catName\">\n";  
      
        
    // 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 .= "  <image>\n";
          
    $stringData .= "   <date>$imgDate</date>\n";
          
    $stringData .= "   <title>$imgThDe</title>\n";
          
    $stringData .= "   <desc>$imgDesc</desc>\n";
          
    $stringData .= "   <thumb>$imgThum</thumb>\n";
          
    $stringData .= "   <img>$imgFull</img>\n";  
        
    $stringData .= "  </image>\n"
        }
        
    // End while loop
         
        
    $stringData .= "</category>\n";
     
     }
     
     
    // Write the xml to file
     
    $stringData .= "</gallery>\n";
     
    fwrite($fh$stringData);
     
    fclose($fh);
     
    ?>
    This produces:

    PHP Code:
    <gallery title="Miracle Lift - Before And After Photos" thumbDir="./images/thumbs/" imageDir="./images/" random="true">
    <
    category name="Before Miracle Lift">
      <
    image>
       <
    date></date>
       <
    title></title>
       <
    desc></desc>
       <
    thumb></thumb>
       <
    img></img>
      </
    image>
      <
    image>
       <
    date></date>
       <
    title></title>
       <
    desc></desc>
       <
    thumb></thumb>
       <
    img></img>
      </
    image>
    </
    category>
    <
    category name="After Miracle Lift">
      <
    image>
       <
    date></date>
       <
    title></title>
       <
    desc></desc>
       <
    thumb></thumb>
       <
    img></img>
      </
    image>
    </
    category>
    </
    gallery
    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

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I highly recommend you develop code with error_reporting set high.

    PHP Code:
    //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)

  5. #5
    SitePoint Evangelist
    Join Date
    Dec 2006
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thnaks mate as a rule of thuimb ill do that from now on, 99&#37; of the time it's something really simple with me lol

    cheers

    Grahanm

  6. #6
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Have you sorted the problem then?

    If not, look at your while loop - it should read:
    PHP Code:
    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 :P
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  7. #7
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,200
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jake Arkinstall View Post
    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 :P
    I think simple solution would be using htmlentities in tag values like:
    PHP Code:
    $xml .= "<some_tag>".htmlentities($row['field'])."</some_tag>".PHP_EOL
    instead of striking head with XML library.
    or am i missing something?

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2009
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PHPycho View Post
    I think simple solution would be using htmlentities in tag values like:
    PHP Code:
    $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

    Anyway the link is..
    http://www.phpbuilder.com/board/show...9#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 ) ).
    http://en.wikipedia.org/wiki/ISO/IEC_8859-1

    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •