Writing to a database

I'd like to capture my group's avatar names and timestamps as they pass a sensor on an island and write it to my database. What is the script I need to do that with?

You probably need to be versed in PHP and have a MySQL server setup so you can send info out to it from SL and store the data externally. Maybe some people do it slightly differently though.

I'm not much of an expert in that arena so I can't much help.

There's a pretty good example of an SL-script talking to a PHP-script here:

http://www.lslwiki.net/lslwiki/wakka.php?wakka=ExamplellHTTPRequest

I successfully got the script working but the only avatar name I can capture is the owner of the script. Can I get the avatar's a name that clicked it? I tried all the headers I see on llHTTPRequest but didn't get the result I was looking for.

To do that, you'll need to use parameters in your HTTP request. For example, in your touch event, get the avatar's name with "llDetectedName(0)", and send that to your PHP script as a GET or POST parameter. You can then extract it in PHP using the "$_GET" or "$_POST" arrays.

There's loads of good info about this stuff on the LSL wiki.

Thanks Pedro!

What makes it so difficult is knowing what the function name to look for is

Isis

You have to pass this information in a variable you pass to your php-script. The headers are just one part of the http-request.

requestid = llHTTPRequest("http://my.server.com/my-script.php",
[HTTP_METHOD, "POST",
HTTP_MIMETYPE, "application/x-www-form-urlencoded"],
"name=" + llDetectedName(0) + "&key=" + llDetectedKey(0));

in the PHP-Script, the name can be read with:

$name = $_POST['name'];
$key = $_POSt['key'];

Haruki,

How is the INSERT statement added to the PHP script to write to MySQL?

It's going to depend on what your database schema looks like (your table and columns). The SQL statement itself might look something like:

INSERT INTO resident_touches (`key`, `datetime`) VALUES (?, NOW());

Where you will either explicitly replace the '?' with the resident's key (or name if you index by that instead) or pass it to a function that will execute a prepared statement. The PHP function you actually use to execute the SQL will depend on what database library you are using. I recommend PDO (http://www.php.net/manual/en/book.pdo.php), but there are several other choices (such as the MySQL-specific library http://www.php.net/manual/en/book.mysql.php) as well.

ACK! I meant to ask how to pass the actual values into the INSERT from SL. This is my guess so far ...


$Host='myhostservice.net';
$User='user';
$Password='password';
$sl_name = [key];
$sl_date = [datetime];
$TableName = "resident_touches";

$Link = mysql_connect ($Host, $User, $Password);

$Query = "INSERT INTO $TableName ($sl_name, $sl_date) VALUES (?, NOW());"


mysql_db_query($DBName, $Query, $Link);
mysql_close($Link);

First thing... it makes life easier to 'select' your database in advance, just after the "mysql_connect" bit. You don't need to include it with all your queries then. Looks like this (replace dbname with your database name):

mysql_select_db('dbname');

Next thing, you need to make sure you pass the appropriate data from SL. Haruki showed how to do that above... you just pass something into the 'body' parameter of the llHTTPRequest function.

Finally, you use the PHP array $_REQUEST to get the data out. For example, if you passed the avatar's name as HTTP parameter 'avname', the you'd get it into PHP like this:

$sl_name = $_REQUEST['avname'];

There are security considerations too, but best to learn the basics first. You can go back and secure it later. (Terrible advice for programmers... but hey... it's how most of us do it! )


EDIT: oops... almost forgot. Your query isn't quite right there. The places where you've got $sl_name and $sl_date are actually where your field names go. The values you are inserting actually go in your "VALUES ( " bit, and you need to surround strings with single quotes. Your query might look like this (although it depends how you structured your db table):

$Query = "INSERT INTO $TableName (name, date) VALUES ('$sl_name', $sl_date)";

In this case, I'm assuming your fields are called 'name' and 'date', but you can call them pretty much anything you like. Strictly speaking, MySQL also likes backquotes around the table and field names, like `this`, although it usually copes without them.

Pedro Said:

Your biggest risk when dealing with this stuff is SQL injection. Hackers, or hacker bots will try and feed SQL commands into your GET or POST variables.

http://en.wikipedia.org/wiki/SQL_injection

The easiest way to prevent most of these attacks is simple....use the PHP code below for all your get or post variables at the top of the script before using them.

$VisitorName=mysql_real_escape_string($_GET['VisitorName']);

or if using POST,

$VisitorName=mysql_real_escape_string($_POST['VisitorName']);

For more info vist: http://ca3.php.net/mysql_real_escape_string


Cheers! zg

Yeah. I tend to use prepared statements, which automatically escape strings used to replace placeholders (e.g. '?'). It's easy in PDO; hopefully there's an equivalent in the mysql_* library too.

http://www.php.net/manual/en/pdo.prepare.php

The light bulb has come on! Thank you all for your patience You've saved me alot of work

excuz me i read all your conversation but it is something i didn't understand well.
for example my visitor are detected with a sensor. so in my sensor function i write this:

requestId = llHTTPRequest("url",[HTTP_METHOD,"POST",HTTP_MIMETYPE,"application/x-www-form-urlencoded"], "&avname ="+ llList2String(visitor_list,i)+"&visit_date=" + llList2String(visitor_list,i+1));

so how can i put my avatar name and visit date in my database containing my table where all visitor have to be stocked.

is it like in the php script if i write $_REQUEST with my variables above : avname and visit_date, i will automatically get my visitors name and visit date?
and then if i use my command INSERT to add my visitors in my table i will get all my visitors put in my table?

In your php script, try this:

$avname=mysql_real_escape_string($_POST['avname']);


Use the $avname variable to then do your insert query.

sorry i did what you told me but it didn't march. can i send u my script so that you can tell me what is the problem???