Named variables in sql query

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

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);'; 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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 tart:  :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 !!

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);'; 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

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.

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.

CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/

@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?