Databases allow you to build dynamically driven websites, content management systems amongst other things.

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,

put "SELECT LAST_INSERT_ID()" into mySQL
put revDataFromQuery(tab, return, myConID, mySQL) into theInsertedRecordID
put theInsertedRecordID

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

Deleting 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 "DELETE FROM" && myTable && "WHERE u_id='" & vID &"'" into mySQL
put mySQL
revExecuteSQL myConnection, mySQL
Contact Us

We're not around right now. But you can send us an email and we'll get back to you, asap.

Not readable? Change text. captcha txt

Start typing and press Enter to search