Mysql – fetch all data having a Price BETWEEN 100000 AND 10000000

MySQLmysql-5.6

I have a table named articles with a column named price.

When a user on my web app selects both Price Min and Price Max, I need to perform a query like this:

$priceMin = 100000;
$priceMax = 10000000;

$query = 'SELECT * FROM articles WHERE price BETWEEN $priceMin AND $priceMax';

However, when the selected Price Min is <= 100000 and Price Max is >= 10000000 (10 Million), I don't have any output with a Price greater than 900000 even though rows with values above 900000 do exist.

The table is defined like this:

CREATE TABLE articles ( 
 id int(11) NOT NULL AUTO_INCREMENT, 
 name varchar(100) NOT NULL, 
 price varchar(100) NOT NULL, 
 source varchar(255) NOT NULL, PRIMARY KEY (id) 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 

Best Answer

Storing numeric data in a varchar column is causing your difficulty.

You can see this effect with a very simple test.

First a table with two columns where we'll store identical data; one column is a varchar, the other is an integer.

CREATE TABLE test
(
    v varchar(100) NOT NULL
    , n int NOT NULL
);

Insert some sample data:

INSERT INTO test (v, n)
VALUES (0, '0')
    , (1, '1')
    , (2, '2')
    , (100, '100')
    , (99, '99');

Show the data, ordered by the varchar column:

SELECT *
FROM test
ORDER BY v;

Results:

╔═════╦═════╗
║  v  ║  n  ║
╠═════╬═════╣
║   0 ║   0 ║
║   1 ║   1 ║
║ 100 ║ 100 ║
║   2 ║   2 ║
║  99 ║  99 ║
╚═════╩═════╝

Show the data, ordered by the integer column:

SELECT *
FROM test
ORDER BY n;
╔═════╦═════╗
║  v  ║  n  ║
╠═════╬═════╣
║   0 ║   0 ║
║   1 ║   1 ║
║   2 ║   2 ║
║  99 ║  99 ║
║ 100 ║ 100 ║
╚═════╩═════╝

The varchar column is sorted alphabetically, whereas the integer column is sorted numerically.

You should consider altering the price column to a numeric data type that supports the precision and magnitude you require. Refer to the MySQL developer guide for fixed-point data types for more details. I'd suggest considering decimal(18, 4) as a starting point for prices. 18 digits is the precision, i.e. the total number of digits that can be stored. 4 is the scale, which is the number of digits to the right of the decimal point. If the price column was defined as decimal(18, 4), it would be able to store values such as 99999999999999.9999, and allow them to be sorted correctly. Defining the column as a varchar is problematic since it will allow you to store hedgehog as well as 10000, which will lead to a variety of problems, not least of which will be the inability to obtain a total price of articles in a group.