CodeIgniter Forums
Named variables in sql query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: General Help (https://forum.codeigniter.com/forum-24.html)
+--- Thread: Named variables in sql query (/thread-79507.html)



Named variables in sql query - stepgr - 06-26-2021

HI,
I have this query which runs perfect on plain sql (phpmyadmin etc) :
Code:
SELECT
MONTHNAME (itd.DateOfshippment) AS Month
,COUNT(itm.Name) AS Operations
FROM ImportsMaster itm
INNER JOIN ImportsDetails itd on itm.ID  = itd.IDMSTR
WHERE itd.Market IS NOT NULL
AND itm.Trader LIKE "%TEXT%"
AND itd.DateOfshippment between :start and :end
GROUP BY MONTH(itd.DateOfshippment)
Now I'm trying to do the same in ci (3) but I need to pass start and end variables

from post data, I have the following code in my model :

Code:
Public function get_results () {
$sql = "SELECT
MONTHNAME (itd.DateOfshippment) AS Month
,COUNT(itm.Name) AS Operations
FROM ImportsMaster itm
INNER JOIN ImportsDetails itd on itm.ID  = itd.IDMSTR
WHERE itd.Market IS NOT NULL
AND itm.Trader LIKE "%TEXT%"
AND itd.DateOfshippment between :start: and :end:
GROUP BY MONTH(itd.DateOfshippment)"
Code:
$this->db->query($sql, [
            'start' => $this->input->post('start'),
            'end' => $this->input->post('end')
        ]);
}
According to documentation I need to enclose the named variables in colons but they're not
getting replaced by the actual data coming from the post data (which I can see if I echo the relevant named variable) .
I tried just to be sure to hard code the data in the code to check like this in the model :
Code:
$this->db->query($sql, [
            'start' => '2021-01-01',
            'end' => '2021-03-30'        ]);
But it also doesn't get replaced so there must be something else I'm missing here  but I can't figure out what.
Maybe I need to escape  the named variables somehow but I have tried escaping them with single quotes or ` ` but
no change .  I tried to use active record also but I cant make a proper query like the one I already have here.

George


RE: Named variables in sql query - InsiteFX - 06-26-2021

Try this, not tested but fixes some errors in your code see the quotes and semi-colons at the end.
PHP Code:
$sql 'SELECT
MONTHNAME (itd.DateOfshippment) AS Month
,COUNT(itm.Name) AS Operations
FROM ImportsMaster itm
INNER JOIN ImportsDetails itd on itm.ID  = itd.IDMSTR
WHERE itd.Market IS NOT NULL
AND itm.Trader LIKE "%TEXT%"
AND itd.DateOfshippment between :start: and :end:
GROUP BY MONTH(itd.DateOfshippment);'




RE: Named variables in sql query - stepgr - 06-26-2021

(06-26-2021, 01:51 AM)InsiteFX Wrote: Try this, not tested but fixes some errors in your code see the quotes and semi-colons at the end.
PHP Code:
$sql 'SELECT
MONTHNAME (itd.DateOfshippment) AS Month
,COUNT(itm.Name) AS Operations
FROM ImportsMaster itm
INNER JOIN ImportsDetails itd on itm.ID  = itd.IDMSTR
WHERE itd.Market IS NOT NULL
AND itm.Trader LIKE "%TEXT%"
AND itd.DateOfshippment between :start: and :end:
GROUP BY MONTH(itd.DateOfshippment);'


Thanks  but the profiler still returns the Confusedtart:  :end: in the query instead of actual values :

Code:
Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':start: and :end: GROUP BY MONTH(itd.DateOfshippment)' at line 8

SELECT MONTHNAME (itd.DateOfshippment) AS Month ,COUNT(itm.Name) AS Operations FROM ImportsMaster itm INNER JOIN ImportsDetails itd on itm.ID = itd.IDMSTR WHERE itd.Market IS NOT NULL AND itm.Trader LIKE "%TEXT%" AND itd.DateOfshippment between :start: and :end: GROUP BY MONTH(itd.DateOfshippment);

I think it should return the actual values from the post data  eg 2021-01-01 instead  of :start:  or not ?

Never mind I found a way to do it with active record actually and some php logic in between . I wanted also same
CASE WHEN conditions and finally it was much easier with active record
Thanks Anyway !!


RE: Named variables in sql query - InsiteFX - 06-26-2021

Try this one, no time to test right now busy.
You need to pass in the start and end strings for the dates to search.
PHP Code:
$sql 'SELECT
MONTHNAME (itd.DateOfshippment) AS Month
,COUNT(itm.Name) AS Operations
FROM ImportsMaster itm
INNER JOIN ImportsDetails itd on itm.ID  = itd.IDMSTR
WHERE itd.Market IS NOT NULL
AND itm.Trader LIKE "%TEXT%"
AND itd.DateOfshippment between "{$start}" and "{$end}"
GROUP BY MONTH(itd.DateOfshippment);'




RE: Named variables in sql query - stepgr - 06-29-2021

(06-26-2021, 05:41 AM)InsiteFX Wrote: Try this one, no time to test right now busy.
You need to pass in the start and end strings for the dates to search.
PHP Code:
$sql 'SELECT
MONTHNAME (itd.DateOfshippment) AS Month
,COUNT(itm.Name) AS Operations
FROM ImportsMaster itm
INNER JOIN ImportsDetails itd on itm.ID  = itd.IDMSTR
WHERE itd.Market IS NOT NULL
AND itm.Trader LIKE "%TEXT%"
AND itd.DateOfshippment between "{$start}" and "{$end}"
GROUP BY MONTH(itd.DateOfshippment);'


Just tested this also , not working either  here is my full code in the model :

Code:
            $sql = 'SELECT
            MONTHNAME (`itd`.`DateOfshippment`) AS `Months`,
            COUNT(`itm`.`Name`) AS `Operations`
            FROM `ImportsMaster` `itm`
            INNER JOIN `ImportsDetails` `itd` on `itm`.`ID`  = `itd`.`IDMSTR`
            WHERE `itd`.`Market` IS NOT NULL
            AND `itm`.`Trader` LIKE "{$trader}"
            AND `itd`.`DateOfshippment` between "{$start}" and "{$end}"
            GROUP BY MONTH(`itd`.`DateOfshippment`)';
            $this->db->query($sql, [
                'start' => $this->input->post('start'),
                'end' => $this->input->post('end'),
                'trader' => $this->input->post('trader')
            ]);

I've tried your suggestions as well as numerous other  ones , the blinded values are not getting replaced
no matter what .
Also the named bindings documentation seems to be referencing a very old version of codeigniter . In the query builder
class I can only find the documentation about bindings which is very different .
Anyway I can't be the only one here with a similar problem , nobody else in here ever wanted to create a dynamic query using
raw sql ? Some other similar queries I've managed to convert to  query builder but this one uses MONTHNAME and COUNT
to return some values which I haven't figure out yet if I can do in a single query as in raw sql.


RE: Named variables in sql query - includebeer - 06-29-2021

If you try to insert variable in a string you need to use double quotes. Single quotes will just ignore the dollar sign and take it as literal text.


RE: Named variables in sql query - php_rocs - 06-30-2021

@stepgr ,

Did you try the ? instead of the value names to see if that makes a difference? Also, what version of CI are you using?