When we need to create a small dynamic web application, using PHP and MySQL are our obvious options. When we need to create a self-hosted IoT solution like this guide ESP32 Arduino IoT Relay Control, either we need to instruct the user to create a MySQL database, create tables and populate with data or we can create an installer script so that the job is “automatically” done.
When we install WordPress, the tables are automatically created and get populated with data. For our example of ESP32 Arduino IoT Relay Control, we do not need highly complex scripts.
When we ask the users to run SQL queries, some of the users fail. Adding a web installer helps all the users able to use the software. If WordPress users had to run SQL queries to install, then it would not become popular so fast.
---
Can We Create a Database With PHP Installer Script?
Yes! But usually, we do not create a database but create tables (from PHP) and interact with the table (from PHP). This is how we can create a database in PHP:
The above example creates a database named ESP32IoT.
How Can We Create Tables With PHP Script?
In our example IoT project, we required either manually creating the tables running SQL query:
1 2 3 | CREATE TABLE `esp32`.`LED_status` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `status` INT(11) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB; INSERT INTO `LED_status` (`id`, `status`) VALUES ('1', '0') |
This is the approximate PHP equivalent of the first SQL query:
How Can Populate Data?
Again for the above example, this is the PHP equivalent:
Conclusion
There are not many ways to create a single table, or multiple rows and insert data since the SQL queries are constant. However, to make the total installer professional and failproof, we need to provide a pre-installation script to check the server environment and let the user know if something is wrong.
