Variable number of SQL query conditions

[ime]

Hi to all.
I'm making a web application in which users enter text
for a person's name, last name, sex, etc.
But user doesn't have to populate all text boxes, so I don't know how to
make query without these conditions (for example if I have an empty string
for a name) .
I've got 9 text boxes, so I can't write all combinations - it's to many of
them.
Thanks a lot

 

[Guest]

Hi Ime,

I generally use lots of ifs, i know you have to write a lot of cide, but it
works, and its less than doing 9 different select statements! eg (in C#,
sorry)

string query, where;
query "SELECT * FROM table WHERE (";

// textbox 1
if(textbox1.text != "")
where = "(field = '" + textbox1.text + "')";

// textbox 2
// textbox 3
// etc

// finalise
query += where + ") ORDER BY field ASC";

Of course, you need some code to and in " AND " into your where string, but
thats the basics. Then just query your database with the string query.

HTH


Dan

 

[ime]

Thanks Dan

Hi Ime,

I generally use lots of ifs, i know you have to write a lot of cide, but
it
works, and its less than doing 9 different select statements! eg (in C#,
sorry)

string query, where;
query "SELECT * FROM table WHERE (";

// textbox 1
if(textbox1.text != "")
where = "(field = '" + textbox1.text + "')";

// textbox 2
// textbox 3
// etc

// finalise
query += where + ") ORDER BY field ASC";

Of course, you need some code to and in " AND " into your where string,
but
thats the basics. Then just query your database with the string query.

HTH


Dan

 

[Guest]

Make sure you validate any User Supplied Fields to guard against SQL
injection with this approach though!

 

[IPGrunt]

Make sure you validate any User Supplied Fields to guard against SQL
injection with this approach though!

Good point about injection! The secure way to insert is to us a
parameterized query. That said, I'm not going to use one, but
demonstrate a simple insert.

Why all the ifs?

Make sure your database fields can accept null values.

Then:


String var1, var2, var3, var4;

var1 = TextBox1.Text.Replace ("'","''");
var2 = TextBox2.Text.Replace ("'","''");
var3 ...

Then build your SQL string: (Linebreaks added for clarity).

sql = "INSERT INTO Table
(
field1,
field2,
field3,
field4
) VALUES ( " +
var1 + ", " +
var2 + ", " +
var3 + ", " +
var4 + ";"
)

My syntax may be off, but do you get the idea?

-- ipgrunt