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);
}
?>