mod_rewrite<\/a> rules for the Apache web server.<\/p>\n\r\n<?php\r\n\r\n\/*\r\n\tRESTian based SQL access script.\r\n\r\n\tcreated 20 Oct 2009\r\n\tmodified 15 Oct 2010\r\n\tby Tom Igoe\r\n\t\r\n\tAssumes the .htaccess file of a directory points at this file. \r\n\t\r\n\tincludes secret.php. which should include\r\n\t$username and $password\r\n\tAssumes the URL will look like this:\r\n\t\r\n\thttp:\/\/example.com\/table_name\/\t\t\t\tto view the whole table\r\n\thttp:\/\/example.com\/table_name\/record_num\tto view a particular record\r\n\thttp:\/\/example.com\/item_name\/value\t\t\tto add a value\r\n\thttp:\/\/example.com\/item_name\/value\/delete\tto delete a value\r\n\r\n\r\n*\/\r\n\r\n\/\/ get username & pwd info:\r\ninclude \"secret.php\";\r\n\r\n\/\/ initialize variables:\r\n$sensorValue = null; \/\/ value from the sensor\r\n$date = null; \/\/ date string: YYYY-MM-DD\r\n$time = null; \/\/ time string: HH:mm:ss in 24-hour clock\r\n$recordNumber = null; \/\/ which record to delete\r\n$list = false; \/\/ whether or not to list results in HTML format\r\n$databaseName = 'databaseName'; \t\/\/ put in your database name here\r\n$tableName = null;\t\t\t \/\/ put in the table name you want to access\r\n\r\n\r\n\/\/ split the URI string into tokens:\r\n$tokens = explode(\"\/\", $_SERVER['REQUEST_URI']);\r\nprint_r($tokens);\r\n\r\n\/\/ if you have three tokens, you have the table name:\r\nif (count($tokens) > 3) {\r\n\t$tableName = $tokens[3];\r\n\techo \"table name: \".$tableName.\"<br>\";\r\n}\r\n\r\n\/\/ if you have four tokens, you have the record number to browse\r\n\/\/ or the item name to put the value into:\r\nif (count($tokens) > 4) {\r\n\t\/\/ if it's an integer, then it's the record number:\r\n\tif (intval($tokens[4]) > 0) {\r\n\t\t$recordNumber = $tokens[4];\r\n\t\techo \"record number: \".$recordNumber.\"<br>\";\r\n\t\r\n\t} else {\r\n\t\/\/ if it's not an integer, it's an item name:\r\n\t\techo \"item name: \".$tokens[4].\"<br>\";\r\n\t} \t\r\n}\r\n\r\n\/\/ if you have five tokens, you have the sensor value,\r\n\/\/ or you have the record to delete:\r\nif (count($tokens) > 5) {\r\n\tif (isset($recordNumber)) {\r\n\t\tif ($tokens[5] == \"delete\") {\r\n\t\t\t$action = \"delete\";\r\n\t\t}\r\n\t} else {\r\n\t\t$sensorValue = $tokens[5];\r\n\t\techo \"sensor value: \".$sensorValue.\"<br>\";\r\n\t}\r\n}\r\n\r\n\/\/ open the database:\r\n$link = open_database('localhost', $databaseName, $username, $password);\r\n\r\n\r\nif (isset($tableName)) {\r\n\t\tif (isset($sensorValue) && !isset($recordNumber)) {\r\n\t\t\techo \"inserting new record\";\r\n\t\t\t\/\/ make sure date and time have values:\r\n\t \t\t if (!isset($date) || !isset($time)) {\r\n\t \t\/\/ if not values, generate them from the server time\r\n\t \t\/\/ (I should probably properly check for valid date and time strings here):\r\n\t \tlist($date, $time) = split(\" \", date(\"Y-m-d H:i:s\"));\r\n\t }\r\n\t\r\n\t \/\/ Only insert if we got a sensor value:\r\n\t if (isset($sensorValue)) {\r\n\t insert_record($tableName, $sensorValue, $date, $time);\r\n\t }\r\n\t}\r\n\t\r\n\tif (!isset($sensorValue) && !isset($recordNumber)) {\r\n\t\techo \"listing table\";\r\n\t\t echo \"<html><head><\/head><body>\";\r\n \t\/\/ browse the whole table:\r\n \tbrowse_table($tableName);\r\n \techo \"<\/body><\/html>\";\r\n\t}\r\n\t\r\n\tif (!isset($sensorValue) && isset($recordNumber)) {\r\n\t\techo \"listing record\";\r\n\t\t if (isset($recordNumber)) {\r\n\t list_record($tableName, $recordNumber);\r\n\t }\r\n\t}\r\n\t\r\n\tif (($action == \"delete\") && isset($recordNumber)) {\r\n\t\techo \"deleting record\";\r\n\t\t \/\/ only delete if we got a record number:\r\n\t if (isset($recordNumber)) {\r\n\t delete_record($tableName, $recordNumber);\r\n\t }\r\n\t}\r\n\t\r\n}\r\n\r\n\r\n\/\/ close the database:\r\nclose_database($link);\r\n\r\nend;\r\n\r\n\/\/ Functions -------------------------------\r\n\r\n\/\/ Connect to a server and open a database:\r\nfunction open_database($myServer, $myDatabase, $myUser, $myPwd) {\r\n $myLink = mysql_connect($myServer, $myUser, $myPwd)\r\n or die('Could not connect: ' . mysql_error());\r\n if ($list == 1) {\r\n echo 'Connected successfully';\r\n }\r\n mysql_select_db($myDatabase) or die('Could not select database');\r\n return $myLink;\r\n}\r\n\r\n\/\/ close an open database:\r\nfunction close_database($myLink) {\r\n mysql_close($myLink);\r\n}\r\n\r\n\/\/ select all from a table:\r\nfunction browse_table($myTable) {\r\n $query = mysql_real_escape_string(\"SELECT * FROM `$myTable`\");\r\n $result = mysql_query($query) or die('Query failed: ' . mysql_error());\r\n\r\n \/\/ Printing results in HTML\r\n echo \"<table>\\n\";\r\n while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {\r\n echo \"\\t<tr>\\n\";\r\n foreach ($line as $col_value) {\r\n echo \"\\t\\t<td>$col_value<\/td>\\n\";\r\n }\r\n echo \"\\t<\/tr>\\n\";\r\n }\r\n echo \"<\/table>\\n\";\r\n \/\/ Free resultset\r\n mysql_free_result($result);\r\n}\r\n\r\n\/\/ insert a new record in the table:\r\nfunction insert_record($myTable, $recValue, $recDate, $recTime) {\r\n $query = stripslashes(mysql_real_escape_string(\"INSERT INTO `$myTable` (`Value`, `Date`, `Timestamp`) VALUES ('$recValue', '$recDate','$recTime')\"));\r\n\r\n $result = mysql_query($query) or die('Query failed: ' . mysql_error());\r\n \/\/ Free resultset\r\n mysql_free_result($result);\r\n\r\n}\r\n\r\n\r\n\/\/ delete a record from the table:\r\nfunction list_record($myTable, $recNum) {\r\n $query = mysql_real_escape_string(\"SELECT * FROM `$myTable` WHERE `ID` = $recNum\");\r\n $result = mysql_query($query) or die('Query failed: ' . mysql_error());\r\n \r\n \/\/ Printing results in HTML\r\n echo \"<table>\\n\";\r\n while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {\r\n echo \"\\t<tr>\\n\";\r\n foreach ($line as $col_value) {\r\n echo \"\\t\\t<td>$col_value<\/td>\\n\";\r\n }\r\n echo \"\\t<\/tr>\\n\";\r\n }\r\n echo \"<\/table>\\n\";\r\n\r\n \/\/ Free resultset\r\n mysql_free_result($result);\r\n}\r\n\r\n\/\/ delete a record from the table:\r\nfunction delete_record($myTable, $recNum) {\r\n\r\n $query = mysql_real_escape_string(\"DELETE FROM `$myTable` WHERE `ID` = $recNum LIMIT 1\");\r\n $result = mysql_query($query) or die('Query failed: ' . mysql_error());\r\n \r\n \/\/ Printing results in HTML\r\n echo \"<table>\\n\";\r\n while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {\r\n echo \"\\t<tr>\\n\";\r\n foreach ($line as $col_value) {\r\n echo \"\\t\\t<td>$col_value<\/td>\\n\";\r\n }\r\n echo \"\\t<\/tr>\\n\";\r\n }\r\n echo \"<\/table>\\n\";\r\n\r\n \/\/ Free resultset\r\n mysql_free_result($result);\r\n}\r\n\r\n?>\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 … <\/p>\n
Continue reading “SQL RESTian example in PHP”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100,3],"tags":[101],"class_list":["post-979","post","type-post","status-publish","format-standard","hentry","category-code","category-PHP","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/979","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/comments?post=979"}],"version-history":[{"count":27,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/979\/revisions"}],"predecessor-version":[{"id":1017,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/posts\/979\/revisions\/1017"}],"wp:attachment":[{"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/media?parent=979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/categories?post=979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tigoe.com\/pcomp\/code\/wp-json\/wp\/v2\/tags?post=979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}