MySQL Extension and Variables

[jonc]

Hi guys,

I'm testing out the MySQL extension and I'm just wondering if there's a way to use a variable in the query section. For example, I want to use {{page.title}} in the where clause of the query, how do I do this?

I tried something like {{ mysql.value("select name from users where username = '{{ page.title }}'") }} but I get an empty resultset. I'm not even sure if this is the correct syntax.

Thanks in advance.

[crb]

Concatenation

Something like

{{ mysql.value("select name from users where username = " .. page.title .. ";") }}

perhaps?

[jonc]

That worked! I just had to add additional single quotes to the code you gave me so it looks like this:
{{ mysql.value("select name from users where username = '" .. page.title .. "'") }}

Thanks a lot! You saved me a lot of time man, I would've never figured that out .

[crb]

No problem. I had to RTFM to find it, but I just knew where to find the manual

[SteveB]

Make sure to use the string.sqlescape function to avoid SQL injection problems:

{{ mysql.value("select name from users where username = '" .. string.sqlescape(page.title) .. "'") }}

[pratheesh]

Hi Steve,
Can i use simple page level variables to parameterize the query string ?
I know that mysql extension doesnt support pubsub but i just want to do something like this

var fname="pratheesh"
var lname="kumar"

mysql.table("select * from user where first_name='" .. fname .. "' and last_name = '" ..lname .. "'")

is this doable ? I'm trying out various combinations. If i get it working. I'll post it here.

[pratheesh]

Not sure if this is the best way,
but this works

{{ var mylist=["2142"];
payx.table("select first_name from user where user_id =" ..mylist[0]..";")
}}

[SteveB]

Yes, you can use anything you like as a variable as long as it is defined.