CTLModuleMaker - Where clause concerning dates

[nmotion]

Hi all,

First, great module, highly recommend it.

I have one small thing I can't get to work.

I need to order a list by date which is easy to do. Now the hard part. I also need to add a WHERE clause that says that all posts older than todays date should not be included.

In SQL it would look something like this:

WHERE date < NOW()
ORDER BY date

Is that possible in CTLModuleMaker.

[plger]

The only method right now would be to enable "Allow manual sql queries" and use the "query" parameter to enter the sql where clause (I think there are infos on that in the faq).
(By the way, if you want elements not older than today's date - and not time - use date > CURRENT_DATE instead of NOW())

[nmotion]

I can't get to accept anything that resembles a date.

Ex.

Code: Select all

{cms_module module="aktiviteter" what="aktivitet" nbperpage="7" query="A.start = '2009-03-15'"}

It will only take simple number quires like:

Code: Select all

{cms_module module="aktiviteter" what="aktivitet" nbperpage="7" query="A.pris = 0}

I can't get to accept text values either. What am I doing wrong?

[plger]

There's probably a bug.
in action.default.php, you should see :

Code: Select all

if(isset($params["query"])){

	$queryid = (int) $params["query"];

	if($query = $this->get_queries(array("id"=>$queryid))){

		// we retrieve the name of the function that will do the query

		$query = $query[0];

		$what = $query->what;

		$getfunction = "get_level_".$what;

		$parentlevel = $this->get_nextlevel($what, false);

		$whereclause = $query->whereclause;

		$wherevalues = $query->wherevalues;

		$customorder = ($query->queryorder == ""?false:$query->queryorder);

	}elseif($this->GetPreference("allow_sql",false)){

		$getfunction = "get_level_".$what;

		$parentlevel = $this->get_nextlevel($what, false);

		$whereclause = $params["query"]; // query should be sanitized...

		$wherevalues = array();

		$customorder = false;

		$itemlist = $this->$getfunction(array(), false, $id, $linkreturnid, $orderby, $limit, $query);

	}else{

		$message = $this->Lang("error_wrongquery");

		if($queryid != $query && $msg = mysql_error())		$message .= "<br/>".$this->Lang("givenerror").$msg;

		echo $this->ShowErrors($message);

		return false;

	}

	if(!isset($params["alias"]) && $nbperpage){

		// we need pagination - so we first retrieve the total number of items fitting the query

		$total = $this->countsomething($what,"id",array(),$whereclause, $wherevalues, $parentlevel);

	}

	if(isset($total) && $total == 0){

		$itemlist = array();	// we already know there's nothing to query

	}else{

		$itemlist = $this->$getfunction(array(), false, $id, $linkreturnid, $orderby, $limit, $whereclause, $wherevalues, $customorder);

	}

}else{

try replacing it with:

Code: Select all

if(isset($params["query"])){

	$queryid = (int) $params["query"];

	if($query = $this->get_queries(array("id"=>$queryid))){

		// we retrieve the name of the function that will do the query

		$query = $query[0];

		$what = $query->what;

		$getfunction = "get_level_".$what;

		$parentlevel = $this->get_nextlevel($what, false);

		$whereclause = $query->whereclause;

		$wherevalues = $query->wherevalues;

		$customorder = ($query->queryorder == ""?false:$query->queryorder);

	}elseif($this->GetPreference("allow_sql",false)){

		$getfunction = "get_level_".$what;

		$parentlevel = $this->get_nextlevel($what, false);

		$whereclause = $params["query"]; // query should be sanitized...

		$wherevalues = array();

		$customorder = false;

	}else{

		$message = $this->Lang("error_wrongquery");

		if($queryid != $query && $msg = mysql_error())		$message .= "<br/>".$this->Lang("givenerror").$msg;

		echo $this->ShowErrors($message);

		return false;

	}
	$itemlist = $this->$getfunction(array(), false, $id, $linkreturnid, $orderby, $limit, $whereclause, $wherevalues, $customorder);

	if(!isset($params["alias"]) && $nbperpage){

		// we need pagination - so we first retrieve the total number of items fitting the query

		$total = $this->countsomething($what,"id",array(),$whereclause, $wherevalues, $parentlevel);

	}else{
		$total = count($itemlist);
	}

}else{

and let me know what comes out of it