php—如何更新数据库所有行上的特定列

chhqkbe1  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(310)

我必须更新opcash列当用户在上一个日期创建一个新条目时，我必须更新上一个日期opcash列和今天的opcash列，其中opcash是贷方总金额-借方总金额的总和..并且当用户在贷方列输入时，opcash必须与贷方总金额相加，并且必须当用户在借记栏中输入金额时，该过程将持续所有天..我的问题是，当用户按正确的顺序进行操作时，opcash值是正确的..即如果用户在连续的一天创建一个分录，即如果用户在2018年12月25日、2018年12月26日创建一个分录，2018年12月27日..如果用户在2018年12月26日创建条目，并且如果用户在2018年12月25日创建条目，则opcash值在2018年12月25日更新，但在2018年12月26日未更新..请帮助我在用户在前一天创建新条目时更改opcash值。。

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');
}

日记本

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
mysqlphpcodeigniter

2条答案

按热度按时间
rdlzhqv9

rdlzhqv91#

我创建了一个sqlfiddle来展示如何获得与daytot表相同的结果，而不必维护该表。它还将日期列显示为日期类型，而不是varchar。
通过在表中使用日期类型而不是varchar，可以避免将来可能出现的一些问题。它还允许您按日期正确排序和选择、选择日期范围等。它还消除了与何时将行插入daybook表有关的任何问题，并处理对daybook表的更新和删除。
sql小提琴
mysql 5.6架构设置：

CREATE TABLE `daybook` (
      `recno` int(5) NOT NULL,
      `date` DATE 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, '2018/12/24', '1', '1', 'account', 'details', 500, 'c', 0, 0, '', '', '', '', '', 0),
    (440, '2018/12/24', '1', '1', 'praveen accounts', 'laavanya accounts', 500, 'c', 0, 0, '', '', '', '', '', 0),
    (441, '2018/12/25', '1', '1', 'praveen kumar accounts', 'praveen kumar accounts', 1000, 'c', 0, 0, '', '', '', '', '', 0),
    (442, '2018/12/25', '1', '1', 'abcdef', 'ghijklm', 100, 'd', 0, 0, '', '', '', '', '', 0),
    (443, '2018/12/25', '1', '1', 'xxxxxx', 'yyyyyyy', 100, 'c', 0, 0, '', '', '', '', '', 0),
    (444, '2018/12/24', '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;

-- 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` DATE 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
('2018/12/24', 76, 2000, 0, 2000, 0),
('2018/12/25', 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：

SELECT 
  `date` as `tdate`,
  SUM(IF(`dc` = 'c',`amount`,0)) as `total_credit`,
  SUM(IF(`dc` = 'd',`amount`,0)) as `total_debit`,
  SUM(IF(`dc` = 'c',`amount`,0)) - SUM(IF(`dc` = 'd',`amount`,0)) as `opcash`,
  0 as `clcash`
FROM `daybook`
GROUP BY `date`

结果：

|      tdate | total_credit | total_debit | opcash | clcash |
|------------|--------------|-------------|--------|--------|
| 2018-12-24 |         2000 |           0 |   2000 |      0 |
| 2018-12-25 |         1100 |         100 |   1000 |      0 |
赞(0）分享  回复(0）举报  2021-06-17
wixjitnu

wixjitnu2#

尝试更改查询中的日期格式。表中的日期格式通常为y-m-d：

$newDate = date("Y-m-d", strtotime($date));
赞(0）分享  回复(0）举报  2021-06-17
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com