Need help on Insert data to mySQL database


 
Thread Tools Search this Thread
Top Forums Programming Need help on Insert data to mySQL database
# 1  
Old 07-21-2014
Need help on Insert data to mySQL database

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 can works but cannot store in mySQL database.

This is my control.php:
PHP Code:
<?php
error_reporting
(E_ALL);
ini_set('display_errors','On');

session_start();

$MySQLUsername "gpio";
$MySQLPassword "kang";

$MySQLHost "localhost";
$MySQLDB "gpio";

If ((
$MySQLUsername == "USERNAME HERE") || ($MySQLPassword == "PASSWORD HERE")){
    print 
'ERROR - Please set up the script first';
    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 "SELECT username, salt FROM users WHERE username = 'admin';";
        
$resetResult mysql_query($resetQuery);
        If (
mysql_num_rows($resetResult) < 1){
            
mysql_close();
            
header('location: control.php');
        }
        
$resetData mysql_fetch_array($resetResultMYSQL_ASSOC);
        
$resetHash hash('sha256'$salt hash('sha256'$password));
        
$hash hash('sha256'$password);
        function 
createSalt(){
            
$string md5(uniqid(rand(), true));
            return 
substr($string08);
        }
        
$salt createSalt();
        
$hash hash('sha256'$salt $hash);
        
mysql_query("UPDATE users SET salt='$salt' WHERE username='admin'");
        
mysql_query("UPDATE users SET password='$hash' WHERE username='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 "SELECT UserID, password, salt FROM users WHERE username = '$username';";
    
$loginResult mysql_query($loginQuery);
    If (
mysql_num_rows($loginResult) < 1){
        
mysql_close();
        
header('location: control.php?error=incorrectLogin');
    }
    
$loginData mysql_fetch_array($loginResultMYSQL_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>GPIO Control - Login</title>
    </head>
    <body>
    <table border="0" >
    <form name="login" action="control.php" method="post">
    <tr>
    <td>Username: </td><td><input type="text" name="username"></td>
    </tr>
    <tr>
    <td>Password: </td><td><input type="password" name="password"></td>
    </tr>
    <tr>
    <td colspan="2" ><input type="submit" value="Log In"></td>
    </tr>
    </form>
    </table>
    </body>
    </html>
    '
;
    die();
}
If (isset(
$_GET['action'])){
    If (
$_GET['action'] == "logout"){
        
$_SESSION = array();
        
session_destroy();
        
header('Location: control.php');
    } else If (
$_GET['action'] == "setPassword"){
        print 
'
        <form name="changePassword" action="control.php" method="post">
        <input type="hidden" name="action" value="setPassword">
        <p>Enter New Password: <input type="password" name="password1">  Confirm: <input type="password" name="password2"><input type="submit" value="submit"></p>
        </form>
        '
;
    } else {
        
$action $_GET['action'];
        
$pin mysql_real_escape_string($_GET['pin']);
        if (
$action == "turnOn"){
            
$setting "1";
            
mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '1', 'Red LED', NOW(), NULL) SELECT '4' FROM pinStatus WHERE pinNumber='4';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '2', 'Blue LED', NOW(), NULL) SELECT '17' FROM pinStatus WHERE pinNumber='17';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '3', 'LED', NOW(), NULL) SELECT '18' FROM pinStatus WHERE pinNumber='18';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '4', 'LED', NOW(), NULL) SELECT '21' FROM pinStatus WHERE pinNumber='21';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '5', 'LED', NOW(), NULL) SELECT '22' FROM pinStatus WHERE pinNumber='22';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '6', 'Green LED', NOW(), NULL) SELECT '23' FROM pinStatus WHERE pinNumber='23';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '7', 'LED', NOW(), NULL) SELECT '24' FROM pinStatus WHERE pinNumber='24';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '8', 'LED', NOW(), NULL) SELECT '25' FROM pinStatus WHERE pinNumber='25';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
mysql_close();
            
