SQL statemnet with WHERE clause and array values

Scriptcase 9 General
#1

In one app, I build a list of variables, that I insert into an array, say {pr[0]}. I am using an array since the number of variables is unknown.

I then want to use the sc macro, to add additional conditions to an SQL search, using these values. Example:

where… prod = {pr[0]} or prod = {pr[1]} or prod = {pr[2]} …

The problem I encounter is that the SQL statement will not allow array values. Standard variables will not do the job, since I don’t know in advance how many I have.

Anyone have ideas?

L

#2

Hi lewis200,

Let’s say you have the values in an array $pr = array(1,2,3); similar to $pr[]=1; $pr[]=2; $pr[]=3;

Convert the array into a comma separated list:
$my_list = implode(',',$pr);

Use this list in the WHERE of the SQL:
$mysql = 'SELECT ... WHERE prod IN (' . $my_list . ') '

Sometimes it is better to use FIND_IN_SET:
$mysql = 'SELECT ... WHERE FIND_IN_SET (prod , "' . $my_list . "') '

Hope this helps.

Gunter Eibl
Web Development & Scriptcase Coach

3 Likes
#3

TY Gunter, will try tomorrow