>Cause I tried to use VFP functions and they failed I assume MySQL has a different sintax so I'll have to learn it.
Yes, that's true. Here are some examples:
Immediate IF:
VFP:
SELECT IIF(MyField=1,0,1) AS NewField
MySQL:
SELECT CAST(IF(MyField=1,0,1) AS Decimal(1,0)) AS NewField
String Concatenation:
VFP:
SELECT myField1 + " " + MyField2 AS NewField
MySQL:
SELECT CONCAT(MyField1,' ',MyField2) AS NewField
String Comparison:
VFP:
SELECT * FROM MyTable WHERE ALLTR(MyField) == "THIS"
MySQL:
SELECT * FROM MyTable WHERE MyField = "THIS"
VFP:
SELECT * FROM MyTable WHERE MyField = "THIS"
MySQL:
SELECT * FROM MyTable WHERE MyField LIKE "THIS%"
VFP:
SELECT * FROM MyTable WHERE "THIS" $ MyField
MySQL:
SELECT * FROM MyTable WHERE MyField LIKE "%THIS%"
Remember, string comparison in MySQL is NOT case sensitive!
Check empty Date:
VFP:
SELECT * FROM MyTable WHERE MyDate = { }
MySQL:
SELECT * FROM MyTable WHERE MyDate = 0
(same for time fields)
Also remember, when you use variables do the following:
SQLEXEC(1,SELECT * FROM MyTable WHERE MyField = 'ThisValue','cuAlias')
PRIVATE pcValue
pcValue = "ThisValue"
SQLEXEC(1,SELECT * FROM MyTable WHERE MyField = ?pcValue,'cuAlias')
This will save you from lots of headache with comparisons of different data types, especially date and time fields.
Christian Isberner
Software Consultant