Trying to build function with optional search, limit, order parameters but when i move to PDO approach i get bound variables errors.

[Ysguy]

Hello,

I am using the f3 $db->exec database calls to access the database without a hitch. However i realize that just building a big query and passing it along may make it subject to SQL injection.

Normally i build my queries like this and pass in the known parameters like this in a PDO'eque manner as suggested in the documentation.

$result = $db->exec("SELECT * FROM TABLENAME WHERE `COLUMNA` = ? AND `COLUMNB` = ?",1=>'value',2=>'value');

Then i realized i could use placeholders, so it became

$result = $db->exec("SELECT * FROM TABLENAME WHERE `COLUMNA` = :colA AND `COLUMNB` = :colB",Array(array(':colA'=>'value',':colB'=>'value')));

But that only works if all the WHERE clauses exist. If i have an optional where clause and it doesnt exist - i.e.

"AND `COLUMNB` = :colB"

I get the following error

Error

"PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens"

I realize i can fix it by removing the ':paramName'=>'value that it isn't being queried for to resolve the issue, but i'd like to dyanmically respond to the incoming requests without having to have a bunch of IF statements.

My actual code is below. 

Just imaging that $sWhere $sOrder $sLimit are optional variables define by the incoming query. They exist or not. 

$primary = $db->exec(
 array("SELECT *, JSON_UNQUOTE(json_extract(reqDetails,'$.description'))
 AS description
 FROM `guestRequest`
 LEFT join users on sharerID = users.userID
 LEFT JOIN company on users.companyID = company.companyID
 $sWhere $sOrder $sLimit")
 ,
 array(array(':setQuery'=>'%'.$setQuery.'%',
             ':setQuery2'=>'%'.$setQuery.'%',
             ':sortSort'=>$sortSort,
             ':sortOrder'=>$sortOrder,
             ':pLimit'=>$pagingLimit,
             ':pOffset'=>$pagingOffset
            )
      )
 );

I realize, looking over the documentation for f3 i should probably use a mapper, but i have no idea how to do so as all the examples are to output single lines of code and not arrays of results (I need to pass json to my tables etc). 

Would someone be so kind as to lead me in the right direction to solve this?

[ved]

I realize i can fix it by removing the ':paramName'=>'value that it isn't being queried for to resolve the issue, but i'd like to dyanmically respond to the incoming requests without having to have a bunch of IF statements.

I'm not sure you can achieve that without conditionals, since a conditional is exactly what you're asking for.

You should build the parameters array dynamically according to the available parameters that were submitted.

 

I realize, looking over the documentation for f3 i should probably use a mapper, but i have no idea how to do so as all the examples are to output single lines of code and not arrays of results (I need to pass json to my tables etc). 

If you use the find() method of the mappers, you'll get an array of results. If using the load() method, you'll hydrate the mapper with the first result, but you can then use the seek(), prev() or next() methods to navigate through all the affected results.

[Ysguy]

I appreciate you pointing me in the right direction. I'll give it a try! I certainly don't want people to do my homework for me!