+ 1

SQLITE3 PHP bindValue - can't get string to bind correctly for IN clause

Hello SQLite masters, I have ben struggling to get this working for 4 days now. I have a SELECT query I am trying to use via PHP to pull data from Sqlite database file. I am using a SQLite3Stmt object to make a statement, then binding values to it. The query looks like: SELECT * FROM transactionData WHERE trxCategory IN (:categories) AND trxDate >= :dateFrom AND trxDate <= :dateTo ORDER BY trxDate as returned by the getSQL() function in PHP. I played with the values and query manually on the sqlite3 CLI and I can get it to work there. I had to quote the date values, and the categories list string in the IN (.,.) clause has to be like: 'String1','String2','String3' etc... I simplified the query, and can get it working in PHP using only date range, but the categories string seems to be breaking the selection. I simplified the categories string down to 1 single category and it worked in PHP. Here is a sample of the categories string: strCategories: 'Business | Accountant','Business | Advertising | Website Hosting' I am single quoting the values in the PHP code, but I am unsure what the bindValue() function does in terms of escaping and quoting? Would the | pipe characters be causing any issues here? They are seperation characters in the category strings. I don't think they matter because the single category query works and it has a pipe character in it. I have tried (i think) all combinations of single quoting and not single quoting to try to determine what bindValue() is doing to the string before it sends it to the DB but I can't find any combination that works.

5th Jun 2021, 8:31 PM
Nathan Stanley
Nathan Stanley - avatar
5 odpowiedzi
+ 1
Well after trying everything I could to make this work, I switched my code to use SQLite3::query function instead and the query string works perfectly as expected. I don't know WTF SQLite3Stmt::bindValue() and SQLite3Stmt::execute() functions are doing but it DOESN'T WORK. Glad to have wasted 4 days on this. Not.
6th Jun 2021, 5:15 AM
Nathan Stanley
Nathan Stanley - avatar
+ 2
Nathan, Thanks for mention. Sorry can't help, not too familiar with SQLite. Good job for solving the case! 👍
6th Jun 2021, 7:47 AM
Ipang
+ 2
I guess it's the time for one that works even though it wasn't the one recommended hehehe : D
6th Jun 2021, 7:54 AM
Ipang
+ 1
Ipang any chance you can help me with this?
5th Jun 2021, 11:56 PM
Nathan Stanley
Nathan Stanley - avatar
+ 1
Haha well not really solved, just got fed up with it not working as expected so switched to using SQLite::query function instead. Everyone online says to use SQLite3Stmt class functions but I can't even get it to work.
6th Jun 2021, 7:50 AM
Nathan Stanley
Nathan Stanley - avatar