Solved

Building XML with PHP and MySQL

Posted on 2009-04-02
3
416 Views
Last Modified: 2013-12-12
I'm in an utter mind-melt and having started covering the same ground of problems I'm hoping you guys (from whom I've already learnt a lot) can help me.

I'm trying to get a PHP to build a section of XML from a MySQL table.

The end-result should look something like this:-

---------------------------------------------------------------------------------------------------
Code:
   <Monkey_Muffin>
      <R0>
         <C0>
             <section width="200" height="75" lineThickness="0" lineColour="0x333333"/>
            <text>
               <txt1 />
               <txt2 text="Monkeys" y="2" font="Myriad Pro" fontSize="12" fontColour="0xFFFFFF" align="left" />
               <txt3 text="I like them" y="15" />
            </text>
            <image>
            </image>
         </C0>
         <C1>
            <section width="75" height="75" lineThickness="0" lineColour="0x333333" lineAlpha="100" />
            <text>
            </text>
            <image src="Monkey_Muffin.jpg" fit="false" width="70" height="70" x="2.5" y="2.5" alpha="100" />
         </C1>  
      </R0>
   </Monkey_Muffin>
---------------------------------------------------------------------------------------------------

The table in the MySQL DB contains:-
---------------------------------------------------------------------------------------------------
Code:
Field   Type   Null   Default
name   varchar(20)   Yes  
id   int(11)   Yes  
row   int(11)   Yes  
section   int(11)   Yes  
width   int(255)   Yes  
height   int(255)   Yes  
lineThickness   int(255)   Yes  
lineColour   varchar(255)   Yes  
lineAlpha   int(255)   Yes  
---------------------------------------------------------------------------------------------------

and a few other bits and pieces...

Baring in mind that there could be multiple rows and multiple sections/columns within rows

What I'm trying to get the PHP to do is start

    * ID1 - print the name
    * In ID 1 - where row 1 (in XML terms here, not SQL), print all buttons rows (in SQL terms here)
    * then move onto row 2 until no further rows
    * then repeat process for next ID


In the format outlined above. I have got this working for simpler examples, and I'm thus trying to use code from those... but it's much more complicated. I'm currently trying:-

Code:
---------------------------------------------------------------------------------------------------
<?php
&#160; &#160; &#160; &#160; $dbh = mysql_connect("xxx", "xxx", "xxx");
&#160; &#160; &#160; &#160; mysql_select_db("xxx");

&#160; &#160; &#160; &#160; $query = "SELECT * FROM table WHERE id = 1"; // get cycle up on this
     
&#160; &#160; &#160; &#160; $result = mysql_query($query, $dbh);

&#160; &#160; &#160; &#160; $fields = mysql_num_fields($result);

      echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";

         while ($row=mysql_fetch_array($result)) {
               for ($i=0; $i<$fields; $i++) {
                     $name=mysql_field_name($result, $i);  
                           if ($row['id']= 1) { // get this number to come from earlier variable
                              if ($row['row']= 1) {   // get this number to cycle upwards until done                          
                                 if ($row['section']= 1) {
                                 echo  "<$name=\"$row[$name]\" />\n"; }
                                 else { echo "can put a stop on this for end of row"; }
                              }
                              else { echo "row not 0"; }
                           }
                           else {
                              echo "not showing";
                           }

               }
               echo "/>\n<";
         }


?>
---------------------------------------------------------------------------------------------------

Admittedly with hard-coded ID etc. atm just to see if I can get it working. I can get bits working with bits of info, but I think I'm on completely the wrong road.

Any directional help would be really great.
0
Comment
Question by:mediadonkey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Expert Comment

by:adrian_brooks
ID: 24055956
Have you considered the option of having MySQL itself export your data as XML?

A good example of this would be this mysql command line;

./mysql -ujon test --xml -e 'SELECT * FROM t1' > t1.xml

What this will do is take the result of the SELECT query and route the results to a file called t1.xml

Hope this offered some help. :)

~A~
0
 
LVL 1

Accepted Solution

by:
tyfius earned 500 total points
ID: 24084692
You can also use the built-in SimpleXML functionality to create this. It will take all the required nested creation out of your hands.

I added a small snippet which should get you started. It's basically just the correct creation of the array you should worry about.
<?php
function toxml($data, $root = 'data', $xml = NULL) {
  // Turn off compatibility mode as SimpleXML throws a wobbly if you don't.
  if (ini_get('zend.ze1_compatibility_mode') == 1) {
    ini_set('zend.ze2_compatibility_mode', 0);
  }
 
  if ($xml == NULL) {
    $xml = simplexml_load_string('<?xml version="1.0" encoding="UTF-8"?><'. $root .' />');
    $xml->addAttribute('xmlns', 'http://www.example.com/xmlns');
  }
 
  ksort($data);
 
  foreach ($data as $key => $value) {
    // No numeric keys in our XML please!
    if (is_numeric($key)) {
      $key = 'unknownNode_'. (string)$key;
    }
 
    // Replace any non alpha numeric character.
    $key = preg_replace('/[^a-z]/i', '', $key);
 
    if (is_array($value)) {
      $node = $xml->addChild($key);
      toxml($value, $root, $node);
    }
    else {
      if (!empty($value)) {
        $xml->addChild($key, $value);
      }
    }
  }
 
  return $xml->asXML();
}
 
function foo() {
  $result = mysql_query("");
  $rows = array();
  while ($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
  }
  
  // Return XML content
  return toxml($rows, 'root');
}

Open in new window

0
 

Author Closing Comment

by:mediadonkey
ID: 31565706
Fixed another way, but this will equally work.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to refresh a page from a sub domain in ajax ? 34 43
JQuery Search Filter 2 68
MSSQL - Lock Row from reading by other programs 9 57
Special characters in a TCPDF 4 25
Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question