my friend and I are working on a project to create a signal control system for our railroad club. We are using Programmable Logic Controllers (PLC) to monitor when a section of track is occupied, and to set signal aspects accordingly. We want to send data to a MarieDB SQL table that is hosted on a Raspberry Pi. I have already installed Apache2, PHP, and the MariaDB on the Raspberry. I have created a database and added a table with relevant columns.
The PLC can communicate with a PHP server using a get command. Below is an example of what it is sending:
http://192.168.7.56/MLSCTC/SANDBOX.PHP?B=001&S=1
There are three columns in the database:
Location Type
Location ID
Status ID
in the example above ‘B’ is the Location Type, ‘001’ is the location, and ‘1’ is the status. I know I need to take this URL and convert it into a SQL cxommand that is sent to the DB. I think I have the SQL command worked out (shown below) but what I don’t know is how to get from the URL to the SQL command.
I have done some “contact forms” using POST method before, but I think this is a little bit different.
<?php $servername = "localhost"; $username = "MLSCTC"; $password = ""; $dbname = "MLSCTCTEST"; try { $conn = new PDO("mysql:host=$servername; dbname=$dbname", $username, $password); // set the PDO error mode to exception $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO LocStat (location_type, location_id, status_id) VALUES ('B','001','1') ON DUPLICATE KEY UPDATE statsus_id='1'"; // use exec() because no results are returned $conn->exec($sql); echo "Inserted successfully"; } catch(PDOException $e) { echo $sql . "" . $e->getMessage(); } $conn = null; ?>