CODEWITHSUNDEEP
phpmysql
Alihamra
Alihamra

Reputation: 484

PHP String Contains Integer

I've the following strings as follows :

1BG200,1M400,1BA1000

And I want to to compare the above strings into Desc Order...

Code:

$sql = "SELECT * FROM collected WHERE c_no BETWEEN '".$from."' AND '".$to."' ORDER BY c_no Desc";

Output :

1M400
1BG200
1BA1000

It should be 1000 is larger, then 400, 200..How can i compare them ? I believe its not right to compare string that contains integer ! And I can't find a correct solution for my issue ?

Some people suggested using preg_match or substr..But as you can see there are single and double characters ex ( M and BG ).

Sorry, I'm not that familiar with PHP.. Please Help !

Upvotes: 6

Views: 309

Answers (5)

Quicker
Quicker

Reputation: 1246

in general preg is expensive. To gain a number from the given pattern I would do something like this:

digitstri='1M400'; // just an example
number=intval(is_number(digitstri{2})?substr(digitstri,2):substr(digitstri,3))

I think, the sorting from there is clear...

Upvotes: 0

Joe Meyer
Joe Meyer

Reputation: 4405

You could add a custom function to your MySQL. Found one that looks like it could strip out all the non digit characters MySQL strip non-numeric characters to compare.

I would highly recommend doing this over bringing everything back to php and sorting if you ever decide to use limit / offset due to large results being returned. Otherwise you would have to pull everything back to PHP then splice an array at which point I feel it would be an inefficient use of resources.

Alternatively, you could add a sort column to your table if that is a feasable option, to allow you to better utilize indexes in MySQL which depending on your record set may be a huge performance difference.

Upvotes: 1

flec
flec

Reputation: 3019

You could add the user defined preg-functions to your mysql (https://github.com/mysqludf/lib_mysqludf_preg). Then use PREG_CAPTURE in your order by clause.

Upvotes: 0

Otto
Otto

Reputation: 929

You can use a custom sort, looking only at the numerical part

function cmp($a, $b)
{
    $numa = intval(preg_replace('/[0-9]*[A-Z]+/', '', $a));
    $numb = intval(preg_replace('/[0-9]*[A-Z]+/', '', $b));
    if($a == $b) return 0;
    return ($a < $b) ? -1 : 1;
}

//Now get the list and sort
usort($list, "cmp");

Upvotes: 3

jcubic
jcubic

Reputation: 66490

You can use preg_replace('/[0-9][A-Z]+/', '', $var) to remove first number and more then one letter after, and then use php usort.

Upvotes: 2

Related Questions