Question posted in 
I have to update the column opcash when the user creates a new entry on the previous date i have to update the prev date opcash column and also today opcash column where opcash is the sum of total credit amount – sum of total debit amount..And that opcash have to be added with total credit amount when the user enters on credit column and have to be subtracted when the user enters the amount on debit column the process continue for all the days..My problem is the opcash value is correct when the user proceeds with correct order..that is if the user creates a entry on a continous day that is if the user creates a entry on 25-12-2018,26-12-2018,27-12-2018..If the user creates a entry on 26-12-2018 and if the user creates a entry on 25-12-2018 the opcash value is updated on 25-12-2018 but not updated on 26-12-2018..Please help me to change the opcash value when the user create a new entry on the previous day..
public function insert()
{
$session_data = $this->session->userdata('logged_in');
$data['username'] = $session_data['username'];
$dc=$this->input->post('dc');
$startdate = $this->input->post('TDate');
$date = str_replace('/', '-', $startdate);
$newDate = date("d/m/Y", strtotime($date));
if($dc=='c'){
$amount=$this->input->post('credit1');
}
else if ($dc=='d') {
$amount=$this->input->post('debit');
}
$data=array(
'date'=>$newDate,
'code' =>$this->input->post('TName'),
'project' =>$this->input->post('TName'),
'part' =>$this->input->post('part1'),
'part1' =>$this->input->post('part2'),
'dc'=>$this->input->post('dc'),
'amount'=>$amount,
);
$this->db->insert('daybook',$data);
this->db->where('dc','c');
$this->db->select_sum('amount');
$total_credit= $this->db->get_where('daybook',array('date' => $newDate))-
>row_array();
$this->db->where('dc','d');
$this->db->select_sum('amount');
$total_debit= $this->db->get_where('daybook',array('date' => $newDate))-
>row_array();
$prev_date = date('d/m/Y', strtotime('-1 day'));
$result2 = $this->db->query("SELECT opcash FROM daytot WHERE
tdate='$newDate'")->row_array();
$dc=$this->input->post('dc');
$total_credit1=0;
$total_debit1=0;
$opcash=0;
if($dc=='d')
{
$amount=$this->input->post('debit');
$opcash=$result2['opcash']-$amount;
}
if($dc=='c')
{
$amount=$this->input->post('credit1');
$opcash=$result2['opcash']+$amount;
}
$data1 = array(
'tdate'=>$newDate,
'total_credit'=>$total_credit['amount'],
'total_debit'=>$total_debit['amount'],
'opcash'=>$opcash,
);
$data2 = array(
'tdate'=>$newDate,
'total_credit'=>$total_credit['amount'],
'total_debit'=>'0',
'opcash'=>$this->input->post('amount')+$result2['opcash'],
);
$data3 = array(
'tdate'=>$newDate,
'total_credit'=>'0',
'total_debit'=>$total_debit['amount'],
'opcash'=>$result2['opcash']-$this->input->post('amount'),
);
$startdate = $this->input->post('TDate');
$date = str_replace('/', '-', $startdate);
$newDate = date("d/m/Y", strtotime($date));
$objQuery = $this->db->get_where('daytot', array('tdate' => $newDate));
if ($objQuery->num_rows() > 0) { //UPDATE
$this->db->where('tdate', $newDate);
$this->db->update('daytot', $data1);
}
else{
$dc=$this->input->post('dc');
if ($dc=='c') {
$this->db->insert('daytot', $data2);
}
if ($dc=='d') {
$this->db->insert('daytot', $data3);
}
}
$this->session->set_flashdata('Add', 'Inserted Successfully');
redirect('BookKeeping/daybook','refresh');
}
Daybook
—
Table structure for table `daybook`
--
CREATE TABLE `daybook` (
`recno` int(5) NOT NULL,
`date` varchar(15) NOT NULL,
`code` varchar(10) NOT NULL,
`project` varchar(10) NOT NULL,
`part` varchar(255) NOT NULL,
`part1` varchar(255) NOT NULL,
`amount` int(5) NOT NULL,
`dc` varchar(5) NOT NULL,
`ledreno` int(10) NOT NULL,
`vouno` int(10) NOT NULL,
`pvouno` varchar(100) NOT NULL,
`type` varchar(100) NOT NULL,
`mark` varchar(100) NOT NULL,
`slno` varchar(100) NOT NULL,
`shift` varchar(100) NOT NULL,
`pacno` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `daybook`
--
INSERT INTO `daybook` (`recno`, `date`, `code`, `project`, `part`, `part1`, `amount`, `dc`, `ledreno`, `vouno`, `pvouno`, `type`, `mark`, `slno`, `shift`, `pacno`) VALUES
(439, '24/12/2018', '1', '1', 'account', 'details', 500, 'c', 0, 0, '', '', '', '', '', 0),
(440, '24/12/2018', '1', '1', 'praveen accounts', 'laavanya accounts', 500, 'c', 0, 0, '', '', '', '', '', 0),
(441, '25/12/2018', '1', '1', 'praveen kumar accounts', 'praveen kumar accounts', 1000, 'c', 0, 0, '', '', '', '', '', 0),
(442, '25/12/2018', '1', '1', 'abcdef', 'ghijklm', 100, 'd', 0, 0, '', '', '', '', '', 0),
(443, '25/12/2018', '1', '1', 'xxxxxx', 'yyyyyyy', 100, 'c', 0, 0, '', '', '', '', '', 0),
(444, '24/12/2018', '1', '1', 'abcdef', 'ghijklm', 1000, 'c', 0, 0, '', '', '', '', '', 0);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `daybook`
--
ALTER TABLE `daybook`
ADD PRIMARY KEY (`recno`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `daybook`
--
ALTER TABLE `daybook`
MODIFY `recno` int(5) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=445;COMMIT;
Daytot
-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 26, 2018 at 07:37 AM
-- Server version: 10.1.25-MariaDB
-- PHP Version: 5.6.31
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `ci_fina`
--
-- --------------------------------------------------------
--
-- Table structure for table `daytot`
--
CREATE TABLE `daytot` (
`tdate` varchar(15) NOT NULL,
`id` int(11) NOT NULL,
`total_credit` int(11) NOT NULL,
`total_debit` int(11) NOT NULL,
`opcash` int(11) NOT NULL,
`clcash` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `daytot`
--
INSERT INTO `daytot` (`tdate`, `id`, `total_credit`, `total_debit`, `opcash`, `clcash`) VALUES
('24/12/2018', 76, 2000, 0, 2000, 0),
('25/12/2018', 77, 1100, 100, 2000, 0);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `daytot`
--
ALTER TABLE `daytot`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `daytot`
--
ALTER TABLE `daytot`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=78;COMMIT;
[1]: https://i.stack.imgur.com/REU36.png
[2]: https://i.stack.imgur.com/fVagp.png
2
Answers
Try to change the date format in your query. Normally the date format in the table is Y-m-d:
I created a SQLFiddle to show how you can get the same results as your daytot table without having to maintain that table. It also shows the date columns as DATE type instead of VARCHAR.
By using the DATE type in your table instead of the VARCHAR, you prevent several possible problems in the future. It also allows you to sort and select by date correctly, to select date ranges, etc. It also eliminates any problems related to when rows are inserted into the daybook table, and handles updates and deletes to the daybook table.
SQL Fiddle
MySQL 5.6 Schema Setup:
Query 1:
Results: