execute mySQL

[dayzrespite]

I wasn't sure if to start a new topic or to mention this here. So forgive me if this is not the place.

I am not that familiar with MySQL but, I was playing around with the ExecuteSQL () Function in order to find all records that matched a certain criteria. I came up with this:

ExecuteSQL ( "

SELECT fullName

FROM people

WHERE fullName LIKE '%" & GLOBAL::SEARCH & "%' " ; " ; " ; ¶)

This would return the names of people that match the text entered in a global field called Search. If the value in the search field was "Alex" it would return Alexis, Alexander. However if the value was "alex" (the letter A in lowercase) it would not return Alexis nor Alexander.

Is this normal MySQL behavior? If so, is there a way to make my search not be case sensitive.

I found that the following works, but I am wondering if there is a more elegant or correct way of doing this.

ExecuteSQL (

"SELECT fullName

FROM people

WHERE LOWER(fullName) LIKE '%" & Lower ( GLOBAL::SEARCH ) & "%' " ; " ; " ; ¶)

[RalphL]

This has nothing to do with MySQL which is a different database program. You are making an SQL query in FMP, The code you show:

ExecuteSQL (

"SELECT fullName

FROM people

WHERE LOWER(fullName) LIKE '%" & Lower ( GLOBAL::SEARCH ) & "%' " ; " ; " ; ¶)

will find every name that contains whatever is in GLOBAL::SEARCH. I don't think that is what you want. You have a wildcard before and after GLOBAL::SEARCH.

I would change the where clause to: WHERE fullName LIKE '%" & Upper ( GLOBAL::SEARCH ) & "%' " This will find all first and last names that start with GLOBAL::SEARCH. Leave out the first % and you will get first names that start with GLOBAL::SEARCH. If you only want last names then use lastName in the Where clause.

Look at FQL heading for more on using SQL in FMP. Kevin Frank's SQL Sandbox is a useful learning tool and included some custom functions for working with SQL in FMP/

Edited April 14, 2012 by Lee Smith