-1

I tried to fetch 50 products from a database with the highest price but if i set the LIMIT at 50 it's just fetching the first 50 products order by the price. This is not what i want. How can i setup the mysql query right or should i fetch all and set the limit in the php fetch_assoc()?

SQL Query:

SELECT id, product_name, product_url, product_price, product_delivery_time, product_on_stock, product_language, product_type 
FROM product 
WHERE is_active = '1' AND not product_price = 'N/A' 
  AND product_price > (SELECT max(product_price) from product)

I tried different SQL queries but without success. I'm not familiar with sub-queries and i think somewhere their is the problem.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    `just fetching the first 50 products order by the price`....can't you just order by price in descending order? – ADyson Oct 28 '22 at 10:50
  • 1
    Schema info, sample data and expected result would probably help us understand this a lot more easily, btw. See [Tips for asking a good Structured Query Language (SQL) question](https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question). I don't think the PHP bit is really relevant. – ADyson Oct 28 '22 at 10:51
  • 1
    Are you using a string type column for the price? That would be a bad idea. – Paul Spiegel Oct 28 '22 at 10:55
  • 2
    why there is a sub-query? and how any product_price in the product table can be GREATER than max(product_price) from product table ? – Your Common Sense Oct 28 '22 at 10:55
  • You're looking for product prices greater than the max product price. Is that part of the problem? – Reisclef Oct 28 '22 at 10:55
  • 1
    `product_price = 'N/A'`...oh yeah that's a massive red flag. Learn what `null` is for! If you store the price as a number, then you can easily sort it numerically in ascending or descending order. Sorting it when implemented as a text/varchar field may not produce the results you expect. Please use the right data type for the job! Then you could `ORDER BY price DESC LIMIT 50` to get the 50 highest-priced items. – ADyson Oct 28 '22 at 10:56
  • Understood! Will change the DataType but its horrible because all the numbers are stored as curreny format like: 6.99 Error for Integer: MariaDB [poke_db]> alter table product modify product_price int; ERROR 1292 (22007): Truncated incorrect INTEGER value: '6.99' After research i chaned it into: MariaDB [poke_db]> alter table product modify product_price decimal; Now all prices are broken because sql removed everything behind the dot. Was the developers stoned and forget to add currency format as dataType? –  Oct 28 '22 at 13:11
  • 1
    decimal should be a good data type to store numbers with decimal points, such as currency amounts. However when creating the column you do need to specify the precision (i.e. the number of decimal places it should store) - see https://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql for more info. It's also generally advisable to read the manual ([here](https://dev.mysql.com/doc/refman/5.7/en/precision-math-decimal-characteristics.html) and/or [here](https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html)) when using something you haven't used before. – ADyson Oct 28 '22 at 13:21

2 Answers2

1

Order by price. Without any knowledge of the schema this could help.

order by price desc limit 50

DenicioCode
  • 8,668
  • 4
  • 18
  • 33
1

The problem was the DataType:

product_price = db.Column(db.String(80))

I changed it in:

product_price = db.Column(db.Numeric(15, 2))

and now the (modified) sql query is working:

SELECT id, product_name, product_url, product_price, product_delivery_time, product_on_stock, product_language, product_type 
FROM product 
WHERE is_active = '1' AND NOT product_price = '0' 
ORDER BY product_price DESC LIMIT 50

Thanks everyone for the help.

ADyson
  • 57,178
  • 14
  • 51
  • 63