If this is the wrong board for this post, please advise and I will move it.
My friend and I are working on a signal system for our local railroad club. He has designed a system using programmable logic controllers (PLC) to monitor whether a section of track (a block) is occupied, and how to set the signals accordingly (red, yellow, green, flashing red). The PLC hardware can send a GET command or a PUSH command to a PHP server containing the data for a signal or a block.
We have a Raspberry Pi 2B that I have installed Apache 2.0 and PHP on. The data being sent consists of a location code and a status for that location. The location code is 4 characters, the status code is 3 characters. Examples of the data are below:
for blocks: N=01&S=0
We want to store the data in a SQL database with one row for each location code. When the PLC sends an update, we want the appropriate row in the SQL table to be updated with the most recent status. We would also like to have a column with a last updated date/time. (See attached image).
Our phase one is to run this on an Intranet not connected to the outside world. We want to have a web-page that graphically displays the status of the location codes (using a base image, and overlays is one option). We want to have the database push changes to the web-page, since the updates will be frequent, and constantly pushing the refresh is not an option. Alternatively, the web-page should auto-refresh every few seconds but only with updates. Phase two is to add the ability to change the signal color from the web-page and send it back the PLC and to be able to change to position of the switch points to direct trains onto one track or another.
Depending on how complex phase two is, we may push that to phase three, and first create a second web page that is deployed via the internet so we can broadcast the status on our club web-page. This doesn't need to be updated as frequently. An example at another club can be seen here: http://trainmtn.org/dailyphotos/tm1090.html
We have multiple questions:
1. What is the best database to use for this project?
2. Is it better to use a GET or PUSH command?
3. How do we insert the data elements into the database?
4. How do we push the data to the web-page?
We are not looking for someone to do the work for us, but rather to guide us in the right direction, so we can learn the best way to do this. We appreciate your help.