Want to get lowest value from sql

Question

In my database there are 5 values: 50, 75, 95, 125 and 200. When I query the minimum value I get 125, but I should get 50.

Here is my code:

$result_upTICKET = mysql_query("SELECT ID, EID, COMMISSION, MIN(PRICE) as PRICE FROM `tickets` WHERE EID='$EID_UPcoMing' AND STATUS='1'");

while($row_upTICKET = mysql_fetch_array($result_upTICKET))
{
    $PRICE_upTICKET= $row_upTICKET['PRICE'];
    $COMMISSION_upTICKET= $row_upTICKET['COMMISSION'];
}

What did I get wrong?

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 20 '17 at 13:23
is 125 the minimum of the data results? If so then the result is expected. `MIN` only operates on the result set not the entire table. — apokryfos, Jun 20 '17 at 13:23
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 20 '17 at 13:23

score 1 · Accepted Answer · answered Jun 20 '17 at 13:20

1

Try this one

SELECT ID, EID, COMMISSION, PRICE FROM `tickets` WHERE EID='$EID_UPcoMing' AND STATUS='1' ORDER BY PRICE ASC LIMIT 1

answered Jun 20 '17 at 13:20

Hemant Anjana

114
7

1

Turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Jun 20 '17 at 13:23
Thanks for reply, but it again shown 125 value even i try by DESC order but it could not show 50 it shown 95. – Hassan Jun 20 '17 at 13:25
Are you sure that your column data type is integer?? – Hemant Anjana Jun 20 '17 at 13:32
No its varchar(50). is it problem ? – Hassan Jun 20 '17 at 13:36
Yes, This is only your problem. Change it as integer. – Hemant Anjana Jun 20 '17 at 13:37
o brother thank you so much ... its working now!! i will keep it for next time thanks.. – Hassan Jun 20 '17 at 13:39
Welcome, If it works and solved your problem than accept my answer. – Hemant Anjana Jun 20 '17 at 13:42

score 0 · Answer 2 · answered Jun 20 '17 at 13:24

0

you are using where condition also. It filters rows first then apply/Select columns and min. I am sure that your where condition pull out row with price 50 and that's why min is coming as 125 instead of 50

answered Jun 20 '17 at 13:24

Passionate Coder

7,154
2
19
44

score 0 · Answer 3 · answered Jun 20 '17 at 13:47

0

Please execute your query like,We need to convert into interger

mysql_query("SELECT ID, EID, COMMISSION, MIN(CONVERT(PRICE, SIGNED INTEGER)) as PRICE FROM tickets WHERE EID='$EID_UPcoMing' AND STATUS='1'");

answered Jun 20 '17 at 13:47

Swapnil Kumbhar

440
4
10

Want to get lowest value from sql

3 Answers3