Grid SQL based on address variables?

I’m seeing posts that dance around this topic, but don’t seem to address it directly. Some clarification would be appreciated.

I’d like to pass variables on the address bar to a Grid Application, so only records meeting the criteria of the variable provided are displayed.

For instance, if the SQL for the data for the grid looks like this:

SELECT location_id, location_active, location_name FROM locations

I get a list of all the customer locations.

But if I only want a list of the locations for a given customer:

SELECT location_id, location_active, location_name FROM locations WHERE customer_id=5

Is there a way to pass “5” to the grid via a link, so

http://{website}/location_grid/location_grid.php?customer_id=5

gives me the subset I need?

I guess the bigger question is this:

Is there a way to conditionally generate the SQL statement based on what variables are passed on the address line?
So,

http://{website}/location_grid/location_grid.php

would use

SELECT location_id, location_active, location_name FROM locations

But

http://{website}/location_grid/location_grid.php?customer_id=5

would use

SELECT location_id, location_active, location_name FROM locations WHERE customer_id=5

?

Help.

Yes, afaik there are several solutions. First of all you can use a global variable in which you can store a part of the sql, ie. the where clause. Second there are scriptcase macro’s to manipulate the sql statement.

sc_select_field ({Field})
This macro modify dynamically a field that will be recovered in the grid.
sc_select_order (“Field”)
This macro modify dynamically the grids “ORDER BY” clause field.
sc_select_where (add)
This macro adds dynamically a condition to the grid WHERE clause.

Hi Albert,

Thank you for your reply! However, I’m not having luck so far. Here’s what I have in my grid:

Under Grid -> Events -> onHeader:

if($_REQUEST['cid'] && $_REQUEST['cid'] != FALSE) {
	$cidval = " WHERE customer_id = '" . intval($_REQUEST['cid']) . "'";
} else {
	$cidval = "";
}
sc_set_global(cidval);

And then under Grid -> SQL -> SQL Select Statement:

SELECT 
    location_id,
    active_id,
    customer_id,
    name,
    address1,
    address2,
    city,
    state_id,
    postalcode,
    country_id,
    phone,
    fax,
    contact_id,
    office_id
FROM 
    ss_customers_locations
[cidval]

When I generate and run, it asks me for a cidval parameter (?) and then whether I put in a value or not, I get a 500 Internal Server Error.

I’m sure I’m screwing this up - can you tell me where?

Take care that you have at least a space after ss_customers_locations. With set global you declare a global variable, but question is: is it the right event. I would use standard events onscriptinit or onload for that. If you declare a global and you don’t declare that as output it will request for the variable at runtime. But only under development environment. You can also write [glob_mysql]= etc.

Hi Albert,

I’ve done a little more digging on this problem and I think I see the cause but don’t have a solution. Here’s what I see:

If I look at the error log for the web server, I see that there’s an error on line 493 of the code, specifically the error is:

[Sun Jun 16 16:24:28 2013] [error] [client {My IP}] PHP Parse error:  syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in {absolute_path_to_website_public_files}/scriptcase7/app/SimpleServicev2/grid_ss_customers_locations/grid_ss_customers_locations.php on line 493

So I go look at that file on line 493, and here’s what I see:

$this -> nm_tabela = "ss_customers_locations \" . $_SESSION[\'cidval\'] . \"";

It looks like ScriptCase is automatically inserting quotes into the string and escaping apostrophes. Is there a way to stop this behavior by prepending the [cidval] in my SQL statement with something?

This is what I do
Here is my url with parameter www.xxx.com/display_judgement/display_judgement.php?kk101=1
In the form/grid I have a sql with where condition id=[kk101]

should work