Order by clause in MySQL is not working inside a foreach loop

I'm trying to get the profiles names which are assigned to a specific subcategory with id=9. When I run the code below, I get the profiles that I want but for some reason the ORDER BY clause in the foreach loop doesn't sort them by their name alphabetically. Instead they are ordered in the same way they are ordered inside the 'profiles' field in 'subcategories' table (the IDs for the profiles are comma separated). For example if in subcategories['profiles'] I have ',5,1,2' the profiles names will be displayed in the following order:

  1. Profile with ID=5
  2. Profile with ID=1
  3. Profile with ID=2

I'm using the explode() function to get the ID for each profile inside the 'subcategory' table and then use that ID to retrieve their information from the 'profile' table using a query inside the foreach loop.

Am I missing anything here? Thanks for your help.

Here's my code:

<?php
$subcategories=mysql_query("select * from subcategories where id='9'");

while ($subcategories = mysql_fetch_array($subcategories)) 

{
 $profiles = $subcategories['profiles'];
 $profiles = explode(',', $profiles);

       foreach ($profiles as $p)
       {
         $all_places = mysql_query("select * from profile where id='$p' and active='1' order by name asc");

           while ($profile = mysql_fetch_array($all_places)) 
           {

               echo $profile['name'];                   

           }

       }

}
?>

Answers


Well the reason why your results do not order by name is because you are retrieving every profile with a new SQL query in your foreach loop for $profiles. So effectively in your scenario, you will end up with 3 SQL queries that returns 1 profile each. Hence, when the "order by" clause is declared, it orders by name within each query, which only contains 1 result each.

does using an IN statement work for you? Eg.

    <?php
$subcategories=mysql_query("select * from subcategories where id='9'");

while ($subcategories = mysql_fetch_array($subcategories)) 

{

//i assume $subcategories['profiles'] are integers separated by comma as mentioned
 $profiles = $subcategories['profiles'];

                 $all_places = mysql_query("select * from profile where id IN ($profiles) and active='1' order by name asc");

                     while ($profile = mysql_fetch_array($all_places)) 
                     {

                       echo $profile['name'];                   

                     }

}
?>

Need Your Help

git repository not recognized during rails deploy with capistrano

ruby-on-rails git deployment capistrano

I'm trying to figure out why I'm getting an error from my remote git repository when I try to deploy my rails app. It's telling me that it's not a git repository, yet I can push and pull from it wi...

The state of a pointer after deallocation

c memory-management

What does the pointer of a dynamically allocated memory points to after calling the free() function.