Integer division in MySQL

Question

I have a MySQL table which has a product_id field (big integer)

1102330008 1102330025 1102330070 1103010009 1103010010 1103020006 ...

I want to select rows which have product_id = 110301****. I tried to use this query:

SELECT * FROM `product` WHERE (product_id/10000)=110301  

but it does not return any values with this message:

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0005 sec )

Answer 1

Use the DIV operator.

mysql> SELECT 5 DIV 2;
    -> 2

Integer division. Similar to FLOOR(), but is safe with BIGINT values. Incorrect results may occur for noninteger operands that exceed BIGINT range.

Answer 2

SELECT *
FROM product
WHERE product_id BETWEEN 1103010000
                     AND 1103019999  

If you want to create your query in PHP, then you can construct your query like

$sql = "
    SELECT *
    FROM product
    WHERE product_id BETWEEN {$product_id_range}0000
                         AND {$product_id_range}9999  
";