SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple Search Fields - resulting sql query?

    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:
    Code:
    SELECT CustID FROM customers WHERE FirstName='$FirstName'
    If the Surname is populated and FirstName is not populated my serach would be:
    Code:
    SELECT CustID FROM customers WHERE Surname='$Surname'
    If both fields are populated the serach would be:
    Code:
    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

  2. #2
    SitePoint Zealot
    Join Date
    Apr 2005
    Location
    Helsingborg, Sweden
    Posts
    168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Addict
    Join Date
    Jun 2008
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used a kind of sql generator.. the code is en vb6 sorry..

    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •