Webhook {PARTICLE_PUBLISHED_AT} to SQL

Getting Started
1

Ok I have a webhook which posts data to the a php script. My php script is inserting the data into a SQL table. All the data is inserting fine except the {PARTICLE_PUBLISHED_AT} value. I am trying to directly insert the {PARTICLE_PUBLISHED_AT} value into a datetime field in the database but it fails. If I substitute the timestamp with a string constant the record will insert correctly so I know it has to do with the format conversion to the SQL datetime field.

I am no SQL expert so any pointers would be appreciated.

2

First, what type of SQL Server?

Also, can you post code Snippets of the PHP SQL insert, and then the SQL script where the variables are declared?

3

Here is the php sql.

$sql = "INSERT INTO `siteData` (`flr`, `fd` ,`dir`,`inS`,`devId`, `ts`) VALUES ('$_POST[FLR]','$_POST[FD]' ,  '$_POST[DIR]' , '$_POST[InS]' ,'$_POST[id]', '$_POST[ts]'  )";


if (mysqli_query($link, $sql)) {
    echo "New record created";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

Here is the SQL:

mysql> describe siteData;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int(11)  | NO   | PRI | NULL    | auto_increment |
| flr   | int(11)  | YES  |     | NULL    |                |
| fd    | int(11)  | YES  |     | NULL    |                |
| dir   | int(11)  | YES  |     | NULL    |                |
| inS   | int(11)  | YES  |     | NULL    |                |
| devId | char(24) | YES  |     | NULL    |                |
| ts    | datetime | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.23-0ubuntu0.18.04.1 |
+-------------------------+
1 row in set (0.01 sec)

And here is the hook response:

"Error: INSERT INTO `siteData` (`flr`, `fd` ,`dir`,`inS`,`devId`,                                     `ts`) VALUES ('0','1' ,  '0' , '0' ,'2e00xxxxxxxxxxxxxxxxxxxxxx', '2018-10-10T20:5                                    3:36.207Z'  )<br>","ttl":60,"published_at":"2018-10-10T20:53:36.426Z","coreid":"                                    particle-internal"}
4

In the Hook response error, there is a very large gap of white space in the middle of the timestamp. Is that real? Or is that like a copy and paste error to the Forum? If it’s real can you put in a Serial print statement that shows you the data before it is published. Then you can look at the serial monitor to validate that isn’t real.

5

That was just a pasting error. Sorry.

INSERT INTO siteData (flr, fd ,dir,inS,devId, ts) VALUES (‘0’,‘1’ , ‘0’ , ‘0’, ‘2e0046xxxxxxxxxxxxxxxxxxx’, ‘2018-10-10T20:53:36.207Z’ )

6

Okay, great. I think the extra characters T & Z in that timestamp are throwing off MySQL. See this link for additional information.

Based on that discussion, I think the PHP statement should be something like this. I’m no good at PHP so try and let us know if it works…:

$sql = "INSERT INTO `siteData` (`flr`, `fd` ,`dir`,`inS`,`devId`, `ts`) VALUES ('$_POST[FLR]','$_POST[FD]' ,  '$_POST[DIR]' , '$_POST[InS]' ,'$_POST[id]', '$_POST[date("Y-m-d H:i:s",ts)]'  )";
1 Like
7

Tried it as you coded it and I got an error so I broke it out line by line. Seems the date() function isn’t converting correctly.

echo $_POST[ts];
$mysqltime = date ("Y-m-d H:i:s", $_POST[ts]);
echo $mysqltime;

2018-10-11T12:50:34.877Z1970-01-01 00:33:38
8

Here is the fix:

echo $_POST[ts];
$mysqltime = date ("Y-m-d H:i:s", strtotime($_POST[ts]) );
echo $mysqltime;

The strtotime() was missing.

Thanks alot for your help. Saved me a ton of time.

1 Like