CODEWITHSUNDEEP
phpmysqlsql-order-by
PaulHanak
PaulHanak

Reputation: 739

"ORDER BY FIELD" MySQL does not seem to be working

long story short, my order by field does not seem to be working for my MySQL call. I can't really find a pattern in the storage...

$theorder is set correctly as 

, 'People-Wedding Photo', 'People-Kids', 'People-Male/Female', 'Architecture-Exterior', 'Architecture-Interior', 'Animal-Birds', 'Backgrounds'

And here is my code. As you can see, I am looping through, doing my format converting....but I still get a wrong order... a RANDOM order....

Is there something I am missing?

$under_link_query = mysql_query("SELECT DISTINCT(type), type FROM ".$prefix."ProFolio_work WHERE type != 'Backgrounds' ORDER BY FIELD(type" . $theorder .")");


    while($u_row = mysql_fetch_assoc($under_link_query)){
        $type = html_entity_decode($u_row['type']);

        $typeForm = preg_replace('/[^a-z0-9]/i', '_', $type);
        ?>
           <a href="#" id="link_<? echo str_replace(' ', '_', $typeForm); ?>">
    <? echo ucfirst($type); ?></a>
            <?

Upvotes: 1

Views: 4497

Answers (1)

MaddHacker
MaddHacker

Reputation: 1128

@Malovich is onto the answer, basically you should use:

ORDER BY $theorder

But keep in mind that default ordering is ascending, and ordering happens sequentially. I've noted that your variable $theorder is set to a string that starts with a comma, which is pretty much your issue.

Make sure $theorder doesn't start with a comma, and then try to just order on one row. if that works, keep adding more.

=============== EDIT =================

Apologies, you're trying to sub-sort a field. Missed that part, so here's what's going on:

You need to provide all fields to sort on, or MySQL will sort each of the fields you haven't provided first, then it will sort on the fields you have provided. If you want, you can provide a sub-set of fields to order on, but you'll need to provide them in reverse order, then use the DESC notation like:

ORDER BY FIELD (field,'lastsort',middlesort','firstsort') desc

This shows your list in the following order:

firstsort items
middlesort items
lastsort items
<all other options, in reverse alphabetical order> items

The key part of this is you'll need to add all field values to sort on. (or use the workaround).

Upvotes: 4

Related Questions