CODEWITHSUNDEEP
phpmysqlsorting
silentcoder
silentcoder

Reputation: 1048

Moody's Rating sorting in mysql

I have a table which have moody rating . Now I want to sort that table according to the same order of rating showing on website. is there any way to do that.

I have tried the solution provided on 1. MySQL 'Order By' - sorting alphanumeric correctly

if I use below query then result is like

SELECT * FROM `moody_rating` ORDER BY `moody_rating`.`rating`+0 ASC

I want same order as use on moody rating

enter image description here

Table Sturcture is

enter image description here

Upvotes: 0

Views: 214

Answers (2)

Tigger
Tigger

Reputation: 9130

Pull your data out then sort in PHP using a usort() like so:

// test data
$raw[] = array('rating' => 'Baa1', 'cn'=>'Some text Baa1');
$raw[] = array('rating' => 'Aaa', 'cn'=>'Some text Aaa');
$raw[] = array('rating' => 'A1', 'cn'=>'Some text A1');
$raw[] = array('rating' => 'Aa2', 'cn'=>'Some text Aa2');

// Assume PHP 5.3.x or newer
usort($raw,function($a,$b){
    // This is the order that you want the results sorted
    $rOrder = array('Aaa'=>1, 'Aa1'=>2, 'Aa2'=>3, 'Aa3'=>4, 
                    'A1'=>5, 'A2'=>6, 'A3'=>7, 'Baa1'=>8, 
                    'Baa2'=>9, 'Baa3'=>10);
    return $rOrder[$a['rating']] - $rOrder[$b['rating']];
});

Result should be:

array(4) {
  [0]=>
  array(2) {
    ["rating"]=> string(3) "Aaa"
    ["cn"]=> string(13) "Some text Aaa"
  }
  [1]=>
  array(2) {
    ["rating"]=> string(3) "Aa2"
    ["cn"]=> string(13) "Some text Aa2"
  }
  [2]=>
  array(2) {
    ["rating"]=> string(2) "A1"
    ["cn"]=> string(12) "Some text A1"
  }
  [3]=>
  array(2) {
    ["rating"]=> string(4) "Baa1"
    ["cn"]=> string(14) "Some text Baa1"
  }
}

Upvotes: 0

Logan Wayne
Logan Wayne

Reputation: 5991

This will be a bit tricky, but you can separate the table for different types of mood. Store the different types of mood in order to mood_tb table:

mood_tb:

 mood_id | mood
---------+------
    1    | Aaa
    2    | Aa1
    3    | Aa2
and so on...

Then, when storing the rating, instead of the text itself (e.g. Aaa, Aa1, etc.), you'll be storing the id of the corresponding mood:

moody_rating:

 id | column 1 | rating
----+----------+--------
  1 |   text   |    1
  2 |   text   |    3
  3 |   text   |    2

So, when you try to display them in order of mood:

SELECT * FROM moody_rating a
    LEFT JOIN mood_tb b ON a.rating = b.mood_id
    ORDER BY a.rating

Upvotes: 2

Related Questions