codedudey codedudey - 6 months ago 17
PHP Question

MySQL query does not return all available rows in a simple query

I have a query which searches a table for product sizes where purchased is greater then sold and groups it by size so i can index all available sizes in cart.

However, it does not return all available sizes.

Here is the query:

SELECT *
FROM stock_live
WHERE `purchased` > `sold` AND (`stock`='1' AND product_id='13')
GROUP BY size
ORDER BY size ASC


Available sizes in the stock_live table there are 3 sizes available, but it only returns two and escapes one.

Here is the MySQL table with the results returned:

CREATE TABLE IF NOT EXISTS `stock_live` (
`ID` int(20) NOT NULL AUTO_INCREMENT,
`waqt` int(11) NOT NULL,
`stock` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`size` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`purchased` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`sold` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`purchase_id` int(11) NOT NULL,
`lot_no` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`file_no` varchar(20) COLLATE utf8_persian_ci NOT NULL,
`Tfrom` varchar(10) COLLATE utf8_persian_ci NOT NULL COMMENT 'Transferred From',
`Tto` tinytext COLLATE utf8_persian_ci NOT NULL COMMENT 'Transferred TO',
`old_pid` int(11) NOT NULL COMMENT 'Old purchase_id',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=1054 ;

--
-- Dumping data for table `stock_live`
--

INSERT INTO `stock_live` (`ID`, `waqt`, `stock`, `product_id`, `size`, `purchased`, `sold`, `purchase_id`, `lot_no`, `file_no`, `Tfrom`, `Tto`, `old_pid`) VALUES
(942, 1445451326, 1, 13, '0.7ML', '14400.00', '0.00', 0, '2200', 'G-54', '930', '', 0),
(21, 1445451326, 1, 13, '250ML', '360.00', '116.00', 43, '240', 'F-82', '0', '|277|330|712', 0);


And it escapes this row:

INSERT INTO `stock_live` (`ID`, `waqt`, `stock`, `product_id`, `size`, `purchased`, `sold`, `purchase_id`, `lot_no`, `file_no`, `Tfrom`, `Tto`, `old_pid`) VALUES
(295, 1445451326, 1, 13, '150ML', '114', '36', 62, '5011', 'Z-400', '0', '|306|331', 0);

Answer

Edit, it seems the problem is in your table schema.

You defined purchased and sold as VARCHAR fields. It's doing string comparison, and "114" < "36" in string comparison, just as "Apple" < "Blackberry".

Redefine your table with INT columns so it compares their numeric values:

 ALTER TABLE `stock_live` 
    CHANGE COLUMN `purchased` DECIMAL(10,2) NOT NULL,
    CHANGE COLUMN `sold` DECIMAL(10,2) NOT NULL;