MySql and Php

Adding and manage items with PHP and MySQL is not a nightmare, if you know what you are doing, and the table size will be kept low.

Consider designing the tables before creating them, to better understande how to access the records. Also, to keep things quick, remember that indexing is your friend.

Many here gave you possibe parts of the final solution. What I can add is: always mind security when juggling with databases and IoT.

1. Always change default passwords
2. Use variables in your queries
3. Sanitize queries (i.e. check return values before using them)
4. Use secure wireless networks (possibly WPA2)

And please, keep us informed about your progress: we might even help you write down a tech post.

Luigi

There are some useful tools, like the MySQL console running on both Windows and OSX (I think on Linux too) platforms. This gives you the possibility to design graphically the database, create your queries and test the consistency of the entire system. The same tool can be connected to both local and remote databases to organize tables, queries, static queries and all, as well as producing a graphical view of the database and the various relations between tables. This is a local tool very useful when creating a new database from scratch also because you don't need to stay always connected with the remote web server. I use it for custom database designs. When the database has been created you can move to the online version managing it with phpMySQL that gives a lot of features also for data extraction, data upload (accept xml, csv, excel format, and text only). With PhpMySQL it is also almost easy to create several permissions (without the need to create the corresponding users in the Linux environment).

Indeed, if you are basing your architecture on wordpress, that I strongly suggest also if your web site has nothing to do with the WP themes and plugins, if you have already (you need to) the suite Apache2 + MySQL + PhpMySQL the WP installation also creates its own standard database that can then be updated/upgraded with custom tables, special permissions and more. Frankly, based on my personal experience I always prefer to create a separate custom DB associated with the standard WP database to manage this case of complex data architectures.

Enrico

There are some useful tools, like the MySQL console running on both Windows and OSX (I think on Linux too) platforms. This gives you the possibility to design graphically the database, create your queries and test the consistency of the entire system. The same tool can be connected to both local and remote databases to organize tables, queries, static queries and all, as well as producing a graphical view of the database and the various relations between tables. This is a local tool very useful when creating a new database from scratch also because you don't need to stay always connected with the remote web server. I use it for custom database designs. When the database has been created you can move to the online version managing it with phpMySQL that gives a lot of features also for data extraction, data upload (accept xml, csv, excel format, and text only). With PhpMySQL it is also almost easy to create several permissions (without the need to create the corresponding users in the Linux environment).

Indeed, if you are basing your architecture on wordpress, that I strongly suggest also if your web site has nothing to do with the WP themes and plugins, if you have already (you need to) the suite Apache2 + MySQL + PhpMySQL the WP installation also creates its own standard database that can then be updated/upgraded with custom tables, special permissions and more. Frankly, based on my personal experience I always prefer to create a separate custom DB associated with the standard WP database to manage this case of complex data architectures.

Enrico