Custom SQL Question


#1

Hello,

can search with following SQL Statement:

def execute_search

@result = Search.find_by_sql ["SELECT * FROM customers WHERE 

firstname LIKE ? LIMIT 10",params[:firstname]]

render :template => "search/resultlist", :layout => false

end

My question is, how do I use 2 or more Parameters - and with the “%”? (I
Like to use SQL-Statements because later i will have Queries over a lot
of tables)

Thank you

Thorsten


#2

Am Donnerstag, den 02.03.2006, 17:59 +0100 schrieb Thorsten Brückner:

My question is, how do I use 2 or more Parameters - and with the “%”? (I
Like to use SQL-Statements because later i will have Queries over a lot
of tables)

Here is an example of what you might looking for:

@result = Search.find_by_sql(
[
“SELECT * FROM customers WHERE firstname LIKE ? and lastname LIKE ?
LIMIT 10”,
“%#{params[:firstname]}%”,
“%#{params[:lastname]}%”
]

The first question mark is replaced with the second element in the
array, the second question mark with the third, etc.

“%#{params[:firstname]}%”

You wrap your data into percent signs, before they are injected into the
SQL string.


Norman T.

http://blog.inlet-media.de


#3

Thorsten Brückner wrote:

end

My question is, how do I use 2 or more Parameters - and with the “%”? (I
Like to use SQL-Statements because later i will have Queries over a lot
of tables)

try something like

@result = Search.find_by_sql [“SELECT * FROM customers WHERE
firstname LIKE ? AND lastname LIKE ? LIMIT 10”,
params[:firstname],params[:lastname]]

Thank you

Thorsten

Regards Neil


#4

Thank you,

it works fine!

Thorsten

Norman T. wrote:

Am Donnerstag, den 02.03.2006, 17:59 +0100 schrieb Thorsten Brückner:

My question is, how do I use 2 or more Parameters - and with the “%”? (I
Like to use SQL-Statements because later i will have Queries over a lot
of tables)

Here is an example of what you might looking for:

@result = Search.find_by_sql(
[
“SELECT * FROM customers WHERE firstname LIKE ? and lastname LIKE ?
LIMIT 10”,
“%#{params[:firstname]}%”,
“%#{params[:lastname]}%”
]

The first question mark is replaced with the second element in the
array, the second question mark with the third, etc.

“%#{params[:firstname]}%”

You wrap your data into percent signs, before they are injected into the
SQL string.


Norman T.

http://blog.inlet-media.de