Skip to main content
Inspiring
Jul 23, 2008 4:42 PM UTC
Question

Getting everything but empty fields in MySql query

I was trying to get all users email addresses that actually have a email
address entered in the database. I tried:

<cfquery name="AllUsersCSV" datasource="SalleBoise">
select #form.sql#
from inforeq
where #Search1#=<cfqueryparam cfsqltype="cf_sql_smallint" value="1"> <cfif
form.sql eq "Cust_Email">and Cust_Email not ''</cfif>
</cfquery>

But kept getting a "You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '''' at line 3" error

What would be the 'proper' way to search for these records?


    This topic is closed to new replies. Start a new post to keep the conversation going.

    4 replies

    Inspiring
    Jul 23, 2008 6:25 PM UTC
    Thanks Dan, the <> worked just great......

    "Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
    news:g67ofl$j26$1@forums.macromedia.com...
    > Your specific error comes from the word "not". It should be either <> ",
    > is
    > not null", or both, depending on how you store your data.
    >
    > There may be some other issues, depending on what the values of form.sql
    > and
    > Search1 are.
    >
    >
    >


    Translate
    Inspiring
    Jul 23, 2008 6:54 PM UTC
    Steve Grosz wrote:
    > Thanks Dan, the <> worked just great......

    The on-line MySQL documentation can also be very helpful for syntax problems or questions
    http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_not-equal
    Translate
    Inspiring
    Jul 23, 2008 5:30 PM UTC
    You can print your SQL statement and copy the statement and execute it in SQL. This way you can easily figure out the problem.

    Hope the above helps.
    Thanks,
    Satheesh.
    Translate
    Inspiring
    Jul 23, 2008 5:07 PM UTC
    Your specific error comes from the word "not". It should be either <> ", is not null", or both, depending on how you store your data.

    There may be some other issues, depending on what the values of form.sql and Search1 are.

    Translate