'.print_r($_SERVER, TRUE).'';
//echo $_SERVER['QUERY_STRING'] . "
\n\r";;
// usage/url format is:
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Accounts&operation=LISTALL
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Devices&operation=LISTALL
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Accounts&operation=SELECT&email=new@gmail.com
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Accounts&operation=SELECT&id=1
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Devices&operation=SELECT&mac=aa:bb:cc:dd
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Accounts&operation=INSERT&email=new@gmail.com&account=two%20words&password=abc
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Devices&operation=INSERT&accountsid=1&mac=aa:bb:cc:dd
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Accounts&operation=DELETE&id=2
//http://hwang.lasierra.edu/~enoch/CPTG%20384%20Mobile%20App/SQL/SQL.php?table=Accounts&operation=UPDATE&id=2&veify=1
// get the url parameters
parse_str($_SERVER['QUERY_STRING']); // this uses GET. Same as $operation = $_GET['operation'];
if ($operation == "") { // if nothing then use POST
$table = $_POST['table'];
$operation = $_POST['operation'];
$id = $_POST['id'];
$email = $_POST['email'];
$account = $_POST['account'];
$password = $_POST['password'];
$verified = $_POST['verified'];
$accountsid = $_POST['accountsid'];
$mac = $_POST['mac'];
$description = $_POST['description'];
$debug = $_POST['debug'];
}
if ($debug == "") $debug = 0;
else $debug = 1;
// get current time
date_default_timezone_set('America/Los_Angeles');
if ($debug) {
echo "
" . date("l M j, Y g:i:s a") . "
\n\r";
echo "
";
echo '$operation = ' . $operation . "
\n\r";
echo '$mac = ' . $mac . "
\n\r";
echo '$email = ' . $email . "
\n\r";
echo '$account = ' . $account . "
\n\r";
echo '$password = ' . $password . "
\n\r";
}
if ($operation == "")
die("Usage: SQL.php?operation=LISTALL");
require_once 'db_login.php';
if ($debug) echo "Going to connect...
";
// Create connection
$db_server = mysqli_connect($db_hostname, $db_username, $db_password, $db_database);
// Check connection
if (!$db_server) {
die("Unable to connect to MySQL: " . mysqli_connect_error()); // never gets here???
}
if ($debug) echo "Connected to SQL database " . $db_database . "@" . $db_hostname . "
";
/*
// examples
$sql = "SELECT * FROM `Accounts` WHERE 1 ORDER BY `timestamp` DESC";
$sql = "SELECT * FROM `Accounts` WHERE id=\"$id\"";
$sql = "SELECT * FROM `Accounts` WHERE email=\"$email\"";
$sql = "SELECT * FROM `Devices` WHERE mac=\"$mac\"";
$sql = "INSERT INTO `Accounts` (`email`, `account`, `password`) VALUES (\"$email\", \"$account\", \"$password\")";
$sql = "UPDATE `Accounts` SET `email`=\"$email\",`account`=\"$account\",`password`=\"$password\",`verify`=\"$verify\" WHERE `id`=\"$id\"";
$sql = "DELETE FROM `Accounts` WHERE id=\"$id\"";
*/
if ($operation == "LISTALL") {
if ($table == "Devices") {
$sql = "SELECT * FROM `Devices` WHERE 1 ORDER BY `id`";
} else {
//$sql = "SELECT * FROM `Accounts` WHERE 1 ORDER BY `id` DESC";
$sql = "SELECT * FROM `Accounts` WHERE 1 ORDER BY `id`";
}
$result = mysqli_query($db_server, $sql);
if ($result) {
printRecord($table, $result, $debug);
} else {
echo "Database access failed: " . mysql_error() . "\n\r";
}
} else if ($operation == "SELECT") {
if ($table == "Devices") {
if ($accountsid != "") {
$sql = "SELECT * FROM `Devices` WHERE accountsid=\"$accountsid\"";
} else if ($mac != "") {
$sql = "SELECT * FROM `Devices` WHERE mac=\"$mac\"";
}
} else {
if ($id != "") {
$sql = "SELECT * FROM `Accounts` WHERE id=\"$id\"";
} else if ($email != "") {
$sql = "SELECT * FROM `Accounts` WHERE email=\"$email\"";
}
// $sql = "SELECT * FROM `Accounts` WHERE id=\"$id\" ORDER BY `account` DESC";
}
$result = mysqli_query($db_server, $sql);
if ($result) {
$rows = mysqli_num_rows($result);
if ($rows == 0) {
echo "No record for [$id] [$email] found.
";
} else {
printRecord($table, $result, $debug);
}
} else {
//echo "Database access failed: " . mysql_error() . "\n\r";
echo "Failed select record \"$id\".\n\r";
}
} else if ($operation == "INSERT") {
if ($table == "Devices") {
$sql = "INSERT INTO `Devices` (`accountsid`, `mac`, `description`) VALUES (\"$accountsid\", \"$mac\", \"$description\")";
} else {
$sql = "INSERT INTO `Accounts` (`email`, `account`, `password`) VALUES (\"$email\", \"$account\", \"$password\")";
}
$result = mysqli_query($db_server, $sql);
if ($result) {
echo "Inserted record for \"$id\"
\n\r";
} else {
//echo "Insert record \"$id\" failed: " . mysql_error() . "\n\r";
echo "Failed insert record \"$id\".\n\r";
}
} else if ($operation == "UPDATE") {
// if ($timestamp != "")
// $sql = "UPDATE `Accounts` SET `timestamp`=NOW(),`operation`=\"$operation\",`description`=\"$description\",`value`=\"$value\" WHERE `timestamp`=\"$timestamp\"";
// else
if ($table == "Devices") {
if ($id != "")
$sql = "UPDATE `Devices` SET `accountsid`=\"$accountsid\",`mac`=\"$mac\",`description`=\"$description\" WHERE `id`=\"$id\"";
} else {
if ($id != "")
$sql = "UPDATE `Accounts` SET `email`=\"$email\",`account`=\"$account\",`password`=\"$password\",`verify`=\"$verify\" WHERE `id`=\"$id\"";
}
$result = mysqli_query($db_server, $sql);
if ($result) {
echo "Updated record $mac";
} else {
//echo "Update record \"$mac\" failed: " . mysql_error() . "\n\r";
echo "Failed update record \"$mac\".\n\r";
}
} else if ($operation == "DELETE") {
if ($table == "Devices") {
$sql = "DELETE FROM `Devices` WHERE id=\"$id\"";
} else {
$sql = "DELETE FROM `Accounts` WHERE id=\"$id\"";
}
$result = mysqli_query($db_server, $sql);
if ($result) {
echo "Deleted record $id";
} else {
//echo "Delete record \"$id\" failed: " . mysql_error() . "\n\r";
echo "Failed delete record $id";
}
}
mysqli_close($conn);
///////////////////////////////////
// functions
function printRecord($table, $result, $debug) {
$rows = mysqli_num_rows($result);
if ($table == "Devices") {
for ($j = 0; $j < $rows; ++$j) {
$row = mysqli_fetch_row($result);
echo "{\"" . $row[0] . "\", \"" . $row[1] . "\", \"" . $row[2] . "\", \"" . $row[3] . "\"} ";
}
} else { // for table Accounts
if ($debug) {
echo "Number of records: " . $rows . "
\n\r";
// echo "Number of records: " . $rows . "
\n\r";
printf(" %-20s","id");
printf(" %-30s","email");
printf(" %-20s","account");
printf(" %-20s\n","password");
printf(" %-20s\n","verified");
for ($j = 0; $j < $rows; ++$j) {
$row = mysqli_fetch_row($result);
printf(" %-20s",$row[0]);
printf(" %-30s",$row[1]);
printf(" %-20s",$row[2]);
printf(" %-20s\n",$row[3]);
printf(" %-20s\n",$row[4]);
}
echo "
";
} else {
for ($j = 0; $j < $rows; ++$j) {
$row = mysqli_fetch_row($result);
// echo "{\"" . $row[0] . "\", \"" . $row[1] . "\", \"" . $row[2] . "\", \"" . $row[3] . "\"} ";
echo "{\"" . $row[0] . "\", \"" . $row[1] . "\", \"" . $row[2] . "\", \"" . $row[3] . "\", \"" . $row[4] . "\"} ";
}
}
}
}
function get_clean($var) {
return mysqli_real_escape_string($var);
}
?>