Hello

I am trying to build a query that will return a list of names, based on flexible input (ie, a user may only put a state name, or a last name, or combinations thereof). It all works EXCEPT for quotes included in last names (O'Keefe, for example).

If I escape the single quote in the form (o\'keefe), the query works. Of course, this is not acceptable for users. SO, i tried the /\Quote Macro, but that did not work.  I notice that there is a caveat that the Quote Macro cannot be nested "if the quoted string contains commas or unescaped quotes".  Pardon me, but isn't that kind of the point -- ie, to escape those characters where needed?

So, how can I allow for the input of names with apostrophes??  I have included the admittedly complicated query below.  The complication is what allows for flexible input.

thanks,
aleone

SELECT id, firstName, lastName, city, state, zip, country
FROM people
WHERE
^If('^FormParam(id);' eq '',

^If('^FormParam(firstname);' ne '',
  firstName LIKE '%^FormParam(firstname);%' AND);

^If('^FormParam(lastname);' ne '',
   lastName LIKE '%^FormParam(lastname);%' AND);
 
^If('^FormParam(country);' ne '',
   country LIKE '^FormParam(country);',
  
 ^If('^FormParam(state);' ne '',  state LIKE '^FormParam(state);',state LIKE '%'););,
id = '^FormParam(id);');
ORDER BY city, state, lastName, firstName

--- (Edited on 7/29/2007 11:53 am [GMT-0500] by aleone) ---

Thank you, knowmad, but i am still having trouble.

I defined a placeholder parameter 'form:lastname" and simply substituted the FormParam macro for lastname. Here:

SELECT id, firstName, lastName, city, state, zip, country
FROM people
WHERE
^If('^FormParam(id);' eq '',

^If('^FormParam(firstname);' ne '',
  firstName LIKE '%^FormParam(firstname);%' AND);

^If('^FormParam(lastname);' ne '',
   lastName = ? AND);
 
^If('^FormParam(country);' ne '',
   country LIKE '^FormParam(country);',
  
 ^If('^FormParam(state);' ne '',  state LIKE '^FormParam(state);',state LIKE '%'););,
id = '^FormParam(id);');
ORDER BY city, state, lastName, firstName

I still get the same SQL syntax error due to the apostrophe in the last name when i try "o'keefe".  Moreover, i get no return set at all if i leave the last name form param blank (eg, i pick a state instead).  Is this because placeholders don't allow null values?

 I appreciate any help.

Thanks, aleone

--- (Edited on 7/30/2007 9:59 am [GMT-0500] by aleone) ---

Thanks for the feedback, knowmad.

Yes, i know that I changed the LIKE clause and knew its implications.  I was attempting some debugging.  Using the Concatenation function seems to include the question mark, despite the fact that the IF branch is not followed.

Here is the code:

SELECT id, firstName, lastName, city, state, zip, country
FROM people
WHERE
^If('^FormParam(id);' eq '',

^If('^FormParam(firstname);' ne '',
  firstName LIKE '%^FormParam(firstname);%' AND);

^If('^FormParam(lastname);' ne '',
   lastName LIKE concat('%',?,'%') AND);
 
^If('^FormParam(country);' ne '',
   country LIKE '^FormParam(country);',
  
 ^If('^FormParam(state);' ne '',  state LIKE '^FormParam(state);',state LIKE '%'););,
id = '^FormParam(id);');
ORDER BY city, state, lastName, firstName

Running this with no input (should return all rows) yields: (Note the '?' after the WHERE)

• Debug: Query:SELECT id, firstName, lastName, city, state, zip, country FROM people WHERE ? state LIKE '%' ORDER BY city, state, lastName, firstName
• Debug: Processed Placeholder parameters:
• Debug: Error: There was a problem with the query. 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 'state LIKE '%' ORDER BY city, state, lastName, firstName' at line 12

Changing the SQL to an equality test:

...
^If('^FormParam(lastname);' ne '',
   lastName = ? AND);
...

With no input yields no records (should be all), but a seemingly good SQL query:

• Debug: Query:SELECT id, firstName, lastName, city, state, zip, country FROM people WHERE state LIKE '%' ORDER BY city, state, lastName, firstName
• Debug: Processed Placeholder parameters:

If i try passing a last name into this, i get:

 That's a lot of stuff.  Thanks for assisting.

--- (Edited on 7/30/2007 12:18 pm [GMT-0500] by aleone) ---

--- (Edited on 7/30/2007 12:19 pm [GMT-0500] by aleone) ---

knowmad, I really appreciate your help.  I am considering other workarounds.  It is nice to know that i am not missing something glaringly obvious. 

Thanks again,

aleone

--- (Edited on 7/31/2007 12:16 pm [GMT-0500] by aleone) ---