WHERE (#PreserveSingleQuotes(lineaSQL)#) WHERE [Local].idRubroLocal = [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => raulriera [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> Dynamic WHERE (more complex) - Coldfusion Database Access

Dynamic WHERE (more complex) - Coldfusion Database Access

As you can see for this code im not that good with SQL hehe but I manage... works perfect until I try to pass two or more words to it (its a search engine code and the variables are passed via querystring as so word1+word2+word3+etc) Any suggestions?? The preserveSingleQuotes functions doesnt seem to work with 2 or more words in it <cfquery name="buscar" datasource="metroBD"> SELECT FotoLocal.urlThumb, [Local].idLocal, [Local].idRubroLocal, [Local].nombre, [Local].telefono FROM FotoLocal INNER JOIN [Local] ON FotoLocal.idLocal = [Local].idLocal <!--- Buscar por palabra clave ---> <cfif isDefined("nombreLocal") AND nombreLocal IS NOT ""> <!--- Loop sobre las palabras creadas por el ...

  1. #1

    Default Dynamic WHERE (more complex)

    As you can see for this code im not that good with SQL hehe but I manage...
    works perfect until I try to pass two or more words to it (its a search engine
    code and the variables are passed via querystring as so word1+word2+word3+etc)

    Any suggestions??

    The preserveSingleQuotes functions doesnt seem to work with 2 or more words in
    it


    <cfquery name="buscar" datasource="metroBD">
    SELECT FotoLocal.urlThumb, [Local].idLocal, [Local].idRubroLocal,
    [Local].nombre,
    [Local].telefono
    FROM FotoLocal INNER JOIN [Local] ON FotoLocal.idLocal = [Local].idLocal

    <!--- Buscar por palabra clave --->
    <cfif isDefined("nombreLocal") AND nombreLocal IS NOT "">

    <!--- Loop sobre las palabras creadas por el usuario para formar la linea
    SQL
    que realizar? la b?squeda del local por nombre --->
    <cfset lineaSQL = "0=1">

    <cfloop index="nombreLocal" list="#nombreLocal#" delimiters = " :/">
    <cfset lineaSQL = lineaSQL & " OR [Local].nombre LIKE " & "'%" &
    nombreLocal & "%'">
    </cfloop>

    WHERE (#PreserveSingleQuotes(lineaSQL)#)

    <!--- Buscar por rubros --->
    <cfelseif isDefined("idRubro") AND idRubro GT 0>
    WHERE [Local].idRubroLocal = <cfqueryparam cfsqltype="cf_sql_numeric"
    value="#idRubro#">

    </cfif>
    </cfquery>

    raulriera Guest

  2. #2

    Default Re: Dynamic WHERE (more complex)

    It would help if you could print out lineaSQL after it has been assembled.
    That way we could see what you're dealing with. If you are able to print the
    actual instance of the SQL that is causing the error, all the better.

    jdeline Guest

  3. #3

    Default Re: Dynamic WHERE (more complex)

    the SQL is this one

    0=1 OR [Local].nombre LIKE '%word1%' OR [Local].nombre LIKE '%word2%' OR
    [Local].nombre LIKE '%word3%'

    but when using it in a cfquery it returns an error.. even if I use
    preserveSingleQuotes

    raulriera Guest

  4. #4

    Default Re: Dynamic WHERE (more complex)

    Your error message indicates that each variable is surrounded by double
    single-quotes.

    if your variables are called 'word2' and 'word2' it appears that the code
    previous to the query is causing the problem. The variable output to the query
    seems to be ''word1''.

    Please post the entire page so we can help better.

    coderWil Guest

  5. #5

    Default Re: Dynamic WHERE (more complex)

    Hmmm well yes the error is that one, but the thing is that I dont know what is
    causing it.. since as you can see by the code im not using doble quotes on the
    variables and none of the words typed in contains quotes either...

    About posting the whole page.. that is all... you have the code, the "desired"
    output using cfdump and the error page

    raulriera Guest

  6. #6

    Default Re: Dynamic WHERE (more complex)

    "since as you can see by the code im not using doble quotes on the variables ..." CoderWil suggests you have two consecutive single quotes around word2, not double quotes.
    jdeline Guest

  7. #7

    Default Re: Dynamic WHERE (more complex)

    PreserveSingleQuotes() adds a single quote wherever one is found in order to
    preserve the full string in SQL. Since you're already starting your WHERE
    statement with ' 0=1', you can just create the SQL dynamically in the loop
    without involving a variable:



    cf_menace Guest

  8. #8

    Default Re: Dynamic WHERE (more complex)

    Thanks thats is a perfect answer.. the thing is that if I use just #lineaSQL#
    without the function I still get the error.. I solved it with WHERE
    #Replace(lineaSQL,"''","'","All")# which is "dumb" to do but works.. ur
    solution was the other way I was going to do it (the right way since involves
    the <cfqqueryparam> tag but I didnt want to rewrite the little thing hehe

    Thanks for ur help.. I will use ur code sample next time

    raulriera Guest

Similar Threads

  1. Help for quite complex query.
    By _andrea.l in forum MySQL
    Replies: 3
    Last Post: January 1st, 04:22 PM
  2. Complex SQL?
    By Toni Van Remortel in forum MySQL
    Replies: 5
    Last Post: December 7th, 06:08 PM
  3. Can Anyone help with this complex page?
    By BucksSteve in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: August 6th, 08:44 AM
  4. dynamic drop downlists in an editable dynamic datagrid.
    By mkhans@gmail.com in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 22nd, 09:01 PM
  5. Complex Dynamic Form Calc?
    By bigbrain28 in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 24th, 07:28 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139