Basic MySQL Query
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Basic MySQL Query
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!
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!
-
- Livecode Opensource Backer
- Posts: 366
- Joined: Tue Apr 10, 2012 9:18 am
Re: Basic MySQL Query
Tell us what the table is named and what the column is named
Re: Basic MySQL Query
Table is "Proxic", column to be located is "NameP", column to be returned is "MessageP".
Thanks!
Thanks!
Re: Basic MySQL Query
Here your SQL code:
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#####
Code: Select all
SELECT MessageP FROM Proxic WHERE NameP='what i''m looking for';
########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#####
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Basic MySQL Query
I'd like to suggest one small but significant change to this.
Change the code:
to:
This avoids the need to escape the quotes and also protects against SQL injection attacks.
Pete
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
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
Pete
Re: Basic MySQL Query
Perfect. Works as desired. Thanks guys!