Mysql Query doesn't work as expected (with left joins)

Hello mysql and sql experts

Hello mysql and sql experts

I have a problem with my query.

I have 3 tables (As you can see in the images below)

One table for the event One table for the user account And one table where the event table and user account table is linked to (Teilnehmer table)

When a user Sign up to a event, the EventID and also the UserID will be stored in the Teilnehmer table.

That works fine.

Now the problem is, I've created a query which should only return the events, which are Enabled = 1, the user didn't Sign up to and the Count of FK_eventID is < Event.Anzahl

but that doesn't work.

The Count and Enabled join works, but although the user sign up to a event, the event will still be returned

can anybody see my problem ?

SELECT v.ID, 
       v.NameVeranstaltung, 
       v.Datum, 
       v.Uhrzeit, 
       v.Anzahl, 
       v.Beschreibung, 
       v.Enabled, 
       Count(t.FK_eventID) 
FROM   Veranstaltung AS v 
       LEFT JOIN Teilnehmer AS t 
              ON t.FK_eventID = v.ID 
       LEFT JOIN Users AS u 
              ON t.FK_userID = 17 
WHERE  v.Enabled = 1 
GROUP  BY t.FK_eventID, 
          v.NameVeranstaltung 
HAVING Count(t.FK_eventID) < v.Anzahl 


First CRUD Node Express Js Mysql Example

First CRUD Node Express Js Mysql Example

In this node express js tutorial, we would love to share with you how to create a crud application in node express js with mysql

In this node express js tutorial, we would love to share with you how to create a crud application in node express js with mysql

Hello developers, Today we will discuss how to install node js framework express js and how to create crud (create, update, read, delete) application in node js using express js framework with mysql database. Today we would love to show you how to create crud application in node js.

We will create crud application in node express js framework with mysql database. we will do each thing step by step and easy.

In this node express js crud application. We will use ejs templating engine. It is very simple and easy to understand for everyone.

Let’s start create CRUD with Node.Js , Express, MySQL

Create Express js Project

Use the below command and create your express project with name expressfirst

express --view=ejs expressfirst

After successfully created expressfirst folder in your system. Next follow the below commands and install node js in your project :

cd expressfirst

npm install  

Next we need to install some required pacakges, go to terminal and use the below commands :

 npm install express-flash --save
 npm install express-session --save
 npm install express-validator --save
 npm install method-override --save
 npm install mysql --save

Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.

In this node js mysql crud tutorial express flash is used to display a warning, error and information message

Express-session is used to made a session as like in PHP. In this node js mysql crud tutorial, session is needed as the express requirement of express-flash.

Express validator is used to validate form data it is easy to use. express-validator highly effective and efficient way to accelerate the creation of applications.

NPM is used to run a DELETE and PUT method from an HTML form. In several web browsers only support GET and POST methods.

Driver to connect node.js with MySQL

Database Connection with Mysql

Next we need to create one folder name lib and create a new file name db.js inside this folder. We will connect node js to mysql using this file

lib/db.js
 var mysql=require('mysql');
 var connection=mysql.createConnection({
   host:'localhost',
   user:'your username',
   password:'your password',
   database:'your database name'
 });
connection.connect(function(error){
   if(!!error){
     console.log(error);
   }else{
     console.log('Connected!:)');
   }
 });  
module.exports = connection; 

Changes in app.js

We need to some changes in app.js file. go to app.js file and put some code here :

 var createError = require('http-errors');
 var express = require('express');
 var path = require('path');
 var cookieParser = require('cookie-parser');
 var logger = require('morgan');
 var expressValidator = require('express-validator');
 var flash = require('express-flash');
 var session = require('express-session');
 var bodyParser = require('body-parser');

 var mysql = require('mysql');
 var connection  = require('./lib/db');

 var indexRouter = require('./routes/index');
 var usersRouter = require('./routes/users');
 var customersRouter = require('./routes/customers');

 var app = express();

// view engine setup
 app.set('views', path.join(__dirname, 'views'));
 app.set('view engine', 'ejs');

 app.use(logger('dev'));
 app.use(bodyParser.json());
 app.use(bodyParser.urlencoded({ extended: true }));
 app.use(cookieParser());
 app.use(express.static(path.join(__dirname, 'public')));

 app.use(session({ 
     secret: '123456cat',
     resave: false,
     saveUninitialized: true,
     cookie: { maxAge: 60000 }
 }))

 app.use(flash());
 app.use(expressValidator());

 app.use('/', indexRouter);
 app.use('/users', usersRouter);
 app.use('/customers', customersRouter);

 // catch 404 and forward to error handler
 app.use(function(req, res, next) {
   next(createError(404));
 });

 // error handler
 app.use(function(err, req, res, next) {
   // set locals, only providing error in development
   res.locals.message = err.message;
   res.locals.error = req.app.get('env') === 'development' ? err : {};
 // render the error page
   res.status(err.status || 500);
   res.render('error');
 });
 module.exports = app;

Create Route

Next We need to create one route file name customers.js inside routes folder. After created this file, We will implement crud logic in this file. Go to routes/customers.js and use the below code :

var express = require('express');
var router = express.Router();
var connection  = require('../lib/db');
 
 
/* GET home page. */
router.get('/', function(req, res, next) {
      
 connection.query('SELECT * FROM customers ORDER BY id desc',function(err,rows)     {
 
        if(err){
         req.flash('error', err); 
         res.render('customers',{page_title:"Customers - Node.js",data:''});   
        }else{
            
            res.render('customers',{page_title:"Customers - Node.js",data:rows});
        }
                            
         });
        
    });
 
 
// SHOW ADD USER FORM
router.get('/add', function(req, res, next){    
    // render to views/user/add.ejs
    res.render('customers/add', {
        title: 'Add New Customers',
        name: '',
        email: ''        
    })
})
 
// ADD NEW USER POST ACTION
router.post('/add', function(req, res, next){    
    req.assert('name', 'Name is required').notEmpty()           //Validate name
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
  
    var errors = req.validationErrors()
     
    if( !errors ) {   //No errors were found.  Passed Validation!
         
     
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
         
     connection.query('INSERT INTO customers SET ?', user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                     
                    // render to views/user/add.ejs
                    res.render('customers/add', {
                        title: 'Add New Customer',
                        name: user.name,
                        email: user.email                    
                    })
                } else {                
                    req.flash('success', 'Data added successfully!');
                    res.redirect('/customers');
                }
            })
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })                
        req.flash('error', error_msg)        
         
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/add', { 
            title: 'Add New Customer',
            name: req.body.name,
            email: req.body.email
        })
    }
})
 
// SHOW EDIT USER FORM
router.get('/edit/(:id)', function(req, res, next){
   
connection.query('SELECT * FROM customers WHERE id = ' + req.params.id, function(err, rows, fields) {
            if(err) throw err
             
            // if user not found
            if (rows.length <= 0) {
                req.flash('error', 'Customers not found with id = ' + req.params.id)
                res.redirect('/customers')
            }
            else { // if user found
                // render to views/user/edit.ejs template file
                res.render('customers/edit', {
                    title: 'Edit Customer', 
                    //data: rows[0],
                    id: rows[0].id,
                    name: rows[0].name,
                    email: rows[0].email                    
                })
            }            
        })
  
})
 
// EDIT USER POST ACTION
router.post('/update/:id', function(req, res, next) {
    req.assert('name', 'Name is required').notEmpty()           //Validate nam           //Validate age
    req.assert('email', 'A valid email is required').isEmail()  //Validate email
  
    var errors = req.validationErrors()
     
    if( !errors ) {   
 
        var user = {
            name: req.sanitize('name').escape().trim(),
            email: req.sanitize('email').escape().trim()
        }
         
connection.query('UPDATE customers SET ? WHERE id = ' + req.params.id, user, function(err, result) {
                //if(err) throw err
                if (err) {
                    req.flash('error', err)
                     
                    // render to views/user/add.ejs
                    res.render('customers/edit', {
                        title: 'Edit Customer',
                        id: req.params.id,
                        name: req.body.name,
                        email: req.body.email
                    })
                } else {
                    req.flash('success', 'Data updated successfully!');
                    res.redirect('/customers');
                }
            })
         
    }
    else {   //Display errors to user
        var error_msg = ''
        errors.forEach(function(error) {
            error_msg += error.msg + '<br>'
        })
        req.flash('error', error_msg)
         
        /**
         * Using req.body.name 
         * because req.param('name') is deprecated
         */ 
        res.render('customers/edit', { 
            title: 'Edit Customer',            
            id: req.params.id, 
            name: req.body.name,
            email: req.body.email
        })
    }
})
       
// DELETE USER
router.get('/delete/(:id)', function(req, res, next) {
    var user = { id: req.params.id }
     
connection.query('DELETE FROM customers WHERE id = ' + req.params.id, user, function(err, result) {
            //if(err) throw err
            if (err) {
                req.flash('error', err)
                // redirect to users list page
                res.redirect('/customers')
            } else {
                req.flash('success', 'Customer deleted successfully! id = ' + req.params.id)
                // redirect to users list page
                res.redirect('/customers')
            }
        })
   })
 
 
module.exports = router;

Create views

First we will create one foleder name customers inside the views folder.

Next we need to create three views file name add.ejs, edit.ejs and index.ejs. We will create three view files inside the views/customers folder.

Create first file index.ejs

Index.ejs file, we will display the list of customers.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <div>
    <a href="/" class="btn btn-primary ml-3">Home</a>  
    <a href="/customers/add" class="btn btn-secondary ml-3">New Customer</a> 
    <a href="/customers" class="btn btn-info ml-3">Customer List</a>
</div>    
<!--   <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %> -->
  
