Dynamic SQL select

Scriptcase 9 General
#1

Ok I did not see this anywhere in the docs… how does one dynamically alter a Select statement.

Lets say I have a grid of students.

I have schoolid set as a global variable.
I also have sec_level and [teacherid] set as global variables.

What I want to do is this:

$add_sql = “”;

$teacherid = [teacherid];
$sec_level = [sec_level];

if ( $sec_level == 6 ) //this is a teacher that logged in
{
$add_sql = " AND teacher = ‘$teacherid’ ";
}

$sql=" Select * from student_table WHERE schoolid = [schoolid] ";
$sql.= $add_sql;

Where do I place this code? OnScriptInit?

#2

Well holy moly I actually figured it out…
I will post it here for others.

On the OnScriptInit event of the Grid.

$userid = [userid];
$schoolid = [schoolid];
$sec_level = [sec_level];
if($sec_level==6)
{
sc_select_where(filter) = " AND primary_teacher = ‘$userid’ AND schoolid=’$schoolid’";
}

#3

sc_select_where(filter) ?
Are you sure?
According to the SC manual the command is sc_select_where(add)

Anyway this is a way to do it, but sc_select_where has (had?) some issues so I always use the alternative way of having a variable in the grid select statement.
Something like:


Select col1, col2
From table
Where [param_sql_where]

Note that in this case [param_sql_where] cannot be null and when no filter is needed you have to set it to ‘1=1’