Passing arguments to Prepared Statement

Passing arguments to Prepared Statement - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Passing arguments to Prepared Statement (/showthread.php?tid=92265)

Passing arguments to Prepared Statement - Pardner - 01-02-2025

I am converting an old website to ci4 framework. the old site used flags and filters to build a prepared statement to search a products table. I found the prepared query doc, but I am having trouble passing additional arguments into the prepared function. For example if a var $flag was used to toggle whether to search on title only or full table :

Code:
        $search = $this->request->getVar("search");

        // test flag

        $flag = 1;

        $pQuery = $this->db->prepare(static function ($db) {

            $sql = "SELECT * FROM tblproducts";

            if(($flags&1)!=0){

                 $sql .=" WHERE title LIKE ?";

            } else {

                 $sql .=" WHERE CONCAT(title,mfrdesc,catnum,categoryname,mfrname) LIKE ?";

            }

            return (new Query($db))->setQuery($sql);

        });

        $result = $pQuery->execute($search);

How can I pass the $flag variable into the prepared statement?

I've tried:

Code:
$search = $this->request->getVar("search");

// test flag

$flag = 1;

$pQuery = $this->db->prepare(static function ($db,$flag) {

    $sql = "SELECT * FROM tblproducts";

    if(($flags&1)!=0){

        $sql .=" WHERE title LIKE ?";

    } else {

        $sql .=" WHERE CONCAT(title,mfrdesc,catnum,categoryname,mfrname) LIKE ?";

    }

    return (new Query($db))->setQuery($sql);

});

$result = $pQuery->execute($search);

but that causes "Too few arguments to function App\\Controllers\\ApiController::App\\Controllers\\{closure}()..." error

I've also tried

Code:
$search = $this->request->getVar("search");

// test flag

$flag = 1;

$pQuery = $this->db->prepare(static function ($db) {

    $sql = "SELECT * FROM tblproducts";

    if(($flags&1)!=0){

        $sql .=" WHERE title LIKE ?";

    } else {

        $sql .=" WHERE CONCAT(title,mfrdesc,catnum,categoryname,mfrname) LIKE ?";

    }

    return (new Query($db))->setQuery($sql);

},["flag"=>$flag]);

$result = $pQuery->execute($search);

but that does not pass the $flag into the static function.

There are additional filters and flags that need to be implement, so it would be great if I could create the SQL statement and pass it into the prepared function.

Any help would be greatly appreciated.
~Pardner

RE: Passing arguments to Prepared Statement - michalsn - 01-02-2025

This is how you have to pass the parameter:

PHP Code:
$pQuery = $this->db->prepare(static function ($db) use ($flag) {
    // ...
}); 

RE: Passing arguments to Prepared Statement - Pardner - 01-02-2025

That works, thanks!

RE: Passing arguments to Prepared Statement - InsiteFX - 01-02-2025

Just a note here:

getVar()

Important

This method exists only for backward compatibility. Do not use it in new projects.
Even if you are already using it, we recommend that you use another, more appropriate method.