Check and update the row depending on same or different value

[dishadcruze]

I am trying to update the status if all the rows value is set to same value.

 

my query like this

$ord = "SELECT * FROM plitems WHERE pur_order_id='".$order_id."'";
$ord1 = mysqli_query($con, $ord);

while($ord2 = mysqli_fetch_array($ord1)) {

if(($ord2['invoice_status']=='Invoiced')) 
{
$m5 = mysqli_query($con, "UPDATE pur_orders SET status='Invoiced' WHERE pur_order_id='".$order_id."'");

}
else
{
$m5 = mysqli_query($con, "UPDATE pur_orders SET status='Partial' WHERE pur_order_id='".$order_id."'");

}

But here i have 2 line items in plitems table, 1st line item status is Invoiced and 2nd line item status is Partial, But still it is setting pur_orders status as Invoiced instead of Partial

 

I tried doing like this

UPDATE pur_orders AS p CROSS JOIN ( SELECT MAX(invoice_status) AS invoice_status FROM plitems WHERE pur_order_id = '".$order_id."' HAVING COUNT(DISTINCT invoice_status) = 1) AS t SET p.status = t.invoice_status WHERE p.pur_order_id = '".$order_id."'

Still its noy working

Edited May 26, 2017 by dishadcruze

[ginerjm]

Looks like a data normalization problem here.  If you have line items that belong to an invoice and you mark that invoice as complete (invoiced?) then logically that says that all of the line items have been invoiced.  If you don't want that to hold true (as you are showing), then you need to add something to to your structure to allow that to happen.

 

Think about it.  In a business sense, not a php sense.

[benanamen]

I agree with @ginerjm. It sounds like a bad DB design. Best thing to do is start at the beginning. Post an SQL dump of your DB schema along with a few sample records and we will review it.

[ginerjm]

Well - Benanamen will review it.  I'm not into doing other's design work.

[dishadcruze]

CREATE TABLE IF NOT EXISTS `pur_orders` (
  `order_id` int(100) NOT NULL AUTO_INCREMENT,
  `pur_order_id` varchar(12) NOT NULL,
  `vendor_id` int(100) NOT NULL,
  `contact_person` varchar(20) NOT NULL,
  `reference` varchar(255) NOT NULL,
  `t_c` char(200) NOT NULL,
  `payment` varchar(100) NOT NULL,
  `validity` int(10) NOT NULL,
  `date_created` date NOT NULL,
  `last_modified` date NOT NULL,
  `authorise` varchar(15) NOT NULL,
  `status` varchar(10) NOT NULL,
  `user_id` int(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `pur_orders`
--

INSERT INTO `pur_orders` (`order_id`, `pur_order_id`, `vendor_id`, `contact_person`, `reference`, `t_c`, `payment`, `validity`, `date_created`, `last_modified`, `authorise`, `reject_comment`, `status`, `user_id`, `convert_invoice`) VALUES
(3, 'PO003', 10, '7', 'Test for Purchase Invoice', 'test TC', 'Test', 15, '2017-05-25', '2017-05-25', 'Yes', '', 'Open', 1, 'No'),
(4, 'PO004', 2, '1', 'Purchase Order For Test Vendor ', 'dfgdfgfdg', 'Test', 5, '2017-05-25', '2017-05-25', 'Yes', '', 'Invoiced', 1, 'No'),
(5, 'PO005', 10, '7', 'asfsdafsdfsdfsdfsdf', 'dfgdfgfdg', 'Test', 5, '2017-05-27', '2017-05-27', 'Yes', '', 'Open', 1, 'No');




CREATE TABLE IF NOT EXISTS `plitems` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `pur_order_id` varchar(20) NOT NULL,
  `item` varchar(100) NOT NULL,
  `uom` varchar(10) NOT NULL,
  `description` text NOT NULL,
  `quantity` int(50) NOT NULL,
  `selling_price` float(10,2) NOT NULL,
  `discount` double NOT NULL,
  `tax` int(10) NOT NULL,
  `tax_amount` float(10,2) NOT NULL,
  `total` float(10,2) NOT NULL,
  `invoice_status` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

--
-- Dumping data for table `plitems`
--

INSERT INTO `plitems` (`id`, `pur_order_id`, `item`, `uom`, `description`, `quantity`, `selling_price`, `discount`, `tax`, `tax_amount`, `total`, `invoice_status`) VALUES
(5, 'PO003', 'ITM0019', 'NOS', 'sdfsdfds\r\n', 30, 120.00, 1, 1, 178.20, 3564.00, 'Invoiced'),
(6, 'PO003', 'ITM0021', 'NOS', 'Topi', 25, 100.00, 1, 2, 49.50, 2475.00, 'Invoiced'),
(7, 'PO004', 'ITM0040', 'NOS', '18.5" ', 20, 550.00, 1, 1, 544.50, 10890.00, 'Invoiced'),
(8, 'PO004', 'ITM0032', 'kg', 'djflksjfkdlj', 35, 120.00, 1, 2, 83.16, 4158.00, 'Invoiced'),
(9, 'PO005', 'ITM0039', 'NOS', 'Western Digital', 25, 800.00, 1, 1, 990.00, 19800.00, NULL),
(10, 'PO005', 'ITM003', 'kg', 'dsfsdfsdfsdfdsf', 18, 2000.00, 1, 2, 712.80, 35640.00, NULL);

@benanamen

My tables. Thanks

[benanamen]

Is this from any third party application or was this custom written for you?

[dishadcruze]

I have written it.

[benanamen]

Can you pm me a zip of the whole application and the full SQL?

[dishadcruze]

Sorry, i work for an organization there it wont allow me to post the complete application.

[Jacques1]

There's no reason to keep the status per item and then again the total status per order. This is in fact duplicate data which can lead to conflicting information (the item status and the order status may fall out of sync due to bugs, human error etc.).

 

Just calculate the status whenever needed. For example, count the items per status:

SELECT
    status,
    COUNT(*) AS item_count
FROM
    plitems
WHERE
    pur_order_id = ?    -- do - not - insert - raw - PHP - values - into - query - strings
GROUP BY
    status
;

This will give you something like this:

status   | item_count
---------+------------
Open     | 2
Invoiced | 8

Now you can figure out what the total status is.

 

Unfortunately, your tables are a hot mess. You have no foreign keys, you use unrestricted VARCHARs for all kinds of things, and you additionally use the status NULL (what?) when you probably mean “Open”.

 

Fix the mess:

• Booleans belong into BOOLEAN fields, not VARCHARs that say “Yay”/“Nay”
• The status belongs into an ENUM field -- or an integer field which is a foreign key to an external status table
• Using FLOATs for money is a really, really bad idea, because floats cannot accurately store all decimal number representations and will have rounding errors in calculations -- something you definitely don't want. You need a fixed-precision DECIMAL
• References to other tables (user_id, vendor_id etc.) must be explicitly enforced with foreign keys, not just implicitly assumed
• Why are you using the pur_order_id VARCHAR instead of the much more appropriate order_id to link the tables? Can two orders have the same pur_order_id?

Since your current tables accept pretty much any data, you may run into nonsensical information while repairing the database -- that will have to be fixed as well, of course.

 

Also, if there's anybody in your organization who knows a bit about designing a proper database, now is the time to get his/her help.