Multiple Search Fields - resulting sql query?

[JATaylor]

How would I implement a search in which there could be multiple inputs to search against?

For example i have a client database which i need to search, the search form has 2 inputs FirstName and Surname.

If the FirstName is populated and the Surname is not populated my search would be:

SELECT CustID FROM customers WHERE FirstName='$FirstName'

If the Surname is populated and FirstName is not populated my serach would be:

SELECT CustID FROM customers WHERE Surname='$Surname'

If both fields are populated the serach would be:

SELECT CustID FROM customers WHERE FirstName='$FirstName' AND Surname='$Surname'

How would this be implemented?? Do I need to create a search for each possible situation and then control which one is used by an if statement - i'm guessing not as that would very long winded for a longer example such as searching by address which may have 5 or more input fields, any of which may be populated.

If its relevant i'm using PHP also

Thanks

James

[Borje]

I'd build the query in your front application, with the appropriate logic, (be it in php or whatever) before sending it to the database.

[JATaylor]

Thanks for the reply Borje, perhaps i have posted this in the wrong forum given your responce!

I am actually trying to build the query in the front end application via php. What i am struggling with is how to cope with the varitations of the query dependant upon which input fields are populated.

Perhaps a Moderator could move this post to the appropriate forum, perhaps the php one?

[gderas]

I used a kind of sql generator.. the code is en vb6 sorry..

Dim strSql as String

'#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#
'Define the first part of the select
'#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#

strSql = "SELECT CustID FROM customers WHERE "

'#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#
'1st condition
'#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#

If Len(text1.Text) = 0 Then
	'do nothing or something
Else
	If Len(text2.Text) = 0 Then
		strSql = strSql & "FirstName like '%" & text1.Text & "%' "
		
	else
	    strSql = strSql & "FirstName like '%" & text1.Text & "%' and "
	end if
End If

'#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#
'2nd condition
'#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#*#

If Len(text2.Text) = 0 Then
	'do nothing or something
Else
    strSql = strSql & "Surname like '%" & text2.Text & "%' "
End If