Jump to content

Check and update the row depending on same or different value


dishadcruze

Recommended Posts

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 :(

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.