[El Forum]

[eluser]maria clara[/eluser]
hi,
i encounter this error when im trying to add a new data in my jqgrid. the data was shown but it shows this in my console.

Code:

Error Number: 1064</p><p>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'return) as `return`
FROM (`erp_ar_sales`)
WHERE `client_id` IS NULL
AND `status`' at line 1</p><p>SELECT SUM(gross) as sales, SUM(paid) as payment, SUM(credit) as credit, SUM(debit) as debit, SUM(return) as `return`
FROM (`erp_ar_sales`)
WHERE `client_id` IS NULL
AND `status` != '2'
AND `is_deleted` = '0'</p>

hope someone can help me..


thanks in advance,
maria

[El Forum]

[eluser]whobutsb[/eluser]
Possibly remove the single quotes around 'return' at the end, in your model. Can you post your model function?

[El Forum]

[eluser]maria clara[/eluser]
here's the function in my MODEL:

Code:

function updateCustomerBalance($data)
    {
        $this->db->select('SUM(gross) as sales, SUM(paid) as payment, SUM(credit) as credit, SUM(debit) as debit, SUM(return) as `return`');
        $this->db->where('client_id', $data['client_id']);
        $this->db->where('status !=', '2');
        $this->db->where('is_deleted', '0');
        $sales = $this->db->get('ar_sales')->row_array();
        
        $sales['balance'] = $sales['sales'] - $sales['payment'] - $sales['credit'] + $sales['debit'] - $sales['return'];
        
        $this->db->where('cust_id', $data['client_id']);
        $this->db->update('maint_customers', $sales);
    }

and also im having this error

Code:

<p>Message:  Undefined index:  client_id</p>
<p>Filename: models/ccm_receipt_db.php</p>
<p>Line Number: 292</p>

and this points to the database error i encountered

[El Forum]

[eluser]WebsiteDuck[/eluser]
You need to put backticks around the first return like so:

Code:

$this->db->select('SUM(gross) as sales, SUM(paid) as payment, SUM(credit) as credit, SUM(debit) as debit, SUM(`return`) as `return`');

Also $data['client_id'] doesn't exist, per your error

[El Forum]

[eluser]maria clara[/eluser]
i have put this:

Code:

$this->db->select("SUM(gross) as sales, SUM(paid) as payment, SUM(credit) as credit, SUM(debit) as debit, SUM(`return`) as `return`");

and it doesn't show the database error log.
but i have this in my console message

Code:

<h4>A PHP Error was encountered</h4>

<p>Severity: Notice</p>
<p>Message:  Undefined index:  client_id</p>
<p>Filename: models/ccm_receipt_db.php</p>
<p>Line Number: 292</p>

<p>Severity: Notice</p>
<p>Message:  Undefined index:  client_id</p>
<p>Filename: models/ccm_receipt_db.php</p>
<p>Line Number: 299</p>

[El Forum]

[eluser]John_Betong[/eluser]
 
Try this and see what is in your $data array()

Code:

function updateCustomerBalance($data)
{
  echo '<pre>';
    print_r($data);
  echo '</pre>';
  die();
  ...
  ...
  ...

 
 
 

[El Forum]

[eluser]Dyllon[/eluser]
[quote author="maria clara" date="1263288589"]i have put this:

Code:

$this->db->select("SUM(gross) as sales, SUM(paid) as payment, SUM(credit) as credit, SUM(debit) as debit, SUM(`return`) as `return`");

and it doesn't show the database error log.
but i have this in my console message

Code:

<h4>A PHP Error was encountered</h4>

<p>Severity: Notice</p>
<p>Message:  Undefined index:  client_id</p>
<p>Filename: models/ccm_receipt_db.php</p>
<p>Line Number: 292</p>

<p>Severity: Notice</p>
<p>Message:  Undefined index:  client_id</p>
<p>Filename: models/ccm_receipt_db.php</p>
<p>Line Number: 299</p>

[/quote]

The error is really self explanatory, your $data array does not contain a key 'client_id' but you're referencing it.

Check your controller, why are you expecting a 'client_id' and why isn't one being supplied? and further more why not add some error checking if there's a possibility the 'client_id' could be missing?

[El Forum]

[eluser]maria clara[/eluser]
[quote author="John_Betong" date="1263289532"] 
Try this and see what is in your $data array()

Code:

function updateCustomerBalance($data)
{
  echo '<pre>';
    print_r($data);
  echo '</pre>';
  die();
  ...
  ...
  ...

 
 
 [/quote]


i have tried it and it shows this..

Code:

</div><pre>Array
(
    [tran_no] => 5343
    [inv_total] =>
    [pay_total] => 343
    [others_total] =>
    [company_id] => 1
)
</pre>

[El Forum]

[eluser]WebsiteDuck[/eluser]
[quote author="maria clara" date="1263290103"]

Code:

</div><pre>Array
(
    [tran_no] => 5343
    [inv_total] =>
    [pay_total] => 343
    [others_total] =>
    [company_id] => 1
)
</pre>

[/quote]

There is no client_id in your $data array

[El Forum]

[eluser]maria clara[/eluser]
i commented for a while the $data['client_id']

Code:

$this->db->where('client_id'/*, $data['client_id']*/);
        $this->db->where('status !=', '2');
        $this->db->where('is_deleted', '0');
        $sales = $this->db->get('ar_sales')->row_array();
        
        $sales['balance'] = $sales['sales'] - $sales['payment'] - $sales['credit'] + $sales['debit'] - $sales['return'];
        
        $this->db->where('cust_id'/*, $data['client_id']*/);
        $this->db->update('maint_customers', $sales);

and now it doesn't show the error. i still figuring out where can that be placed??