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

Question

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.

Answer 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.

Answer 2

Nathan,
Thanks for mention.
Sorry can't help, not too familiar with SQLite.

Good job for solving the case! 👍

Answer 3

I guess it's the time for one that works even though it wasn't the one recommended hehehe : D

Answer 4

Ipang any chance you can help me with this?

Answer 5

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.