CodeIgniter Forums
Select Query Error - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Select Query Error (/showthread.php?tid=73846)

Select Query Error - lfmluis - 06-12-2019

Hi everyone, i have a problem with a select query, 


Code:
$where = "SELECT FORMAT(SUM(payed), 2) AS payedAmount FROM client_credit WHERE creditStatus = 2 AND idperson = '"
           .$post_data['idperson']. "' AND contractDate
       BETWEEN '"
           . $startDate . "' AND '" . $endDate . "'";
$query = $this->db->query($where);
       if ($query->num_rows() > 0) {
           return $query->result();
       } else {
           return null;
       }

But i get a boolean result, $query = false, and when i execute the same query in mysql console i get the right result, but here i get boolean.

Thank you. Confused

RE: Select Query Error - jreklund - 06-12-2019

Did you add an echo $where; to get the generated SQL or did you just guess what it actually generated and run it in MySQL console?

Also, your code are vulnerable to SQL Injection, you should fix that ASAP!
https://www.codeigniter.com/user_guide/database/queries.html#query-bindings
https://en.wikipedia.org/wiki/SQL_injection

RE: Select Query Error - Wouter60 - 06-12-2019

Insert these 2 lines just for testing purposes after $query = $this->db->query($where);
PHP Code:
echo $this->db->last_query();
die(); 

This will echo the query like it's being sent to MySQL.
Check if this is what you expected.

RE: Select Query Error - hc-innov - 06-12-2019

are you sure your idperson is a string in your database??
change:

PHP Code:
$where = "SELECT FORMAT(SUM(payed), 2) AS payedAmount FROM client_credit WHERE creditStatus = 2 AND idperson = '"
           .$post_data['idperson']. "' AND contractDate
       BETWEEN '"
           . $startDate . "' AND '" . $endDate . "'"; 
to
PHP Code:
$where = "SELECT FORMAT(SUM(payed), 2) AS payedAmount FROM client_credit WHERE creditStatus = 2 AND idperson = "
           .$post_data['idperson']. " AND contractDate
       BETWEEN '"
           . $startDate . "' AND '" . $endDate . "'";