getting unknown column in where clause

Question

am getting an error like

Unknown column 'code' in 'where clause'
SELECT * FROM `payment` WHERE `code` = 'ORD00023'

even though i had used joined method for joining of payment table and services table.Here iam finding the solution of previous row value but its not getting.

this is my model

public function order_balance($order_code)
{
$this->db->query("
SELECT p1.*
      , p2.balance AS previous_balance 
  FROM payment p1 
  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('payment p1', array('code' => $order_code));
return $query;
}

this is my table payment

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 table services

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  

Here i have joined the payment table and services table but still not getting

see my table

id  order_id    customer_id     amount  actual_amount   paid_amount     balance     type
50  31             16           650         750.00        250.00        500.00      Cash
51  1              16           100         600.00        300.00        300.00      Cash
52  2              16           100         400.00        200.00        200.00      Cash
53  3              16           800         1000.00       600.00        400.00      Cash
54  4              15           400         400.00        300.00        100.00      Cash
55  5              15           500         600.00        575.00        25.00       Cash
56  6              16           350         750.00        600.00        150.00      Cash

in this table the customer_id of 16 having the previous row value of balance 25 and am getting like this but i want the last customer_id 16 balance value as the previous customer_id 16 value. for example my result should look like this

id     order_id    customer_id   amount   actual_amount   paid_amount   balance     type
56       6         16          350          750.00        600.00        400.00      Cash

Answer 1

This changes i had made in my model and got the result

public function order_balance($order_code)
{

  $query=$this->db->query("SELECT payment.*, t3.balance AS pre_balance FROM payment 
  INNER JOIN (SELECT max(id) AS id, customer_id FROM payment GROUP BY customer_id) t ON payment.id = t.id AND payment.customer_id = t.customer_id
  LEFT JOIN (SELECT t1.* FROM payment t1
  WHERE (
    SELECT count(*) FROM payment t2 WHERE t1.customer_id = t2.customer_id AND t1.id <= t2.id
) <= 2
AND t1.id not IN (SELECT max(id) FROM payment GROUP BY customer_id)) t3 ON payment.customer_id = t3.customer_id LEFT JOIN services s ON payment.customer_id = s.customer_id AND s.code = ? ", array($order_code)); 
 return $query;
}

Answer 2

This is showing error because you are missing alias of table name, in which column code is available. replace code

$query = $this->db->get_where('payment p1', array('code' => $order_code));

with

$query = $this->db->get_where('payment p1', array('services.code' => $order_code));

or

$query = $this->db->get_where('payment p1', array('s.code' => $order_code));

Answer 3

Its because you are writing two different query

To see add $this->db->last_query() after both. You will see two queries are executing.

//1st query

$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");
echo  $this->db->last_query();

//2nd query
$query = $this->db->get_where('payment p1', array('code' => $order_code));
echo  $this->db->last_query();

both are independent from each other.

Try this

$query = $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 
where code='".$order_code."'  ORDER BY p1.id DESC");

OR

$this->db->select('p1.*, p2.balance AS previous_balance');
$this->db->from('payment p1');
$this->db->join('payment p2','p1.order_id = p2.order_id + 1 AND p1.customer_id 
= p2.customer_id','');
$this->db->join('services s','p1.customer_id = s.customer_id','left');
$this->db->where('s.code',$order_code);
$this->db->order_by('p1.id', 'DESC');
$query = $this->db->get();

Answer 4

Did you want this?

$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 AND s.code = ? ORDER BY p1.id DESC", array($order_code));