SQL RESTian example in PHP

Here’s a PHP script that reads and writes from a SQL database. This example assumes you’ve got a SQL database account on the same machine that the script is running on. It also assumes you’re using a .htaccess file that looks something like this:

RewriteEngine On
RewriteBase /directoryname/
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^.*$ sqlRestDatalog.php

/directoryname/ above is the path to your directory from the root of the server. So, for example, if you had an account on a server with a URL like this:

http://www.myserver.com/~myaccount/directoryname/

The path for this in the .htaccess file would be /~myaccount/directoryname/.

For more on this,look up the mod_rewrite rules for the Apache web server.

<?php

/*
	RESTian based SQL access script.

	created 20 Oct 2009
	modified 15 Oct 2010
	by Tom Igoe
	
	Assumes the .htaccess file of a directory points at this file. 
	
	includes secret.php. which should include
	$username and $password
	Assumes the URL will look like this:
	
	http://example.com/table_name/				to view the whole table
	http://example.com/table_name/record_num	to view a particular record
	http://example.com/item_name/value			to add a value
	http://example.com/item_name/value/delete	to delete a value


*/

// get username & pwd info:
include "secret.php";

// initialize variables:
$sensorValue = null;         // value from the sensor
$date = null;                // date string: YYYY-MM-DD
$time = null;                // time string: HH:mm:ss in 24-hour clock
$recordNumber = null;        // which record to delete
$list = false;               // whether or not to list results in HTML format
$databaseName = 'databaseName';   	// put in your database name here
$tableName = null;			 // put in the table name you want to access


// split the URI string into tokens:
$tokens = explode("/", $_SERVER['REQUEST_URI']);
print_r($tokens);

// if you have three tokens, you have the table name:
if (count($tokens) > 3) {
	$tableName = $tokens[3];
	echo "table name: ".$tableName."<br>";
}

// if you have four tokens, you have the record number to browse
// or the item name to put the value into:
if (count($tokens) > 4) {
	// if it's an integer, then it's the record number:
	if (intval($tokens[4]) > 0) {
		$recordNumber = $tokens[4];
		echo "record number: ".$recordNumber."<br>";
	
	} else {
	// if it's not an integer, it's an item name:
		echo "item name: ".$tokens[4]."<br>";
	} 	
}

// if you have five tokens, you have the sensor value,
// or you have the record to delete:
if (count($tokens) > 5) {
	if (isset($recordNumber)) {
		if ($tokens[5] == "delete") {
			$action = "delete";
		}
	} else {
		$sensorValue = $tokens[5];
		echo "sensor value: ".$sensorValue."<br>";
	}
}

// open the database:
$link = open_database('localhost', $databaseName, $username, $password);


if (isset($tableName)) {
		if (isset($sensorValue) && !isset($recordNumber)) {
			echo "inserting new record";
			// make sure date and time have values:
	   		 if (!isset($date) || !isset($time)) {
	        	// if not values, generate them from the server time
	        	// (I should probably properly check for valid date and time strings here):
	        	list($date, $time) = split(" ", date("Y-m-d H:i:s"));
	    }
	
	    // Only insert if we got a sensor value:
	    if (isset($sensorValue)) {
	        insert_record($tableName, $sensorValue, $date, $time);
	    }
	}
	
	if (!isset($sensorValue) && !isset($recordNumber)) {
		echo "listing table";
		 echo "<html><head></head><body>";
    	// browse the whole table:
    	browse_table($tableName);
    	echo "</body></html>";
	}
	
	if (!isset($sensorValue) && isset($recordNumber)) {
		echo "listing record";
		 if (isset($recordNumber)) {
	        list_record($tableName, $recordNumber);
	    }
	}
	
	if (($action == "delete") && isset($recordNumber)) {
		echo "deleting record";
		 // only delete if we got a record number:
	    if (isset($recordNumber)) {
	        delete_record($tableName, $recordNumber);
	    }
	}
	
}


// close the database:
close_database($link);

end;

//    Functions    -------------------------------

// Connect to a server and open a database:
function open_database($myServer, $myDatabase, $myUser, $myPwd) {
    $myLink = mysql_connect($myServer, $myUser, $myPwd)
       or die('Could not connect: ' . mysql_error());
    if ($list == 1) {
        echo 'Connected successfully';
    }
    mysql_select_db($myDatabase) or die('Could not select database');
    return $myLink;
}

// close an open database:
function close_database($myLink) {
    mysql_close($myLink);
}

// select all from a table:
function browse_table($myTable) {
    $query = mysql_real_escape_string("SELECT * FROM `$myTable`");
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());

    // Printing results in HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "\t<tr>\n";
           foreach ($line as $col_value) {
               echo "\t\t<td>$col_value</td>\n";
           }
           echo "\t</tr>\n";
    }
    echo "</table>\n";
    // Free resultset
    mysql_free_result($result);
}

// insert a new record in the table:
function insert_record($myTable, $recValue, $recDate, $recTime) {
    $query = stripslashes(mysql_real_escape_string("INSERT INTO `$myTable` (`Value`, `Date`, `Timestamp`) VALUES ('$recValue', '$recDate','$recTime')"));

    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    // Free resultset
    mysql_free_result($result);

}


// delete a record from the table:
function list_record($myTable, $recNum) {
    $query = mysql_real_escape_string("SELECT * FROM `$myTable` WHERE `ID` = $recNum");
     $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    
     // Printing results in HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "\t<tr>\n";
           foreach ($line as $col_value) {
               echo "\t\t<td>$col_value</td>\n";
           }
           echo "\t</tr>\n";
    }
    echo "</table>\n";

    // Free resultset
    mysql_free_result($result);
}

// delete a record from the table:
function delete_record($myTable, $recNum) {

    $query = mysql_real_escape_string("DELETE FROM `$myTable` WHERE `ID` = $recNum LIMIT 1");
    $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    
     // Printing results in HTML
    echo "<table>\n";
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
        echo "\t<tr>\n";
           foreach ($line as $col_value) {
               echo "\t\t<td>$col_value</td>\n";
           }
           echo "\t</tr>\n";
    }
    echo "</table>\n";

    // Free resultset
    mysql_free_result($result);
}

?>