xml + sql

[Arabica]

Hi,

right at the beginning I want to excuse if this was discussed elsewhere in the forum. But since it's not impossible in the forum to search for words with three chars, XML and SQL is hard to find. So, sorry for possible double posting.

I'm trying to create a XML-file from the following Infos:

imageID
imagetitle
imagefilename
thumbfilename
description

and last but the main problem

categoryname


Since the name of the category is in an other table I'm stuck how to get the name.... I took the Flash Gallery Addon (http://www.pixelpost.org/extend/addons/pixelpost-flash-gallery/) and modified the xml generator. So here is my result


<?php
if( $_GET['view']!='addons' ) {


header('Content-Type: text/xml; charset=utf-8');
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Cache-Control: no-cache");
header("Pragma: no-cache");



require("includes/pixelpost.php");
require("includes/functions.php");

start_mysql('includes/pixelpost.php','front');

$cfgrow = sql_array("SELECT * FROM ".$pixelpost_db_prefix."config");

$tz = $cfgrow['timezone'];
$datetime = gmdate("Y-m-d H:i:s",time()+(3600 * $tz)); // current date+time
$cdate = $datetime; // for future posting, current date+time
$i = 1;


echo '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<gallery title="'.htmlspecialchars($cfgrow['sitetitle']).'" thumbDir="./images/" imageDir="./images/">'.PHP_EOL;

$query2 = "SELECT count(*) AS slides FROM {$pixelpost_db_prefix}catassoc AS CAT INNER JOIN {$pixelpost_db_prefix}pixelpost AS PIX ON (CAT.image_id = PIX.id) WHERE (datetime<='".$cdate."')";
$query2 = mysql_query($query2);
$row2 = mysql_fetch_array($query2);
if ( $row2['slides'] != 0 ) {
$query2 = "SELECT distinct(PIX.id), PIX.image, PIX.headline, PIX.body, PIX.datetime, PIX.category
FROM {$pixelpost_db_prefix}catassoc AS CAT
INNER JOIN {$pixelpost_db_prefix}pixelpost AS PIX ON (CAT.image_id = PIX.id)
WHERE (datetime<='".$cdate."')
ORDER BY datetime DESC";
$query2 = mysql_query($query2);
while($row2 = mysql_fetch_array($query2)){
echo '<image id="'.$row2['id'].'"';

if (file_exists(dirname(dirname($_SERVER['SCRIPT_FILENAME'])).'/images/fullsize_'.utf8_encode($row2['image']))) {
echo ' img ="fullsize_'.utf8_encode($row2['image']).'"';
}
else {
echo ' img="'.utf8_encode($row2['image']).'"';
}
echo' thumb="'.utf8_encode($row2['image']).'" ' .
'title="'.utf8_encode(htmlspecialchars($row2['headline'])).'" ' .
'desc="'.utf8_encode((htmlspecialchars($row2['body']))).'" ' .
'date="'.utf8_encode($row2['datetime']).'" ' .
'category="'.utf8_encode($row2['category']).'"/>'.PHP_EOL;
}
}

echo'</gallery>';

}

?>


so I now get the id of the category but not the Name.

Since I'm kind of 'untrained' in building SQL statements I hope you could help me.

Thank you and greetings from Berlin
Frank

[Dkozikowski]

Try this:

In the above code find:

FROM {$pixelpost_db_prefix}catassoc AS CAT
INNER JOIN {$pixelpost_db_prefix}pixelpost AS PIX ON (CAT.image_id = PIX.id)


Replace with:

FROM {$pixelpost_db_prefix}categories AS CAT
INNER JOIN {$pixelpost_db_prefix}pixelpost AS PIX ON (CAT.id = PIX.id)


ALSO

Find:

'category="'.utf8_encode($row2['category']).'"/>'.PHP_EOL;


replace with:

'category="'.utf8_encode($row2['name']).'"/>'.PHP_EOL;


Disclaimer: The above code has not been tested.

[Arabica]

Hi Dkozikowski,

thanks for you attempt to help me out of the dark ;-)

Unfortunately it doesn't work. The modified file only fetches the first result out of the database and finishes the job.... and the category field stays blank.

I guess (and this definitely a guess) the nesting of the two queries isn't right. Do you have any other ideas or probably someone else.

Any try and error experiments are welcome.

thanks
frank

[Dkozikowski]

Here is a quick solution without getting too crazy with MySQL joins:

http://pastie.textmate.org/private/jesl6iafoo2diqpsuzmj1w

[Arabica]

Yeah Yeah Yeah ...... you saved me from building a dirty work-around ;-) The solution works smooth as silk. Thank you very much. Now I can concentrate on building the frontend for the xml....

greetings from a happy
frank



PS: Just one last question. Do you have a hint how to get the exif_infos ou tof the database in a well formed XML. The problem is the exif_infos contains double quotes, which I don't know how to handle in the xml. Output is something like this

........ 43" category="Arabica" exif="a:54:{s:13:"VerboseOutput";s:1:"0";s:6:" .........

[Dkozikowski]

Yes, the EXIF is stored in the database as a serialized array.

You must first unserialize (http://us2.php.net/unserialize) it before you can work with it.

Once unserialized, the EXIF info will be available for use within a standard array...

[Arabica]

thanks again, i'll have a look at it

[Arabica]

The XML-file works like a charm, just the special characters are driving me nuts.

For example:
I have an image-title called "Buschwindröschen" the character ö is stored correct in the database and also displayed correct in the html-template. (see here (http://www.bruehgruppe.de/index.php?showimage=221)).
BUT when the xml is written via PHP with the command
htmlspecialchars(utf8_encode($row2['headline']))
the result is "Buschwindröschen" (as you can see here (http://www.bruehgruppe.de/xmlList.php)).

So how can I fetch or write the data correct, so the XML is well formed in UTF-8??

thanks
frank