Outputting PHP to XML?

[christsealed]

Hi,

I'm working on a Flash image gallery that parses an XML file in order to retrieve its image data. Below is the PHP that creates the XML.

I’m creating a UNION of two tables that hold the information Flash needs to parse. In my select statement, I’m limiting MySQL to return only results WHERE the galleryTitle is the same as the folder. In that way, I’m able to return only galleries that have images in them, since the folders will hold the images. I’m also aware that for production purposes, this means the galleryTitle will HAVE to be the same as the folder, but I’m fine with that, as I intend to write a function that automates the folder creation process.



<?PHP
$link = mysql_connect ("localhost", "root", "");
mysql_select_db ("test");

$query = 'SELECT galleries.*, images.folder, images.imgName, images.imgDescription FROM galleries, images WHERE galleryTitle = folder';

$results = mysql_query($query);

echo "<?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"yes\"?>\n";
echo "<galleries>\n";
while ($line = mysql_fetch_assoc($results)) {
echo "<gallery title=\"{$line["galleryTitle"]}\" intro=\"{$line["galleryIntro"]}\">\n";
$i=0;
if (count($line["imgName"])>$i) {
$i++;
echo "<img src=\"{$line["folder"]}/{$line["imgName"]}\" description=\"{$line["imgDescription"]}\" />\n";
}
echo "</gallery>\n";
}
echo "</galleries>";

mysql_close($link);

?>

So far, this works perfectly. And in truth, I don’t even really need the “for” loop, as it was part of a failed experiment to see if it could help me order my results in the format that I wanted, which is below.

<?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"yes\"?>
<galleries>
<gallery title="architecture" intro="These are the photos of various buildings in the cities I visited .">
<img src="architecture/1.jpg" description="Description 1" />
<img src="architecture/2.jpg" description="Description 2" />
<img src="architecture/3.jpg" description="Description 3" />
<img src="architecture/4.jpg" description="Description 4" />
<img src="architecture/5.jpg" description="Description 5" />
</gallery>
</galleries>

However, with or without the “for” loop, what I get is this:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<galleries>
<gallery title="architecture" intro="These are the photos of various buildings in the cities I visited .">
<img src="architecture/1.jpg" description="Description 1" />
</gallery>
<gallery title="architecture" intro="These are the photos of various buildings in the cities I visited .">
<img src="architecture/2.jpg" description="Description 2" />
</gallery>
<gallery title="architecture" intro="These are the photos of various buildings in the cities I visited .">
<img src="architecture/3.jpg" description="Description 3" />
</gallery>
<gallery title="architecture" intro="These are the photos of various buildings in the cities I visited .">
<img src="architecture/4.jpg" description="Description 4" />
</gallery>
<gallery title="architecture" intro="These are the photos of various buildings in the cities I visited .">
<img src="architecture/5.jpg" description="Description 5" />
</gallery>
</galleries>

I’ve tried grouping the galleryTitle row using the GROUP BY relational operator in the SQL statement. However, that only returns one result for the images, while I have more than that.

ORDER BY is not an option, since it only alphabetizes the results, and doesn’t eliminate any redundancies.

What should I do?

 

[elven_rangers]

There are two ways to solve this problem:

1. Don't get all the data in one query. Get the list of galleries first (in your example there's one gallery) and then loop through galleries and for each get the list of images. For each gallery create and close a <gallery> tag while inside at the img tags.

2. Group by gallery title and inside the gallery loop (which takes place for each row in the result) whenever you get to a new gallery save the name. Then do a check: if the current gallery name equals the previous gallery name, then don't open a new gallery tag, but skip that and allow the image tag to be placed in the same gallery tag. If they are different then first close the previous gallery and open a new gallery tag, also save the new gallery title.

 

[dhvrm]

Unfortunately, you haven't quite grasped the idea behind the one-to-many concept of relational databases. So let's try explaining it one more time.

You have a gallery table. This table contains ONLY the information for the gallery. Suggested columns, based on what you want to accomplish:

gallery_id (PRIMARY INT AUTO_INCREMENT)
gallery_name
gallery_intro
gallery_folder

You also have an images table. Suggested columns, based on what you want to accomplish:

image_id (PRIMARY INT AUTO_INCREMENT)
gallery_id
img_name
img_desc

Notice that I have added the name of the gallery's image folder to the GALLERY table, not the image table.

Now, you create the relationship between the image and its gallery via the gallery_id column in the image table.

So, suppose you have three image galleries:

gallery_id | gallery_name | gallery_info | gallery_folder
1 | Bermuda | Sun and fun on vacation | bermuda
2 | My pets | Cats and dogs I've owned along the way | pets
3 | Family | The joys of my life | family

Now, suppose you have 8 images:
1 | 1 | cabin.jpg | This is the hut where we stayed
2 | 1 | beach.jpg | The beach was so beautiful!
3 | 1 | diving.jpg | A beautiful coral reef was just outside our door
4 | 1 | plane.jpg | This is the bush plane we used to get back and forth. The pilot was a hoot!
5 | 3 | dad.jpg | Here's Dad opening presents on Christmas morning
6 | 2 | fluffy.jpg | Fluffy was the first cat I ever owned
7 | 2 | spot.jpg | Everybody has to own a dog named Spot!
8 | 2 | tweety.jpg | Tweety was a canary we rescued from the coal mine.

This is the proper way to structure your data for what you want. You can now use two queries to get the results you want.

The first query gets JUST THE GALLERY. The second query gets the images for each gallery.

Answers will not accept the code I am trying to post here, so I posted it to Geocities:

http://www.geocities.com/dhvrm/20090601.txt