CODEWITHSUNDEEP
phpmysql
Preys
Preys

Reputation: 103

determine order in a ORDER BY statement

I have a select statement :

$search = "SELECT Scene, Divinite, Attestation
    FROM SceneDivList
    WHERE BINARY Divinite = '$target'
    ORDER BY FIELD( Scene);
$result = mysql_query($search, $con);

In this case the result will be alphabetically order based on the field 'scene'.

Is it possible to order the result not alphabetically but based on the following 'alphabet':

$alphabet = array( 1 => '-' , 2 => ',' , 3 => '.' , 4 => "A",
                   5 => "j", 6 => "a", 7 => "w", 8 => "b", 
                   9 => "p", 10 => "f", 11 => "m", 12 => "n", 
                   13 => "r", 14 => "h", 15 => "H", 16 => "x", 
                   17 => "X", 18 => "s", 19 => "S", 20 => "q", 
                   21 => "k", 22 => "g", 23 => "t", 24 => "T", 
                   25 => "d", 26 => "D");

the result should then be: 

SCENE            ATTESTATION
jnD-Hr-m-nms,t  DI.80,11
jrp             DI.26,12
jrT,t           DI.116,17
aAb,t           DI.138,8
anx             DI.12,5
antjw           DI.148,10
wADw-msdm,t     DI.144,11
bHsw            DI.115,9
pr-n-nb=f           DI.17,7

Upvotes: 2

Views: 113

Answers (5)

pp19dd
pp19dd

Reputation: 3633

If you wanted to brute-string replace a field and sort by it, you need a recursive function in PHP to generate the string:

function replace( $fields ) {
    if( empty( $fields ) ) return( "`Scene`" );

    $t = each( $fields );
    $pop = array_shift( $fields );

    return( sprintf(
        "replace(%s, '%s', '%s')",
        replace($fields),
        $t['key'],
        str_pad($t['value'], 3, '0', STR_PAD_LEFT )
    ));
}

$alphabet  = array( 1 => '-' , 2 => ',' , 3 => '.' , 4 => "A",
                   5 => "j", 6 => "a", 7 => "w", 8 => "b", 
                   9 => "p", 10 => "f", 11 => "m", 12 => "n", 
                   13 => "r", 14 => "h", 15 => "H", 16 => "x", 
                   17 => "X", 18 => "s", 19 => "S", 20 => "q", 
                   21 => "k", 22 => "g", 23 => "t", 24 => "T", 
                   25 => "d", 26 => "D");



$sql = sprintf(
"SELECT Scene, Divinite, Attestation, %s as `neworder`
FROM SceneDivList
WHERE BINARY Divinite = '%s'
ORDER BY `neworder`", 
    replace(array_flip( $alphabet  )),
    "target <- whatever that is"
);

echo $sql;

At this point $sql will contain the following query:

SELECT Scene, Divinite, Attestation, 
replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(
`Scene`, 'D', '026'), 'd', '025'), 'T', '024'
), 't', '023'), 'g', '022'), 'k', '021'), 'q', '020'
), 'S', '019'), 's', '018'), 'X', '017'), 'x', '016'
), 'H', '015'), 'h', '014'), 'r', '013'), 'n', '012'
), 'm', '011'), 'f', '010'), 'p', '009'), 'b', '008'
), 'w', '007'), 'a', '006'), 'j', '005'), 'A', '004'
), '.', '003'), ',', '002'), '-', '001'
) as `neworder`
FROM SceneDivList
WHERE BINARY Divinite = 'target <- whatever that is'
ORDER BY `neworder`

Upvotes: 0

Luca Rainone
Luca Rainone

Reputation: 16468

Jon's answer is the best solution. But here there is a brutal php solution for you

$alphabet = array( 1 => '-' , 2 => ',' , 3 => '.' , 4 => "A",
               5 => "j", 6 => "a", 7 => "w", 8 => "b", 
               9 => "p", 10 => "f", 11 => "m", 12 => "n", 
               13 => "r", 14 => "h", 15 => "H", 16 => "x", 
               17 => "X", 18 => "s", 19 => "S", 20 => "q", 
               21 => "k", 22 => "g", 23 => "t", 24 => "T", 
               25 => "d", 26 => "D");

$replace_engine = 'Scene';                   
foreach($alphabet as $k=>$char) {
    $charint = sprintf("%03s",   $k);
    $replace_engine = "REPLACE(".$replace_engine.",'$char','$charint')";
}


$query = "SELECT $replace_engine as myalphabet, Scene FROM SceneDivList ORDER BY myalphabet";

Upvotes: 1

Paul
Paul

Reputation: 9012

While Jon's answer is the right approach, you might want to try the quick and dirty version by setting up a table (ID, C) where ID is (autoincrement) integer and C is a VARCHAR(1).

With joins the ordering could be achieved to whatever level is required.

$search = "SELECT s.Scene, s.Divinite, s.Attestation
FROM SceneDivList s
INNER JOIN CustomOrder o1 ON o1.C = SUBSTRING(s.Scene,0,1)
INNER JOIN CustomOrder o2 ON o2.C = SUBSTRING(s.Scene,1,1)
INNER JOIN CustomOrder o3 ON o3.C = SUBSTRING(s.Scene,2,1)
WHERE BINARY s.Divinite = '$target'
ORDER BY o1.ID, o2.ID, o3.ID, SUBSTRING(s.Scene,4);
$result = mysql_query($search, $con);

NOTE: While this can be done quick & dirty the query itself will be way slower than before.

Upvotes: 0

Paolo Casciello
Paolo Casciello

Reputation: 8202

There are two solutions: 1) order after query directly in code. the drawback is you always need to gather all the results so you could have performance problems. 2) add a column to the table with the ordering values based on the index of your array.

maybe you could create a mysql function to order on... but i don't know enough of custom mysql functions

... and the solution by Jon proposed 2 min before mine :D

Upvotes: 0

Jon
Jon

Reputation: 437494

The cleanest solution would be to define your own custom collation and specify that the Scene column should use it. If you do that, the query will simply read ORDER BY Scene and that's it (by the way, ORDER BY FIELD(Scene) does not make sense -- did you check out what FIELD does?).

Other than that, you could hack together a nightmare based on STR_REPLACE to adapt the data to the collation instead of doing the opposite but I won't go there.

And of course you could sidestep all of this and do the sorting in PHP where it will be much more convenient, but this approach will not work for queries that join on subqueries that have to be sorted themselves.

Upvotes: 2

Related Questions