Extracting images from database in order. Please help

[geroid]

Hi

I have a database with images stored in it. Lets say that the images are stored with an additional field identifying them as  'summer images', some are 'winter images' and some are 'autumn images'.

How can I write an sql statement that first extracts the summer images, then the winter images and finally the autumn images for display on the webpage.

 

It's easy enough to extract the images as they are found but how would I extract them in the order above without having to write three seperate statements?.

[trq]

You wouldn't. Summer, Winter and Autumn mean nothing to a database. Have your images got any timestamp stored with them per chance?

[geroid]

Thanks for the reply thorpe

My database fields are

image_id (incremental id)

Image_name (the path)

category (summer, winter, autumn)

date (the year - 2009, 2010, 2011 etc.)

 

As I say, I would like to extract all the summer pics first, then the winter then autumn of a particular year. Will I have to write 3 statements or can it be done in one - a loop perhaps.

On the webpage the user can pick a particular year and is then redirected to a page to display images just from that year. So that's no problem. I was just hoping that one statement could extract the images in the order mentioned instead of multiple statements.

[trq]

I was just hoping that one statement could extract the images in the order mentioned instead of multiple statements.

 

Yeah, but the order mentioned doesn't make any sense to a DB. You could pull them out then sort them yourself. eg;

 

$sql = "SELECT image_id, image_name, category FROM imgs WHERE `date` = '2007' ORDER BY category"; // date is a terrible name for a database field by the way, reserved word and all.
if ($result = mysql_query($sql)) {
  if (mysql_num_rows($result)) {
    $data = array();
    while ($row = mysql_fetch_assoc($result)) {
      $cat = $row['category']; unset($row['category']);
      $data[$cat][] = $row;
    }
  }
}

 

You then end up with all your 'summer' pics in $data['summer'], 'winter' in $data['winter'] etc etc.

[geroid]

Thanks

How do I then access this array $data to display the images. I'm a bit unfamiliar with arrays. What would be the code for example to see the contents of the array before I display the images?

[trq]

Thanks

How do I then access this array $data to display the images. I'm a bit unfamiliar with arrays. What would be the code for example to see the contents of the array before I display the images?

 

To simply display whats in an array (for debug purposes)...

 

<pre>
<?php print_r($data); ?>
</pre>

 

That will give you an idea of what the array contains. However, a more usefull example might be...

 

<h1>Summer Images</h1>
<?php
foreach ($data['summer'] as $summer) {
  echo '<img src="' $summer['image_path'] . '" alt="' . $summer['image_name'] .'" /><br />';
}
?>

[geroid]

Thanks

I'll experiment with that now for a while and see how it works out. Much appreciated

[geroid]

Hi Thorpe

The following code is generating an error. Any ideas

 

<h3>Summer Images</h3>

<?php

foreach ($data['summer'] as $summer) {

  echo '<img src="' $summer['image_name'] . '" alt="' . $summer['image_name'] .'" /><br />';

}

 

Error generated is:

Parse error: syntax error, unexpected T_VARIABLE, expecting ',' or ';' in C:\xampp\htdocs\glorcar\summerdit.php on line 52

[FrankA]

Hey, this line might work a little easier like this:

 

echo "<img src=\" ". $summer['image_name'] . "\" alt=\"" . $summer['image_name'] . "\" /><br />";

 

I don't know if that's horribly inefficient, but I have a little bit better of luck with double quotes if I'm trying to append anything containing single quotes. I hope that helps!

 

-Frank

[trq]

Missing a concatenation operator.

 

echo '<img src="' . $summer['image_name'] . '" alt="' . $summer['image_name'] .'" /><br />';