Find the closest number to value not working (SQL)

Question

I have the following SQL Command:

$sql = "SELECT TOP 1
        FROM products
        ORDER BY ABS( price - '$price' ) ASC LIMIT 1";

$result = $conn->query($sql);


while($row = $result->fetch_array()){
    echo "\r\n";
    echo $row['price'];
    echo "\r\n";
}

What I want to do is to find the closest number in column "price" to local variable $price. For example when user input price is 15000 I want to find closest number to this in the "price" column of the products table. Unfortunatly that gives me this problem:

PHP Fatal error:  Uncaught Error: Call to a member function fetch_array() on bool in C:\Users\rojto\Documents\clones\Techalo\techalo-improvements\database.php:25
Stack trace:
#0 {main}

Can someone help me please?

TOP isn't a thing in MySQL, so you're not making much sense – Strawberry May 05 '20 at 17:32 — Strawberry, May 05 '20 at 17:32

score 0 · Accepted Answer · answered May 05 '20 at 16:11

0

You should have a column name in the SELECT clause instead of TOP 1.

Presumably, you want:

SELECT price
FROM products
ORDER BY ABS(price - :price) 
LIMIT 1;

Note that this uses query parameter to pass the target price value rather than concatenating it into the query string. You can have a look at this famous SO post to learn why and how to avoid SQL injection.

answered May 05 '20 at 16:11

GMB

216,147
25
84
135

`ORDER BY ABS(price - :price) ` the " : " character should be $? – Tomáš Roj May 05 '20 at 16:17
@TomášRoj: do have a look at the post I linked in my answer. – GMB May 05 '20 at 16:25

Find the closest number to value not working (SQL)

1 Answers1