header('Location: control.php');
        } else If (
$action == "turnOff"){
            
$setting "0";
            
mysql_query("UPDATE pinStatus SET pinStatus='$setting' WHERE pinNumber='$pin';");
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '1', 'Red LED', NULL, NOW()) SELECT '4' FROM pinStatus WHERE pinNumber='4';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '2', 'Blue LED', NULL, NOW()) SELECT '17' FROM pinStatus WHERE pinNumber='17';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '3', 'LED', NULL, NOW()) SELECT '18' FROM pinStatus WHERE pinNumber='18';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '4', 'LED', NULL, NOW()) SELECT '21' FROM pinStatus WHERE pinNumber='21';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '5', 'LED', NULL, NOW()) SELECT '22' FROM pinStatus WHERE pinNumber='22';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '6', 'Green LED', NULL, NOW()) SELECT '23' FROM pinStatus WHERE pinNumber='23';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '7', 'LED', NULL, NOW()) SELECT '24' FROM pinStatus WHERE pinNumber='24';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
$success=mysql_query("INSERT INTO sensor VALUES(NULL, '8', 'LED', NULL, NOW()) SELECT '25' FROM pinStatus WHERE pinNumber='25';");
            if (!
$success) {
                die(
'Could not Insert: ' mysql_error());
            }
            
mysql_close();
            
header('Location: control.php');
        } else IF (
$action =="edit"){
            
$pin mysql_real_escape_string($_GET['pin']);
            
$query mysql_query("SELECT pinDescription FROM pinDescription WHERE pinNumber='$pin';");
            
$descRow mysql_fetch_assoc($query);
            
$description $descRow['pinDescription'];
            print 
'
            <html><head><title>Update Pin ' 
$pin '</title></head><body>
            <table border="0">
            <form name="edit" action="control.php" method="get">
            <input type="hidden" name="action" value="update">
            <input type="hidden" name="pin" value="' 
$pin '">
            <tr>
            <td><p>Description: </p></td><td><input type="text" name="description" value="' 
$description '"></td><td><input type="submit" value="Confirm"></td>
            </tr>
            </form>
            </table>
            </body></html>
            '
;
            
mysql_close();
        } else IF (
$action =="update"){
            
$pin mysql_real_escape_string($_GET['pin']);
            
$description mysql_real_escape_string($_GET['description']);
            
mysql_query("UPDATE pinDescription SET pinDescription='$description' WHERE pinNumber='$pin';");
            
header('Location: control.php');
        } else {
            
header('Location: control.php');
        }
    }
} else {
    print 
'
        <html>
        <head>
        <title>GPIO Control</title>
        </head>
        <font face="verdana">
        <p><h1>Sensors</h1></p>
        '
;
        
$query mysql_query("SELECT pinNumber, pinStatus FROM pinStatus;");
        
$query2 mysql_query("SELECT pinNumber, pinDescription FROM pinDescription;");
        
$totalGPIOCount mysql_num_rows($query);
        
$currentGPIOCount 0;
        print 
'<table name="GPIO" border="1" cellpadding="5">';
        print 
'<tr><th>GPIO #</th><th>GPIO Description</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 "Turn On";
                
$action "turnOn";
                
$image "off.jpg";
            } else {
                
$buttonValue "Turn Off";
                
$action "turnOff";
                
$image "on.jpg";
            }
            print 
'<tr>';
            print 
'<td >' $pinNumber '</td><td>' $pinDescription '</td><td ><img src="' $image '" width="50"></td><td  valign="middle"><form name="pin' $pinNumber 'edit" action="control.php" method="get"><input type="hidden" name="action" value="' $action '"><input type="hidden" name="pin" value="' $pinNumber '"><input type="submit" value="' $buttonValue '"></form></td><td><form name="pin' $pinNumber '" action="control.php" method="get"><input type="hidden" name="action" value="edit"><input type="hidden" name="pin" value="' $pinNumber '"><input type="submit" value="Edit"></form></td>';
            print 
'</tr>';
            
$currentGPIOCount ++;
        }
        print 
'</table>';
        
mysql_close();
    print 
'
    <br><br>
    <a href="control.php?action=logout">Log Out</a>
    </font>
    </html>
    '
