Just starting to investigate this. We’re using Prepared Statements for all of our MySQL queries. Everything works great until I try to do an IN in the where clause. Example:
Select customer_list.address "address",
customer_list.city "city",
customer_list.name "name",
customer_list.ID "ID"
From customer_list
Where customer_list.country_id In (1,2,3)
This works great as a normal query, but using the Prepared statement it’s only bringing back those that match customer_list.country_id = 1.
Anyone seen this before? Any workarounds (other than do the sql by hand)?
dim rs as RecordSet = ps.SQLSelect
if db.error then
msgbox "DB Error: " + db.ErrorMessage
return
end
while rs.eof= false
dim sName as string = rs.Field(“name”).StringValue
listbox1.AddRow sName
rs.MoveNext
wend
[/code]
It will only bring back customer_list.ID = 5.
If I do the SQL in SQLSelect it brings back the data as expected. There is something weird in how IN clauses are prepared.
Seems this is not limited to your situation… there are dozens of topics on the internet… and what Paul just posted is ONE solution that worked in some cases…
but it your list is consecutive sequential numbers (ie. 1 thru 5) then why not skip the list and use “BETWEEN” instead?
here is a proposed PHP solution
$array = array(1,2,3);
$data = $db->query("SELECT * FROM table WHERE id IN (?a)",$array);
[quote=251734:@Dave S]but it your list is consecutive sequential numbers (ie. 1 thru 5) then why not skip the list and use “BETWEEN” instead?
][/quote]
Because this isn’t a between query. I just chose those numbers at random and they happen to actually be in the list.
[code]dim ari() as integer = array(5,4,3,2,1) //ID’s to select
//Create an appropriate number of ? for the prepared statement
dim ars() as string
for i as integer = 0 to ari.Ubound
ars.Append “?”
next
//Create the SQL Statement.
dim sSQL as string = kSQL.replaceall("?", Join(ars, “,”))
//Prepare it
dim ps1 as MySQLPreparedStatement = db.Prepare(sSQL)
//Now loop through and bind them
for i as integer = 0 to ari.Ubound
ps1.BindType(i, MySQLPreparedStatement.MYSQL_TYPE_LONGLONG)
ps1.bind(i,ari(i))
next
//Get the data and then display it
dim rs as recordset = ps1.SQLSelect
if db.error then
msgbox "DB Error: " + db.ErrorMessage
return
end
while rs.eof = false
dim sName as string = rs.Field(“name”).StringValue
dim sID as string = rs.Field(“country_id”).StringValue
listbox1.AddRow sName, sID
rs.MoveNext
wend[/code]