SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Aug 2004
    Location
    UK
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy PLEASE HELP !!! SELECT statement OR problem.

    Hi all, I'm trying to get the query to search on 3 database fields and cater for every
    possible occurence of people leaving one or more search fields blank.

    Below is what I have so far, but it only works if at least the searchFor field is full.

    Please can you help me get this working.

    Regards Damiano3


    strSql = "SELECT image_id, image_thumbnail, image_name, image_keywords FROM images WHERE "

    If Len(searchFor) <> 0 Then
    strWhere = "image_name LIKE '%" & searchFor & "%'"
    blnFirstParam = True
    End If

    If Len(iref) <> 0 Then
    If blnFirstParam = True Then
    strWhere = strWhere & " OR image_id = " & iref & ""
    Else
    strWhere = "image_id = " & iref & ""
    blnFirstParam = True
    End If
    End If

    If Len(itype) <> 0 Then
    If blnFirstParam = True Then
    strWhere = strWhere & " OR image_type LIKE '%" & itype & "%'"
    Else
    strWhere = "image_type LIKE '%" & itype & "%'"
    blnFirstParam = True
    End If
    End If

    SqlString = strSql & strWhere

  2. #2
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Damiano3
    Below is what I have so far, but it only works if at least the searchFor field is full.
    That's because if the searchFor field is blank, then you've built you SQL to read:
    Code:
    SELECT ... FROM ... WHERE OR ...
    which isn't legal.

    An easy workaround would be to make the following simple change to the line that starts your SQL:
    Code:
    strSql = "SELECT image_id, image_thumbnail, image_name, image_keywords FROM images WHERE 1=1 "
    This will select everything from your table...
    Then in your ASP code, use AND:
    Code:
    If Len(searchFor) <> 0 Then
      strWhere = "AND image_name LIKE '%" & searchFor & "%' "
      blnFirstParam = True
    End If
    Also, why are you using OR in the other two queries? This doesn't really make sense to me... Do you mean to put AND? This is more than likely what the user of your search form will expect to occur.

    jay

  3. #3
    SitePoint Member
    Join Date
    Aug 2004
    Location
    UK
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Thanks for the response.

    you were right about the AND's, but it still does the same thing.

    I also don't quite understand the:

    WHERE 1=1 "

    bit

    this is my new code (I may have taken out too much), please advise:


    strSql = "SELECT image_id, image_thumbnail, image_name, image_keywords FROM images WHERE 1=1 "

    If Len(searchFor) <> 0 Then
    strWhere = "AND image_name LIKE '%" & searchFor & "%' "
    End If

    If Len(iref) <> 0 Then
    strWhere = "AND image_id = " & iref & " "
    End If

    If Len(itype) <> 0 Then
    strWhere = strWhere & "AND image_type LIKE '%" & itype & "%' "
    End If

    SqlString = strSql & strWhere

  4. #4
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Damiano3
    ...but it still does the same thing.
    By this you mean? What is it still doing? Is it not producing ANY results? Pls explain....
    Quote Originally Posted by Damiano3
    I also don't quite understand the:

    WHERE 1=1 " bit
    This is was my fault. I didn't realize you were tracking that a parameter was added to the WHERE expression with the blnFirstParam variable... You could take out that stuff and use the 1=1 stuff as an alternative.

    The 1=1 simply makes it possible to leave all the parameters empty, as the resulting SQL will still be valid (i.e. all records have 1=1 (a constant equal to the same constant always returns true)). With your method, you'd end up with a SQL expression that ended with "FROM images WHERE", which is not valid.

    Hope this explains things.

    If you could post either some more code (your HTML or the entire script), or a more detailed description of what is occurring, that would be great.

    jay

  5. #5
    SitePoint Member
    Join Date
    Aug 2004
    Location
    UK
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Hi Jay,

    Got it working :-)

    as per usual with my work the mistake was simply somewhere else in the code. Laziness and tiredness aren't helpful. sorry and thanks for your patience.

    It now works when no fields, any individual field, any combination of 2 fields or all fields are entered.

    for Reference
    This is the SQL that does it:


    strSql = "SELECT image_id, image_thumbnail, image_name, image_keywords FROM images WHERE 1=1 "

    If Len(searchFor) <> 0 Then
    strWhere = "AND image_name LIKE '%" & searchFor & "%' "
    End If

    If Len(iref) <> 0 Then
    strWhere = "AND image_id = " & iref & " "
    End If

    If Len(itype) <> 0 Then
    strWhere = strWhere & "AND image_type LIKE '%" & itype & "%' "
    End If

    SqlString = strSql & strWhere


    the 1 = 1 thing makes sense now and is v useful

    Thanks again.

    Keep up the good work :-)

    Damiano3


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
  •