Databases

The best way to get started with understanding how database work in LiveCode Server, is by looking at an example scripts. The scripts use a simple database table called users with the following structure: id | surname | firstname | title

Retrieving Records

Retrieving records is done by executing a select statement.

put revOpenDatabase("mysql","localhost", "livecode", "root", "password") into myConID
put "SELECT * FROM users" into mySQL
if myConID is a number then
put revDataFromQuery(tab, return, myConID, mySQL) into myList
set the itemdel to tab
repeat for each line myLine in myList
put item 1 of tLine into vId
put item 2 of tLine into vSurname
put item 3 of tLine into vFirstname
put item 4 of tLine into vTitle
put vId & "-" & vSurname & "-" & vFirstname & "-" & vTitle & "<br />"
end repeat
end if

Inserting Data

put revOpenDatabase("mysql","localhost", "livecode", "root", "") into tConID
if tResult is a number then
put tConID into myConnection
put "connected"
else
put empty into myConnection
put "not connected"
end if
put "users" into myTable
put "u_firstname, u_surname, u_title" into myTableFields
put "Henry" into vFirstname
put "Smith" into vSurname
put "Mr" into vTitle
put "INSERT INTO" && myTable && "(" & myTableFields & ") VALUES (:1, :2, :3)" into mySQL
revExecuteSQL myConnection, mySQL, "vFirstname", "vSurname", "vTitle"
if the result is a number then
put "New record added."
else
put "There was a problem adding the record to the database:" & cr & the result
end if

Getting the ID of a record you just added

You can get the ID of the record Â your just created by using MySQL’s last_insert_ID() statement,

Updating Records

put revOpenDatabase("mysql","localhost", "livecode", "root", "") into tResult
if tResult is a number then
put tResult into myConnection
put "connected"
else
put empty into myConnection
put "not connected"
end if
put "users" into myTable
put "Kitty" into vSurname
//put "INSERT INTO" && myTable && "(" & myTableFields & ") VALUES (:1, :2, :3)" into mySQL
PUT "UPDATE" && myTable && "SET u_surname='" & vSurname & "' WHERE u_id='4'" into mySQL
revExecuteSQL myConnection, mySQL
if the result is a number then
put "New record added."
else
put "There was a problem adding the record to the database:" & cr & the result
end if