Basic MySQL Query

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
A1Qicks
Posts: 79
Joined: Sat Dec 26, 2015 10:47 am

Basic MySQL Query

Post by A1Qicks » Thu Jan 21, 2016 2:11 pm

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!

ghettocottage
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 311
Joined: Tue Apr 10, 2012 9:18 am

Re: Basic MySQL Query

Post by ghettocottage » Thu Jan 21, 2016 3:47 pm

Tell us what the table is named and what the column is named

A1Qicks
Posts: 79
Joined: Sat Dec 26, 2015 10:47 am

Re: Basic MySQL Query

Post by A1Qicks » Fri Jan 22, 2016 11:25 am

Table is "Proxic", column to be located is "NameP", column to be returned is "MessageP".

Thanks!

MaxV
Posts: 1574
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Basic MySQL Query

Post by MaxV » Tue Feb 02, 2016 5:07 pm

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#####
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: Basic MySQL Query

Post by phaworth » Tue Feb 02, 2016 7:29 pm

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

A1Qicks
Posts: 79
Joined: Sat Dec 26, 2015 10:47 am

Re: Basic MySQL Query

Post by A1Qicks » Fri Feb 12, 2016 2:07 pm

Perfect. Works as desired. Thanks guys!

Post Reply

Return to “Databases”