CHAPTER 7 ■ IOT PATTERNS: ON-DEMAND CLIENTS
Database Table (MySQL)
Before you can send HTTP requests from Arduino, you need to build a service that will
receive the data.
The data received from Arduino needs to be stored so that your iOS app can access
and display this information to users. Data storage requirements for this project are
relatively simple. You just need to create a two-column table that can store the count of
open parking spots and a timestamp to track when it was last updated.
This book uses MySQL as the database. Create a new table called PARKING_SPOTS_DATA
using the SQL script provided in Listing 7-1. Run this script in an existing database or
create a new one. The first column will contain a count of parking spots and the second
column will be an auto-generated timestamp. In addition to create table sql ,
Listing 7-1 also contains an insert statement. This statement initializes the count of
parking spots, which will then be updated as data is received from the sensors.
Listing 7-1. Create and Initialize Table SQL
CREATE TABLE PARKING_SPOTS_DATA (
PARKING_SPOTS_COUNT int(11) NOT NULL,
TIMESTAMP timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
)
INSERT INTO PARKING_SPOTS_DATA(PARKING_SPOTS_COUNT) VALUES (1)
F i g u r e 7-5 shows structure of the PARKING_SPOTS_DATA table.Figure 7-5. PARKING_SPOTS_DATA table structure
Code (PHP)
Now that the database table is ready, you need to build two services. The first service will
receive the Arduino sensor data in an HTTP request and accordingly update the open
parking spots count to the database. The second service will act as an interface for the iOS
app—it will return data in a format that the iOS app can parse and display.
This project uses PHP for building the data storage and the interface services. PHP
is a simple and open source server-side processing language that can process HTTP
requests and send HTTP responses.
Create a new folder called smartparking in the public/root folder of your PHP
server. All of the PHP source code for this project will go in the smartparking folder.