Featured Post on Custom Blogging System

[TylerB]

I am building a personal blog for myself which has a fully functional administration panel that allows me to add, edit, and delete blogs very simply. I would like to add a "Featured" blog option which will position the "Featured" blog before all the others, without messing up the order of the other blogs. Right now, the blogs are being called from MySQL database and being displayed by their post date in descending order (newest posts first.)

My question is; What would be the best way to implement this sort of feature on my custom blog? I plan on using a checkbox and reading whether it is checked or not for true/false, if checked the blog is featured, if not checked it is displayed in normal order. But I don't know how to take that one blog and place it atop the others after the mysql query orders them in descending order. I would also like to be able to "Unfeature" the blog and have it be removed from the top and returned to its original position (I'm assuming this won't be an issue because the actual blog ID won't be changing)

I can post some of my PHP code if that will help generate some ideas/answers...

[Inigoesdr]

One way to do it is to store a flag in the posts table that marks the post row as featured or not. Nothing complicated, just a TINYINT(1) where 1 represents featured and 0 is normal. Once you have that you can just query the posts, ordering by the featured field first. Something like this:

SELECT
    *
FROM
    `blog_posts`
WHERE
    `status` = 'published'
ORDER BY
    `featured` DESC, `post_date` DESC