PHP string contains integer

Question

I have the following lines:

1BG200,1M400,1BA1000 

And I want to compare the given lines in 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 more, and then 400, 200. How can you compare them? I believe that it is not correct to compare a string containing an integer! And I can’t find the right solution for my problem?

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

Sorry, I am not familiar with PHP .. Please help!

Answer 1

You can use a custom view by looking only at the numerical part

 function cmp($a, $b) { $numa = intval(preg_replace('/[0-9]*[AZ]+/', '', $a)); $numb = intval(preg_replace('/[0-9]*[AZ]+/', '', $b)); if($a == $b) return 0; return ($a < $b) ? -1 : 1; } //Now get the list and sort usort($list, "cmp"); 

Answer 2

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

Answer 3

You can add a custom function to your MySQL. Found one that looks like it can cross out all characters without a MySQL digit to compare non-numeric characters .

I would highly recommend doing this, returning everything to php and sorting if you ever decide to use a restriction / offset due to the large results returned. Otherwise, you would need to return everything to PHP, and then splicing the array, at which point I feel that it will be an inefficient use of resources.

Alternatively, you can add a sort column to your table, if possible, so you can better use indexes in MySQL, which, depending on your recordset, can have a huge performance difference.

Answer 4

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

Answer 5

overall preg is expensive. To get the number from this 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 sorting from there is clear ...