How Can I Display Categories and Articles in Alphabetical Order?

[Fluoresce]

Can you figure this out?

 

I've got two tables, `articles` and `categories`.  Each article has been assigned to a category.

 

I want to present all of the articles on a single page, one list per category, thus:

 

Category

Article 1

Article 2

Article 3

Article 4

 

Category

Article 1

Article 2

Article 3

 

Category

Article 1

Article 2

Article 3

Article 4

 

I want the categories to be in alphabetical order and the articles to be in alphabetical order.

 

So far, I have only managed to get the categories in alphabetical order.  How can I also get the articles in alphabetical order?

 

As always, any help will be much appreciated.

 

Here's my code:

$sql = "SELECT title, url, cat FROM `articles` 
LEFT JOIN `categories` ON articles.catid = categories.catid 
ORDER BY cat ASC";

$results = mysql_query($sql, $conn) or die(mysql_error());

if(mysql_num_rows($results)) {
     $last_cat = '';                        
     while($row = mysql_fetch_assoc($results)) {
          if($row['cat'] != $last_cat) {
               if($last_cat) {
                    echo "</ol>";
               }
               $last_cat = $row['cat'];
               echo "<h3>$last_cat</h3>";
               echo "<ol>";
               echo "<li><a href=\"{$row['url']}\">{$row['title']}</a></li>";
          }
          else {                                
               echo "<li><a href=\"{$row['url']}\">{$row['title']}</a></li>";
          }
     }
     echo "</ol>";
}
else {
     echo "<p>No articles found.</p>";
}

[mac_gyver]

ORDER BY cat,title

[jazzman1]

You can use ORDER BY on different keys in MySQL:

SELECT * FROM t1 ORDER BY key1, key2;

Or, just mix them:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

Check this out.

[Psycho]

Since all the articles will always be associated with a category you should just use a normal JOIN. And since you are joining on the same field name, you can use the USING() function.

SELECT title, url, cat
FROM `articles`
JOIN `categories` USING(catid)
ORDER BY cat ASC

Also, there is some inefficiency in the logic. For example, why have a line of code at the end of the if() condition to output the article and then have an else statement to output the same thing? Remove that line from the if() condition and then have it come after the if condition. It will then get executed on each iteration of the while() loop. Never write code to do the same thing more than once. Also, when I have to do something at the end of a group (i.e. the category) I prefer to put the data into an array first. It makes the code much cleaner for the output - in my opinion.

$sql = "SELECT title, url, cat
        FROM `articles`
        JOIN `categories` USING(catid)
        ORDER BY cat, title ASC";

$results = mysql_query($sql, $conn) or die(mysql_error());

if(!mysql_num_rows($results))
{
    echo "<p>No articles found.</p>";
}
else
{
    $data = array();
    while($row = mysql_fetch_assoc($results))
    {
        $data[$row['cat']][] = $row;
    }

    foreach($data as $category => $articles)
    {
        echo "<ol>\n";
        echo "<h3>{$category}</h3>\n";
        foreach($articles as $article)
        {
            echo "<li><a href=\"{$article['url']}\">{$article['title']}</a></li>\n";
        }
        echo "</ol>\n";
    }
}

Edited October 3, 2013 by Psycho

[Fluoresce]

Thank you guys very much!  I appreciate your assistance. I learn loads in this forum!

 

And a big thank you to you, Psycho, for the code.