PHP Select between two strings

Question

I've the following mysql table :

Table Name : items

item_no qty
1l1000bk 1
1l1020be 2
1l900bk 12
1l820be 19

After creating a form that submits to other page which contains the following PHP code:

$from = $_POST['from'];
$to = $_POST['to'];

$length = max(strlen($from), strlen($to)); 

$query6 = "SELECT * FROM items WHERE SUBSTRING(item_no, 1, ".$length.")   BETWEEN '".$from."' AND '".$to."' ORDER BY item_no Desc";

$result6 = mysql_query($query6);
if(!$result6){
    mysqli_error();
    exit();
}



if(mysql_num_rows($result6)>0){
$num6 = mysql_num_rows($result6);

for($i=0;$i<$num6;$i++){    

$row6 = mysql_fetch_assoc($result6);
echo $row6['item_no']."</br>";
}
}

The output is completely wrong, accoring to the select statment..I need to output the searched item_no as Descending order !.. Here what it shows :

1l900bk
1l820be
1l1020be
1l1000bk

How come 1l1000 is smaller than 1l900 ? It seems that it only compares the 3rd character ?..How to make sure it compares the full string?

Please Help !

It compares the whole string. To compare the value you want, see this topic: http://stackoverflow.com/questions/12097368/mysql-order-by-string-with-numbers — Royal Bg, Jun 24 '13 at 11:06

score 0 · Answer 1 · answered Jun 24 '13 at 11:11

0

         SELECT column_name(s)
         FROM table_name
         WHERE column_name BETWEEN value1 AND value2;

answered Jun 24 '13 at 11:11

score 0 · Answer 2 · answered Jun 24 '13 at 11:32

0

This is because it is sorted as a string, not as an integer. If you want to sort is, you should make shure all numbers are alike: so if you have 1000 and 900, use 0900. then it will sort ok.

answered Jun 24 '13 at 11:32

verhie

1,298
1
7
7

PHP Select between two strings

2 Answers2