Welcome to the Second Life Forums Archive

These forums are CLOSED. Please visit the new forums HERE

llHTTPRequest <-> mySQL (PHP)

Lasivian Leandros
Hopelessly Obsessed
Join date: 11 Jul 2005
Posts: 238
08-25-2006 23:18
Here's a freebie for you, tho it's probably not for newbies.

(UPDATE: I did finally get this to work, I was apparently sending commands in the wrong format. More details can be found in my free script pack)

I paid someone to write this for me, but i've never been able to get it to work on my server for awhtever reason, so I figure maybe someone else can get some good out of it. :P

It's supposed to move data back and forth from a mySQL database using llHTTPRequest.

Have fun

CODE
<?php

/**
* Date: Aug 24th, 2006
* Written for: (Lasivian)
* Description: A simple HTTP request storage bin for external Second Life storage.
**/

/**
MySQL 4.0 Database Schema

CREATE TABLE `lslstore` (
`data_key` varchar(64) NOT NULL,
`data_group` varchar(64) default NULL,
`data_value` varchar(255) NOT NULL,
`agent_id` varchar(36) NOT NULL,
`access_time` timestamp NOT NULL,
UNIQUE KEY `dedupe` (`data_key`,`agent_id`),
KEY `group_agent` (`data_group`,`agent_id`),
KEY `key_agent` (`data_key`,`agent_id`)
) TYPE=MyISAM;

**/


/**
* Example Usage:
* Store 1: http://localhost/ls.php?command=store&key=name&value=Josh&group=profile&owner_key=1
* Store 2: http://localhost/ls.php?command=store&key=nick&value=Skiz&group=profile&owner_key=1
* Get: http://localhost/ls.php?command=get&key=name&owner_key=1
* Get Group: http://localhost/ls.php?command=getgroup&group=profile&owner_key=1
* Delete: http://localhost/ls.php?command=delete&key=name&owner_key=1
* Delete Group: http://localhost/ls.php?command=deletegroup&group=profile&owner_key=1
**/

// Database Settings
define('DB_HOST','localhost'); // MySQL Server Host
define('DB_USER','USERNAME'); // MySQL User
define('DB_PASS','PASSWORD'); // MySQL Password
define('DB_NAME','DBNAME'); //The name of the database

//Connect to the database or throw a nasty error
$db = mysql_connect(DB_HOST,DB_USER,DB_PASS) or die(mysql_error());
mysql_select_db(DB_NAME,$db) or die(mysql_error());

/**
* Here is the guts of the script. We are going to validate the request
* that was made to see if it was a valid action then call a function
* that will insert or return the data to the user.
*
* If you only want to allow GET or POST methods you can change the
* parameter that is being passed to the command. REQUEST covers
* nearly all request types including get, post, cookies and sessions.
*
* ex: change cmd_store($_REQUEST) to cmd_store($_POST)
**/

switch(strtoupper($_REQUEST['command'])){
case 'STORE':
cmd_store($_REQUEST);
break;
case 'GET':
cmd_get($_REQUEST);
break;
case 'GETGROUP':
cmd_get_group($_REQUEST);
break;
case 'DELETE':
cmd_delete($_REQUEST);
break;
case 'DELETEGROUP':
cmd_delete_group($_REQUEST);
break;
case 'DELETEALL':
cmd_delete_all($_REQUEST);
break;
default:
echo ("Invalid Command Requested");
}

//close the database connection
mysql_close($db);

//stop execution
die();

/**************************************************************************************/

/**
* Database Functionality
* The following functions are passed the full request above and will use that
* request to manage the database and process requests.
**/

/**************************************************************************************/

/**
* Command: STORE
* Usage: Stores a string in the database
* Required Parameters: key, value, group, agent
**/
function cmd_store($params){
// Check for required parameters
$req = array('key','value','group');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "REPLACE INTO lslstore (data_key,data_value,data_group,agent_id, access_time) VALUES ('$key', '$value', '$group', '$owner_id',
NOW())";
$result = mysql_query($sql) or die(mysql_error());
echo 'Store successful.';
}

/**
* Command: GET
* Usage: Retrieves a string from the database
* Required Parameters: key
**/
function cmd_get($params){
// Check for required parameters
$req = array('key');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "SELECT data_value FROM lslstore WHERE data_key = '$key' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
//spit out the results
while($row = mysql_fetch_assoc($result)){
echo $row['data_value']."\n";
}
}

/**
* Command: GET_GROUP
* Usage: Retrieves a set of keys and values for a group
* Required Parameters: key, group
**/
function cmd_get_group($params){
// Check for required parameters
$req = array('group');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "SELECT data_key, data_value FROM lslstore WHERE data_group = '$group' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
//spit out the results in format: key=value
while($row = mysql_fetch_assoc($result)){
echo $row['data_key'].'='.$row['data_value']."\n";
}
}

/**
* Command: DELETE
* Usage: Deletes a key from the database
* Required Parameters: key
**/
function cmd_delete($params){
// Check for required parameters
$req = array('key');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE data_key = '$key' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...';
}

/**
* Command: DELETE_GROUP
* Usage: Deletes a group from the database
* Required Parameters: group
**/
function cmd_delete_group($params){
// Check for required parameters
$req = array('group');
foreach($req as $rkey){
$$rkey = mysql_real_escape_string($params[$rkey]);
}
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE data_group = '$group' AND agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...';
}

/**
* Command: DELETE_ALL
* Usage: Deletes all entries for a agent/owner
* Required Parameters: (none)
**/
function cmd_delete_all($params){
$owner_id = get_owner_id();
//create and execute the query
$sql = "DELETE FROM lslstore WHERE agent_id = '$owner_id'";
$result = mysql_query($sql) or die(mysql_error());
echo 'Deleted...'; // Tell the world we're deleting data
}

//returns which owner id to use (modify as needed, uses ENV then owner_key if not set)
function get_owner_id(){
if(isset($_SERVER["HTTP_X_SECONDLIFE_OWNER_KEY"])){
return mysql_real_escape_string($_SERVER["HTTP_X_SECONDLIFE_OWNER_KEY"]);
}else{
if(isset($_REQUEST['owner_key'])){
return mysql_real_escape_string($_REQUEST['owner_key']);
}else{
die('You must specify an owner_key parameter');
}
}
}
?>
_____________________
From: someone
"SL is getting to be like a beat up old car with a faulty engine which keeps getting a nice fresh layer of paint added on, while the engine continues to be completely unreliable." - Kex Godel
Nada Epoch
The Librarian
Join date: 4 Nov 2002
Posts: 1,423
Discussion Thread
08-27-2006 16:59
/54/9d/133214/1.html
_____________________
i've got nothing. ;)