SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    can someone review my sql?

    This sql statement is suppose to have col13=1, and col11=a radio button and then the rest are just text search fields that are optional(and don't have to be filled out completely). But I can never get it to return any values.

    SELECT tbl3.col1, tbl3.col11, tbl3.col2, tbl3.col3, tbl3.col4, tbl3.col5, tbl3.col6, tbl3.col7, tbl3.col8, tbl3.col13, tbl3.col17, tbl3.col18, tbl3.col19
    FROM tbl3
    WHERE tbl3.col13 = 1 and tbl3.col11 = colcheck and tbl3.col2 like '%colname%' and tbl3.col3 like '%coldba%' and tbl3.col4 like '%coladdress1%' and tbl3.col5 like '%coladdress2%' and tbl3.col6 like '%colcity%' and tbl3.col7 like '%colstate%' and tbl3.col8 like '%colzip%' and tbl3.col17 like '%colrisk%' and tbl3.col18 like '%colfedid%' and tbl3.col19 like '%colclientid%'
    ORDER BY tbl3.col2 ASC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the last time i worked on this query, you said "Thanks I gave your code a try and it worked perfectly" (sql question from a nOOb)

    my answer is the same -- there's probably no row that has the particular combinations that you are feeding the query

    do a reponse.write or echo or whatever it's called in your scripting language, to display the query prior to execution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    are you sure you're actually inserting your varible?

    in asp it would be this:
    "....... tbl3.col2 like '%" & colname & "%' .........." the point is i escaped the quotes and inserted my variable. not sure about php

  4. #4
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. I've made changes to the query since my last post. That is why I posted again. I'll check things over like you suggest. I did want to confirm that this sql will allow for some/all of the %% fields to be left blank and the query will not take it as a null value in it's search. Or am I wrong here?



    Quote Originally Posted by r937
    the last time i worked on this query, you said "Thanks I gave your code a try and it worked perfectly" (sql question from a nOOb)

    my answer is the same -- there's probably no row that has the particular combinations that you are feeding the query

    do a reponse.write or echo or whatever it's called in your scripting language, to display the query prior to execution

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, but if you have a scripting language, i would strongly recommend that you test each form field, and if it's empty, don't include that criterion in the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think I'm beginning to understand a bit more now. I read over your article regarding the "multiple field database search" and using "ANY". So, even if I use % % around my parameters and my parameter is empty, %% still does not equate to "anything". So, if my paramter = %%, it won't return anything in that field in the search, if that record's field happens to be empty as well. So in the end '%%' is not equal to "null". What does an empty paramter with the wildcards on both sides, '%%', equal then? What value does it have?


    Quote Originally Posted by r937
    yeah, but if you have a scripting language, i would strongly recommend that you test each form field, and if it's empty, don't include that criterion in the WHERE clause

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you meant to say LIKE, not equal

    ... WHERE foo LIKE '%bar%'

    will return values with bar in them

    ... WHERE foo LIKE '%%'

    will return all non-null values

    BUT

    when you say LIKE '%...' where there's a wildcard at the front, then the database cannot use an index on that column, consequently it decides, at least insofar as that column is concerned, to do a table scan

    or something like that

    in any case, if you want all rows, insofar as this column is concerned, i think the best strategy is not to have any possibility of the database optimizer screwing up, by not including the LIKE condition for that column at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks. now i'll try out your if statements from your other article to try and eliminate the empty %% searches.


    Quote Originally Posted by r937
    you meant to say LIKE, not equal

    ... WHERE foo LIKE '%bar%'

    will return values with bar in them

    ... WHERE foo LIKE '%%'

    will return all non-null values

    BUT

    when you say LIKE '%...' where there's a wildcard at the front, then the database cannot use an index on that column, consequently it decides, at least insofar as that column is concerned, to do a table scan

    or something like that

    in any case, if you want all rows, insofar as this column is concerned, i think the best strategy is not to have any possibility of the database optimizer screwing up, by not including the LIKE condition for that column at all

  9. #9
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You wouldn't happen to have any php/mysql examples that would pull this off would you? I've been trying to do this but can't figure it out.

    Quote Originally Posted by r937
    in any case, if you want all rows, insofar as this column is concerned, i think the best strategy is not to have any possibility of the database optimizer screwing up, by not including the LIKE condition for that column at all

  10. #10
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is something like this possible in sql ?

    SELECT
    tbl3.col13,
    tbl3.col11,
    tbl3.col1,
    tbl3.col2,
    tbl3.col3,
    tbl3.col4,
    tbl3.col5,
    tbl3.col6,
    tbl3.col7,
    tbl3.col8
    FROM tbl3 LEFT JOIN tbl4 ON tbl3.col1 = tbl4.col13
    WHERE
    (tbl3.col13=1) AND (tbl3.col11=1)
    if 'colname' is not null then
    AND (tbl3.col2 Like '%colname%')
    end if
    if 'coldba' is not null then
    AND (tbl3.col3 Like '%coldba%')
    end if
    if 'coladdress1' is not null then
    AND (tbl3.col4 Like '%coladdress1%')
    end if
    if 'coladdress1' is not null then
    AND (tbl3.col5 Like '%coladdress2%')
    end if
    if 'colcity' is not null then
    AND (tbl3.col6 Like '%colcity%')
    end if
    if 'colstate' is not null then
    AND (tbl3.col7 Like '%colstate%')
    end if
    if 'colzip' is not null then
    AND (tbl3.col8 Like '%colzip%')
    end if
    ORDER BY tbl3.col2

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by johnjohn2
    Is something like this possible in sql ?
    no

    your IF/ENDIF stuff is php

    the entire sql string is assembled by concatenating -- or not -- those various bits

    then the string is sent to mysql as one executable statement with no IFs in it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •