Grid SQL based on address variables?

Applications Grid / Reports
#1

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.

#2

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.

#3

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?

#4

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.

#5

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?

#6

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