|
Answer» Hi guys, I would like to seek help on inserting data whenever the switch is on or off to my sensor mySQL database in phpMyAdmin from my control.php. I'm using Raspberry PI as my hardware and follow a few tutorials to create my own Web Control Interface, it works perfectly without insert method. After I implemented insert method to my control.php and execute it, it cannot works and cannot store.
This is my control.php:
Code: [Select]<?php
session_start();
$MySQLUsername="MySQLUsername";
$MYSQLUsersensor="MYSQLUsersensor";
$MySQLPassword="MySQLPassword";
$MySQLHost="localhost";
$MySQLDB="gpio";
$MYSQLDBsensor="sensor";
If(($MySQLUsername=="USERNAMEHERE")||($MySQLPassword=="PASSWORDHERE")){
print'ERROR-Pleasesetupthescriptfirst';
exit();
}
$dbConnection=mysql_connect($MySQLHost,$MySQLUsername,$MySQLPassword);
mysql_select_db($MySQLDB,$dbConnection);
If(isset($_POST['action'])){
If($_POST['action']=="setPassword"){
$password1=$_POST['password1'];
$password2=$_POST['password2'];
If($password1!=$password2){
header('Location:control.php');
}
$password=mysql_real_escape_string($_POST['password1']);
If(strlen($password)>28){
mysql_close();
header('location:control.php');
}
$resetQuery="SELECTusername,saltFROMusersWHEREusername='admin';";
$resetResult=mysql_query($resetQuery);
If(mysql_num_rows($resetResult)<1){
mysql_close();
header('location:control.php');
}
$resetData=mysql_fetch_array($resetResult,MYSQL_ASSOC);
$resetHash=hash('sha256',$salt.hash('sha256',$password));
$hash=hash('sha256',$password);
functioncreateSalt(){
$string=md5(uniqid(rand(),true));
returnsubstr($string,0,8);
}
$salt=createSalt();
$hash=hash('sha256',$salt.$hash);
mysql_query("UPDATEusersSETsalt='$salt'WHEREusername='admin'");
mysql_query("UPDATEusersSETpassword='$hash'WHEREusername='admin'");
mysql_close();
header('location:control.php');
}
}
If((isset($_POST['username']))&&(isset($_POST['password']))){
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string($_POST['password']);
$loginQuery="SELECTUserID,password,saltFROMusersWHEREusername='$username';";
$loginResult=mysql_query($loginQuery);
If(mysql_num_rows($loginResult)<1){
mysql_close();
header('location:control.php?error=incorrectLogin');
}
$loginData=mysql_fetch_array($loginResult,MYSQL_ASSOC);
$loginHash=hash('sha256',$loginData['salt'].hash('sha256',$password));
If($loginHash!=$loginData['password']){
mysql_close();
header('location:control.php?error=incorrectLogin');
}else{
session_regenerate_id();
$_SESSION['username']="admin";
$_SESSION['userID']="1";
mysql_close();
header('location:control.php');
}
}
If((!isset($_SESSION['username']))||(!isset($_SESSION['userID']))){
print'
<html>
<HEAD>
<title>GPIOControl-Login</title>
</head>
<body>
<tableborder="0"align="center">
<formname="login"action="control.php"method="post">
<tr>
<td>Username:</td><td><inputtype="TEXT"name="username"></td>
</tr>
<tr>
<td>Password:</td><td><inputtype="password"name="password"></td>
</tr>
<tr>
<tdcolspan="2"align="center"><inputtype="submit"value="LogIn"></td>
</tr>
</form>
</table>
</body>
</html>
';
die();
}
If(isset($_GET['action'])){
If($_GET['action']=="logout"){
$_SESSION=array();
session_destroy();
header('Location:control.php');
}elseIf($_GET['action']=="setPassword"){
print'
<formname="changePassword"action="control.php"method="post">
<inputtype="hidden"name="action"value="setPassword">
<p>EnterNewPassword:<inputtype="password"name="password1">Confirm:<inputtype="password"name="password2"><inputtype="submit"value="submit"></p>
</form>
';
}else{
$action=$_GET['action'];
$pin=mysql_real_escape_string($_GET['pin']);
if($action=="turnOn"){
$setting="1";
mysql_query("UPDATEpinStatusSETpinStatus='$setting'WHEREpinNumber='$pin';");
mysql_close();
header('Location:control.php');
}elseIf($action=="turnOff"){
$setting="0";
mysql_query("UPDATEpinStatusSETpinStatus='$setting'WHEREpinNumber='$pin';");
mysql_close();
header('Location:control.php');
}elseIF($action=="edit"){
$pin=mysql_real_escape_string($_GET['pin']);
$query=mysql_query("SELECTpinDescriptionFROMpinDescriptionWHEREpinNumber='$pin';");
$descRow=mysql_fetch_assoc($query);
$description=$descRow['pinDescription'];
print'
<html><head><title>UpdatePin'.$pin.'</title></head><body>
<tableborder="0">
<formname="edit"action="control.php"method="get">
<inputtype="hidden"name="action"value="update">
<inputtype="hidden"name="pin"value="'.$pin.'">
<tr>
<td><p>Description:</p></td><td><inputtype="text"name="description"value="'.$description.'"></td><td><inputtype="submit"value="Confirm"></td>
</tr>
</form>
</table>
</body></html>
';
mysql_close();
}elseIF($action=="update"){
$pin=mysql_real_escape_string($_GET['pin']);
$description=mysql_real_escape_string($_GET['description']);
mysql_query("UPDATEpinDescriptionSETpinDescription='$description'WHEREpinNumber='$pin';");
header('Location:control.php');
}else{
header('Location:control.php');
}
}else{
$now=newDateTime();
$action=$_POST['action'];
$pin=mysql_real_escape_string($_POST['pin']);
$dbConnectionSensor=mysql_connect($MySQLHost,$MySQLUserSensor,$MySQLPassword);
mysql_select_db($MySQLDBSensor,$dbConnectionSensor);
if($action=="turnOn"&&"4"){
$setting="1";
mysql_query("INSERTINTOsensor('1','RedLED',$now,'')");
}elseIf($action=="turnOn"&&"17"){
$setting="1";
mysql_query("INSERTINTOsensor('2','BlueLED',$now,'')");
}elseIf($action=="turnOn"&&"18"){
$setting="1";
mysql_query("INSERTINTOsensor('3','GreenLED',$now,'')");
}elseIf($action=="turnOn"&&"21"){
$setting="1";
mysql_query("INSERTINTOsensor('4','LED',$now,'')");
}elseIf($action=="turnOn"&&"22"){
$setting="1";
mysql_query("INSERTINTOsensor('5','LED',$now,'')");
}elseIf($action=="turnOn"&&"23"){
$setting="1";
mysql_query("INSERTINTOsensor('6','LED',$now,'')");
}elseIf($action=="turnOn"&&"24"){
$setting="1";
mysql_query("INSERTINTOsensor('7','LED',$now,'')");
}elseIf($action=="turnOn"&&"25"){
$setting="1";
mysql_query("INSERTINTOsensor('8','LED',$now,'')");
mysql_close();
header('Location:control.php');
}elseIf($action=="turnOff"&&"4"){
$setting="0";
mysql_query("INSERTINTOsensor('1','RedLED','',$now)");
}elseIf($action=="turnOff"&&"17"){
$setting="0";
mysql_query("INSERTINTOsensor('2','BlueLED','',$now)");
}elseIf($action=="turnOff"&&"18"){
$setting="0";
mysql_query("INSERTINTOsensor('3','GreenLED','',$now)");
}elseIf($action=="turnOff"&&"21"){
$setting="0";
mysql_query("INSERTINTOsensor('4','LED','',$now)");
}elseIf($action=="turnOff"&&"22"){
$setting="0";
mysql_query("INSERTINTOsensor('5','LED','',$now)");
}elseIf($action=="turnOff"&&"23"){
$setting="0";
mysql_query("INSERTINTOsensor('6','LED','',$now)");
}elseIf($action=="turnOff"&&"24"){
$setting="0";
mysql_query("INSERTINTOsensor('7','LED','',$now)");
}elseIf($action=="turnOff"&&"25"){
$setting="0";
mysql_query("INSERTINTOsensor('8','LED','',$now)");
mysql_close();
header('Location:control.php');
}
}
}else{
print'
<html>
<head>
<title>GPIOControl</title>
</head>
<fontface="verdana">
<p><h1>Sensors</h1></p>
';
$query=mysql_query("SELECTpinNumber,pinStatusFROMpinStatus;");
$query2=mysql_query("SELECTpinNumber,pinDescriptionFROMpinDescription;");
$totalGPIOCount=mysql_num_rows($query);
$currentGPIOCount=0;
print'<tablename="GPIO"border="1"cellpadding="5">';
print'<tr><th>GPIO#</th><th>GPIODescription</th><th>Status</th><th>Action</th><th>Edit</th></tr>';
while($currentGPIOCount<$totalGPIOCount){
$pinRow=mysql_fetch_assoc($query);
$descRow=mysql_fetch_assoc($query2);
$pinNumber=$pinRow['pinNumber'];
$pinStatus=$pinRow['pinStatus'];
$pinDescription=$descRow['pinDescription'];
If($pinStatus=="0"){
$buttonValue="TurnOn";
$action="turnOn";
$image="off.jpg";
}else{
$buttonValue="TurnOff";
$action="turnOff";
$image="on.jpg";
}
print'<tr>';
print'<tdalign="center">'.$pinNumber.'</td><td>'.$pinDescription.'</td><tdalign="center"><imgsrc="'.$image.'"width="50"></td><tdalign="center"valign="middle"><formname="pin'.$pinNumber.'edit"action="control.php"method="get"><inputtype="hidden"name="action"value="'.$action.'"><inputtype="hidden"name="pin"value="'.$pinNumber.'"><inputtype="submit"value="'.$buttonValue.'"></form></td><td><formname="pin'.$pinNumber.'"action="control.php"method="get"><inputtype="hidden"name="action"value="edit"><inputtype="hidden"name="pin"value="'.$pinNumber.'"><inputtype="submit"value="Edit"></form></td>';
print'</tr>';
$currentGPIOCount++;
}
print'</table>';
mysql_close();
print'
<br><br>
<ahref="control.php?action=logout">LogOut</a>
</font>
</html>
';
}
?>
This is my pinDirection SQL database:
Code: [Select] DROP TABLE IF EXISTS `pinDirection`;
CREATE TABLE IF NOT EXISTS `pinDirection` (
`pinID` int(11) NOT NULL AUTO_INCREMENT,
`pinNumber` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`pinDirection` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`pinID`),
UNIQUE KEY `pinNumber` (`pinNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;
This is my pinStatus SQL database:
Code: [Select] DROP TABLE IF EXISTS `pinStatus`;
CREATE TABLE IF NOT EXISTS `pinStatus` (
`pinID` int(11) NOT NULL AUTO_INCREMENT,
`pinNumber` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
`pinStatus` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`pinID`),
UNIQUE KEY `pinNumber` (`pinNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;
This is my sensor SQL database:
Code: [Select] CREATE TABLE IF NOT EXISTS `sensor` (
`id` int(11) NOT NULL,
`sensor` text NOT NULL,
`switchOnLog` datetime NOT NULL,
`switchOffLog` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Hope you guys could help me out with it as soon as possible, thanks in ADVANCE, guys
|
