MySQL / XML Question

Question

jeffc418 0 Junior Poster in Training

15 Years Ago

Hi all! So I've been reading about the "FOR XML AUTO" function which sounds great, but it hasn't been working when I try it in phpmyadmin. I get an error in the area of "FOR XML AUTO", I know SimpleXML is disabled on my package but would that be why it's not working there? Once I get that to work, I need any values with a similar column to combine, such as:

orders cname cadd
1000 Name1 Address1
2000 Name2 Address2
2000 Name3 Address3

I need the output to be:

<orders value="1000">
<cname value="Name1">
<cadd>Address 1</cadd>
</cname>
</orders>

<orders value="2000">
<cname value="Name 2">
<cadd>Address 2</cadd>
</cname>
<cname value="Name3">
<cadd>Address 3</cadd>
</cname>
</orders>

See how the ones with the similar index merge? THANKS SO MUCH :). Any help is appreciated!

mysql php xml

1 Contributor
2 Replies
261 Views
1 Week Discussion Span
Latest Post 15 Years Ago Latest Post by jeffc418

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jeffc418 0 Junior Poster in Training · Answer 1 · 2009-05-04T04:33:42+00:00

So I've been trying really hard to get this to work :D!!! Here's my code, I just keep getting two month tags in one spot and I just can't figure out why...if anyone could help that would amazing. Thanks!

<?php

class XmlWriter {
    var $xml;
    var $indent;
    var $stack = array();
    function XmlWriter($indent = '  ') {
        $this->indent = $indent;
        $this->xml = '<?xml version="1.0" encoding="utf-8"?>'."\n";
    }
    function _indent() {
        for ($i = 0, $j = count($this->stack); $i < $j; $i++) {
            $this->xml .= $this->indent;
        }
    }
    function push($element, $attributes = array()) {
		$this->_indent();
        $this->xml .= '<'.$element;
        foreach ($attributes as $key => $value) {
		    $this->xml .= ' value="'.htmlentities($value).'"';
        }
        $this->xml .= ">\n";
        $this->stack[] = $element;
		
    }
	function pushyear($element, $yearattr = array()) {
	global $year, $array;
		foreach ($yearattr as $chiava) {
		if ($year[$chiava] == 1) {
		$this->_indent();
        $this->xml .= '<'.$element;
        foreach ($yearattr as $key => $value) {
		    $this->xml .= ' value="'.htmlentities($value).'"';
        }
        $this->xml .= ">\n";
        $this->stack[] = $element;
		}
		
		if ($year[$chiava] > 1) {
		$count[$chiava] = $year[$chiava];
		$year[$chiava] = 0;
		$this->_indent();
        $this->xml .= '<'.$element;
        foreach ($yearattr as $key => $value) {
		    $this->xml .= ' value="'.htmlentities($value).'"';
        }
        $this->xml .= ">\n";
        $this->stack[] = $element;
		$remaining = $count[$chiava] - 1;}
		
		}
				
    }
	
	function pushmonth($element, $monthattr = array()) {
	global $month, $array, $years;
	foreach ($years as $y) {
		foreach ($monthattr as $chiava) {
		if ($month[$y][$chiava] == 1) {
		$this->_indent();
        $this->xml .= '<'.$element;
        foreach ($monthattr as $key => $value) {
		    $this->xml .= ' value="'.htmlentities($value).'"';
        }
        $this->xml .= ">\n";
        $this->stack[] = $element;
		}
		
		if ($month[$y][$chiava] > 1) {
		$count[$y][$chiava] = $month[$y][$chiava];
		$month[$y][$chiava] = 0;
		$this->_indent();
        $this->xml .= '<'.$element;
        foreach ($monthattr as $key => $value) {
		    $this->xml .= ' value="'.htmlentities($value).'"';
        }
        $this->xml .= ">\n";
        $this->stack[] = $element;
		$remaining = $count[$y][$chiava] - 1;}
		}
		}
	}	
	
    function element($element, $content, $attributes = array()) {
        $this->_indent();
        $this->xml .= '<'.$element;
        foreach ($attributes as $key => $value) {
            $this->xml .= ' '.$key.'="'.htmlentities($value).'"';
        }
        $this->xml .= '>'.htmlentities($content).'</'.$element.'>'."\n";
    }
    function emptyelement($element, $attributes = array()) {
        $this->_indent();
        $this->xml .= '<'.$element;
        foreach ($attributes as $key => $value) {
            $this->xml .= ' '.$key.'="'.htmlentities($value).'"';
        }
        $this->xml .= " />\n";
    }
    function pop() {
        $element = array_pop($this->stack);
        $this->_indent();
        $this->xml .= "</$element>\n";
    }
    function getXml() {
        return $this->xml;
    }
}

