Skip to main content ↑ ↓
Topic solved
This topic has been marked as solved and requires no further attention.
Topic: What are the accepted "Query Params" (Read 5225 times) previous topic - next topic

What are the accepted "Query Params"

I'm playing with the Record Count elements.

In the query builder, i see there are parameters, but only 3 are given as example: USER_ID, datetime_now() and Active. I've been able to figure some others, like Random (from the API documentation) but not sure if that list there is complete or some are missing.

The fact is that I haven't been able to accomplish what i was trying to do:

Essentially, the Record Count elements count all the entries in a table, but I wanted to limit that a bit with a WHERE clause. This is what i was trying. Imagine a product table and a category table. And when i click in the Category View display on top how many products in that category. So, my logic works if i pass the ID of that category as Paraemeter, but I can't figure how to pass it dynamically, so when I am in different categories it shows the number of products from that category only.

I know this can be done in some other ways, but this is a test of the Record Count element and it's associated query builder.
 

Re: What are the accepted "Query Params"

Reply #1 –
Hi ewwgee.

You can pass in any variable to the query parameter list.

Here's is a sample statement.

( !empty( $_GET['category'] ) ? $_GET['category'] : null )

Re: What are the accepted "Query Params"

Reply #2 –
Thanks for pointing in that direction. Knowing that $_GET works there, I think I can find the right params

Re: What are the accepted "Query Params"

Reply #3 –
It doesn't work for me.. Will try with a very simple DB in a new test project.

The parameters for the GET seem to be correct, but somehow, i don't see the getcount_products passing any query string parameters.. in fact, if i do something like this directly in the URL http://server.local/getcount_products?category=1  I see it returns the JSOn response with the correct value, so I know that works.  

Also tried to do some "hacking" in the ComponentsController.php without luck :(

Re: What are the accepted "Query Params"

Reply #4 –
Please can you share sample of the getcount_products function.

Since you are modifying the ComponentController,
I'll suggest you do something like this in the function.

Code: [Select]
if(!empty($_GET['category'])){
$sqltext = "SELECT  count(*) AS num FROM products where category=?";
$arr = $db->rawQueryValue($sqltext, array($_GET['category']));
} 
else{
$sqltext = "SELECT  count(*) AS num FROM products";
$arr = $db->rawQueryValue($sqltext,);
}

Re: What are the accepted "Query Params"

Reply #5 –
Not working either.. getting the infamouse NaN result.

This is the code that is being generated at ComponentsController.php

    /**
     * getcount_products Model Action
     * @return Value
     */
   function getcount_products(){
      $db = $this->GetModel();
      $sqltext="SELECT COUNT(*) AS num FROM products WHERE category=?"    ;
      $arr=$db->rawQueryValue($sqltext, array(( !empty( $_GET['category'] ) ? $_GET['category'] : null )));
      
      render_json($arr[0]) ;
   }

Re: What are the accepted "Query Params"

Reply #6 –
Quote from: Emman –
Hi ewwgee.

You can pass in any variable to the query parameter list.

Here's is a sample statement.

( !empty( $_GET['category'] ) ? $_GET['category'] : null )



and two fields values or variables?????

Re: What are the accepted "Query Params"

Reply #8 –
this query
select id,name
from customers
where country=xxxx
and state=yyyyy

Re: What are the accepted "Query Params"

Reply #10 –
thank u for the answers.
this is form
OptionList1: Countrys
OptionList2: States
OptionList3: Cities

1. Select id,name from countrys
2. select id_state,name from states where id_country=xxx
3. select id_citie,name from cities where id_country=abc and id_state=jmk

For second option list its working but third Not.
My answer is how i can get values from elements selected an run query

Re: What are the accepted "Query Params"

Reply #11 –
@jfgonzalez you have to set the Dynamic Select Lookup for each parent select e.g OptionList1: Countrys and OptionList2: States.
Dependent Field is the field that will depend on the current option list or select field you are working on.
Match Field is the database table field that contains the value the next select(i.e  OptionList2: States) depends on.