Solved

Getting the correct order history records

Posted on 2010-09-15
12
388 Views
Last Modified: 2012-08-13
When I change the id_order_state in ps_order_history it adds another record
instead of changing the current id_order_history. I need to obtain the list of the
records in ps_order_history where the highest the id_order numbers = the id_order
where the id_order_state = 12.

For example, in this table, the first record has an id_order_state of 12. But in
subsequent updates the status has been updated to a 2, I want only the records where
there hasn't been an update. Did I make that clear enough?
(Table definitions and data listed below)

Here is my original SQL, but it is always returning id_order_history 1 even when it
has been updated. Actually, I'm not even sure this is the most efficient way to do this.
Please don't berate me for my SQL, I'm nt that bright on JOINS, but, please, make
suggestions for improvement.

SELECT o.id_order, 
       osl.`name`
FROM `ps_order_history` oh
INNER JOIN `ps_orders` o 
  ON ( o.`id_order` = oh.`id_order` )
INNER JOIN `ps_order_detail` od 
  ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os 
  ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl 
  ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
WHERE osl.`name` = 'Generation Error'

Open in new window


CREATE TABLE IF NOT EXISTS `ps_orders` (
  `id_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_carrier` int(10) unsigned NOT NULL,
  `id_lang` int(10) unsigned NOT NULL,
  `id_customer` int(10) unsigned NOT NULL,
  `id_cart` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_order`),
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `ps_orders` (`id_order`, `id_carrier`, `id_lang`, 
                         `id_customer`, `id_cart`) 
                      VALUES (1, 2, 1, 2, 1);

CREATE TABLE IF NOT EXISTS `ps_order_history` (
  `id_order_history` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_employee` int(10) unsigned NOT NULL,
  `id_order` int(10) unsigned NOT NULL,
  `id_order_state` int(10) unsigned NOT NULL,
  `date_add` datetime NOT NULL,
  PRIMARY KEY (`id_order_history`),
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `ps_order_history` (`id_order_history`, `id_employee`, 
                                `id_order`, `id_order_state`, 
                                `date_add`) 
                         VALUES (1, 0, 1, 12, '2010-09-14 11:19:04'), 
                                (2, 0, 1, 2, '2010-09-15 19:31:25'), 
                                (3, 0, 1, 2, '2010-09-15 19:33:33'), 
                                (4, 0, 1, 2, '2010-09-15 19:36:56'), 
                                (5, 0, 1, 2, '2010-09-15 19:37:40'), 
                                (6, 0, 1, 2, '2010-09-15 19:41:03'), 
                                (7, 0, 1, 2, '2010-09-15 19:41:35');

CREATE TABLE IF NOT EXISTS `ps_order_state` (
  `id_order_state` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice` tinyint(1) unsigned DEFAULT '0',
  PRIMARY KEY (`id_order_state`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `ps_order_state` (`id_order_state`, `invoice`) 
                      VALUES (1, 0), (2, 1), (3, 1), (4, 1), 
                             (5, 1), (6, 0), (7, 1), (8, 0), 
                             (9, 1), (10, 0),(11, 0),(12, 0);

CREATE TABLE IF NOT EXISTS `ps_order_state_lang` (
  `id_order_state` int(10) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `template` varchar(64) NOT NULL,
  UNIQUE KEY `order_state_lang_index` (`id_order_state`,`id_lang`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `ps_order_state_lang` (`id_order_state`, `name`, 
                                   `template`) 
           VALUES (1, 'Awaiting cheque payment', 'cheque'), 
                  (2, 'Payment accepted', 'payment'), 
                  (3, 'Preparation in progress', 'preparation'), 
                  (4, 'Shipped', 'shipped'), 
                  (5, 'Delivered', ''), 
                  (6, 'Canceled', 'order_canceled'), 
                  (7, 'Refund', 'refund'), 
                  (8, 'Payment error', 'payment_error'), 
                  (9, 'On backorder', 'outofstock'), 
                  (10, 'Awaiting bank wire payment', 'bankwire'), 
                  (11, 'Awaiting PayPal payment', ''), 
                  (12, 'Generation Error', 'download_product_error');

Open in new window

0
Comment
Question by:EddieShipman
  • 7
  • 5
12 Comments
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687257
did you mean to type o.`id_lang` = 1 on Line 11?
SELECT o.id_order, 
       osl.`name`
FROM `ps_order_history` oh
INNER JOIN `ps_orders` o 
  ON ( o.`id_order` = oh.`id_order` )
INNER JOIN `ps_order_detail` od 
  ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os 
  ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl 
  ON (osl.`id_order_state` = oh.`id_order_state` AND o.`id_lang` = 1)
WHERE osl.`name` = 'Generation Error'

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33687275
you had osl.`id_lang` = 1 which isn't a valid column. I think you meant to type o.`id_lang` = 1 instead. Line 11
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 33687630
Yes, I neglected to fix that in the query that I posted. The table actually has that column.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Author Comment

by:EddieShipman
ID: 33692188
Ok, let me repost how this should look...

When I change the id_order_state in ps_order_history it adds another record instead of changing the current id_order_history. I need to obtain the list of the records in ps_order_history where the highest the id_order numbers = the id_order where the id_order_state = 12.

For example, in the ps_order_history table, the first record has an id_order_state of 12. But the status for id_order 1 has been updated to a 2, I want only the records where there hasn't been an update. I should only get the record for id_order 2. Did I make that clear enough?
(Table definitions and data listed below)

Here is my original SQL, but it is always returning id_order_history 1 even when it has been updated. Actually, I'm not even sure this is the most efficient way to do this. Please don't berate me for my SQL, JOINS are not one of my strong suits but, please, make suggestions for improvement.

SELECT o.id_order, 
       osl.`name`
FROM `ps_order_history` oh
INNER JOIN `ps_orders` o 
  ON ( o.`id_order` = oh.`id_order` )
INNER JOIN `ps_order_detail` od 
  ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os 
  ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl 
  ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
WHERE osl.`name` = 'Generation Error'

CREATE TABLE IF NOT EXISTS `ps_orders` (
  `id_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_carrier` int(10) unsigned NOT NULL,
  `id_lang` int(10) unsigned NOT NULL,
  `id_customer` int(10) unsigned NOT NULL,
  `id_cart` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id_order`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `ps_orders` (`id_order`, `id_carrier`, `id_lang`, 
                         `id_customer`, `id_cart`) 
                      VALUES (1, 2, 1, 2, 1),
                             (2, 2, 1, 2, 2);

CREATE TABLE IF NOT EXISTS `ps_order_history` (
  `id_order_history` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_employee` int(10) unsigned NOT NULL,
  `id_order` int(10) unsigned NOT NULL,
  `id_order_state` int(10) unsigned NOT NULL,
  `date_add` datetime NOT NULL,
  PRIMARY KEY (`id_order_history`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `ps_order_history` (`id_order_history`, `id_employee`, 
                                `id_order`, `id_order_state`, 
                                `date_add`) 
                         VALUES (1, 0, 1, 12, '2010-09-14 11:19:04'), 
                                (2, 0, 1, 2, '2010-09-15 19:31:25'), 
                                (3, 0, 2, 12, '2010-09-15 19:37:40');

CREATE TABLE IF NOT EXISTS `ps_order_state` (
  `id_order_state` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `invoice` tinyint(1) unsigned DEFAULT '0',
  PRIMARY KEY (`id_order_state`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `ps_order_state` (`id_order_state`, `invoice`) 
                      VALUES (1, 0), (2, 1), (3, 1), (4, 1), 
                             (5, 1), (6, 0), (7, 1), (8, 0), 
                             (9, 1), (10, 0),(11, 0),(12, 0);

CREATE TABLE IF NOT EXISTS `ps_order_state_lang` (
  `id_order_state` int(10) unsigned NOT NULL,
  `name` varchar(64) NOT NULL,
  `template` varchar(64) NOT NULL,
  `id_lang` INT(10) UNSIGNED NOT NULL
  UNIQUE KEY `order_state_lang_index` (`id_order_state`,`id_lang`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `ps_order_state_lang` (`id_order_state`, `name`, 
                                   `id_lang`, `template`) 
           VALUES (1, 'Awaiting cheque payment', 1, 'cheque'), 
                  (2, 'Payment accepted', 1, 'payment'), 
                  (3, 'Preparation in progress', 1, 'preparation'), 
                  (4, 'Shipped', 1, 'shipped'), 
                  (5, 'Delivered', 1, ''), 
                  (6, 'Canceled', 1, 'order_canceled'), 
                  (7, 'Refund', 1, 'refund'), 
                  (8, 'Payment error', 1, 'payment_error'), 
                  (9, 'On backorder', 1, 'outofstock'), 
                  (10, 'Awaiting bank wire payment', 1, 'bankwire'), 
                  (11, 'Awaiting PayPal payment', 1, ''), 
                  (12, 'Generation Error', 1, 'download_product_error');

Open in new window

0
 
LVL 26

Author Comment

by:EddieShipman
ID: 33692376
OOPS, need to remove this line:

INNER JOIN `ps_order_detail` od
  ON od.`id_order` = o.`id_order`
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 33692397
Also, need a comma after this line, in create code for ps_order_state_lang:
 `id_lang` INT(10) UNSIGNED NOT NULL
0
 
LVL 26

Author Comment

by:EddieShipman
ID: 33692620
Well, after getting my dummy data situated correctly and playing with it a while.
I came up with the SQL below. It returns the data I need but is it the most optimal?
BTW, it includes some other tables and columns that aren't in the dummy tables/data I posted above.
SELECT o.id_order
     , CONCAT( LEFT( c.`firstname` , 1 ) , ', ', c.`lastname` ) AS `customer`
     , osl.`name`
     , p.reference
     , pd.physically_filename as filename
FROM `ps_order_history` oh
INNER JOIN `ps_orders` o ON ( o.`id_order` = oh.`id_order` )
LEFT JOIN `ps_customer` c ON ( c.`id_customer` = o.`id_customer` )
INNER JOIN `ps_order_detail` od ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
LEFT JOIN `ps_product` p ON p.`id_product` = od.`product_id`
INNER JOIN ps_product_download pd ON pd.`id_product` = p.`id_product`
WHERE oh.`id_order` NOT IN (SELECT `id_order` 
                            FROM `ps_order_history` 
                            WHERE `id_order_state` <> 12)

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33696945
everything looks excellent accept for the WHERE.
Since you are working with `ps_order_history` AKA oh, you can specify directly that you want to return only `id_order_state` = 12
SELECT o.id_order
     , CONCAT( LEFT( c.`firstname` , 1 ) , ', ', c.`lastname` ) AS `customer`
     , osl.`name`
     , p.reference
     , pd.physically_filename as filename
FROM `ps_order_history` oh
INNER JOIN `ps_orders` o ON ( o.`id_order` = oh.`id_order` )
LEFT JOIN `ps_customer` c ON ( c.`id_customer` = o.`id_customer` )
INNER JOIN `ps_order_detail` od ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
LEFT JOIN `ps_product` p ON p.`id_product` = od.`product_id`
INNER JOIN ps_product_download pd ON pd.`id_product` = p.`id_product`
WHERE `id_order_state` = 12

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33696956
Also, If you are planning to "group by" you can replace WHERE with HAVING when you need to.
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33696981
possibly for even more speed we can subquery the `id_order_state` = 12:
SELECT o.id_order
     , CONCAT( LEFT( c.`firstname` , 1 ) , ', ', c.`lastname` ) AS `customer`
     , osl.`name`
     , p.reference
     , pd.physically_filename as filename
FROM 
(
     SELECT id_order, oh.`id_order_state`
     FROM `ps_order_history` 
     WHERE `id_order_state` = 12
) oh
INNER JOIN `ps_orders` o ON ( o.`id_order` = oh.`id_order` )
LEFT JOIN `ps_customer` c ON ( c.`id_customer` = o.`id_customer` )
INNER JOIN `ps_order_detail` od ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
LEFT JOIN `ps_product` p ON p.`id_product` = od.`product_id`
INNER JOIN ps_product_download pd ON pd.`id_product` = p.`id_product`

Open in new window

0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 33696993
ignore #33696981 there are errors.

fixed:
SELECT o.id_order
     , CONCAT( LEFT( c.`firstname` , 1 ) , ', ', c.`lastname` ) AS `customer`
     , osl.`name`
     , p.reference
     , pd.physically_filename as filename
FROM 
(
     SELECT id_order, `id_order_state`
     FROM `ps_order_history` 
     WHERE `id_order_state` = 12
) oh
INNER JOIN `ps_orders` o ON ( o.`id_order` = oh.`id_order` )
LEFT JOIN `ps_customer` c ON ( c.`id_customer` = o.`id_customer` )
INNER JOIN `ps_order_detail` od ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
LEFT JOIN `ps_product` p ON p.`id_product` = od.`product_id`
INNER JOIN ps_product_download pd ON pd.`id_product` = p.`id_product`

Open in new window

0
 
LVL 19

Accepted Solution

by:
NerdsOfTech earned 500 total points
ID: 33696999
fixed again :0

subquery version:
SELECT o.id_order
     , CONCAT( LEFT( c.`firstname` , 1 ) , ', ', c.`lastname` ) AS `customer`
     , osl.`name`
     , p.reference
     , pd.physically_filename as filename
FROM 
(
     SELECT `id_order`, `id_order_state`
     FROM `ps_order_history` 
     WHERE `id_order_state` = 12
) oh
INNER JOIN `ps_orders` o ON ( o.`id_order` = oh.`id_order` )
LEFT JOIN `ps_customer` c ON ( c.`id_customer` = o.`id_customer` )
INNER JOIN `ps_order_detail` od ON od.`id_order` = o.`id_order`
LEFT JOIN `ps_order_state` os ON os.`id_order_state` = oh.`id_order_state`
LEFT JOIN `ps_order_state_lang` osl ON (osl.`id_order_state` = oh.`id_order_state` AND osl.`id_lang` = 1)
LEFT JOIN `ps_product` p ON p.`id_product` = od.`product_id`
INNER JOIN ps_product_download pd ON pd.`id_product` = p.`id_product`

Open in new window

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Selecting specific rows 3 53
simple mysql statement 3 37
Create a Select Query and Populate a Table 3 48
mcrypt_create_iv() is deprecated 4 142
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now