Announcement

Collapse
No announcement yet.

Grid SQL based on address variables?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
    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:
    Code:
    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
    Code:
    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,
    Code:
    http://{website}/location_grid/location_grid.php
    would use
    Code:
    SELECT location_id, location_active, location_name FROM locations
    But
    Code:
    http://{website}/location_grid/location_grid.php?customer_id=5
    would use
    Code:
    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.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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:
      Code:
      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:
      Code:
      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?

      Comment


      • #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.
        Albert Drent
        aducom software netherlands
        scriptcase partner, reseller, support and (turn-key) development
        www.scriptcase.eu / www.scriptcase.nl

        Comment


        • #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:
          Code:
          [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:

          Code:
          $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?
          Last edited by anomaly0617; 06-16-2013, 05:48 PM.

          Comment


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

            should work

            Comment

            Working...
            X