Moody's Rating sorting in mysql

Question

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

Table Sturcture is

score 2 · Answer 1 · answered Dec 20 '16 at 08:00

2

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

answered Dec 20 '16 at 08:00

Logan Wayne

6,001
16
31
49

These ratings are in product table. So there is no particular id – silentcoder Dec 20 '16 at 08:03
@KapilSharma - please give more info to your post, and to your future post, in order to help you out easily. Maybe you can give the table structure of `moody_rating` and `product` tables – Logan Wayne Dec 20 '16 at 08:05
1

My best solution matches with what Logan suggested. (Alternatively, you could add an extra column to the mood_tb specific to ranking, but irrelevant to the ID order in case anything changes in the future). Regardless, it's essentially: add a new table, like Logan suggested, add each 'moody' into the table, then go back to your initial table and convert moody_ratings' values to whatever ID (from your new table) represents each 'moody'. After converting and verifying whatever values you had in 'moody_ratings', change its column type to an int. Note that you'd have to update the website's SQL. – clicheName Dec 20 '16 at 08:48
Yes, Its the last hope. I am waiting if anyone have any solution. BTW +1 for answer – silentcoder Dec 20 '16 at 08:52

score 0 · Answer 2 · answered Dec 20 '16 at 09:10

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"
  }
}

Moody's Rating sorting in mysql

2 Answers2