CODEWITHSUNDEEP
phpmysql
Jonny Blamca
Jonny Blamca

Reputation: 1

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

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.

Upvotes: 0

Views: 39

Answers (2)

Barmar
Barmar

Reputation: 780879

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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

Upvotes: 1

Related Questions