Cant get photon code,webhook,php to log data to mysql

General
1

Hi, all i have been at this now for a good few days trying to figure out how to get a variable from my photon to a mysql database i have tried to make it as simple as i can so I can get to grips with it but I keep getting a webhook error. now im not to great with php or the json and i can just about get by with the photon code. however if i can just get this inserting into the database im sure i will be able to work through it and any advice would be greatly revived.

i keep getting a webhook error of:
SQLSTATE[28000] [1045] Access denied for user ‘iotelect’@‘localhost’ (using password: NO)

here is my photon code


char msg[256];        // used for snprintf for Publish.
/////////////////////////////////////////////////////////////////////////////////////
int val1 = 200;
int val2 = 500;
int led1 = D7;

unsigned long lastTime = 0; // to store the last time for the timer
unsigned long now; // to store the now time which is used to compare against last time

void setup() {
    
     pinMode(led1, OUTPUT);

 }
void loop() {
    
            now = millis();
	        if ((now - lastTime) >= 15000)
	   {
            lastTime = now;
    
    publishDataToCloud();
     digitalWrite(led1, HIGH);
     delay(1000);
       digitalWrite(led1, LOW);
	   }
}




void publishDataToCloud()
{
//lu is for unsigned long, f is for float
snprintf(msg, sizeof(msg), "{\"value1\":%lu, \"value2\":%lu}", val1, val2);
Particle.publish("mysql", msg, PRIVATE, NO_ACK);
 return;
 }

here is my php on my webserver

<?php


$content = (file_get_contents("php://input"));

$decoded = json_decode($content, true);

$value1 = $decoded['value1'];
$value2 = $decoded['value2'];

try {
    $conn = new PDO("mysql:host=$localhost;dbname=$iotelect_photon", $this_is_my_username, $This_is_my_password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO `photonvalues` (`value1`, `value2`) VALUES ('$value1', '$value2')";
    $conn->exec($sql);
    echo "New record created successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }
$conn = null;
?>

then in my webhook all i have is

Event name: mysql
url is : my php webpage on my server
request type: post
request format : webform
device: any

under advance CUSTOM
field1 {{PARTICLE_EVENT_VALUE}}

thanks for your time

Ben

2

I'm not a PHP guy but are these grave accents ( ` ) not supposed to be single quotes ( ' ) rather?

Also your val1 & val2 are int but in your snprintf() you are treating them as unsigned int, why?

In Particle Console | Build your connected product you can look at your particular webhook and should see the last few triggers and what request they actually triggered.
Check if that looks right.
In your PHP you may also want to log the raw request along with all your decoded variables to see what you get.

You may also check the database if this user exists and has INSERT privilege on your DB.
This might also provide some useful hints
mysql - SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: YES) Symfony2 - Stack Overflow

1 Like
3

@jearle performed something similar in this post, for some ideas on the PHP for mySQL:

2 Likes
4

Thanks! Another suggestion I would have is make sure that your php code is working correctly to post to your database before working about the webhook part. They way it says access denied makes me think it maybe could be a username/password issue with database.

You can put the http address of your php code in you browser and go directly to it should create an artificial record . If it doesn’t the issue may be with the php/database connection

5

Thank you for your replies.

ScruffR i have changed my snprintf() statement to:

snprintf(msg, sizeof(msg), "{\"value1\":%d, \"value2\":%d}", val1, val2);

im not great at coding and have just started to get back into it, most of the things i try and learn are from what i have read on the particle forum and as every detail of the code is not fully explained some of the code i write is just a copy of what others have done however now you have informed me i have looked it up and hopefully i have corrected my mistake?

i based this on the project i read from jearle shared project however as i did not fully understand as the code i thought i would start simple and just try and insert any data, one i had successfully achieved that I was going to add to it. This type of project is the main reason i purchased the photon (i now have 4 photons and 2 electrons and loads of boards from control everything. however i always get stuck and then come away for months then when i go back its like starting again.

I am pretty sure i have my username and password is correct and have confirmed this by checking on my webserver cpanel and also speaking to them direct to try and solve the problem.
has anyone had this error before?

6

I think i have found the problem so i have now declared varibles which hold the db name and other details which are passed to the conn statement it is now inserting data into my database and i am over the moon.

however the data entered is 0 and 0 not 200 and 500 but at least it is inserting

thanks again jearle for sharing that project you have really helped me out and inspired me to pick up my photon again.

2 Likes