Basic MySQL Query

[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!

[ghettocottage]

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

[A1Qicks]

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

Thanks!

[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#####

[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

[A1Qicks]

Perfect. Works as desired. Thanks guys!