[SOLVED] XML PHP

[ayok]

Hi,

I'm now trying to make an XML with PHP from mySQL database. I'd like to get an XML output like below:

<?xml version="1.0" encoding="UTF-8"?>
<menu>
                      <gallery name="Category1">
	            <image source="pics/original/117.jpg" thumb="pics/117.jpg" title="first pic"/>
	            <image source="pics/original/117.jpg" thumb="pics/117.jpg" title="second pic"/>

	</gallery>
	<gallery name="Category2">
		<image source="pics/original/118.jpg" thumb="pics/118.jpg" title="third pic"/>
		<image source="pics/original/121.jpg" thumb="pics/121.jpg" title="forth pic"/>
		<image source="pics/original/122.jpg" thumb="pics/122.jpg" title="fifth pic"/>
	</gallery>
	<gallery name="Category3">
		<image source="pics/original/187.jpg" thumb="pics/187.jpg" title="sixth pic"/>
		<image source="pics/original/196.jpg" thumb="pics/196.jpg" title="seventh pic"/>
		<image source="pics/original/197.jpg" thumb="pics/197.jpg" title="eight pic"/>
		<image source="pics/original/203.jpg" thumb="pics/203.jpg" title="ninth pic"/>
	</gallery>

</menu>

 

So far I've tried with the php code below:

<?php

header("Content-type: text/xml"); 

$host = "localhost"; 
$user = "root"; 
$pass = ""; 
$database = "gallery"; 

$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host."); 
mysql_select_db($database, $linkID) or die("Could not find database."); 

$query = "SELECT * FROM pictures,category WHERE pictures.category=category.id";; 
$resultID = mysql_query($query, $linkID) or die("Data not found."); 

$xml_output .= "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; 
$xml_output .= "<menu>\n"; 
for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++){ 
    $row = mysql_fetch_assoc($resultID);
    $xml_output .= "<gallery name=\"" .$row['name']."\">\n"; 
     
    $resultID2 = mysql_query($query, $linkID) or die("Data not found."); 
    for($x = 0 ; $x < mysql_num_rows($resultID2) ; $x++){
    	$row2 = mysql_fetch_assoc($resultID2); 
    	$xml_output .= "\t<image source=\"original/" .$row2['image']."\""; 
    	$xml_output .= "\tthumb=\"" .$row2['image']."\"";
    	$xml_output .= "\ttitle=\"" .$row2['title']."\"/>\n";
    }
    $xml_output .= "</gallery>\n"; 
}

$xml_output .= "</menu>\n"; 
echo $xml_output;

?>

 

But I only get this result:

 <?xml version="1.0" encoding="UTF-8" ?> 
- <menu>
- <gallery name="Category1">
  <image source="original/187.jpg" thumb="187.jpg" title="first pic" /> 
  <image source="original/121.jpg" thumb="121.jpg" title="second pic" /> 
  <image source="original/122.jpg" thumb="122.jpg" title="third pic" /> 
  <image source="original/150.jpg" thumb="150.jpg" title="forth pic" /> 
  <image source="original/203.jpg" thumb="203.jpg" title="fifth pic" /> 
  <image source="original/150.jpg" thumb="150.jpg" title="sixth pic" /> 
  <image source="original/118.jpg" thumb="118.jpg" title="seventh pic" /> 
  <image source="original/203.jpg" thumb="203.jpg" title="eight pic" /> 
  <image source="original/208.jpg" thumb="208.jpg" title="ninth pic" /> 
  </gallery>
  </menu>

 

Could anybody help how can i get the result as I want?

 

Thank you

ayok

[ayok]

Nobody?

[Barand]

Don't use "SELECT * ... " as anyone else looking at the query has no idea what is being selected from which table. In addition, it's inefficient to retrieve more than you need in a query.

[ayok]

Thanks for the reply Barand,

 

I try to correct the script.

<?php
header("Content-type: text/xml"); 
include "dbconnect.php";
$category = mysql_query("SELECT id,name FROM category ORDER BY id") or DIE(mysql_error());

