PHP按问题排序

Question

I am having hard time to solve this puzzle. I have a website which is showing some products. I am trying to show the product by ASC order but it is not working properly.

Here is my current query and output:

$sql=executeQuery("select * from ".PREFIX."prod_info where prodId='".$pId."' order by ProductName+0) ASC");


AM 65
AM 67
AM 69
AM 104 (Chuck) 
AM 105 (Sin) 
AM 73
AM 76
AC 20 

Any help would be appreciated

Answer 1

You should have:

order by ProductName

Instead of:

order by ProductName+0

The latter converts ProductName to an integer. If it starts with a letter, then it will be treated as 0. What is probably happening is that all are starting with letters, all get ordered by the value 0 -- and nothing interesting is happening.

EDIT:

You appear to want to order the ProductName alphabetically by the first two digits and then numerically by the rest. You can do this as:

order by left(ProductName, 2),
         char_length(ProductName),
         ProductName

If you really have extraneous words at the end, after the number, then it gets harder. The following may work better:

order by left(ProductName, 2),
         substr(ProductName, 3) + 0,
         ProductName