Using databases in Second Life or Open Sim to enhance user experience Tom Connors, SciEthis Interactive 2012
Second Life and Open Sim each have a built in system for editing the virtual world that allows for finely crafted user experiences -‐ namely, the Linden Scripting Language. Although this language is very useful for most functionality developers and users desire, one feature unattainable with just LSL is persistent storage. Shopkeepers wishing to monitor customer purchases, game administrators wishing to
maintain a high score list, and teachers wishing to log test scores, as well as a myriad of other
individuals, can benefit from using a database in conjunction with their LSL. The process of setting up and using a database is fairly straightforward, but more complex than the average LSL based system. In this tutorial, I will tell you what you need to do before setting up your database system, I will
demonstrate how to set up your system, and I will point you in the right direction for further resources.
We will need to use three components to bring our system to fruition:
LSL script -‐ used to interact with the avatar or virtual world. Either sends data to or receives data from (or both) a PHP webpage.
PHP webpage -‐ used to relay the information between the LSL script and the mySQL database. This is necessary because LSL does not have functions for performing mySQL queries.
mySQL database -‐ the place where the data is stored and accessed via mySQL queries: statements telling the database what to do with data.
The following steps detail how to prepare your database.
Acquire a web host -‐ Find a web hosting company that offers mySQL and PHP and buya site. Web hosting hub is a good option. There's many others. You just need a host that allows you to edit the code of your pages and to use mySQL and PHP.
Install the free software necessary for editing your code and uploading files to the server. I recommend TextWrangler for editing the code if you are using a Mac and Notepad++ if you're running Windows. For uploading your files, FileZilla is a great option for either operating system. Many web hosts will offer built in text editors that may be good enough.
Create a test html page and upload it to the server to test your site. Call the page "test.html". Sample code below: <html> <body> <h1>It works!</h1> </body> </html>
Once you can see this page in your browser, you're ready to test php. Create another test page called "test.php". Sample code below:
<?php
echo ("Even php works!"); ?>
Once php is established to work, it's time to set up the database. Your web host will probably make this easy for you. Look for options such as "MySQL Databases" or "phpMyAdmin." Setting up the database will be different based on your web host, but the process shouldn't be complicated.
Make a database. For the purpose of this tutorial, we will assume we've made a database called "Test_DB."
Databases need tables to store the actual data. You must make a table within the database. Let's assume we make one called "test_table."
Add whatever fields you need in the table. We'll assume we are using our database to relate point values to second life keys. This will require a "points" field and a "key" field.
Now it is time to write test php code for our table. Let's look at a simple example. Let's call this one "test_read.php"
!!! CODE STARTS BELOW THIS LINE !!!
<?php
//set up the connection to the database
//$con is a variable that holds the connection information
//the connection information is obtained with the function mysql_connect //mysql_connect takes three parameters:
//host of database - here is it "localhost." This will probably be the case for you as well //database user name - the name of a user permitted to access the table.
//user name password - the password of that user. $con = mysql_connect("localhost","user","password"); //error checking
//could be phrased "If the connection doesn't work, end this process and give an error report" if(!$con)
{
die('Could not connect: ' . mysql_error()); }
//select which specific database we want mysql_select_db("Test_DB", $con);
//build the query - the command/request to give to the database $query = "SELECT * FROM test_table WHERE 1";
//run the query, store the result in a variable called $result $result = mysql_query($query);
//check how many rows we got back with our query $rows_returned = mysql_num_rows($result); //if we got anything back,
if($rows_returned > 0) {
//cycle through and print what we got. while($row = mysql_fetch_array($result)) {
echo $row[points] . ' ' . $row[key] . '<br/>'; }
}
//close the connection mysql_close($con); ?>
!!! END PHP CODE !!!
With that example, we saw how to get what is in the table. The next example will show us how to write things to the table. Let's call this one test_write.php.
!!! CODE BEGINS BELOW THIS LINE !!!
<?php
//first create variables to hold the data we'll write to the table. //a variable for the avatar's key.
$avatar_key = '12345678-1234-1234-1234-123456789101'; //a variable for the avatar's points:
$points = 90;
//connect to the database
$con = mysql_connect("localhost","user","password"); //error checking
//could be phrased "If the connection doesn't work, end this process and give an error report" if(!$con)
{
die('Could not connect: ' . mysql_error()); }
mysql_select_db("Test_DB", $con);
//build the query - the command/request to give to the database
$query = "INSERT INTO test_table (test_table.key, test_table.points) VALUES ('" . $avatar_key . "', '" . $points . "')";
//print out the query on the screen for us to examine echo $query . '<br/>';
//run the query, store the result in a variable called $result $result = mysql_query($query);
//will print 1 is success, nothing otherwise. echo ($result);
//close the connection mysql_close($con); ?> !!! END PHP CODE !!!
Now we have seen how to add data to a database and how to retrieve it using php and mysql queries. Next, we have to write some lsl code. We'll start with a script that writes an avatar's key and an arbitrary point value to the database whenever the object the script is in is touched. Put this script in an object and compile it (but don't touch the object yet).
!!! BEGIN LSL CODE !!!
//define a string that contains the url of the php page we are going use. string writingPage = "http://www.yourWebsite.com/test_write.php";
//define a key that will contain the key of the request to send the data to the database. This is useful in error checking. key requestKey;
default {
touch_start(integer numDetected) {
//define a key that contains the key of the avatar who touched the object. key toucher = llDetectedKey(0);
//define an integer that contains the number of points this avatar gets. integer points = 90;
//define a string to contain the body of the request. string body = "";
//define a string to contain the complete url of the request. //note that the complete url is more than just the page's address. //you need additional information for the php, explained later.
string URL = writingPage + "?" + "key=" + (string)toucher + "&points=" + (string)points; //define a list to contain the parameters of the http request.
list parameters = [HTTP_METHOD, "GET"];
//send the request
requestKey = llHTTPRequest(URL, parameters, body); }
http_response( key request_id, integer status, list metadata, string body ) {
//if this is a response to the request we just sent (it probably is.) if(request_id == requestKey)
{
//tell us what was returned.
llOwnerSay("Status: " + (string)status); llOwnerSay("Body: " + body); } } } !!! END LSL CODE !!!
Let's take a look at the line:
string URL = writing_page + "?" + "key=" + (string)avatarKey + "&points=" + (string)points; If we format that so that all of the variable names are replaced with their values (assuming the toucher's key is "d456c56a-‐665a-‐494a-‐91f2-‐d7d8412da6fe"), we get this for the url:
http://www.myWebsite.com/lsl/writeToTest.php?key=d456c56a-‐665a-‐494a-‐91f2-‐ d7d8412da6fe&points=90
Everything up to the .php makes sense; that's just the address and page name. So what's the question mark and all that other stuff? These parts of the url after the page name are parameters to the php file.
The question mark tells the browser "the stuff that follows will be php parameters." The bit that looks like
key=d456c56a-‐665a-‐494a-‐91f2-‐d7d8412da6fe
tells the php file that it has access to a parameter named key with that value. The same thing can be seen with the points part
&points=90
The ampersand just means "this is another parameter." The php file now has access to a parameter named points, set to 90.
So, how do we make use of these parameters in our php? Here's an example to help:
!!! BEGIN PHP CODE !!!
<?php
//use the key parameter to set the $avatar_key variable $avatar_key = $_GET['key'];
//use the points parameter to set the $points variable $points = $_GET['points'];
//display the results. echo $avatar_key . '<br/>'; echo $points; ?> !!! END PHP !!!
The above php uses its two parameters, key and points, to set two variables, $avatar_key and $points. We can use this in our test_write.php file. Rather than hard coding the variables that we write to the database as we did in the first example using test_write.php, we can get those variables using parameters. Update your test_write.php, save it, then touch your object.
Did the object say your query, then the number one? That's good! That means your query was successful.
Check your database table by reloading test_read.php to see if your data made it there.
Obviously, we also need to read from the database, so let's take a look at that. The script will send an avatar key to a php page, and get back that person's number of points.
We will need to modify test_read.php before this code will run properly.
!!! BEGIN LSL SCRIPT !!!
//define a string that contains the url of the php page we are going use. string readingPage = "http://www.yourWebsite.com/test_read.php";
//define a key that will contain the key of the request to send the data to the database. //We need this to know we're getting the right data when we receive the response. key requestKey;
default {
touch_start(integer numDetected) {
//define a key that contains the key of the avatar who touched the object. key toucher = llDetectedKey(0);
//define a string to contain the body of the request. string body = "";
//define a string to contain the complete url of the request. //note that the complete url is more than just the page's address. //you need additional information for the php, explained later. string URL = readingPage + "?" + "key=" + (string)toucher; //define a list to contain the parameters of the http request. list parameters = [HTTP_METHOD, "GET"];
//send the request
}
//this function runs when we hear back from the php page.
http_response(key request_id, integer status, list metadata, string body) {
//check that the request_id matches our previously defined requestKey. A security measure. if(request_id == requestKey)
{
llOwnerSay("Got back a point value of " + body); } } } !!! END LSL SCRIPT !!!
Now let's look at the slightly modified test_read.php. The three changes to note are: 1) setting $avatar_key using the 'key' parameter
2) using a different sql query with a WHERE clause
3) when checking rows returned, we don't need to loop through them we just return the first row's point value.
!!! BEGIN PHP CODE !!!
<?php
//get the key from the parameter $avatar_key = $_GET['key'];
//set up the connection to the database
$con = mysql_connect("localhost","userName","password"); //error checking
if(!$con) {
die('Could not connect: ' . mysql_error()); }
//select which specific database we want mysql_select_db("Test_DB", $con);
//build the query - the command/request to give to the database
$query = "SELECT test_table.points FROM test_table WHERE test_table.key = '" . $avatar_key . "'"; //run the query, store the result in a variable called $result
$result = mysql_query($query);
//check how many rows we got back with our query $rows_returned = mysql_num_rows($result); //if we got anything back,
if($rows_returned > 0) {
$row = mysql_fetch_array($result); echo $row[points];
}
//close the connection mysql_close($con); ?>
!!! END PHP CODE !!!
Once you've got that written and saved, go back into SL/OS and click on your object. You should get back a point value of 90.
These php and lsl scripts can be endlessly modified to handle your specific database needs. There's a multitude of websites to help you write your lsl, php, and mysql queries. Here's a few I find most helpful:
For an introduction to php:
http://w3schools.com/php/default.asp
For information about using mysql with php: http://w3schools.com/php/php_mysql_intro.asp
For general lsl information:
http://lslwiki.net/lslwiki/wakka.php?wakka=HomePage
