+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forumdisplay.php?fid=21)
+--- Thread: [solved!] A Database Error Occurred (/showthread.php?tid=26347)
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[eluser]whobutsb[/eluser]
Possibly remove the single quotes around 'return' at the end, in your model. Can you post your model function?
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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>[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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();
...
...
...
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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>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?
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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>[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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>There is no client_id in your $data array
[solved!] A Database Error Occurred - El Forum - 01-11-2010
[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??