Here I want to get the previous row balance value in to my field.
在这里,我想将前一行余额值输入到我的字段中。
The last id of customer_id '16' of balance is 200, but I want to get the previous ids value in to the field and this is my table
customer_id'16'的余额的最后一个id是200,但是我想把以前的id值输入到字段中,这是我的表
id order_id customer_id amount actual_amount paid_amount balance type
25 11 16 100.00 50.00 50.00 Cash
26 12 16 200.00 100.00 100.00 Cash
27 13 16 150.00 100.00 50.00 Cash
28 14 16 300.00 250.00 50.00 Cash
29 14 16 170.00 100.00 70.00 Cash
30 15 16 100 170.00 70.00 100.00 Cash
31 16 16 400 500.00 300.00 200.00 Cash
this is my model
这是我的模特
public function order_balance($order_code)
{
$this->db->join('services','payment.customer_id=services.customer_id','left');
$this->db->select('payment.*,payment.balance,payment.actual_amount,payment.customer_id');
$this->db->order_by('payment.id','desc');
$query = $this->db->get_where('payment',array('code' => $order_code));
return $query->previous_row();
}
This is my control:
这是我的控制:
public function final_payment($order_code)
{
$data['active_mn']='';
$data['result'] = $this->Account_model->order_balance($order_code);
$this->load->view('final_payment',$data);
}
My services table looks like this:
我的服务表如下所示:
id code customer_id particulars
11 ORD00011 16 phone
12 ORD00012 16 gdf
13 ORD00013 16 ghgfh
14 ORD00014 16 tv
15 ORD00015 16 ghfg
16 ORD00016 16 tv
17 ORD00017 16 gdfg
18 ORD00018 16 desk
19 ORD00019 16 gdf
My result should be like this:
我的结果应该是这样的:
id order_id customer_id amount actual_amount paid_amount balance type
31 16 16 400 500.00 300.00 100.00 Cash
2 个解决方案
#1
1
One option here would be to do a self join with the payment
table, joining on the customer_id
and order_id
columns. You can add another join function call and then use the result in your select. The following solution uses a raw query since Codeigniter does not seem to tolerate arithmetic in the join condition:
这里的一个选择是与支付表进行自联接,加入customer_id和order_id列。您可以添加另一个连接函数调用,然后在您的选择中使用结果。以下解决方案使用原始查询,因为Codeigniter似乎不能容忍连接条件中的算术:
public function order_balance($order_code)
{
$this->db->query("SELECT p1.*, p2.balance AS previous_balance FROM payment p1 INNER JOIN payment p2 ON p1.order_id = p2.order_id + 1 AND p1.customer_id = p2.customer_id LEFT JOIN services s ON p1.customer_id = s.customer_id ORDER BY p1.id DESC");
$query = $this->db->get_where('p1', array('code' => $order_code));
return $query->previous_row();
}
The query has the form:
查询具有以下形式:
SELECT p1.*, p2.balance AS previous_balance
FROM payment p1 INNER JOIN payment p2
ON p1.order_id = p2.order_id + 1 AND
p1.customer_id = p2.customer_id
LEFT JOIN services s
ON p1.customer_id = s.customer_id
ORDER BY p1.id DESC
#2
0
There are lots of ways of writng this. Here's one way. I don't know codeigniter, so you'll have to reverse engineer it...
有很多方法可以写这个。这是一种方式。我不知道codeigniter,所以你必须对它进行逆向工程......
SELECT a.id
, a.order_id
, a.customer_id
, a.amount
, a.actual_amount
, a.paid_amount
, b.balance
, a.type
FROM
( SELECT x.*
, MAX(y.id) prev
FROM payment x
JOIN payment y
ON y.id < x.id
AND y.customer_id = x.customer_id
GROUP
BY x.id
) a
JOIN payment b
ON b.id = a.prev
ORDER
BY id DESC LIMIT 1;
#1
1
One option here would be to do a self join with the payment
table, joining on the customer_id
and order_id
columns. You can add another join function call and then use the result in your select. The following solution uses a raw query since Codeigniter does not seem to tolerate arithmetic in the join condition:
这里的一个选择是与支付表进行自联接,加入customer_id和order_id列。您可以添加另一个连接函数调用,然后在您的选择中使用结果。以下解决方案使用原始查询,因为Codeigniter似乎不能容忍连接条件中的算术:
public function order_balance($order_code)
{
$this->db->query("SELECT p1.*, p2.balance AS previous_balance FROM payment p1 INNER JOIN payment p2 ON p1.order_id = p2.order_id + 1 AND p1.customer_id = p2.customer_id LEFT JOIN services s ON p1.customer_id = s.customer_id ORDER BY p1.id DESC");
$query = $this->db->get_where('p1', array('code' => $order_code));
return $query->previous_row();
}
The query has the form:
查询具有以下形式:
SELECT p1.*, p2.balance AS previous_balance
FROM payment p1 INNER JOIN payment p2
ON p1.order_id = p2.order_id + 1 AND
p1.customer_id = p2.customer_id
LEFT JOIN services s
ON p1.customer_id = s.customer_id
ORDER BY p1.id DESC
#2
0
There are lots of ways of writng this. Here's one way. I don't know codeigniter, so you'll have to reverse engineer it...
有很多方法可以写这个。这是一种方式。我不知道codeigniter,所以你必须对它进行逆向工程......
SELECT a.id
, a.order_id
, a.customer_id
, a.amount
, a.actual_amount
, a.paid_amount
, b.balance
, a.type
FROM
( SELECT x.*
, MAX(y.id) prev
FROM payment x
JOIN payment y
ON y.id < x.id
AND y.customer_id = x.customer_id
GROUP
BY x.id
) a
JOIN payment b
ON b.id = a.prev
ORDER
BY id DESC LIMIT 1;