$xml = new XmlWriter();
$array = array(
    array('2007', '03', '29', 'Event I'),
	array('2007', '03', '30', 'Event II'),
    array('2008', '05', '30', 'Event III'),
    array('2008', '06', '29', 'Event IV'),
	array('2008', '06', '31', 'Event V'),
	array('2009', '06', '01', 'Event VI'),
);

$xml->push('calendar');

// year counter
$j=0;
foreach ($array as $finder) {
if ($year[$finder[0]] != "") {
$year[$finder[0]] = $i[$finder[0]];
$highest[$finder[0]][$j] = $i[$finder[0]];
$j++;
}

if ($year[$finder[0]] == "") {
$year[$finder[0]] = 1; 
$i[$finder[0]] = 0;
$i[$finder[0]]++;
$j++;}

$i[$finder[0]]++;
}

//debug
$year2 = $year;
print_r($year2);

//month counter
$j=0;
foreach ($array as $counter) {
if ($month[$counter[0]][$counter[1]] == ""){
$month[$counter[0]][$counter[1]] = 1;
$m[$counter[0]][$counter[1]] = 2; }
else {
$month[$counter[0]][$counter[1]] = $m[$counter[0]][$counter[1]];
$highest2[$counter[0]][$counter[1]][$j] = $m[$counter[0]][$counter[1]];
$j++;
$m[$counter[0]][$counter[1]]++; }
}

	$i=0;
	foreach ($array as $findyear) {
	$years2[$i] = $findyear[0];
	$i++; }
	$years = array_unique($years2);
$month2 = $month;
print_r($month2);
print_r($highest2);
			  
//begin
$k=0;
foreach ($array as $animal) {
    $xml->pushyear('year', array('0' => $animal[0]));
    $xml->pushmonth('month', array('1' => $animal[1]));
	$xml->push('day', array('2' => $animal[2]));
    $xml->element('event', $animal[3]);
	$xml->pop();
	if ($month2[$animal[0]][$animal[1]] == 1) {
	$xml->pop(); }
	if ($month2[$animal[0]][$animal[1]] > 1) {
	   if (!min($highest2[$animal[0]][$animal[1]]) == $highest2[$animal[0]][$animal[1]][$k]) {
	   $xml->pop(); } }
	if ($year2[$animal[0]] == 1) {
	$xml->pop(); }
	if ($year2[$animal[0]] > 1) {
		if (max($highest[$animal[0]]) == $highest[$animal[0]][$k]) {
	  $xml->pop(); } }
$k++;}
$xml->pop();

print $xml->getXml();

?>

Here's the output:

Array
(
    [2007] => 2
    [2008] => 3
    [2009] => 1
)
Array
(
    [2007] => Array
        (
            [03] => 2
        )
 
    [2008] => Array
        (
            [05] => 1
            [06] => 2
        )
 
    [2009] => Array
        (
            [06] => 1
        )
 
)
Array
(
    [2007] => Array
        (
            [03] => Array
                (
                    [0] => 2
                )
 
        )
 
    [2008] => Array
        (
            [06] => Array
                (
                    [1] => 2
                )
 
        )
 
)
<calendar> 
  <year value="2007"> 
    <month value="03"> 
      <day value="29"> 
        <event>Concert</event> 
      </day> 
      <day value="30"> 
        <event>Concert II</event> 
      </day> 
    </month> 
  </year> 
  <year value="2008"> 
    <month value="05"> 
      <day value="30"> 
        <event>Show</event> 
      </day> 
    </month> 
    <month value="06"> 
      <month value="06"> 
        <day value="29"> 
          <event>Signing</event> 
        </day> 
      </month> 
      <month value="06"> 
        <day value="31"> 
          <event>Signing II</event> 
        </day> 
      </month> 
    </month> 
    <year value="2009"> 
      <month value="06"> 
        <day value="01"> 
          <event>Reading</event> 
        </day> 
      </month> 
    </year> 
  </year>

jeffc418 0 Junior Poster in Training · Answer 2 · 2009-05-12T05:46:00+00:00

If anyone could help that'd be superb, thank you ;)