;
}
?>
This is my pinStatus SQL database:
Code:
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:
CREATE TABLE IF NOT EXISTS `sensor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sensorId` int(11) NOT NULL,
  `sensor` text COLLATE utf8_unicode_ci NOT NULL,
  `switchOnLog` datetime DEFAULT NULL,
  `switchOffLog` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

This is my error message for mySQL:
Could not Insert: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT '4' FROM pinStatus WHERE pinNumber='4'' at line 1

Hope you guys could help me out with it as soon as possible, thanks in advance, guys.
# 2  
Old 07-24-2014
MySQL Field name

I expect you already fixed the issue.

I see you are using quotes for field names
like this:

" select '4' from bla bla bla"

Are you tried perform the query without quotes in the field name?
like this:

" select 4 from bla bla bla "

And what's exactly is number '4'? is the field name?

I'll hope be helpful for you.
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help on Insert data to phpMyAdmin mySQL database from Shell Script

Sorry to disturb you, I would like to seek help on inserting data whenever the switch is on or off to my phpMyAdmin mySQL database from my Shell Script. I'm using Raspberry PI as my hardware and I have follow this LINK: instructables.com/id/Web-Control-of-Raspberry-Pi-GPIO/?ALLSTEPS to create my... (4 Replies)
Discussion started by: aoiregion
4 Replies

2. Shell Programming and Scripting

Scrape 10 million pages and save the raw html data in mysql database

I have a list of 10 million page urls. I want those pages scraped and saved in the mysql database as raw html. I own a Linux VPS server with 1GB RAM and WHM/cPanel. I would like to scrape at least 100,000 urls in 24 hours. So can anyone give me some sample shell scripting code? (4 Replies)
Discussion started by: Viruthagiri
4 Replies

3. Web Development

INSERT data to a Database Table from a text file

If you have a text file and if you want to Insert data to your Database Table, You can do it with these queries LOAD DATA LOCAL INFILE '/path/yourTextFile.txt' INTO TABLE yourTableName FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (0 Replies)
Discussion started by: sitex
0 Replies

4. Shell Programming and Scripting

Insert value of vmstat to database.

Hi guys , I m trying to store the output of vmstat 1 10 into a database. I have done the necessary homework to connect bash to interact with the database. Program logic. 1)storing the output of vmstat 1 10 to a file named abc.txt 2)I m applying a filter to vmstat to get desired output... (4 Replies)
Discussion started by: pinga123
4 Replies

5. Shell Programming and Scripting

shell script to insert data from gps.txt to mysql database

Hi, I have gps receiver, by using gpsd data i can read gps log data to my database(my sql). Steps: 1. telenet localhost 2947 > gps.txt (press enter) 2. r (press enter) //then i will get the data like below in gps.txt file Trying 127.0.0.1... Connected to localhost.... (1 Reply)
Discussion started by: gudivada213
1 Replies

6. Shell Programming and Scripting

how to insert data into database by reading it from a text file??

Hi....can you guys help me out in this script?? Below is a text file and it contains these: GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL005675 000001... (4 Replies)
Discussion started by: pallavishetty
4 Replies

7. Shell Programming and Scripting

How to insert data into MYSql database from a text file

Hi, Need to get help from you guys about this issue. I need to insert data into MySql database from a text file which is located in other server. The text file is something look like below: Date | SubscriberNo | Call Duration 20/7/07 | 123456788 | 20 20/7/07 | 123412344 | 30 The... (4 Replies)
Discussion started by: shirleyeow
4 Replies

8. Shell Programming and Scripting

how to insert data in database based on text file?

Hi....can you guys help me out in this script?? Below is a text file script....called Bukom.txt and it contains these: BUKOM 20060101 2.5 2.6 2.7 2.8 2.9 2.3 2.1 BUKOM 20060102 2.4 2.5 2.6 2.7 2.7 2.6 2.4 BUKOM 20060103 2.1 ... (9 Replies)
Discussion started by: forevercalz
9 Replies
Login or Register to Ask a Question