Page 1 of 1
Basic MySQL Query
Posted: Thu Jan 21, 2016 2:11 pm
by A1Qicks
Hey, my project doesn't focus on MySQL but I need a small section from a database to make it work.
I've looked online but I'm not having much success and I'm not overeager to learn MySQL just for a couple of lines of code!
Can anyone tell me what I'd need to do to find a row by the entry in the first column, and get the database to return the corresponding entry in the second column?
Thanks!
Re: Basic MySQL Query
Posted: Thu Jan 21, 2016 3:47 pm
by ghettocottage
Tell us what the table is named and what the column is named
Re: Basic MySQL Query
Posted: Fri Jan 22, 2016 11:25 am
by A1Qicks
Table is "Proxic", column to be located is "NameP", column to be returned is "MessageP".
Thanks!
Re: Basic MySQL Query
Posted: Tue Feb 02, 2016 5:07 pm
by MaxV
Here your SQL code:
Code: Select all
SELECT MessageP FROM Proxic WHERE NameP='what i''m looking for';
so I'd write:
########CODE#######
put "runrev.com" into tDatabaseAddress
put "runrev_test" into tDatabaseName
put "runrev_example" into tDatabaseUser
put "example" into tDatabasePassword
-- connect to the database
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
-- check if it worked and display an error message if it didn't
-- & set the connection ID global
if tResult is a number then
put tResult into gConnectionID
answer info "Connected to the database." & cr & "Connection ID = " & gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database:" & cr & tResult
end if
#retrieve data
put "SELECT MessageP FROM Proxic WHERE NameP='what i''m looking for';" into tSQL
put revDataFromQuery(tab, return, gConnectionID, tSQL) into tData
if item 1 of tData = "revdberr" then
answer error "There was a problem querying the database:" & cr & tData
else
put tData into field "Data"
end if
#####END OF CODE#####
Re: Basic MySQL Query
Posted: Tue Feb 02, 2016 7:29 pm
by phaworth
I'd like to suggest one small but significant change to this.
Change the code:
Code: Select all
put "SELECT MessageP FROM Proxic WHERE NameP='what i''m looking for';" into tSQL
put revDataFromQuery(tab, return, gConnectionID, tSQL) into tData
to:
Code: Select all
put "what I'm looking for" into tSearch
put "SELECT MessageP FROM Proxic WHERE NameP=:1;" into tSQL
put revDataFromQuery(tab, return, gConnectionID, tSQL,"tSearch") into tData
This avoids the need to escape the quotes and also protects against SQL injection attacks.
Pete
Re: Basic MySQL Query
Posted: Fri Feb 12, 2016 2:07 pm
by A1Qicks
Perfect. Works as desired. Thanks guys!