Sort files in a directory using PHP + Mysql

HI !

Basically I have an image carousel that, where each image is linked to article dynamically. I have a script that reads the directory, store the file names into an arr开发者_Go百科ay. Then I trim off the file extension, query the database for the article I want the image to link to by searching for the article with the same/ similar name. e.g: blets.jpg links to the belts article. SO I got that working.

But what I would like to do is be able to have the images be diplayed in the order my articles have assigned to them in the ordering column.

What I have now fetches the images and displays them alphabetically, what I 'm trying to do is have them displayed in the order I have assigned to my articles.

So here's my code:

     echo "<div id='carousel'>\n";

    // Get image file name
  // open directory
  $myDirectory = opendir("./images/products/carousel/spring-summer-2011/");

  while($fileName = readdir($myDirectory))  // get each file
  {
    $dirArray[] = $fileName;
  }

  closedir($myDirectory); // close directory


  //sort($dirArray); // sort files


  echo "<div class='infiniteCarousel'>
        <div class='wrapper'>
          <ul>\n";

  foreach ($dirArray as $file)
  {
      if (substr("$file", 0, 1) != ".") //don't list hidden files
      {
      $name = substr($file, 0, strrpos($file, '.')); // trim file name extension
      $res = mysql_query("Select id, alias, ordering from content where alias like '{$name}'  ORDER BY ordering"); // order by is pretty useless here !!
              while ($row = mysql_fetch_array($res))
              {
              $id = $row['id'];
              $alias = $row['alias'];
              $ordering = $row['ordering'];

      echo "<li><a href='index.php?option=com_content&view=article&id={$id}' title='{$alias}'>\n";
      echo "<img src='./images/products/carousel/spring-summer-2011/{$file}' height='80' width='120' alt='{$alias}' />";
      echo "</a></li>\n";
              }

      }
  }

  echo "</ul>\n</div>\n</div>\n";

  echo "</div>"; //Close Carousel

I've left my comments in the code. And I basically know what needs to be done, just not sure how to do it. I need a pro !? help.

---

$Line[] = array();

foreach ($dirArray as $file)
 {
  if (substr("$file", 0, 1) != ".") //don't list hidden files
  {
  $name = substr($file, 0, strrpos($file, '.')); // trim file name extension
  $res = mysql_query("Select id, alias, ordering from content where alias like '{$name}'  ORDER BY ordering"); // order by is pretty useless here !!
          while ($row = mysql_fetch_array($res))
          {
          $id = $row['id'];
          $alias = $row['alias'];
          $ordering = $row['ordering'];

  $Line[] = "<li id='$ordering'><a href='index.php?option=com_content&view=article&id={$id}' title='{$alias}'>\n <img src='./images/products/carousel/spring-summer-2011/{$file}' height='80' width='120' alt='{$alias}' /></a></li>\n";
          }

  }
  }

sort($Line);
foreach ( $Line as $ordering => $line ) {
echo "$line";
}

---

If I understand you correctly, there is no need to read the directory and store the images in an array.

You can just query the database to get the collection of articles you want to show in the order you want and add .jpg to the name of the article to get the name of the image.

An additional advantage is that you only have to query the database once for all your articles instead of doing a separate query for each article.

---

I recommend that you grab the file names as you are but do the query and the rendering separately. Each time you query for the file information and such you should store that information as an object/array into another array. After you finish all of your queries you can then sort based on the ordering value. Finally you would iterate over this array and render the results.

Hope this helps, let me know if you need more specific details.

---

Alright !!!

The eagle has landed. OK , so I got AR's solution to work without a hitch.

@Benjamin I got yours to work too, but I had to take function out of the foreach loop and change it to:

usort($rows, function($a, $b)
{
    return $a > $b ? 1 : -1;
});

then it worked.

I learned a lot from this experience and so I thank you all. You really helped me to further understand arrays.

Now it works fine on my testing server (LAMP) but go figure, it won't work on Godaddy's server. Ouf !

Peace.

---

You can first fetch all the rows in an array, then use usort() to sort them according to your specific criteria:

$rows = array();
foreach ($dirArray as $file) {
    // ...
    while ($row = mysql_fetch_array($res)) {
        $rows[] = $row;
    }
}

function my_sort($a, $b)
{
    return $a['ordering'] - $b['ordering'];
}

usort($rows, 'my_sort');

foreach ($rows as $row) {
    // display the data
}

However, as advised by jeroen, you should group all your queries into one, this complicates a bit the code, but is way more efficient!

(By the way, you might also be interested in using mysql_fetch_assoc() over mysql_fetch_array(), if you're only using $row as an associative array.)