Foreach to pull first record in scanned directory

[dkjuk]

Hello,
I am trying to create a photography gallery and at the minute I have it so it uses session variables to pull the cat titles and where a cat title is present it loops through the folder which is passed from a mysql lookup.

$dir    = "photography/".$gallery;
 $files1 = scandir($dir);
 $picturecount = count($files1) - 3;
 
$sqlstr = " SELECT * from gallery where FOLDER = '".$gallery."' order by DATED DESC " ;
$link = mysql_connect( "localhost", $user, $pass );
if ( ! link )
    die ( "Could not Connect to MYSQL" );
mysql_select_db( $db, $link )
  or die  ( "Could not open $db: ".mysql_error() );
$result = mysql_query( $sqlstr );
$num_rows= mysql_num_rows( $result );
while  ($a_row = mysql_fetch_array( $result ) ) {
   $title = $a_row["TITLE"];
   echo "<h2>" . $a_row['TITLE'] . "</h2>";
   echo "<h7>" . $a_row['NOTES'] . "</h7>";
  
 $dir    = "photography/".$a_row["FOLDER"];

echo "<ul>";
}

mysql_close( $link );

  foreach ($files1 as &$value) {
   if(preg_match("/.jpeg|.jpg|.gif\z/i", $value)){
     echo "<li><a rel='gallery_group' href=photography/".$gallery."/".$value."><img src=photography/".$gallery."/thumbs/".$value."></a></li>";
   }
  }

I have this bit working no problem, if there is a category present it will pull back all the images and thumbnails.
Now I have created the gallery page where I want it to display the category titles and use the scandir with a foreach statement to scan all the folders in a directory and pull back the first file per directory to display. I can't work out for the life of me what it should be. I pull back the titles from a database and it loops through the database to find the records. It is the actual displaying of the thumbnail I can't work out.

Any help appreciated.

[cereal]

First file basing on what? Name, creation date, update date, extension?
Why don't you add a "cover" field in the gallery table? So when inserting images the user can choose the image to display on top.

[dkjuk]

This is the code that I have at the minute for the gallery page when no cat variable is detected

echo "<h2>Categories</h2>";
 
 $sqlstr = " SELECT * from gallery order by DATED DESC " ;
$link = mysql_connect( "localhost", $user, $pass );
if ( ! link )
    die ( "Could not Connect to MYSQL" );
mysql_select_db( $db, $link )
  or die  ( "Could not open $db: ".mysql_error() );

// echo $sqlstr;

echo "<ul>";
$dir1    = "photography";
$value = scandir($dir1);
$result = mysql_query( $sqlstr );
$num_rows= mysql_num_rows( $result );

while  ($a_row = mysql_fetch_array( $result ) ) {
echo "<li>";
    if ( $_SESSION["validuser"]  == "true"){


echo "<a href=../manage/editgallery.php?ID=".$a_row['ID'].">EDIT/DELETE</a><br>";
echo "<a href=../manage/addphoto.php?FOLDER=".$a_row['FOLDER'].">Add Photos</a><br><br>";}
  
   echo $a_row["TITLE"];
   
 
echo "<a href=index.php?content=gallery&cat=".$a_row["FOLDER"]."><img src=../catthumbs/".$a_row['FOLDER'] .".gif></a></li>";
 
}
echo "</center>";
mysql_close( $link );
}

I've had to bodge round it because I can't work out the for each statement, I have told it to pull the thumbnail from a directory called catthumbs and there needs to be an image within that directory that matches the category name, not a clever way of doing it. It works but loading the images dynamically so that the latest image becomes the category thumbnail.

[dkjuk]

First file basing on what? Name, creation date, update date, extension?
Why don't you add a "cover" field in the gallery table? So when inserting images the user can choose the image to display on top.

Ideally the first file in the directory that was the most recently added.

[cereal]

So you don't save images names to database? In that case just check last inserted ID for that gallery. Anyway, I prefer glob to scandir:

<?php
# glob sorts only by alphabetic order, GLOB_NOSORT flag speeds up the function
$list = glob("{*.jpg,*.gif,*.png}",GLOB_BRACE|GLOB_NOSORT);
array_multisort(array_map('filemtime', $list), SORT_DESC, $list);
echo $list[0]; # get last modify time
?>

When you run stat, into a command line, to a file you get 3 different dates:

Access: 2012-01-19 00:56:04.104197718 +0100
Modify: 2012-01-13 15:54:58.000000000 +0100
Change: 2012-01-23 12:22:36.672104877 +0100

You can use fileatime, filemtime and filectime functions to get those values: each time you open an image or use touch command Access time is changed (fileatime); each time the inode information of the file is modified, like renaming, moving the file, setting new permissions the Change time is modified (filectime); last, each time the contents of a file is updated Modify time changes, so this function should be the most important for you: filemtime.

More info: http://php.net/manual/en/function.filemtime.php
Hope is helpful, bye :)

[dkjuk]

Hmmm, unless I am reading it wrong, I can't see how that will help me pull just one record, I have no issue with the actual pulling of the records, it is just that it returns all the files within the directory rather than just the one I need for my category title.

I have the website up on a test site if it makes it easier to explain?
I want it to loop through the database to find the category titles - this is working
I want it to pull a single thumbnail from a directory - the directory will be dynamically created on each loop by a database field which will be pulled - this doesn't work. I can't figure out the code to just get a single image. When there is a category in the URL it runs the top code I put up and correctly displays it, I want to use similar code to go through a directory and pull a single file.

[cereal]

Can you post gallery table structure? Just run show create table gallery; in MySQL.

I'm not understanding if you are saving images filename into the database or not. If yes, you need something like this:

select * from gallery as g, (select max(id) as id from gallery group by folder) as gg where g.id = gg.id order by g.id desc;

This query will output last entry for each folder field. Let me know, bye.