<% if (messages.success) { %>
    <p class="alert alert-success mt-4"><%- messages.success %></p>
<% } %>  
<br>
  <table class="table">
<thead>
  <tr>
    <th scope="col">#</th>
    <th scope="col">Name</th>
    <th scope="col">Email</th>
    <th width="200px">Action</th>
 
  </tr>
</thead>
<tbody>
  <% if(data.length){
 
  for(var i = 0; i< data.length; i++) {%>  
  <tr>
    <th scope="row"><%= (i+1) %></th>
    <td><%= data[i].name%></td>
    <td><%= data[i].email%></td>
    <td>
    <a class="btn btn-success edit" href="../customers/edit/<%=data[i].id%>">Edit</a>                       
    <a class="btn btn-danger delete" onclick="return alert('Are You sure?')" href="../customers/delete/<%=data[i].id%>">Delete</a>                       
   </td>
  </tr>
  <% }
           
   }else{ %>
       <tr>
          <td colspan="3">No user</td>
       </tr>
    <% } %>    
  
</tbody>
</table>
</body>
</html>

Create second file name add.ejs

Add.ejs file, we will create form for sending to data in database.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
  <% if (messages.error) { %>
  <p style="color:red"><%- messages.error %></p>
<% } %>
  
<% if (messages.success) { %>
    <p style="color:green"><%- messages.success %></p>
<% } %>
<form action="/customers/add" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" name="email" class="form-control" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="">
</div>
 
<input type="submit" class="btn btn-primary" value="Add">
</form>
</body>
</html>

Create third file name edit.ejs

Next create last file edit.ejs, we will to edit data in this form.

<!DOCTYPE html>
<html>
<head>
  <title>Customers</title>
  <link rel='stylesheet' href='/stylesheets/style.css' />
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
</head>
<body>
<form action="/customers/update/<%= id %>" method="post" name="form1">
<div class="form-group">
  <label for="exampleInputPassword1">Name</label>
  <input type="text" class="form-control" name="name" id="name" value="<%= name %>" placeholder="Name">
</div>
<div class="form-group">
  <label for="exampleInputEmail1">Email address</label>
  <input type="email" class="form-control" name="email" id="email" aria-describedby="emailHelp" placeholder="Enter email" value="<%= email %>">
  
</div>
 
<button type="submit" class="btn btn-info">Update</button>
</form>
</body>
</html>

Test Node js Crud app

run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/customers

Conclusion

In this node express js crud tutorial – We have created node js crud (create, read, update, delete) application with mysql database. We have also successfully install node js framework express with use ejs templating engine.

Learn More

MySQL Databases With Python Tutorial

Build a Basic CRUD App with Node and React

Build a Simple CRUD App with Python, Flask, and React

Build a Basic CRUD App with Laravel and Vue

Build a Simple CRUD App with Spring Boot and Vue.js

Build a Basic CRUD App with Laravel and Angular

Build a Basic CRUD App with Laravel and React

Express.js & Node.js Course for Beginners - Full Tutorial

Build a CRUD App with Angular and Firebase

Angular 7 + Spring Boot CRUD Example

The Complete Node.js Developer Course (3rd Edition)

Angular & NodeJS - The MEAN Stack Guide

NodeJS - The Complete Guide (incl. MVC, REST APIs, GraphQL)

Node.js: The Complete Guide to Build RESTful APIs (2018)

How To Set Up Laravel, Nginx, and MySQL with Docker Compose

How To Set Up Laravel, Nginx, and MySQL with Docker Compose

How To Set Up Laravel, Nginx, and MySQL with Docker Compose - This tutorial will take you through the steps to install and build a web application using the Laravel framework, with Nginx as the web server and ...

How To Set Up Laravel, Nginx, and MySQL with Docker Compose - This tutorial will take you through the steps to install and build a web application using the Laravel framework, with Nginx as the web server and ...

Introduction

Over the past few years, Docker has become a frequently used solution for deploying applications thanks to how it simplifies running and deploying applications in ephemeral containers. When using a LEMP application stack, for example, with PHPNginxMySQL and the Laravel framework, Docker can significantly streamline the setup process.

Docker Compose has further simplified the development process by allowing developers to define their infrastructure, including application services, networks, and volumes, in a single file. Docker Compose offers an efficient alternative to running multiple docker container create and docker container run commands.

In this tutorial, you will build a web application using the Laravel framework, with Nginx as the web server and MySQL as the database, all inside Docker containers. You will define the entire stack configuration in a docker-compose file, along with configuration files for PHP, MySQL, and Nginx.

Prerequisites

Before you start, you will need:

  • One Ubuntu 18.04 server, and a non-root user with sudo privileges. Follow the Initial Server Setup with Ubuntu 18.04 tutorial to set this up.

  • Docker installed, following Steps 1 and 2 of How To Install and Use Docker on Ubuntu 18.04.

  • Docker Compose installed, following Step 1 of How To Install Docker Compose on Ubuntu 18.04.

Step 1 — Downloading Laravel and Installing Dependencies

As a first step, we will get the latest version of Laravel and install the dependencies for the project, including Composer, the application-level package manager for PHP. We will install these dependencies with Docker to avoid having to install Composer globally.

First, check that you are in your home directory and clone the latest Laravel release to a directory called laravel-app:

cd ~
git clone https://github.com/laravel/laravel.git laravel-app

Move into the laravel-app directory:

cd ~/laravel-app

Next, use Docker’s composer image to mount the directories that you will need for your Laravel project and avoid the overhead of installing Composer globally:

docker run --rm -v $(pwd):/app composer install

Using the -v and--rmflags with docker run creates an ephemeral container that will be bind-mounted to your current directory before being removed. This will copy the contents of your ~/laravel-app directory to the container and also ensure that the vendor folder Composer creates inside the container is copied to your current directory.

As a final step, set permissions on the project directory so that it is owned by your non-root user:

sudo chown -R $USER:$USER ~/laravel-app

This will be important when you write the Dockerfile for your application image in Step 4, as it will allow you to work with your application code and run processes in your container as a non-root user.

With your application code in place, you can move on to defining your services with Docker Compose.

Step 2 — Creating the Docker Compose File

Building your applications with Docker Compose simplifies the process of setting up and versioning your infrastructure. To set up our Laravel application, we will write a docker-compose file that defines our web server, database, and application services.

Open the file:

nano ~/laravel-app/docker-compose.yml

In the docker-compose file, you will define three services: app, webserver, and db. Add the following code to the file, being sure to replace the root password for MYSQL_ROOT_PASSWORD, defined as an environment variable under the db service, with a strong password of your choice:

~/laravel-app/docker-compose.yml

version: '3'
services:

  #PHP Service
  app:
    build:
      context: .
      dockerfile: Dockerfile
    image: digitalocean.com/php
    container_name: app
    restart: unless-stopped
    tty: true
    environment:
      SERVICE_NAME: app
      SERVICE_TAGS: dev
    working_dir: /var/www
    networks:
      - app-network

  #Nginx Service
  webserver:
    image: nginx:alpine
    container_name: webserver
    restart: unless-stopped
    tty: true
    ports:
      - "80:80"
      - "443:443"
    networks:
      - app-network

  #MySQL Service
  db:
    image: mysql:5.7.22
    container_name: db
    restart: unless-stopped
    tty: true
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: laravel
      MYSQL_ROOT_PASSWORD: your_mysql_root_password
      SERVICE_TAGS: dev
      SERVICE_NAME: mysql
    networks:
      - app-network

#Docker Networks
networks:
  app-network:
    driver: bridge

The services defined here include:

  • app: This service definition contains the Laravel application and runs a custom Docker image, digitalocean.com/php, that you will define in Step 4. It also sets the working_dir in the container to /var/www.

  • webserver: This service definition pulls the nginx:alpine image from Docker and exposes ports 80 and 443.

  • db: This service definition pulls the mysql:5.7.22 image from Docker and defines a few environmental variables, including a database calledlaravelfor your application and the root password for the database. You are free to name the database whatever you would like, and you should replace your_mysql_root_password with your own strong password. This service definition also maps port 3306 on the host to port 3306 on the container.

Each container_name property defines a name for the container, which corresponds to the name of the service. If you don’t define this property, Docker will assign a name to each container by combining a historically famous person’s name and a random word separated by an underscore.

To facilitate communication between containers, the services are connected to a bridge network called app-network. A bridge network uses a software bridge that allows containers connected to the same bridge network to communicate with each other. The bridge driver automatically installs rules in the host machine so that containers on different bridge networks cannot communicate directly with each other. This creates a greater level of security for applications, ensuring that only related services can communicate with one another. It also means that you can define multiple networks and services connecting to related functions: front-end application services can use a frontend network, for example, and back-end services can use a backend network.

Let’s look at how to add volumes and bind mounts to your service definitions to persist your application data.

Step 3 — Persisting Data

Docker has powerful and convenient features for persisting data. In our application, we will make use of volumes and bind mounts for persisting the database, and application and configuration files. Volumes offer flexibility for backups and persistence beyond a container’s lifecycle, while bind mounts facilitate code changes during development, making changes to your host files or directories immediately available in your containers. Our setup will make use of both.

  • Warning: By using bind mounts, you make it possible to change the host filesystem through processes running in a container, including creating, modifying, or deleting important system files or directories. This is a powerful ability with security implications, and could impact non-Docker processes on the host system. Use bind mounts with care.

In the docker-compose file, define a volume called dbdata under the db service definition to persist the MySQL database:

~/laravel-app/docker-compose.yml

...
#MySQL Service
db:
  ...
    volumes:
      - dbdata:/var/lib/mysql
    networks:
      - app-network
  ...

