mySQL Query... need some help

[Frank D. Engel, Jr.]

Also, if your field is accepting user input, be careful about 
"escaping" the strings.  Otherwise, if the user inputs text with a 
quote delimiter (') in it, the query may fail in the database, or give 
unexpected results.  Run each text field you aren't absolutely sure of 
through a function something like this:

function quoteString inText
   put inText into x
   replace "'" with "''" in x
   return x
end quoteString

Now you are looking at something more like:

put revDataFromQuery(,,databaseID, "SELECT * FROM mybuddies WHERE 
FirstName = " & quoteString(field "myQuery"))

On Dec 17, 2004, at 5:05 AM, Ben Rubinstein wrote:

> on 17/12/04 3:58 am, docmann wrote
>
>> on mouseUp
>>   get revDataFromQuery(,,"SELECT * FROM mybuddies WHERE FirstName =
>> field 'myQuery'")
>> end mouseUp
>
> I can see two issues here (I won't say that there may not be other 
> ones!).
>
> The first one is that you need at least two parameters to
> 'revDataFromQuery': database id, and SQL query.  You've got the SQL 
> query,
> but not the id.  But I'm guessing you may have just left that out of 
> the
> message?  Anyway, you need to open the database before you can call
> 'revDataFromQuery': see revOpenDatabase for more information.
>
> The second problem is that the query string passed as a parameter to
> 'revDataFromQuery' is actually passed directly to the database, in 
> this case
> MySQL.  So MySQL gets this query:
>
>     SELECT * FROM mybuddies WHERE FirstName = field 'myQuery'
>
> But MySQL doesn't know anything about your Revolution fields.  So you 
> need
> to get the data out of the Revolution field before passing the query to
> MySQL.  You could do something like this, just using Transcript's 
> standard
> string concatenation operator:
>
>   put field "myQuery" into tName
>   put "SELECT * FROM mybuddies WHERE FirstName = '" & tName & "'" into 
> tSQL
>   get revDataFromQuery(,, iDatabaseID, tSQL)
>
> That way, if the field contains "Bob", then the variable tSQL will be 
> set to
> (and MySQL will get):
>
>        SELECT * FROM mybuddies WHERE FirstName = 'Bob'
>
> Because this is quite a common pattern in making queries from 
> databases -
> most of the query is hard wired, but part is dynamic - revDataFromQuery
> includes a special feature to make this more convenient.  The string 
> you
> pass to revDataFromQuery can contain placeholders, which 
> revDataFromQuery
> fills in from variables, the names of which are passed in as additional
> parameters.  In this simple case, it's hardly simpler, but it can be 
> useful
> in more complicated cases.  The following code will send exactly the 
> same
> string to MySQL:
>
>   put field "myQuery" into tName
>   put "SELECT * FROM mybuddies WHERE FirstName = ':1'" into tSQL
>   get revDataFromQuery(,, iDatabaseID, tSQL, "tName")
>
> (Note that the name of the variable holding the value to be 
> substituted for
> the placeholder is passed, rather than the value - unusual in 
> Transcript,
> and slightly confusing.)
>
> Hope this helps,
>
>   Ben Rubinstein               |  Email: benr_mc at cogapp.com
>   Cognitive Applications Ltd   |  Phone: +44 (0)1273-821600
>   http://www.cogapp.com        |  Fax  : +44 (0)1273-728866
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>
-----------------------------------------------------------
Frank D. Engel, Jr.  <fde101 at fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com