Sort MySQL result set by nullable column with nulls first, then another column

Question

I am creating a "Pinned" feature for my forum and I am looking for a way to put my pinned topics at the beginning of the array so that they are "stuck" to the top of the page.

If the topic is not pinned then topic_pinned=NULL if it is pinned then topic_pinned=0.

The array is sorted by topic_updated. The pinned topics need to stay sorted by topic_updated while staying at the top of the page, and then under the pinned topics are the non-pinned topics, which are also sorted by topic_updated.

Topic Array ($forum_topic_results):

Array
(
    [0] => Array
        (
            [topic_id] => 4
            [topic_subject] => Test Subject #4
            [topic_date] => 2015-09-10 18:34:18
            [topic_by] => 1
            [topic_pinned] => 
            [topic_updated] => 2015-09-10 20:37:22
        )

    [1] => Array
        (
            [topic_id] => 3
            [topic_subject] => Test Subject #3
            [topic_date] => 2015-08-22 09:24:40
            [topic_by] => 1
            [topic_pinned] => 0
            [topic_updated] => 2015-09-04 22:02:31
        )

    [2] => Array
        (
            [topic_id] => 2
            [topic_subject] => Test Subject #2
            [topic_date] => 2015-08-15 10:56:00
            [topic_by] => 1
            [topic_pinned] => 
            [topic_updated] => 2015-09-04 19:45:32
        )

    [3] => Array
        (
            [topic_id] => 1
            [topic_subject] => Test Subject #1
            [topic_date] => 2015-08-30 19:48:17
            [topic_by] => 1
            [topic_pinned] => 0
            [topic_updated] => 2015-09-03 00:44:38
        )
)

PHP:

/**
 * getAllTopics
 *
 * Retreives the topics of the chosen category from the `forum_topics` table.
 *
 * @param   $cat_id
 * @access  public
 */
public function getAllTopics($cat_id=NULL)
{
    $database=$this->database;

    $database->query('SELECT topic_id, topic_subject, topic_date, topic_by, topic_pinned, topic_locked FROM forum_topics WHERE topic_cat = :catid ORDER BY topic_updated DESC', array(':catid' => $cat_id));
    $result = $database->statement->fetchAll(PDO::FETCH_ASSOC);

    return $result;
}

# Get topics
$forum_topic_results = $this->getAllTopics($_GET['cat']);
foreach($forum_topic_results as $forum_topic_row)
{
    # Get user's username.
    $topic_by=SearchUser($forum_topic_row['topic_by']);

    $data.='<tr>'.
        '<td>'.
            '<h3><a href="'.$_SERVER['PHP_SELF'].'?action=forum_posts&topic='.$forum_topic_row['topic_id'].'">'.$forum_topic_row['topic_subject'].'</a></h3>'.
            'by '.$topic_by['username'].' on '.date('D M d, Y g:i a', strtotime($forum_topic_row['topic_date'])).
        '</td>'.
    '</tr>';
}

Outcome I want:

Array
(
    [0] => Array
        (
            [topic_id] => 3
            [topic_subject] => Test Subject #3
            [topic_date] => 2015-08-22 09:24:40
            [topic_by] => 1
            [topic_pinned] => 0
            [topic_updated] => 2015-09-04 22:02:31
        )

    [1] => Array
        (
            [topic_id] => 1
            [topic_subject] => Test Subject #1
            [topic_date] => 2015-08-30 19:48:17
            [topic_by] => 1
            [topic_pinned] => 0
            [topic_updated] => 2015-09-03 00:44:38
        )

    [2] => Array
        (
            [topic_id] => 4
            [topic_subject] => Test Subject #4
            [topic_date] => 2015-09-10 18:34:18
            [topic_by] => 1
            [topic_pinned] => 
            [topic_updated] => 2015-09-10 20:37:22
        )

    [3] => Array
        (
            [topic_id] => 2
            [topic_subject] => Test Subject #2
            [topic_date] => 2015-08-15 10:56:00
            [topic_by] => 1
            [topic_pinned] => 
            [topic_updated] => 2015-09-04 19:45:32
        )
)

What do you want and what is expected outcome? Also what you tried so for? please paste your code effort also.THANKS. — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Sep 11, 2015 at 5:07
possible duplicate of Reference: all basic ways to sort arrays and data in PHP — scrowler
– scrowler, Commented Sep 11, 2015 at 5:09
Also, do you store this data in a database? It would be trivial to retrieve the data ordered in advance and then there would be no need to sort. — gview
– gview, Commented Sep 11, 2015 at 5:12

M0rtiis · Accepted Answer · 2015-09-11 05:15:56Z

4

Do antother way.

Mark in your database table PINNED as 1.

NOT PINNED as 0.

After that just add (replace ORDER BY) to your query where u select topics:

ORDER BY `topic_pinned` DESC, `topic_updated` DESC

edited Sep 11, 2015 at 5:15

answered Sep 11, 2015 at 5:10

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aziz Saleh Over a year ago

Does not account for the date!

M0rtiis Over a year ago

really? the point is to stay pinned topics on the top. u read only about how to sort an array. but i provided solution how to avoid this (with ONE line of code) by passing logic to sql.

gview Over a year ago

No his point is, that you would need at least ORDER BY topic_pinned, topic_updated

Draven Over a year ago

Leaving topic_pinned=NULL works just fine. Thank you!

gview · Accepted Answer · 2015-09-11 06:08:27Z

Just for completeness, if you actually still wanted or needed to do this to the arrays via php I'd probably opt for using usort. It doesn't require manufacturing columnar arrays.

usort lets you write either a function or provide an anonymous function that does the comparison logic. Since you have an array of arrays, and the comparison needs to be done utilizing several array elements, you could easily write a simple comparison function so long as you keep in mind that:

The comparison function must return an integer less than, equal to, or greater than zero if the first argument is considered to be respectively less than, equal to, or greater than the second.

Something like this (off the cuff, untested code)

function cmp($a, $b) {
    if ($a['topic_pinned'] == $b['topic_pinned']) {
        // Have to compare Dates
        $adate = new DateTime($a['topic_updated']);
        $bdate = new DateTime($b['topic_updated']);
        return ($adate < $bdate) ? -1 : 1;
    } elseif ($a['topic_pinned'] == null) {
        return 1;
    } else {
        return -1;
    }  
}

usort($array, 'cmp');

As you can see this gets complicated fast, due to the need to convert the datetime strings into datetime objects so that you can actually do comparisons.

All the more reason why using the sql database is better/faster/easier in this circumstance.

Pathik Vejani · Accepted Answer · 2015-09-11 05:20:20Z

0

This should work:

<?php
$finalArr = $forum_topic_results;
foreach ($finalArr as $key => $row) {
    $topic_pinned[$key] = $row['topic_pinned'];
}
array_multisort($topic_pinned, SORT_DESC, $finalArr);
return $finalArr;
?>

more information about an array_multisort: array_multisort

edited Sep 11, 2015 at 5:20

answered Sep 11, 2015 at 5:14

Pathik Vejani

4,49711 gold badges66 silver badges110 bronze badges

2 Comments

gview Over a year ago

Seems to me this is missing the comparison of topic_updated. If the data is pre-supposed to already be sorted by date, this is probably ok however.

Pathik Vejani Over a year ago

@gview yes.. this should also work.. but if possible then we should only use query..

Collectives™ on Stack Overflow

Sort MySQL result set by nullable column with nulls first, then another column

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related