[solved!] A Database Error Occurred

#1
[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

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

#3
[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

#4
[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

#5
[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>

#6
[eluser]John_Betong[/eluser]
&nbsp;
Try this and see what is in your $data array()
Code:
function updateCustomerBalance($data)
{
  echo '<pre>';
    print_r($data);
  echo '</pre>';
  die();
  ...
  ...
  ...
&nbsp;
&nbsp;
&nbsp;

#7
[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?

#8
[eluser]maria clara[/eluser]
[quote author="John_Betong" date="1263289532"]&nbsp;
Try this and see what is in your $data array()
Code:
function updateCustomerBalance($data)
{
  echo '<pre>';
    print_r($data);
  echo '</pre>';
  die();
  ...
  ...
  ...
&nbsp;
&nbsp;
&nbsp;[/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>

#9
[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

#10
[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??


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.