Hello, I was wondering how to post imp’s ON/OFF values to mysql database using php. . .I’m not sure where to start from…can anyone lead me to right direction please? thanks
You’ll need a website of course. Most likely it’s a shared webhost like GoDaddy or Hostmonster or whatever. Then you’ll need to know how to script with PHP and use the MySQL database. The agent will POST directly to a URL you have that is a PHP script. The PHP script reads the JSON data, decodes it, and writes to the database.
This would be a good place to start:
https://www.google.com/?gws_rd=ssl#q=php+script+write+posted+json+to+mysql+example
Your JSON array could be as simple as a button number and it’s value (1 or 0).
Hello, I got some php scripts from past work i used to store values to mysql but not too sure how to pass agent value to my php script though or is it even possible?
Maybe stating the obvious here. Most basic method is to use the “GET” request from the agent to your web server which is hosting the php script file.
In the agent code you would use the get method. Note there is also a post method and a put method available. Your call as to which method you want to use. Depending on which method you choose this would then need to be handled on php code side too.
For GET method in agent, first thing is set up the URL correctly. For example
`
function HttpGetWrapper(btnstate)
{
local headers=“Content-Type: application/x-www-form-urlencoded”; // add / amend headers as you see fit.
local URL = format ('http://www.yourwebsitedomainname.com/yourphpfilename.php?btn=%s', btnstate); // use this if btnstate is the string 'ON' or 'OFF'.
// Otherwise if you want btnstate to be 1 or 0 you can use
local URL = format (‘http://www.yourwebsitedomainname.com/yourphpfilename.php?btn=%d’, btnstate);
local request = http.get(URLname , headers);
local response = request.sendsync();
return response;
}
`
Then when your agent receives the button state from your imp device you simply pass this on using the “HttpGetWrapper()” function. The response received back from the function will likely be 200, and some content. The content body depends on what you send back via the php file.
Then within the php file you simply use the get method to extract the data :
`
`
You need to start out with something simple.
Below is a PHP script that reads whatever the Imp Agent posts to it (JSON) and puts it into a CSV (comma separated variable) file. It will simply let you test that you can pass data from your agent to your website.
Here is the PHP script. Call it anything you want, upload to your website, and then use that URL in your agent post.
`
<?php
// Set your default timezone
date_default_timezone_set('America/Chicago');
// Retrieve POST JSON from Imp
$data = json_decode(file_get_contents('php://input'), true);
foreach ($data as $key=>$value) {
$line .= $key . ',' . $value . ',';
}
// Write button press to .csv file
$myFile = "ImpButtons.csv";
$fh = fopen($myFile, 'a+') or die("can't open file");
$line .= "timestamp,".$now=time();
fwrite($fh, $line."\
");
fclose($fh);
?>
`
In your imp agent, you can try posting any JSON to your website URL. For now, just send something to test it.
On this web page:
https://electricimp.com/docs/api/http/jsonencode/
… use the example Agent and Device code to test it.
This line (line 3):
local url = “https://www.mywebservice/object”;
you will enter your own website URL … the URL of your PHP script.
If you get it to work (the agent posts the JSON to your website), you’ll see a file on your website called “ImpButtons.csv”. That will be logging all of your button presses.
Your final PHP script will be writing your data to a MySQL database instead of the text file. You have enough examples of that in my previous post.
So this is what i got so far for my connect.php and agent but the database is showing blank values…any idea why?
PHP
`<?php
define (‘DB_NAME’, ‘databasename’);
define (‘DB_USER’, ‘username’);
define (‘DB_PASSWORD’, ‘password’);
define (‘DB_HOST’, ‘localhost’);
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$link) {
die('Could not connect: ’ . mysql_error());
}
$db_selected = mysql_select_db(DB_NAME, $link);
//read the json file contents
$jsondata = file_get_contents(‘value’);
//convert json object to php associative array
$data = json_decode($jsondata, true);
//get info
$temp = $data['status'];
// $time = $data[‘time’];
if (!$db_selected) {
die('Can\'t use ’ . DB_NAME . ': ’ . mysql_error());
}
$value = $_POST [‘status’];
//$value1 = $_POST [‘time’];
$sql = “INSERT INTO imp (status) VALUES (’$value’)”;
if (!mysql_query($sql)) {
die('Error: ’ . mysql_error());
}
mysql_close();
?>`
Did you try running the little PHP script that I previously posted?
The script that writes to a simple .csv file?
I see things in your script that might be errors.
Start simple.
Do the simplest script to test the passing of a value from the agent to your website.
Worry about the database AFTER you have something that works.
I have but it’s not working for me… do i have to change anything to the php script you posted?
You should not have to.
Show us the device and agent code you are using.
Who is the webhost you are using?
This is all the codes i got so far.
I’m using private Webhost provided by University
Agent
`device.on(“senddata”, function(data) {
// Set URL to your web service
local url = “http://mayar.ac.uk/~120/Led/simple.php”;
// Set Content-Type header to json
local headers = { “Content-Type”: “application/json” };
// encode data and log
local body = http.jsonencode(data);
server.log(body);
// send data to your web service
http.post(url, headers, body).sendsync();
});`
Device
`// Alias the GPIO pin as 'button’
button <- hardware.pin8;
function buttonPress() {
local state = button.read();
if (state == 1) {
// The button is released
server.log(“Release”);
} else {
// The button is pressed
server.log(“Press”);
}
}
// Configure the button to call buttonPress() when the pin’s state changes
button.configure(DIGITAL_IN_PULLDOWN, buttonPress);`
Php
`<?php
// Set your default timezone
date_default_timezone_set(‘America/Chicago’);
// Retrieve POST JSON from Imp
$data = json_decode(file_get_contents(‘php://input’), true);
foreach ($data as $key=>$value) {
$line .= $key . ‘,’ . $value . ‘,’;
}
// Write button press to .csv file
$myFile = “ImpButtons.csv”;
$fh = fopen($myFile, ‘a+’) or die(“can’t open file”);
$line .= “timestamp,”.$now=time();
fwrite($fh, $line."
");
fclose($fh);
?>`
There is no connectivity between your device and agent. You need to have something like agent.send(“senddata”,{ state=state });
As Coverdriven said, you need to connect the device and agent.
To test out the PHP script called “simple.php”, use the PHP script below to simulate the imp. It will post to your “simple.php” script to make sure it works.
Call this one “test.php” and run it. Then see if data shows up in the “ImpButtons.csv” file …
`
<?php
$data = array("button" => "one", "buttonValue" => "1");
$data_string = json_encode($data);
$ch = curl_init('http://mayar.ac.uk/~120/Led/simple.php');
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
curl_setopt($ch, CURLOPT_POSTFIELDS, $data_string);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, array(
'Content-Type: application/json',
'Content-Length: ' . strlen($data_string))
);
$result = curl_exec($ch);
echo $result;
?>
`
My tuppence worth… there is no need to complicate this using files or json.
Although strictly speaking the GET method is used to retrieve whatever information is requested, you can also use this method as a starting point to send data to your database using URL via a web browser (simple method for testing).
So if you use the simple GET method you can test your php script using your URL…
http://mayar.ac.uk/~120/Led/simple.php?btnVal=1
PHP Script
`
`
And that should update your data table for you. Then when happy with this script you can simply swap out the $_GET with a $_POST and then you can use the POST method via the imp agent or simply stick with the GETmethod.
Hello thanks for the reply . . .i got most of them working but i’m not getting the reading from the agent to mySQL . . . .do i have to change “?” to anything? $mydata = "INSERT INTO imp (status) VALUES(?)";
No the “?” remains as that is then handled or filled in later via the bind_param function. With the bind_param function it is important to match the data type defined in your “imp” table for the “status” column. So if, for example, your “status” column is defined as type INT the you use ‘i’. If it is defined as type varchar or text for example then you use ‘s’.
Note if you’re using ‘s’ then you do not use $DB_Val (as shown in code example) as that is typed as an integer. Use $DB_Str instead.
Then for Imp code…
Device
function buttonPress() { local state = button.read(); agent.send("btnPress", {"bSt":state}); // you will be sending 1 or 0 here .................... etc. }
Agent
`
device.on(“btnPress”, function(data) {
local URLname = format (“http://mayar.ac.uk/~120/Led/simple.php?btnVal=%d”, data.bSt);
// NOTE: IMP FORUM SHOWS ODDITY HERE WHEN I SAVE WITH USE OF DOUBLE QUOTES. YOU NEED TO USE DOUBLE QUOTES BUT WILL SHOW USING SINGLE QUOTES FOR THIS POST
local URLname = format ('http://mayar.ac.uk/~120/Led/simple.php?btnVal=%d', data.bSt);
server.log(URLname); // just for initial validation as don't really need this
local request = http.get(URLname);
local response = request.sendsync();
server.log("your php script response: " + response.body);
});
`
is it right? or i missed anything out because I’m using Ubidots too but now when I press the led lights up but database only showing 0’s but no 1’s. thanks
Agent
`device.on(“saveValue”, function(lux) {
server.log(“Trying to post to Ubi the value:”);
server.log(lux);
local headers = { “Content-Type”: “application/json”, “X-Auth-Token”: “q1Wr3DdELgPUaL5o6thag5hiAlXE45PIPLahNurAS5YqT5jYQn1mvxd8sWfj” }; // Replace the token with yours
local url = “http://things.ubidots.com/api/v1.6/variables/5641e9b0762542154306e85b/values”; // Replace the Variable ID with yours
local string = {“value”: lux};
local request = http.post (url, headers, http.jsonencode(string));
local response = request.sendsync();
});
device.on(“btnPress”, function(data) {
local myUrl = format (“http://mayar.abertay.ac.uk/~1203864/Led/test.php”, data.bSt);
server.log(myUrl); // just for initial validation as don’t really need this
local request = http.get(myUrl);
local response = request.sendsync();
server.log("your php script response: " + response.body);
});
`
This is incorrect in your agent code: local myUrl = format (“http://mayar.abertay.ac.uk/~1203864/Led/test.php”, data.bSt);
You are missing the “?” after test.php and you are missing the key “btnVal”, which is what your php script code is looking for and the value, which is linked to “%d”.
So the statement should read
local myUrl = format (“http://mayar.abertay.ac.uk/~1203864/Led/test.php?btnVal=%d”, data.bSt);
Hmm ok. i will try that out and what if i want to pass the value to mysql without any button press?..for example temperature value but without button press?
There would need to be a timer (interrupt) that is set at a specific interval to trigger the imp/agent to post the temp. Like every 30 seconds, 60 seconds, etc. depending on how many data points you need over a specific amount of time.