[solved]CTLModuleMaker SQL Query Problem

[kank]

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

[kendo451]

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

[kank]

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?

[kank]

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

[kendo451]

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

[kank]

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.

[kendo451]

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.

[kank]

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.

[kank]

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!?!?

[klenkes]

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?

[kank]

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.

[klenkes]

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!

[kank]

Especially now that they work