The named volume dbdata persists the contents of the /var/lib/mysql folder present inside the container. This allows you to stop and restart the db service without losing data.

At the bottom of the file, add the definition for the dbdata volume:

~/laravel-app/docker-compose.yml

...
#Volumes
volumes:
  dbdata:
    driver: local

With this definition in place, you will be able to use this volume across services.

Next, add a bind mount to the db service for the MySQL configuration files you will create in Step 7:

~/laravel-app/docker-compose.yml

...
#MySQL Service
db:
  ...
    volumes:
      - dbdata:/var/lib/mysql
      - ./mysql/my.cnf:/etc/mysql/my.cnf
  ...

This bind mount binds ~/laravel-app/mysql/my.cnf to /etc/mysql/my.cnf in the container.

Next, add bind mounts to the webserver service. There will be two: one for your application code and another for the Nginx configuration definition that you will create in Step 6:

~/laravel-app/docker-compose.yml

#Nginx Service
webserver:
  ...
  volumes:
      - ./:/var/www
      - ./nginx/conf.d/:/etc/nginx/conf.d/
  networks:
      - app-network

The first bind mount binds the application code in the ~/laravel-app directory to the /var/www directory inside the container. The configuration file that you will add to ~/laravel-app/nginx/conf.d/ will also be mounted to /etc/nginx/conf.d/ in the container, allowing you to add or modify the configuration directory’s contents as needed.

Finally, add the following bind mounts to the app service for the application code and configuration files:

~/laravel-app/docker-compose.yml

#PHP Service
app:
  ...
  volumes:
       - ./:/var/www
       - ./php/local.ini:/usr/local/etc/php/conf.d/local.ini
  networks:
      - app-network

The app service is bind-mounting the ~/laravel-app folder, which contains the application code, to the /var/www folder in the container. This will speed up the development process, since any changes made to your local application directory will be instantly reflected inside the container. You are also binding your PHP configuration file, ~/laravel-app/php/local.ini, to /usr/local/etc/php/conf.d/local.ini inside the container. You will create the local PHP configuration file in Step 5.

Your docker-compose file will now look like this:

~/laravel-app/docker-compose.yml

version: '3'
services:

  #PHP Service
  app:
    build:
      context: .
      dockerfile: Dockerfile
    image: digitalocean.com/php
    container_name: app
    restart: unless-stopped
    tty: true
    environment:
      SERVICE_NAME: app
      SERVICE_TAGS: dev
    working_dir: /var/www
    volumes:
      - ./:/var/www
      - ./php/local.ini:/usr/local/etc/php/conf.d/local.ini
    networks:
      - app-network

  #Nginx Service
  webserver:
    image: nginx:alpine
    container_name: webserver
    restart: unless-stopped
    tty: true
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./:/var/www
      - ./nginx/conf.d/:/etc/nginx/conf.d/
    networks:
      - app-network

  #MySQL Service
  db:
    image: mysql:5.7.22
    container_name: db
    restart: unless-stopped
    tty: true
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: laravel
      MYSQL_ROOT_PASSWORD: your_mysql_root_password
      SERVICE_TAGS: dev
      SERVICE_NAME: mysql
    volumes:
      - dbdata:/var/lib/mysql/
      - ./mysql/my.cnf:/etc/mysql/my.cnf
    networks:
      - app-network

#Docker Networks
networks:
  app-network:
    driver: bridge
#Volumes
volumes:
  dbdata:
    driver: local

Save the file and exit your editor when you are finished making changes.

With your docker-compose file written, you can now build the custom image for your application.

Step 4 — Creating the Dockerfile

Docker allows you to specify the environment inside of individual containers with a Dockerfile. A Dockerfile enables you to create custom images that you can use to install the software required by your application and configure settings based on your requirements. You can push the custom images you create to Docker Hub or any private registry.

Our Dockerfile will be located in our ~/laravel-app directory. Create the file:

nano ~/laravel-app/Dockerfile

This Dockerfile will set the base image and specify the necessary commands and instructions to build the Laravel application image. Add the following code to the file:

~/laravel-app/php/Dockerfile

FROM php:7.2-fpm

# Copy composer.lock and composer.json
COPY composer.lock composer.json /var/www/

# Set working directory
WORKDIR /var/www

# Install dependencies
RUN apt-get update && apt-get install -y \
    build-essential \
    mysql-client \
    libpng-dev \
    libjpeg62-turbo-dev \
    libfreetype6-dev \
    locales \
    zip \
    jpegoptim optipng pngquant gifsicle \
    vim \
    unzip \
    git \
    curl

