PHP从mysql中选择数据文本范围[重复]

Question

This question already has an answer here:

• How to use alphanumeric fields with BETWEEN clause in Mysql? 3 answers

I want to select a range from an items table that have a list of item numbers as follows :

id  item_no  qty date
1   1l500bk  15  2015-03-20
2   1l501bu  10  2015-03-20
3   1l1501ye  5  2015-03-21
4   1l1520bu  5  2015-03-21
5   1l1521bk  1  2015-03-22

And I've created a form with two input fields that asks the user to enter From item number & TO item number, as follows :

<input type="text" value="" placeholder="From Item Number" name="item1">
<input type="text" value="" placeholder="To Item Number" name="item2">

Now, When I type FROM 1l1500 - TO 1l1600 .. It should output all values within this range, but its not ! the output shows nothing . However, if I type 1l1500bk to 1l1521bk it will only show 1 output ( 1l1500bk ) ?!

Here is the PHP code after page submission :

$query = "SELECT *,SUM(qty) from s_{$value} WHERE item_no BETWEEN '".$_POST['item1']."' AND '".$_POST['item2']."'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result)){

    echo "<tr><td align=center width=19%>{$value}</td><td align=center width=19%>{$row['item_no']}</td><td align=center>{$row['qty']}</td><td align=center>{$row['date']}</td></tr>";

}

I hope this makes sense for you guys, I can't seem to find what is the problem ! Thank you

---

Thanks for @Waleed Ahmed for his help The solution for my question is just by grouping up the id as follows :

$query = "SELECT *,SUM(qty) from s_{$value} WHERE item_no BETWEEN '".$_POST['item1']."' AND  '".$_POST['item2']."'" GROUP BY id;

Plus : If you want to do partial text search

$length = max(strlen($_POST['item1']), strlen($_POST['item2'])); 

$query = "SELECT *,SUM(qty) from s_{$value} WHERE SUBSTRING(item_no, 1, ".$length.") BETWEEN '".$_POST['item1']."' AND  '".$_POST['item2']."'" GROUP BY id;

This output data even if you don't type the whole item number.

</div>

Answer 1

According to this query , it will show only one result as an output. To use aggregate function that is (sum(qty)) here, you have to use "Group By" word to show all results. For example

Not tested, Hope it will work

$query = "SELECT *,SUM(qty) from s_{$value} WHERE item_no BETWEEN '".$_POST['item1']."' AND '".$_POST['item2']."'" GROUP BY id;