Query Build Assistance

The O commented on Aug 19, 2020

How can I achieve the equivalent of this SQL query in KoolReport:

select count(distinct(SID)) from history where `Date` < '$dateval'

where $dateval is a variable in PHP holding the date.

The O commented on Aug 19, 2020

I can't seem to find distinct function so here is my attempt but it still doesn't work neither does it look efficient:

	   $this->src("config")->query("select * from history")
    	   ->pipe(new Filter(array(
		array("`Date`","<",$date_val)
	    )))
	   ->pipe(new Group(array(
               "by"=>array("SID"),
               "count"=>array("SID")
	    )))
	   ->pipe($this->dataStore("checkedsm"));

The O commented on Aug 19, 2020

This permutation also not returning expected results:

$this->src("config")->query(DB::table('history')
->whereRaw('`Date` < "?"', $date_val)
->distinct('SID')->count())
->pipe($this->dataStore("checkedsm"));

Now I'm really confused!

David Winterburn commented on Aug 20, 2020

I think the simplest way is to use the original sql query together with parameter binding to avoid sql injection:

$this->src('config')
->query("select count(distinct(SID)) from history where `Date` < :dateVal")
->params(array(
    ":dateVal" => $dateval
))
->pipe($this->dataStore("checkedsm"));

If you'd like to use KoolReport's query builder let us know. Thanks!

The O commented on Aug 20, 2020

Thanks, this now works with

":dateval" => "2020-08-19"

but what you have above returns null. So it's back to the problem here.

Not sure how to print out the contents of row["Date"] asides generating a table.

The O commented on Aug 20, 2020

I would really like to crack this today, can't wait thru another timezone-induced delay. Can you help?

David Winterburn commented on Aug 20, 2020

Copy the query with filter value directly to your database admin interface to see if it returns any result:

select count(distinct(SID)) from history where `Date` < '2020-08-19'

If it doesn't please change the filter value. Thanks!

The O commented on Aug 20, 2020

It does and gives the value

The O commented on Aug 20, 2020

Ok, found a way.

$dateval was set outside the class (this is all using singlepage option.)

When I now set within the class:

protected $dateval = "2020-08-19";

and then

->params(array(":dateVal" => $this->dateval...

It works.

Thank you.

Ps. I suppose the instantiated class doesn't have access to the global scope.

KoolReport's Forum

Query Build Assistance #1584

Build Your Excellent Data Report