"Ordering by" in mysql query doesn't work when echoing in php later?

Question

This is my code:

while ($shop = mysql_fetch_array($shop_result)) {
    $item_result = mysql_query("SELECT * FROM db_".$shop['ItemCategory']."s WHERE ItemId = '".$shop['ItemId']."' ORDER BY Level");
    $item = mysql_fetch_assoc($item_result);


echo("<item>");
        echo("<Element>" . $item['ItemElement'] . "</Element>");
        echo("<ItemLevel>" . $item['Level'] . "</ItemLevel>");
echo("</item>");

Here is a typical output:

</items>
<item>
<Element>8</Element>
<Level>15</Level>
</item>
<item>
<Element>4</Element>
<Level>2</Level>
</item>
<item>
<Element>4</Element>
<Level>24</Level>
</item>
<item>
<Element>4</Element>
<Level>17</Level>
</item>
<items>

But the third highest Level is at the top? Why? I ordered by ItemLevel? This is my desired output:

</items>
<item>
<Element>4</Element>
<Level>2</Level>
<item>
<Element>8</Element>
<Level>15</Level>
</item>
<item>
<Element>4</Element>
<Level>17</Level>
</item>
<item>
<Element>4</Element>
<Level>24</Level>
</item>
<items>

I really cant figure it out. When I run the same thing in Sequel Pro I get what I want, but it's GUI.

check the value for that specific entry, maybe you have a space or a hidden character or something that is causing the row to be ordered first. check the order shown when they query is done directly in mysql. — Niagaradad
– Niagaradad, Commented Jan 8, 2016 at 21:00
It's definitely because items come form different loops of while — u_mulder
– u_mulder, Commented Jan 8, 2016 at 21:01
You're only fetching one row from $item_result. The loop is only for $shop_result, so the ordering is based on that query. — Barmar
– Barmar, Commented Jan 8, 2016 at 21:21

Gordon Linoff · Accepted Answer · 2016-01-08 21:01:01Z

1

Level would appear to be a string. So, it is ordering as a string not a number.

Here are two fixes First, order as a number:

order by Level + 0

Second, order by length first:

order by length(Level), Level

answered Jan 8, 2016 at 21:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

Niagaradad Over a year ago

i thought of that too but out of the example then the order should have been 15, 17, 2, 24 but supposedly the LEVEL 15 is the one shown first and 17 at the end.

AJ X. Over a year ago

Another option would be to do: ORDER BY Level * 1

Barmar Over a year ago

ORDER BY CAST(level AS DECIMAL)

Jonny Blamca Over a year ago

Level is INT in my database. Could it still be a string for some reason?

Barmar · Accepted Answer · 2016-01-08 21:24:22Z

1

You're not fetching all the rows from $item_result, you're just fetching the first row. You need another while loop.

while ($shop = mysql_fetch_array($shop_result)) {
    $item_result = mysql_query("SELECT * FROM db_".$shop['ItemCategory']."s WHERE ItemId = '".$shop['ItemId']."' ORDER BY Level");
    while ($item = mysql_fetch_assoc($item_result)) {    
        echo("<item>");
                echo("<Element>" . $item['ItemElement'] . "</Element>");
                echo("<ItemLevel>" . $item['Level'] . "</ItemLevel>");
        echo("</item>");
    }
}

answered Jan 8, 2016 at 21:24

Barmar

789k57 gold badges555 silver badges669 bronze badges

Collectives™ on Stack Overflow

"Ordering by" in mysql query doesn't work when echoing in php later?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related