CMS Made Simple Forums

Posted: **Fri Apr 09, 2010 2:23 am**

I have scoured the forums but can't find anyone else with this problem...

Using CTLModuleMaker v1.8.9.3, direct SQL queries like this one....

Code: Select all

{cms_module module="mymodule" what="mylevel" query="A.variable = 5"}

Work perfectly, matching any item with variable=5, however either of the following querys...

Code: Select all

{cms_module module="mymodule" what="mylevel" query="A.variable > 3"}
{cms_module module="mymodule" what="mylevel" query="A.variable < 7"}

...return 'No item found.'

During extensive testing, I've never seen (greater than) working for any variable type.

Has anyone else had working for SQL queries? I'm really stumped. Even if you can point me to the code that executes the search I could employ my sub-standard coding skills and (eventually) hunt down the problem $:-\$

Thanks in advance

Posted: **Fri Apr 09, 2010 3:02 am**

It's probably getting converted to > which doesn't work in SQL.

Posted: **Fri Apr 09, 2010 3:45 am**

Thanks for the fast reply kendo. You're probably right but the module's help page gives this example:

Code: Select all

{cms_module module="mymodule" what="mylevel" query="A.date_modified > '2009-03-15' AND A.active = 1"}

...so I assume the author must have tested it with > at some stage. Do you have any idea what I could try?

Posted: **Fri Apr 09, 2010 4:21 am**

I can now confirm that you are right kendo!

I've just turned the debugger on (why didn't I think of that earlier

) and received the following output:

Code: Select all

(mysql): SELECT * FROM cms_module_mymodule_mylevel A  WHERE A.variable > 3 ORDER BY item_order
 Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 ORDER BY item_order' at line 1

Posted: **Fri Apr 09, 2010 4:27 am**

It isn't the module, it is probably that you are calling the query from a content block with the WYSIWYG turned on. The javascript in TinyMCE will automatically escape all html characters, including > and <. This breaks certain smarty calls like yours.

The solution is to turn off the WYSIWYG on the block from which you are making this call. On page options tab there is a box you can check to turn it off always for that page. Same is true for Global Content Blocks.

Ken

Posted: **Fri Apr 09, 2010 5:00 am**

Thanks again Kendo. I really appreciate your help.

WYSIWYG is definitely turned off.
I've also disabled WYSIWYG from the options tab.
Under Site Admin / Global Settings, Disallow WYSIWYG editors on global content blocks: is selected
I have tried putting the smarty in the template, directly after the tag with exactly the same result.
I've even tried disabling Javascript in my browser and doing CTRL-F5. Same result.

Posted: **Fri Apr 09, 2010 5:03 am**

Well, it may be in the module then. I cannot look at it tonight, but I have to do some stuff with CTL Module Maker in the next couple of weeks, so maybe I'll find the problem if you haven't solved it already.

The guy who made that module is pretty active in the forum. You might ask him.

Posted: **Fri Apr 09, 2010 5:15 am**

Of course, get some sleep

It's the middle of the night for you!

Normally plger is pretty active but his profile page says he hasn't logged on since February 4. I'll keep plugging away and reply here if I solve it. Cheers.

Posted: **Mon Apr 12, 2010 5:46 am**

During another fun-filled day with this

I've discovered that gt; DOES work in SQL because during an advanced search (myvar>3), SQL receives the following:
SELECT * FROM cms_module_mymodule_mylevel A WHERE A.myvar > '3' ORDER BY item_order
Which works perfectly.

However, this direct SQL query: {cms_module module="mymodule" what="mylevel" query="A.myvar > 3"}

Gives the following output:
SELECT * FROM cms_module_mymodule_mylevel A WHERE A.myvar > 3 ORDER BY item_order

Resulting in the SQL 1064 error, as described earlier. So, obviously, the culprit is the & getting translated to & Once again though, it's not javascript and it's not the translation functions in /lib/misc.functions.php because I've commented them out and got the same results. If I could only work out where these translations (> to gt; and & to &) are happening I'm sure I'd be close to a solution!?!?

Posted: **Mon Apr 12, 2010 8:51 am**

mhh... forgive me but my PHP skills are very limited, but I was thinking if:

Code: Select all

$this->SetParameterType("query",CLEAN_STRING);

in MODULENAME.module.php could have something to do with it?

Isn't CLEAN_STRING a PHP function to clean up a string of nonallowed characters?

Posted: **Mon Apr 12, 2010 10:55 pm**

You are more than forgiven klenkes, you're a legend! How on earth did I miss that? Obviously my PHP skills aren't too great either! I spent all day yesterday (mostly in action.default.php) echoing variables that still showed intact (> &) symbols.

I've just commented out the line you mentioned and SQL is now not receiving the query variable, which means you are right.

So the solution is as follows: In MODULENAME.module.php, change this...

Code: Select all

$this->SetParameterType("query",CLEAN_STRING);

To this...

Code: Select all

$this->SetParameterType("query",CLEAN_NONE);

Thankyou v e r y much klenkes.

Posted: **Tue Apr 13, 2010 6:53 am**

Hey, great that I could help!
Now that it works I just started to use manual queries myself, and it's a great tool for more flexibility.

Thanks for the karma!

Posted: **Tue Apr 13, 2010 7:25 am**

Especially now that they work

CMS Made Simple Forums

[solved]CTLModuleMaker SQL Query Problem

[solved]CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: CTLModuleMaker SQL Query Problem

Re: [solved]CTLModuleMaker SQL Query Problem

Re: [solved]CTLModuleMaker SQL Query Problem