I have this query to fetch the order_id from order_history table like this
$sql = "SELECT DISTINCT(`order_id`) FROM `".DB_PREFIX."order_history` WHERE trackcode = '".$trackcode."'";
Situation is ...
When Order is packed the tracking id is assigned to it and added at order_history table. If the order has been send by the other courier service there will be another entry for the same order_id in order_history table. But As per above query it will fetch the order_id for that tracking_no. even it was change at order history table.
Another case :- If the tracking no. wrongly assigned to the order and then changed. hence two order_id will be fetched via this above query.
How could i find the correct way to solve it ?
order_history table is like this
CREATE TABLE `order_history` (
`order_history_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`order_status_id` int(5) NOT NULL,
`notify` tinyint(1) NOT NULL,
`comment` text NOT NULL,
`date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`trackcode` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`carrier` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`warehouse_id` tinyint(1) DEFAULT '0',
`logistic_partner_id` int(11) DEFAULT NULL,
`dest_area_code` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`dest_service_center_code` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`added_by` varchar(60) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`order_history_id`)
) ENGINE=MyISAM AUTO_INCREMENT=130306 DEFAULT CHARSET=utf8;
How Ever I tried this one
SELECT order_id FROM order_history WHERE trackcode='11171920880' ORDER BY date_added DESC LIMIT 1
But it's fetching the order_id even the recently updated tracking code for the same order_id is '1117192089'
My Question is extended one of this one .. its not my solution