$xml_output .= "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; 
$xml_output .= "<menu>\n"; 

for($x = 0 ; $x < mysql_num_rows($category) ; $x++){ 
    $row = mysql_fetch_assoc($category); 
    $row['name'] = str_replace("&", "&", $row['name']); 
    $row['name'] = str_replace("<", "<", $row['name']); 
    $row['name'] = str_replace(">", ">", $row['name']); 
    $row['name'] = str_replace("\"", """, $row['name']); 
    $xml_output .= "<gallery name=\"" .$row['name']."\">\n";

$pictures = mysql_query("SELECT pictures.*,category.id FROM pictures,category WHERE pictures.category=category.id") or DIE(mysql_error());
    for($x = 0 ; $x < mysql_num_rows($pictures) ; $x++){
    	$row2 = mysql_fetch_assoc($pictures); 
    	$xml_output .= "\t<image source=\"original/" .$row2['image']."\""; 
    	$xml_output .= "\tthumb=\"" .$row2['image']."\"";
    
	$row2['title'] = str_replace("&", "&", $row2['title']); 
    	$row2['title'] = str_replace("<", "<", $row2['title']); 
    	$row2['title'] = str_replace(">", ">", $row2['title']); 
    	$row2['title'] = str_replace("\"", """, $row2['title']); 
    	$xml_output .= "\ttitle=\"" .$row2['title']."\"/>\n";
    }	

    $xml_output .= "</gallery>\n";
}

$xml_output .= "</menu>\n"; 
echo $xml_output;
?>

 

But i keep getting wrong result. Could you tell me where I do wrong?

[ayok]

There are two tables. One is pictures table, and the other is category table.

 

On the xml I want to get the pictures are shown per category. So it should look like:

<gallery name="Category1">
		<image source="pics/original/118.jpg" thumb="pics/118.jpg" title="first pic"/>
		<image source="pics/original/121.jpg" thumb="pics/121.jpg" title="second pic"/>
	</gallery>
<gallery name="Category2">
		<image source="pics/original/122.jpg" thumb="pics/118.jpg" title="third pic"/>
		<image source="pics/original/123.jpg" thumb="pics/121.jpg" title="forth pic"/>
		<image source="pics/original/124.jpg" thumb="pics/122.jpg" title="fifth pic"/>
	</gallery>
<gallery name="Category3">
		<image source="pics/original/125.jpg" thumb="pics/118.jpg" title="sixth pic"/>
		<image source="pics/original/126.jpg" thumb="pics/121.jpg" title="seventh pic"/>
		<image source="pics/original/127.jpg" thumb="pics/122.jpg" title="eight pic"/>
	</gallery>

[ayok]

It seems like this line

for($x = 0 ; $x < mysql_num_rows($category) ; $x++){ 

 

doesn't work.

[Barand]

try

<?php
header("Content-type: text/xml"); 
include "dbconnect.php";

$xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n"; 
$xml_output .= "<menu>\n";

$pictures = mysql_query("SELECT p.image,p.title, c.name 
            FROM pictures p INNER JOIN category c ON p.category=c.id
            ORDER BY c.name ") or DIE(mysql_error());
$prevcat='';
while (list ($image, $title, $cat) = mysql_fetch_row($pictures))
{
    if ($prevcat != $cat)
    {
        if ($prevcat != '')
        {
            $xml_output .= "</gallery>\n";
        }
        $xml_output .= "<gallery name=\"$cat\">\n";
        $prevcat = $cat;
    }
    $xml_output .= "<image source=\"pics/original/$image\" thumb=\"pics/$image\" title=\"$title\"/>\n";
}
$xml_output .= "</gallery>\n";  
$xml_output .= "</menu>\n"; 
echo $xml_output;
?>

[ayok]

OMG... Barand... it's true.. You're the super genius.. You safe me! Thank you very much!

 

Love this forum

 

cheers,

ayok