Newbe - MySQL syntax problem? --- SOLVED ---

[AJP]

Hi All, new to LiveCode...looks great so far!

I'm having an issue with the 'WHERE' clause in MySQL. I've looked through the dictionary and forum but cannot find a solution.

If I use this to pull out a specific record from the db all works as expected.

--------------

put "SELECT user FROM auth WHERE user='Jason' " into tSQL

//query the database ( format ready for table)

put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

--------------

The problem is when I want to use a variable for the WHERE clause as below;

--------------

put "Jason" into tUsername

put "SELECT user FROM auth WHERE user='tUsername' " into tSQL

//query the database ( format ready for table)

put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

--------------

This results in no data?

So the question is: How do I use a variable in the WHERE clause?

Thanks

Andy

[dglass]

You have to get your variable out of your string literal SQL statement.

put "SELECT user FROM auth WHERE user='" & tUsername & "'" into tSQL

So that's <equal sign><single quote><double quote> & tUsername & <double quote><single quote><double quote>

That's one way.

Another way is to use a parameter:

put "SELECT user FROM auth WHERE user=:1" into tSQL

Then use the format of DBQuery that accepts the parameters and variables.

[Mark]

Hi Andy,

Your approach would work in PHP but not in LiveCode One of several possible correct versions of your syntax would be:

Code: Select all

put "SELECT user FROM auth WHERE user='" & tuserName & "' into tSQL"

mind the quotation marks: single_quote double_quote & variable_name & double_quote single_quote

Kind regards,

Mark

[AJP]

Wow quick reply and solution!

Thanks dglass and Mark.

Andy