# Clear cache
RUN apt-get clean && rm -rf /var/lib/apt/lists/*

# Install extensions
RUN docker-php-ext-install pdo_mysql mbstring zip exif pcntl
RUN docker-php-ext-configure gd --with-gd --with-freetype-dir=/usr/include/ --with-jpeg-dir=/usr/include/ --with-png-dir=/usr/include/
RUN docker-php-ext-install gd

# Install composer
RUN curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/local/bin --filename=composer

# Add user for laravel application
RUN groupadd -g 1000 www
RUN useradd -u 1000 -ms /bin/bash -g www www

# Copy existing application directory contents
COPY . /var/www

# Copy existing application directory permissions
COPY --chown=www:www . /var/www

# Change current user to www
USER www

# Expose port 9000 and start php-fpm server
EXPOSE 9000
CMD ["php-fpm"]

First, the Dockerfile creates an image on top of the php:7.2-fpm Docker image. This is a Debian-based image that has the PHP FastCGI implementation PHP-FPM installed. The file also installs the prerequisite packages for Laravel: mcrypt, pdo_mysql, mbstring, and imagick with composer.

The RUN directive specifies the commands to update, install, and configure settings inside the container, including cring a dedicated user and group called www. The WORKDIR instruction specifies the /var/www directory as the working directory for the application.

Creating a dedicated user and group with restricted permissions mitigates the inherent vulnerability when running Docker containers, which run by default as root. Instead of running this container as root, we’ve created the www user, who has read/write access to the /var/www folder thanks to the COPY instruction that we are using with the --chown flag to copy the application folder’s permissions.

Finally, the EXPOSE command exposes a port in the container, 9000, for the php-fpm server. CMD specifies the command that should run once the container is created. Here, CMD specifies "php-fpm", which will start the server.

Save the file and exit your editor when you are finished making changes.

You can now move on to defining your PHP configuration.

Step 5 — Configuring PHP

Now that you have defined your infrastructure in the docker-compose file, you can configure the PHP service to act as a PHP processor for incoming requests from Nginx.

To configure PHP, you will create the local.ini file inside the php folder. This is the file that you bind-mounted to /usr/local/etc/php/conf.d/local.ini inside the container in Step 2. Creating this file will allow you to override the default php.ini file that PHP reads when it starts.

Create the php directory:

mkdir ~/laravel-app/php
Next, open the local.ini file:


Next, open the local.ini file:

nano ~/laravel-app/php/local.ini

To demonstrate how to configure PHP, we’ll add the following code to set size limitations for uploaded files:

~/laravel-app/php/local.ini

upload_max_filesize=40M
post_max_size=40M

The upload_max_filesize and post_max_size directives set the maximum allowed size for uploaded files, and demonstrate how you can set php.ini configurations from your local.ini file. You can put any PHP-specific configuration that you want to override in the local.ini file.

Save the file and exit your editor.

With your PHP local.ini file in place, you can move on to configuring Nginx.

Step 6 — Configuring Nginx

With the PHP service configured, you can modify the Nginx service to use PHP-FPM as the FastCGI server to serve dynamic content. The FastCGI server is based on a binary protocol for interfacing interactive programs with a web server. For more information, please refer to this article on Understanding and Implementing FastCGI Proxying in Nginx.

To configure Nginx, you will create an app.conf file with the service configuration in the ~/laravel-app/nginx/conf.d/ folder.

First, create the nginx/conf.d/ directory:

mkdir -p ~/laravel-app/nginx/conf.d

Next, create the app.conf configuration file:

nano ~/laravel-app/nginx/conf.d/app.conf

Add the following code to the file to specify your Nginx configuration:

~/laravel-app/nginx/conf.d/app.conf

server {
    listen 80;
    index index.php index.html;
    error_log  /var/log/nginx/error.log;
    access_log /var/log/nginx/access.log;
    root /var/www/public;
    location ~ \.php$ {
        try_files $uri =404;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass app:9000;
        fastcgi_index index.php;
        include fastcgi_params;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_param PATH_INFO $fastcgi_path_info;
    }
    location / {
        try_files $uri $uri/ /index.php?$query_string;
        gzip_static on;
    }
}

The server block defines the configuration for the Nginx web server with the following directives:

  • listen: This directive defines the port on which the server will listen to incoming requests.

  • error_log and access_log: These directives define the files for writing logs.

  • root: This directive sets the root folder path, forming the complete path to any requested file on the local file system.

In the php location block, the fastcgi_pass directive specifies that the app service is listening on a TCP socket on port 9000. This makes the PHP-FPM server listen over the network rather than on a Unix socket. Though a Unix socket has a slight advantage in speed over a TCP socket, it does not have a network protocol and thus skips the network stack. For cases where hosts are located on one machine, a Unix socket may make sense, but in cases where you have services running on different hosts, a TCP socket offers the advantage of allowing you to connect to distributed services. Because our app container is running on a different host from our webserver container, a TCP socket makes the most sense for our configuration.

Save the file and exit your editor when you are finished making changes.

Thanks to the bind mount you created in Step 2, any changes you make inside the nginx/conf.d/ folder will be directly reflected inside the webserver container.

Next, let’s look at our MySQL settings.

Step 7 — Configuring MySQL

With PHP and Nginx configured, you can enable MySQL to act as the database for your application.

To configure MySQL, you will create the my.cnf file in the mysql folder. This is the file that you bind-mounted to/etc/mysql/my.cnfinside the container in Step 2. This bind mount allows you to override the my.cnf settings as and when required.

To demonstrate how this works, we’ll add settings to the my.cnf file that enable the general query log and specify the log file.

First, create the mysql directory:

mkdir ~/laravel-app/mysql

Next, make the my.cnf file:

nano ~/laravel-app/mysql/my.cnf

In the file, add the following code to enable the query log and set the log file location:

~/laravel-app/mysql/my.cnf

[mysqld]
general_log = 1
general_log_file = /var/lib/mysql/general.log

This my.cnf file enables logs, defining the general_log setting as 1 to allow general logs. The general_log_file setting specifies where the logs will be stored.

Save the file and exit your editor.

Our next step will be to start the containers.

Step 8 — Running the Containers and Modifying Environment Settings

Now that you have defined all of your services in your docker-compose file and created the configuration files for these services, you can start the containers. As a final step, though, we will make a copy of the .env.example file that Laravel includes by default and name the copy .env, which is the file Laravel expects to define its environment:

cp .env.example .env

We will configure the specific details of our setup in this file once we have started the containers.

With all of your services defined in your docker-compose file, you just need to issue a single command to start all of the containers, create the volumes, and set up and connect the networks:

docker-compose up -d

When you run docker-compose up for the first time, it will download all of the necessary Docker images, which might take a while. Once the images are downloaded and stored in your local machine, Compose will create your containers. The -d flag daemonizes the process, running your containers in the background.

Once the process is complete, use the following command to list all of the running containers:

docker ps

You will see the following output with details about your app, webserver, anddbcontainers:

Output
CONTAINER ID        NAMES               IMAGE                             STATUS              PORTS
c31b7b3251e0        db                  mysql:5.7.22                      Up 2 seconds        0.0.0.0:3306->3306/tcp
ed5a69704580        app                 digitalocean.com/php              Up 2 seconds        9000/tcp
5ce4ee31d7c0        webserver           nginx:alpine                      Up 2 seconds        0.0.0.0:80->80/tcp, 0.0.0.0:443->443/tcp

The CONTAINER ID in this output is a unique identifier for each container, while NAMES lists the service name associated with each. You can use both of these identifiers to access the containers. IMAGE defines the image name for each container, while STATUS provides information about the container’s state: whether it’s running, restarting, or stopped.

You can now modify the .env file on the app container to include specific details about your setup.

Open the file using docker-compose exec, which allows you to run specific commands in containers. In this case, you are opening the file for editing:

docker-compose exec app nano .env

Find the block that specifies DB_CONNECTION and update it to reflect the specifics of your setup. You will modify the following fields:

- DB_HOST will be your db database container.

  • DB_DATABASE will be thelaraveldatabase.
  • DB_USERNAME will be the username you will use for your database. In this case, we will use laraveluser.
  • DB_PASSWORD will be the secure password you would like to use for this user account.

/var/www/.env

DB_CONNECTION=mysql
DB_HOST=db
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=laraveluser
DB_PASSWORD=your_laravel_db_password

Save your changes and exit your editor.

Next, set the application key for the Laravel application with the php artisan key:generate command. This command will generate a key and copy it to your .env file, ensuring that your user sessions and encrypted data remain secure:

docker-compose exec app php artisan key:generate

You now have the environment settings required to run your application. To cache these settings into a file, which will boost your application’s load speed, run:

docker-compose exec app php artisan config:cache

Your configuration settings will be loaded into /var/www/bootstrap/cache/config.php on the container.

As a final step, visit http://your_server_ip in the browser. You will see the following home page for your Laravel application:

This is image title

With your containers running and your configuration information in place, you can move on to configuring your user information for the laravel database on the db container.

Step 9 — Creating a User for MySQL

The default MySQL installation only creates therootadministrative account, which has unlimited privileges on the database server. In general, it’s better to avoid using the root administrative account when interacting with the database. Instead, let’s create a dedicated database user for our application’s Laravel database.

To create a new user, execute an interactive bash shell on the db container with docker-compose exec:

docker-compose exec db bash

Inside the container, log into the MySQL root administrative account:

[email protected]:/# tmysql -u root -p

You will be prompted for the password you set for the MySQL root account during installation in your docker-compose file.

Start by checking for the database called laravel, which you defined in your docker-compose file. Run the show databases command to check for existing databases:

mysql> show databases;

You will see the laravel database listed in the output:

Output
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Next, create the user account that will be allowed to access this database. Our username will be laraveluser, though you can replace this with another name if you’d prefer. Just be sure that your username and password here match the details you set in your .env file in the previous step:

mysql> GRANT ALL ON laravel.* TO 'laraveluser'@'%' IDENTIFIED BY 'your_laravel_db_password';

Flush the privileges to notify the MySQL server of the changes:

mysql> FLUSH PRIVILEGES;

Exit MySQL:

mysql> EXIT;

Finally, exit the container:

[email protected]:/# exit

You have configured the user account for your Laravel application database and are ready to migrate your data and work with the Tinker console.

Step 10 — Migrating Data and Working with the Tinker Console

With your application running, you can migrate your data and experiment with the tinker command, which will initiate a PsySH console with Laravel preloaded. PsySH is a runtime developer console and interactive debugger for PHP, and Tinker is a REPL specifically for Laravel. Using the tinker command will allow you to interact with your Laravel application from the command line in an interactive shell.

First, test the connection to MySQL by running the Laravel artisan migrate command, which creates a migrations table in the database from inside the container:

docker-compose exec app php artisan migrate

This command will migrate the default Laravel tables. The output confirming the migration will look like this:

Output

Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table

Once the migration is complete, you can run a query to check if you are properly connected to the database using the tinker command:

docker-compose exec app php artisan tinker

Test the MySQL connection by getting the data you just migrated:

>>>   \DB::table('migrations')->get();

You will see output that looks like this:

Output
=> Illuminate\Support\Collection {#2856
     all: [
       {#2862
         +"id": 1,
         +"migration": "2014_10_12_000000_create_users_table",
         +"batch": 1,
       },
       {#2865
         +"id": 2,
         +"migration": "2014_10_12_100000_create_password_resets_table",
         +"batch": 1,
       },
     ],
   }

You can use tinker to interact with your databases and to experiment with services and models.

With your Laravel application in place, you are ready for further development and experimentation.

Conclusion

You now have a LEMP stack application running on your server, which you’ve tested by accessing the Laravel welcome page and creating MySQL database migrations.

Build a REST API with Express and Sequelize

Build a REST API with Express and Sequelize

In this tutorial all steps for building a REST API with Express and Sequelize.

In this tutorial all steps for building a REST API with Express and Sequelize.

When I started learning Node I made the same mistake as most bootcamp graduates these days. I went straight for NoSQL databases without even considering SQL as an option and I had no idea what I had missed out on until when I was recently exposed to Ruby on Rails and SQL. I learned the difference between the two type of databases and managed to love both of them, equally. Because of my newly found knowledge I decided to write a walkthrough on how to build a REST API using Express and Sequelize.

Let’s start by installing the npm packages that we will use:

npm init
npm install express sequelize sqlite3 body-parser --save
npm install sequelize-cli -g

sequelize init

The command “sequelize init” will create the backbone of our database architecture and will allow us to easily connect to our database to insert/retrieve/delete records. Once that command is completed you will see a couple of extra folders and files in your app and your folder structure should look something like this:

|- express-sequelize
|-- config
|--- config.json
|-- migrations
|-- models
|--- index.js
|-- node_modules
|-- seeders
|-- package.json
|-- package-lock.json

The planned database models and the relationships between them will look something like this:

For this tutorial I will use SQLite3 for the sake of simplicity; in order to use that with Sequelize we have to go into our config/config.json and replace the content of the file with the following:

{
    "development": {
    "dialect": "sqlite",
    "storage": "./database.sqlite3"
  },
    "test": {
    "dialect": "sqlite",
    "storage": ":memory"
  },
    "production": {
    "dialect": "sqlite",
    "storage": "./database.sqlite3"
  }
}

Now that our environment is all set up, it’s time to create the models for our database. Sequelize-CLI allows us to create models on the fly with no effort; we simply have to write the name of the model and the name of the columns in the database that will belong to the current model and the type of data that we will store. Just like this:

sequelize model:create --name Physician --attributes name:string
sequelize model:create --name Patient --attributes name:string
sequelize model:create --name Appointment --attributes physicianId:integer,patientId:integer

After we’re done with this, you’ll see that we have 3 new files inside our *models *folder. All we have to do now is to commit these changes to the database by migrating the changes with the following command:

sequelize db:migrate

This means that our models are created in the database (but, we still have to create the associations between them). We’ll have 2 main models: Physician and Patient. They will be connected by a join table: Appointment. A Physician can have many Patients and vice versa through an Appointment. Let’s start with the Physician. All we have to do is define the association inside the “associate’’ function. The way we do this is on line 11. We select the current model and use the belongsToMany() function that will receive the model we want to connect it to and the optional arguments. In this case we have to include our join table Appointment.

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Physician = sequelize.define(
    "Physician",
    {
      name: DataTypes.STRING
    },
    {}
  );
  Physician.associate = function(models) {
    Physician.belongsToMany(models.Patient, { through: "Appointment", foreignKey: "physicianId" });
  };
  return Physician;
};

We have to do the same with our Patient model:

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Patient = sequelize.define(
    "Patient",
    {
      name: DataTypes.STRING
    },
    {}
  );
  Patient.associate = function(models) {
    Patient.belongsToMany(models.Physician, { through: "Appointment", foreignKey: "patientId" });
  };
  return Patient;
};

And now it’s time to connect these two models with our join model by defining a relationship where Appointment belongs to a Patient and a Physician.

"use strict";
module.exports = (sequelize, DataTypes) => {
  const Appointment = sequelize.define(
    "Appointment",
    {
      physicianId: DataTypes.INTEGER,
      patientId: DataTypes.INTEGER
    },
    {}
  );
  Appointment.associate = function(models) {
    Appointment.belongsTo(models.Physician);
    Appointment.belongsTo(models.Patient);
  };
  return Appointment;
};

This means that our database is ready. It’s time to get started with the Node server. Nothing complicated, just a simple Express server. First of all let’s create a file called server.js and let’s paste this code inside.

const express = require("express");
const bodyparser = require("body-parser");
const port = process.env.PORT || 5000;

const app = express();

app.use(bodyparser.urlencoded({ extended: true }));
app.use(bodyparser.json());

require("./routes/appointmentRoutes")(app);
require("./routes/physicianRoutes")(app);
require("./routes/patientRoutes")(app);

app.listen(port, () => console.log(`Server started on ${port}`));

All that is left is to define the routes where our server will receive requests. For this I will create 3 files inside a folder named “routes”, one file for each model. Each file will have the following routes included:

'GET'    /name
'GET'    /name/:id
'POST'   /name
'PUT'    /name/:id
'DELETE' /name/:id 

In these files we’ll import our models from the database that we’ll use and Sequelize will allow us to call methods on these models that execute specific queries in the database. The content of your files will be almost identical, but with different model names.

This is what “physicianRoutes.js” will look like:

const Physician = require("../models").Physician;
const Patient = require("../models").Patient;

module.exports = function(router) {
  router.get("/physicians", (req, res) => {
    Physician.findAll({
      include: [Patient]
    })
      .then(physicians => {
        res.json(physicians);
      })
      .catch(err => res.json(err));
  });

  router.get("/physicians/:id", (req, res) => {
    Physician.findAll({
      where: { id: req.params.id }
    })
      .then(physician => {
        res.json(physician[0]);
      })
      .catch(err => res.json(err));
  });

  router.post("/physicians", (req, res) => {
    Physician.create({
      name: req.body.name
    })
      .then(res => {
        res.json(res);
      })
      .catch(err => res.json(err));
  });

  router.put("/physicians/:id", (req, res) => {
    Physician.update({ name: req.body.name }, { where: { id: req.params.id } })
      .then(updatedPhysician => {
        res.json(updatedPhysician);
      })
      .catch(err => res.json(err));
  });

  router.delete("/physicians/:id", (req, res) => {
    Physician.destroy({
      where: { id: req.params.id }
    })
      .then(physician => {
        res.json(physician);
      })
      .catch(err => res.json(err));
  });
};

If you compare it to “patientRoutes.js”, the only main difference you will see is the model names.

const Physician = require("../models").Physician;
const Patient = require("../models").Patient;

module.exports = function(router) {
  router.get("/patients", (req, res) => {
    Patient.findAll({
      include: [Physician]
    })
      .then(patients => {
        res.json(patients);
      })
      .catch(err => res.json(err));
  });

  router.get("/patients/:id", (req, res) => {
    Patient.findAll({
      where: { id: req.params.id }
    })
      .then(patient => {
        res.json(patient[0]);
      })
      .catch(err => res.json(err));
  });

  router.post("/patients", (req, res) => {
    Patient.create({
      name: req.body.name
    })
      .then(res => {
        res.json(res);
      })
      .catch(err => res.json(err));
  });

  router.put("/patients/:id", (req, res) => {
    Patient.update({ name: req.body.name }, { where: { id: req.params.id } })
      .then(updatedPatient => {
        res.json(updatedPatient);
      })
      .catch(err => res.json(err));
  });

  router.delete("/patients/:id", (req, res) => {
    Patient.destroy({
      where: { id: req.params.id }
    })
      .then(patient => {
        res.json(patient);
      })
      .catch(err => res.json(err));
  });
};

And “appointmentRoutes.js”:

const Appointment = require("../models").Appointment;
const Physician = require("../models").Physician;
const Patient = require("../models").Patient;

module.exports = function(router) {
  router.get("/appointments", (req, res) => {
    Appointment.findAll({
      include: [Physician, Patient]
    }).then(appointments => {
      res.json(appointments);
    });
  });

  router.get("/appointments/:id", (req, res) => {
    Appointment.findAll({
      where: { id: req.params.id },
      include: [Physician, Patient]
    }).then(appointment => {
      res.json(appointment[0]);
    });
  });

  router.post("/appointments", (req, res) => {
    Appointment.create({
      physicianId: req.body.physicianId,
      patientId: req.body.patientId
    })
      .then(appointments => {
        res.json(appointments);
      })
      .catch(err => res.json(err));
  });

  router.put("/appointments/:id", (req, res) => {
    Appointment.update(
      { physicianId: req.body.physicianId, patientId: req.body.patientId },
      { where: { id: req.params.id } }
    )
      .then(updatedAppointment => {
        res.json(updatedAppointment);
      })
      .catch(err => console.log(err));
  });

  router.delete("/appointments/:id", (req, res) => {
    Appointment.destroy({
      where: { id: req.params.id }
    }).then(appointment => {
      res.json(appointment);
    });
  });
};

These three files will help us to carry out the basic CRUD functionality in our app. Of course we can create more endpoints where we can describe what to do in specific cases or what to return.

This is all for now, if you start up your server and populate your database you can Create, Read, Update and Delete your records.

Sending sensor data to localhost using IoT development board esp8266-12

Sending  sensor data to localhost  using IoT development board esp8266-12

In this article, I am going to show you how to send sensor sensor data to a local webserver using esp8266.

Before delving into this article , let me explain the term localhost.
Localhost refers to the local computer that a program is running on. For example, if you are running a Web browser on your computer, your computer is considered to be the "localhost".

The deliverables for this project are:

Xamp webserver download it here https://www.apachefriends.org/

Arduino IDE download here https://www.arduino.cc/

ESP8266 official website https://www.nodemcu.com/

Smartphone used as a router.

Project requirement: Create a folder in the xamp/htdocs named esp8266. In this folder you are going to save the postData.php and databaseConfig.php

To send the sensor data to a database, we need to write an arduino sketch using a TCP protocol to communicate wirelessly to the local webserver.

Let's get it done now!

Step 1: Install esp8266 board the link to the procedure can be found here https://randomnerdtutorials.com/how-to-install-esp8266-board-arduino-ide/

Step 2: The arduino sketch is shown below. I create two function connectWifi() and sendSensorData() which I invoke respectively in the void setup and void loop. If you are confused please look for a tutorial on basic arduino code.

#include <WiFiClient.h> 
#include <ESP8266WebServer.h>
#include <ESP8266HTTPClient.h>
const char *ssid = "MelleB";  //ENTER YOUR WIFI ssid
const char *password = "refj4497";  //ENTER YOUR WIFI password


void setup() {
connectWifi();
}


void loop() {
SendSensorData();
}
//function to connect to wifi
void connectWifi(){
  delay(1000);
  Serial.begin(115200);
  WiFi.mode(WIFI_OFF);        //Prevents reconnection issue (taking too long to connect)
  delay(1000);
  WiFi.mode(WIFI_STA);        //This line hides the viewing of ESP as wifi hotspot
  WiFi.begin(ssid, password);     //Connect to your WiFi router
  Serial.println("");
  Serial.print("Connecting");
  // Wait for connection
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  //If connection successful show IP address in serial monitor 
  Serial.print("Connected to ");
  Serial.println(ssid);
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());  //IP address assigned to your ESP
}


//function to send sensor data 
void SendSensorData() {
  HTTPClient http;    //Declare object of class HTTPClient
  String sensorData1,sensorData2,sensorData3,sensorData4,sensorData5,sensorData6,sensorData7, postData;
  sensorData1="High";
    sensorData2="High";
      sensorData3="High";
        sensorData4="High";
          sensorData5="High";
            sensorData6="High";
              sensorData7="High";
  //Post Data
  postData = "sensor1=" +  sensorData1 + "&sensor2=" + sensorData2+ "&sensor3=" + sensorData3+  "&sensor4=" + sensorData4+ "&sensor5=" + sensorData5+ "&sensor6=" + sensorData6+  "&sensor7=" + sensorData7;
  
  http.begin("http://192.168.43.142/esp8266/postData.php");              //change the ip to your computer ip address
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");    //Specify content-type header
 
  int httpCode = http.POST(postData);   //Send the request
  String payload = http.getString();    //Get the response payload
 
  Serial.println(httpCode);   //Print HTTP return code
  Serial.println(payload);    //Print request response payload
 
  http.end();  //Close connection
  
  delay(5000);  //Post Data at every 5 seconds
}

Step 3: Create the database sensor and the table name logs and save it in the esp866 folder as databaseConfig.php

//connect to localhost if not exists
	$servername = "localhost";
	$username = "root";
	$password = "";


	// Create connection
	$conn = new mysqli($servername, $username, $password);
	// Check connection
	if ($conn->connect_error) {
	    die("Connection failed: " . $conn->connect_error);
	}


	// Create database
	$sql = "CREATE DATABASE sensor";
	
	    echo "Database created successfully";
	} else {
	    echo "Error creating database: " . $conn->error;
	}


	$conn->close();


	echo "<br>";
//Connect to database and create table
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "sensor";


	// Create connection
	$conn = new mysqli($servername, $username, $password, $dbname);
	// Check connection
	if ($conn->connect_error) {
	    die("Connection failed: " . $conn->connect_error);
	}


	
	$sql = "CREATE TABLE logs (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	sensor1 VARCHAR(30),
	sensor2 VARCHAR(30),
    sensor3 VARCHAR(30),
	sensor4 VARCHAR(50),
	sensor5 VARCHAR(50),
	sensor6 VARCHAR(50),
	sensor7 VARCHAR(50),
	\`Date\` DATE NULL,
	\`Time\` TIME NULL, 
	\`TimeStamp\` TIMESTAMP NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP)";


	if ($conn->query($sql) === TRUE) {
	    echo "Table logs created successfully";
	} else {
	    echo "Error creating table: " . $conn->error;
	}


	$conn->close();
?>

Step 4: Create postData.php in the esp866 folder and paste the code below in it.

//Creates new record as per request
    //Connect to database
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "sensor";


    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Database Connection failed: " . $conn->connect_error);
    }


    //Get current date and time
    date\_default\_timezone_set('Asia/Kolkata');
    $d = date("Y-m-d");
    //echo " Date:".$d."<BR>";
    $t = date("H:i:s");


    if(!empty($\_POST\['sensor1'\]) || !empty($\_POST\['sensor2'\]))
    {
    	$sensorData1 = $_POST\['sensor1'\];
    	$sensorData2 = $_POST\['sensor2'\];
		$sensorData3 = $_POST\['sensor3'\];
		$sensorData4 = $_POST\['sensor4'\];
		$sensorData5 = $_POST\['sensor5'\];
		$sensorData6 = $_POST\['sensor6'\];
		$sensorData7 = $_POST\['sensor7'\];


	    $sql = "INSERT INTO logs (sensor1, sensor2,sensor3,sensor4,sensor5,sensor6,sensor7, Date, Time) VALUES ('".$sensorData1."', '".$sensorData2."', '".$sensorData3."', '".$sensorData4."', '".$sensorData5."', '".$sensorData6."', '".$sensorData7."', '".$d."', '".$t."')";


		if ($conn->query($sql) === TRUE) {
		    echo "OK";
		} else {
		    echo "Error: " . $sql . "<br>" . $conn->error;
		}
	}




	$conn->close();
?>

Step 5: software testing

  • Open your web browser and type localhost/esp8266. Click on databaseConfig.php you should get a message data base sensor created successfully and do the same with postData.php. If you do not get any error message then you are good to go.
  • Open the serial monitor of your arduino and set the baud rate to 115200
  • The wifi ssid and ip address should display to the serial monitor
  • You should get a response 200 from the server and ok message
  • Check your database table you will get all the sensor table high, the date and time.
  • ** The output is shown below**

Please let me know in case of any errors.

React Native Tutorial: SQLite Offline Android/iOS Mobile App

React Native Tutorial: SQLite Offline Android/iOS Mobile App

React Native Tutorial: SQLite Offline Android/iOS Mobile App

Table of Contents:
  • Install React App Creator and Create App
  • Add Navigation Header and required Screen
  • Install and Configure React Native SQLite Storage
  • Show List of Product
  • Show Product Details and Delete Product
  • Add Product
  • Edit Product
  • Run and Test React Native and SQLite Offline Mobile App

The following tools, frameworks, and modules are required for this tutorial:

  • Install React App Creator and Create App
  • Add Navigation Header and required Screen
  • Install and Configure React Native SQLite Storage
  • Show List of Product
  • Show Product Details and Delete Product
  • Add Product
  • Edit Product
  • Run and Test React Native and SQLite Offline Mobile App

Before start to the main steps, make sure that you have installed Node.js and can run npm in the terminal or command line. To check the existing or installed Node.js environment open the terminal/command line then type this command.

node -v
v10.15.1
npm -v
6.8.0
yarn -v
1.10.1

1. Install React App Creator and Create App

The Create React Native App is a tool for creating a React Native App. To install it, type this command in your App projects folder.

sudo npm install -g react-native-cli

Then create a React Native App using this command.

react-native init reactOffline

That command will create a React Native app then install all required modules. The app or project folder will contain these folders and files.

Next, go to the newly created React App folder.

cd reactSqlite

To run on iOS device or simulator run this command.

react-native run-ios

The simulator will open along with the new terminal window. Go to the project folder from the new terminal window then run this command.

react-native start

Right after iOS build success and React Native started, you will see this view in the iOS Simulator.

To run on Android device or simulator, make sure ADB detected the device.

adb devices
List of devices attached
J8AXGF0194047T6&nbsp;&nbsp; &nbsp;device

Next, type this command to run on the Android device or simulator.

react-native run-android

It will open the new terminal windows. Just go to the project folder then type this command.

react-native start

You will see this app in your Android device.

Sometimes, if running React Native app faster than starting React-Native Javascript bundler you see this red-screen of error.

No bundle URL present.

Make sure you’re running a packager server or have included a .jsbundle file in your application bundle.

RCTFatal
__28-[RCTCxxBridge handleError:]_block_invoke
_dispatch_call_block_and_release
_dispatch_client_callout
_dispatch_main_queue_callback_4CF
__CFRUNLOOP_IS_SERVICING_THE_MAIN_DISPATCH_QUEUE__
__CFRunLoopRun
CFRunLoopRunSpecific
GSEventRunModal
UIApplicationMain
main
start
0x0

Don’t worry, just start the Metro Bundler in the newly opened terminal window after you go to the project folder. After Metro Bundler started completely, refresh the React Native app on your device or simulator. In iOS Simulator you will see this error after refresh.

Attempting to reload bridge before it's valid: <RCTCxxBridge: 0x7ff34bc00510>. Try restarting the development server if connected.

-[RCTCxxBridge reload]
&nbsp; &nbsp; RCTCxxBridge.mm:986
-[RCTRedBox reloadFromRedBoxWindow:]
-[RCTRedBoxWindow reload]
-[UIApplication sendAction:to:from:forEvent:]
-[UIControl sendAction:to:forEvent:]
-[UIControl _sendActionsForEvents:withEvent:]
-[UIControl touchesEnded:withEvent:]
-[UIWindow _sendTouchesForEvent:]
-[UIWindow sendEvent:]
-[UIApplication sendEvent:]
__dispatchPreprocessedEventFromEventQueue
__handleEventQueueInternal
__handleEventQueueInternal
__CFRUNLOOP_IS_CALLING_OUT_TO_A_SOURCE0_PERFORM_FUNCTION__
__CFRunLoopDoSources0
__CFRunLoopRun
CFRunLoopRunSpecific
GSEventRunModal
UIApplicationMain
main
start
0x0

Just reload again the React Native app, you will get your React Native app running.

2. Add Navigation Header and required Screen

Above generated React Native App just show blank app with plain text. Now, we will show you how to add the Navigation Header and Home Screen for your app. So, it will look like the Native App. In the terminal or command line, type this command to install React Navigation module and don’t forget to stop the running Metro Bundler before installing the modules.

yarn add react-navigation --save
yarn add react-native-gesture-handler --save
react-native link react-native-gesture-handler

Next, create a folder for components and components files in the root of the app folder.

mkdir components
touch components/ProductScreen.js
touch components/ProductDetailsScreen.js
touch components/ProductAddScreen.js
touch components/ProductEditScreen.js

Open and edit components/ProductScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Product List',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Product List</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Details"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('ProductDetails')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Add Product"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('AddProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Edit Product"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('EditProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Open and edit components/ProductDetailsScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductDetailsScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Product Details',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Product Details</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Details... again"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.push('ProductDetails')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Home"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('Product')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go back"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.goBack()}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Open and edit components/ProductAddScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductAddScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Add Product',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Add Product</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Add Product... again"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.push('AddProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Home"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('Product')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go back"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.goBack()}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Open and edit components/ProductEditScreen.js then add this React codes.

import React, { Component } from 'react';
import { Button, View, Text } from 'react-native';

export default class ProductEditScreen extends Component {
&nbsp; static navigationOptions = {
&nbsp; &nbsp; title: 'Edit Product',
&nbsp; };
&nbsp; render() {
&nbsp; &nbsp; return (
&nbsp; &nbsp; &nbsp; <View style={{ flex: 1, alignItems: 'center', justifyContent: 'center' }}>
&nbsp; &nbsp; &nbsp; &nbsp; <Text>Add Product</Text>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Edit Product... again"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.push('EditProduct')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go to Home"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.navigate('Product')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title="Go back"
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.props.navigation.goBack()}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; );
&nbsp; }
}

Next, open and edit App.js then add replace all codes with this.

import React from 'react';
import { StyleSheet, Text, View } from 'react-native';
import { createAppContainer, createStackNavigator } from 'react-navigation';
import ProductScreen from './components/ProductScreen';
import ProductDetailsScreen from './components/ProductDetailsScreen';
import ProductAddScreen from './components/ProductAddScreen';
import ProductEditScreen from './components/ProductEditScreen';

const RootStack = createStackNavigator(
&nbsp; {
&nbsp; &nbsp; Product: ProductScreen,
&nbsp; &nbsp; ProductDetails: ProductDetailsScreen,
&nbsp; &nbsp; AddProduct: ProductAddScreen,
&nbsp; &nbsp; EditProduct: ProductEditScreen,
&nbsp; },
&nbsp; {
&nbsp; &nbsp; initialRouteName: 'Product',
&nbsp; &nbsp; navigationOptions: {
&nbsp; &nbsp; &nbsp; headerStyle: {
&nbsp; &nbsp; &nbsp; &nbsp; backgroundColor: '#777777',
&nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; &nbsp; headerTintColor: '#fff',
&nbsp; &nbsp; &nbsp; headerTitleStyle: {
&nbsp; &nbsp; &nbsp; &nbsp; fontWeight: 'bold',
&nbsp; &nbsp; &nbsp; },
&nbsp; &nbsp; },
&nbsp; },
);

const RootContainer = createAppContainer(RootStack);

export default class App extends React.Component {
&nbsp; render() {
&nbsp; &nbsp; return <RootContainer />;
&nbsp; }
}

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; backgroundColor: '#fff',
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center',
&nbsp; },
});

After Re-run the React Native app on the iOS/Android Device/Simulator you will see this updated views.

3. Install and Configure React Native SQLite Storage

Before creating an offline CRUD application using local data, we have to install the react-native-sqlite-storage and required UI/UX module.

yarn add react-native-sqlite-storage --save
yarn add react-native-elements --save
react-native link

We will use separate Class for accessing SQLite and do some CRUD (create, read, update, delete) operations. For that, create a new Javascript file on the root of the project folder.

touch Database.js

Open and edit Database.js then add this SQLite import with the configuration.

import SQLite from "react-native-sqlite-storage";
SQLite.DEBUG(true);
SQLite.enablePromise(true);

Add constant variable after that.

const database_name = "Reactoffline.db";
const database_version = "1.0";
const database_displayname = "SQLite React Offline Database";
const database_size = 200000;

Give this file a class name.

export default class Database {

}

Inside the class bracket, add a function for Database initialization that creates Database, tables, etc.

initDB() {
&nbsp; let db;
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; console.log("Plugin integrity check ...");
&nbsp; &nbsp; SQLite.echoTest()
&nbsp; &nbsp; &nbsp; .then(() => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log("Integrity check passed ...");
&nbsp; &nbsp; &nbsp; &nbsp; console.log("Opening database ...");
&nbsp; &nbsp; &nbsp; &nbsp; SQLite.openDatabase(
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_name,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_version,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_displayname,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; database_size
&nbsp; &nbsp; &nbsp; &nbsp; )
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .then(DB => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db = DB;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Database OPEN");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.executeSql('SELECT 1 FROM Product LIMIT 1').then(() => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Database is ready ... executing query ...");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).catch((error) =>{
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Received error: ", error);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Database not yet ready ... populating data");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('CREATE TABLE IF NOT EXISTS Product (prodId, prodName, prodDesc, prodImage, prodPrice)');
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).then(() => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Table created successfully");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }).catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(error);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(db);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(error);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log("echoTest failed - plugin not functional");
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; });
};

Add a function for close Database connection.

closeDatabase(db) {
&nbsp; if (db) {
&nbsp; &nbsp; console.log("Closing DB");
&nbsp; &nbsp; db.close()
&nbsp; &nbsp; &nbsp; .then(status => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log("Database CLOSED");
&nbsp; &nbsp; &nbsp; })
&nbsp; &nbsp; &nbsp; .catch(error => {
&nbsp; &nbsp; &nbsp; &nbsp; this.errorCB(error);
&nbsp; &nbsp; &nbsp; });
&nbsp; } else {
&nbsp; &nbsp; console.log("Database was not OPENED");
&nbsp; }
};

Add a function to get the list of products.

listProduct() {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; const products = [];
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('SELECT p.prodId, p.prodName, p.prodImage FROM Product p', []).then(([tx,results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log("Query completed");
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var len = results.rows.length;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; for (let i = 0; i < len; i++) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let row = results.rows.item(i);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(`Prod ID: ${row.prodId}, Prod Name: ${row.prodName}`)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; const { prodId, prodName, prodImage } = row;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; products.push({
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodId,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodName,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodImage
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(products);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(products);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to get Product by ID.

productById(id) {
&nbsp; console.log(id);
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('SELECT * FROM Product WHERE prodId = ?', [id]).then(([tx,results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(results);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(results.rows.length > 0) {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; let row = results.rows.item(0);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(row);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to save a new product to the SQLite database.

addProduct(prod) {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('INSERT INTO Product VALUES (?, ?, ?, ?, ?)', [prod.prodId, prod.prodName, prod.prodDesc, prod.prodImage, prod.prodPrice]).then(([tx, results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(results);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to update a product.

updateProduct(id, prod) {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('UPDATE Product SET prodName = ?, prodDesc = ?, prodImage = ?, prodPrice = ? WHERE prodId = ?', [prod.prodName, prod.prodDesc, prod.prodImage, prod.prodPrice, id]).then(([tx, results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(results);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

Add a function to delete a product.

deleteProduct(id) {
&nbsp; return new Promise((resolve) => {
&nbsp; &nbsp; this.initDB().then((db) => {
&nbsp; &nbsp; &nbsp; db.transaction((tx) => {
&nbsp; &nbsp; &nbsp; &nbsp; tx.executeSql('DELETE FROM Product WHERE prodId = ?', [id]).then(([tx, results]) => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; console.log(results);
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; resolve(results);
&nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; }).then((result) => {
&nbsp; &nbsp; &nbsp; &nbsp; this.closeDatabase(db);
&nbsp; &nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; });
&nbsp; }); &nbsp;
}

4. Show List of Product

To show or display the list of product, open and edit components/ProductScreen.js then replace all imports with these imports.

import React, { Component } from 'react';
import { StyleSheet, FlatList, ActivityIndicator, View, Text } from 'react-native';
import { ListItem, Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable before the class name.

const db = new Database();

Next, replace navigationOptions with these.

static navigationOptions = ({ navigation }) => {
&nbsp; return {
&nbsp; &nbsp; title: 'Product List',
&nbsp; &nbsp; headerRight: (
&nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; buttonStyle={{ padding: 0, backgroundColor: 'transparent' }}
&nbsp; &nbsp; &nbsp; &nbsp; icon={{ name: 'add-circle', style: { marginRight: 0, fontSize: 28 } }}
&nbsp; &nbsp; &nbsp; &nbsp; onPress={() => {&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; navigation.navigate('AddProduct', {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onNavigateBack: this.handleOnNavigateBack
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; }}
&nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; ),
&nbsp; };
};

Add a constructor function.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; isLoading: true,
&nbsp; &nbsp; products: [],
&nbsp; &nbsp; notFound: 'Products not found.\nPlease click (+) button to add it.'
&nbsp; };
}

Add a function to initialize the screen.

componentDidMount() {
&nbsp; this._subscribe = this.props.navigation.addListener('didFocus', () => {
&nbsp; &nbsp; this.getProducts();
&nbsp; });
}

Add a function to get the product list from Database class.

getProducts() {
&nbsp; let products = [];
&nbsp; db.listProduct().then((data) => {
&nbsp; &nbsp; products = data;
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; products,
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; }
&nbsp; })
}

Add a variable to iterate the listed product in the view.

keyExtractor = (item, index) => index.toString()

Add a function to render the List Item.

renderItem = ({ item }) => (
&nbsp; <ListItem
&nbsp; &nbsp; title={item.prodName}
&nbsp; &nbsp; leftAvatar={{
&nbsp; &nbsp; &nbsp; source: item.prodImage && { uri: item.prodImage },
&nbsp; &nbsp; &nbsp; title: item.prodName[0]
&nbsp; &nbsp; }}
&nbsp; &nbsp; onPress={() => {
&nbsp; &nbsp; &nbsp; this.props.navigation.navigate('ProductDetails', {
&nbsp; &nbsp; &nbsp; &nbsp; prodId: `${item.prodId}`,
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }}
&nbsp; &nbsp; chevron
&nbsp; &nbsp; bottomDivider
&nbsp; />
)

Add a function to render the rest of List view.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff"/>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; if(this.state.products.length === 0){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; <Text style={styles.message}>{this.state.notFound}</Text>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <FlatList
&nbsp; &nbsp; &nbsp; keyExtractor={this.keyExtractor}
&nbsp; &nbsp; &nbsp; data={this.state.products}
&nbsp; &nbsp; &nbsp; renderItem={this.renderItem}
&nbsp; &nbsp; />
&nbsp; );
}

Finally, add a stylesheet for the whole screen after the class bracket.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp;flex: 1,
&nbsp; &nbsp;paddingBottom: 22
&nbsp; },
&nbsp; item: {
&nbsp; &nbsp; padding: 10,
&nbsp; &nbsp; fontSize: 18,
&nbsp; &nbsp; height: 44,
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; },
&nbsp; message: {
&nbsp; &nbsp; padding: 16,
&nbsp; &nbsp; fontSize: 18,
&nbsp; &nbsp; color: 'red'
&nbsp; }
});

5. Show Product Details and Delete Product

From the list of product view, you will see that list item has an action button to show the product details. Next, open and edit components/ProductDetailsScreen.js then replace the imports with these imports.

import React, { Component } from 'react';
import { ScrollView, StyleSheet, Image, ActivityIndicator, View, Text } from 'react-native';
import { Card, Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable.

const db = new Database();

Add a function as the constructor.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; isLoading: true,
&nbsp; &nbsp; product: {},
&nbsp; &nbsp; id: '',
&nbsp; };
}

Add a function to initialize the screen.

componentDidMount() {
&nbsp; this._subscribe = this.props.navigation.addListener('didFocus', () => {
&nbsp; &nbsp; const { navigation } = this.props;
&nbsp; &nbsp; db.productById(navigation.getParam('prodId')).then((data) => {
&nbsp; &nbsp; &nbsp; console.log(data);
&nbsp; &nbsp; &nbsp; product = data;
&nbsp; &nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; &nbsp; product,
&nbsp; &nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; &nbsp; &nbsp; id: product.prodId
&nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; }).catch((err) => {
&nbsp; &nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; &nbsp; }
&nbsp; &nbsp; })
&nbsp; });
}

Add a function to delete a product data.

deleteProduct(id) {
&nbsp; const { navigation } = this.props;
&nbsp; this.setState({
&nbsp; &nbsp; isLoading: true
&nbsp; });
&nbsp; db.deleteProduct(id).then((result) => {
&nbsp; &nbsp; console.log(result);
&nbsp; &nbsp; this.props.navigation.goBack();
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; }
&nbsp; })
}

Add a function to render the whole Product Details view.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff" />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <ScrollView>
&nbsp; &nbsp; &nbsp; <Card style={styles.container}>
&nbsp; &nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Image
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; style={{width: 150, height: 150}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; source={{uri: this.state.product.prodImage}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product ID: {this.state.product.prodId}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product Name: {this.state.product.prodName}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product Desc: {this.state.product.prodDesc}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <View>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Text style={{fontSize: 16}}>Product Price: {this.state.product.prodPrice}</Text>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; <View style={styles.detailButton}>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; backgroundColor={'#CCCCCC'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'edit'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Edit'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; this.props.navigation.navigate('EditProduct', {
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; prodId: `${this.state.id}`,
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }} />
&nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; &nbsp; <View style={styles.detailButton}>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; backgroundColor={'#999999'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; color={'#FFFFFF'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'delete'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Delete'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.deleteProduct(this.state.id)} />
&nbsp; &nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; </Card>
&nbsp; &nbsp; </ScrollView>
&nbsp; );
}

Finally, add the stylesheet for this screen after the class bracket.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; padding: 20
&nbsp; },
&nbsp; subContainer: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; paddingBottom: 20,
&nbsp; &nbsp; borderBottomWidth: 2,
&nbsp; &nbsp; borderBottomColor: '#CCCCCC',
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; },
&nbsp; detailButton: {
&nbsp; &nbsp; marginTop: 10
&nbsp; }
})

6. Add Product

To add or save a new Product, open and edit the components/ProductAddScreen.js then replace all imports with these imports.

import React, { Component } from 'react';
import { StyleSheet, ScrollView, ActivityIndicator, View, TextInput } from 'react-native';
import { Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable.

const db = new Database();

Add a constructor inside the class bracket after the navigationOptions.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; prodId: '',
&nbsp; &nbsp; prodName: '',
&nbsp; &nbsp; prodDesc: '',
&nbsp; &nbsp; prodImage: '',
&nbsp; &nbsp; prodPrice: '0',
&nbsp; &nbsp; isLoading: false,
&nbsp; };
}

Add a function to update the input text values.

updateTextInput = (text, field) => {
&nbsp; const state = this.state
&nbsp; state[field] = text;
&nbsp; this.setState(state);
}

Add a function to save a product to the SQLite table.

saveProduct() {
&nbsp; this.setState({
&nbsp; &nbsp; isLoading: true,
&nbsp; });
&nbsp; let data = {
&nbsp; &nbsp; prodId: this.state.prodId,
&nbsp; &nbsp; prodName: this.state.prodName,
&nbsp; &nbsp; prodDesc: this.state.prodDesc,
&nbsp; &nbsp; prodImage: this.state.prodImage,
&nbsp; &nbsp; prodPrice: this.state.prodPrice
&nbsp; }
&nbsp; db.addProduct(data).then((result) => {
&nbsp; &nbsp; console.log(result);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; &nbsp; this.props.navigation.state.params.onNavigateBack;
&nbsp; &nbsp; this.props.navigation.goBack();
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; })
}

Add a function to render the whole add product view.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff"/>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <ScrollView style={styles.container}>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product ID'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodId}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodId')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Name'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodName}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodName')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; multiline={true}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; numberOfLines={4}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Description'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodDesc}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodDesc')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Image'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodImage}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodImage')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Price'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodPrice}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; keyboardType='numeric'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodPrice')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.button}>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'save'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Save'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.saveProduct()} />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; </ScrollView>
&nbsp; );
}

Finally, add the style for the whole screen.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; padding: 20
&nbsp; },
&nbsp; subContainer: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; marginBottom: 20,
&nbsp; &nbsp; padding: 5,
&nbsp; &nbsp; borderBottomWidth: 2,
&nbsp; &nbsp; borderBottomColor: '#CCCCCC',
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; }
})

7. Edit Product

To edit a product, open and edit components/ProductEditScreen.js then replace all imports with these imports.

import React, { Component } from 'react';
import { StyleSheet, ScrollView, ActivityIndicator, View, TextInput } from 'react-native';
import { Button } from 'react-native-elements';
import Database from '../Database';

Instantiate the Database as a constant variable.

const db = new Database();

Add the constructor after the navigationOptions function.

constructor() {
&nbsp; super();
&nbsp; this.state = {
&nbsp; &nbsp; prodId: '',
&nbsp; &nbsp; prodName: '',
&nbsp; &nbsp; prodDesc: '',
&nbsp; &nbsp; prodImage: '',
&nbsp; &nbsp; prodPrice: '0',
&nbsp; &nbsp; isLoading: true,
&nbsp; };
}

Add a function to initialize the screen that will get product data.

componentDidMount() {
&nbsp; const { navigation } = this.props;
&nbsp; db.productById(navigation.getParam('prodId')).then((data) => {
&nbsp; &nbsp; console.log(data);
&nbsp; &nbsp; const product = data;
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; prodId: product.prodId,
&nbsp; &nbsp; &nbsp; prodName: product.prodName,
&nbsp; &nbsp; &nbsp; prodDesc: product.prodDesc,
&nbsp; &nbsp; &nbsp; prodImage: product.prodImage,
&nbsp; &nbsp; &nbsp; prodPrice: product.prodPrice,
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState = {
&nbsp; &nbsp; &nbsp; isLoading: false
&nbsp; &nbsp; }
&nbsp; })
}

Add a function to update the input text value.

updateTextInput = (text, field) => {
&nbsp; const state = this.state
&nbsp; state[field] = text;
&nbsp; this.setState(state);
}

Add a function to update the product data.

updateProduct() {
&nbsp; this.setState({
&nbsp; &nbsp; isLoading: true,
&nbsp; });
&nbsp; const { navigation } = this.props;
&nbsp; let data = {
&nbsp; &nbsp; prodId: this.state.prodId,
&nbsp; &nbsp; prodName: this.state.prodName,
&nbsp; &nbsp; prodDesc: this.state.prodDesc,
&nbsp; &nbsp; prodImage: this.state.prodImage,
&nbsp; &nbsp; prodPrice: this.state.prodPrice
&nbsp; }
&nbsp; db.updateProduct(data.prodId, data).then((result) => {
&nbsp; &nbsp; console.log(result);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; &nbsp; this.props.navigation.state.params.onNavigateBack;
&nbsp; &nbsp; this.props.navigation.goBack();
&nbsp; }).catch((err) => {
&nbsp; &nbsp; console.log(err);
&nbsp; &nbsp; this.setState({
&nbsp; &nbsp; &nbsp; isLoading: false,
&nbsp; &nbsp; });
&nbsp; })
}

Add a function to render the whole Edit Product screen.

render() {
&nbsp; if(this.state.isLoading){
&nbsp; &nbsp; return(
&nbsp; &nbsp; &nbsp; <View style={styles.activity}>
&nbsp; &nbsp; &nbsp; &nbsp; <ActivityIndicator size="large" color="#0000ff"/>
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; )
&nbsp; }
&nbsp; return (
&nbsp; &nbsp; <ScrollView style={styles.container}>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product ID'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodId}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodId')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Name'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodName}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodName')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; multiline={true}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; numberOfLines={4}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Description'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodDesc}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodDesc')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Image'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodImage}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodImage')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.subContainer}>
&nbsp; &nbsp; &nbsp; &nbsp; <TextInput
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; placeholder={'Product Price'}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; value={this.state.prodPrice}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; keyboardType='numeric'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onChangeText={(text) => this.updateTextInput(text, 'prodPrice')}
&nbsp; &nbsp; &nbsp; &nbsp; />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; &nbsp; <View style={styles.button}>
&nbsp; &nbsp; &nbsp; &nbsp; <Button
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; large
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; leftIcon={{name: 'save'}}
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; title='Save'
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onPress={() => this.updateProduct()} />
&nbsp; &nbsp; &nbsp; </View>
&nbsp; &nbsp; </ScrollView>
&nbsp; );
}

Finally, add the stylesheet after the class bracket.

const styles = StyleSheet.create({
&nbsp; container: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; padding: 20
&nbsp; },
&nbsp; subContainer: {
&nbsp; &nbsp; flex: 1,
&nbsp; &nbsp; marginBottom: 20,
&nbsp; &nbsp; padding: 5,
&nbsp; &nbsp; borderBottomWidth: 2,
&nbsp; &nbsp; borderBottomColor: '#CCCCCC',
&nbsp; },
&nbsp; activity: {
&nbsp; &nbsp; position: 'absolute',
&nbsp; &nbsp; left: 0,
&nbsp; &nbsp; right: 0,
&nbsp; &nbsp; top: 0,
&nbsp; &nbsp; bottom: 0,
&nbsp; &nbsp; alignItems: 'center',
&nbsp; &nbsp; justifyContent: 'center'
&nbsp; }
})

8. Run and Test React Native and SQLite Offline Mobile App

As we show you at the first step, run the React Native and SQLite app using this command.

react-native run-android
react-native run-ios

After the new terminal window open, just go to the project folder then run this command.

react-native start

Now, you will see the whole application in the Android/iOS Device.

That it’s, the React Native and SQLite Offline Mobile App. You can get the full source code from our GitHub.