Flutter DataTable + MySQL

By | September 28, 2019

In this demo we will create a flutter app that communicates with the Server and create a table, insert records, update records, fetch all records and delete records.

The Data from the Server will be displayed in a DataTable.


Flutter Data Table + MySQL

Flutter Data Table + MySQL


Watch Video Tutorial


Here I am using XAMPP to create a local Server.
You can download XAMPP from here.
https://www.apachefriends.org/download.html

If you want to learn using SQLite in Flutter, then follow this link.


Server Side

In the Server I am creating a script inside a folder named “EmployeesDB”.

We will we connecting to the database and do a insert, update, select and delete in the database.

The script will look like this

<?php

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "TestDB";
    $table = "Employees"; // lets create a table named Employees.

    // we will get actions from the app to do operations in the database...
    $action = $_POST["action"];
    
    // Create Connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check Connection
    if($conn->connect_error){
        die("Connection Failed: " . $conn->connect_error);
        return;
    }

    // If connection is OK...

    // If the app sends an action to create the table...
    if("CREATE_TABLE" == $action){
        $sql = "CREATE TABLE IF NOT EXISTS $table ( 
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            first_name VARCHAR(30) NOT NULL,
            last_name VARCHAR(30) NOT NULL
            )";

        if($conn->query($sql) === TRUE){
            // send back success message
            echo "success";
        }else{
            echo "error";
        }
        $conn->close();
        return;
    }

    // Get all employee records from the database
    if("GET_ALL" == $action){
        $db_data = array();
        $sql = "SELECT id, first_name, last_name from $table ORDER BY id DESC";
        $result = $conn->query($sql);
        if($result->num_rows > 0){
            while($row = $result->fetch_assoc()){
                $db_data[] = $row;
            }
            // Send back the complete records as a json
            echo json_encode($db_data);
        }else{
            echo "error";
        }
        $conn->close();
        return;
    }

    // Add an Employee
    if("ADD_EMP" == $action){
        // App will be posting these values to this server
        $first_name = $_POST["first_name"];
        $last_name = $_POST["last_name"];
        $sql = "INSERT INTO $table (first_name, last_name) VALUES ('$first_name', '$last_name')";
        $result = $conn->query($sql);
        echo "success";
        $conn->close();
        return;
    }

    // Remember - this is the server file.
    // I am updating the server file.
    // Update an Employee
    if("UPDATE_EMP" == $action){
        // App will be posting these values to this server
        $emp_id = $_POST['emp_id'];
        $first_name = $_POST["first_name"];
        $last_name = $_POST["last_name"];
        $sql = "UPDATE $table SET first_name = '$first_name', last_name = '$last_name' WHERE id = $emp_id";
        if($conn->query($sql) === TRUE){
            echo "success";
        }else{
            echo "error";
        }
        $conn->close();
        return;
    }

    // Delete an Employee
    if('DELETE_EMP' == $action){
        $emp_id = $_POST['emp_id'];
        $sql = "DELETE FROM $table WHERE id = $emp_id"; // don't need quotes since id is an integer.
        if($conn->query($sql) === TRUE){
            echo "success";
        }else{
            echo "error";
        }
        $conn->close();
        return;
    }

?>

Flutter Side

Now we have the server side ready. Next we will create the model class for the object coming from the Server.
Its an employee record which has an id, first_name and a last_name. You can look at the create table query in the php code above.

Employee Model

Create a new file named Employee.dart and copy the below contents.

class Employee {
  String id;
  String firstName;
  String lastName;

  Employee({this.id, this.firstName, this.lastName});

  factory Employee.fromJson(Map<String, dynamic> json) {
    return Employee(
      id: json['id'] as String,
      firstName: json['first_name'] as String,
      lastName: json['last_name'] as String,
    );
  }
}

Now we will create a service class to call the Webs Services with the proper action like create, update etc.

Create a new file named Services.dart and copy this code into it.

import 'dart:convert';
import 'package:http/http.dart'
    as http; // add the http plugin in pubspec.yaml file.
import 'Employee.dart';

class Services {
  static const ROOT = 'http://localhost/EmployeesDB/employee_actions.php';
  static const _CREATE_TABLE_ACTION = 'CREATE_TABLE';
  static const _GET_ALL_ACTION = 'GET_ALL';
  static const _ADD_EMP_ACTION = 'ADD_EMP';
  static const _UPDATE_EMP_ACTION = 'UPDATE_EMP';
  static const _DELETE_EMP_ACTION = 'DELETE_EMP';

  // Method to create the table Employees.
  static Future<String> createTable() async {
    try {
      // add the parameters to pass to the request.
      var map = Map<String, dynamic>();
      map['action'] = _CREATE_TABLE_ACTION;
      final response = await http.post(ROOT, body: map);
      print('Create Table Response: ${response.body}');
      if (200 == response.statusCode) {
        return response.body;
      } else {
        return "error";
      }
    } catch (e) {
      return "error";
    }
  }

  static Future<List<Employee>> getEmployees() async {
    try {
      var map = Map<String, dynamic>();
      map['action'] = _GET_ALL_ACTION;
      final response = await http.post(ROOT, body: map);
      print('getEmployees Response: ${response.body}');
      if (200 == response.statusCode) {
        List<Employee> list = parseResponse(response.body);
        return list;
      } else {
        return List<Employee>();
      }
    } catch (e) {
      return List<Employee>(); // return an empty list on exception/error
    }
  }

  static List<Employee> parseResponse(String responseBody) {
    final parsed = json.decode(responseBody).cast<Map<String, dynamic>>();
    return parsed.map<Employee>((json) => Employee.fromJson(json)).toList();
  }

  // Method to add employee to the database...
  static Future<String> addEmployee(String firstName, String lastName) async {
    try {
      var map = Map<String, dynamic>();
      map['action'] = _ADD_EMP_ACTION;
      map['first_name'] = firstName;
      map['last_name'] = lastName;
      final response = await http.post(ROOT, body: map);
      print('addEmployee Response: ${response.body}');
      if (200 == response.statusCode) {
        return response.body;
      } else {
        return "error";
      }
    } catch (e) {
      return "error";
    }
  }

  // Method to update an Employee in Database...
  static Future<String> updateEmployee(
      String empId, String firstName, String lastName) async {
    try {
      var map = Map<String, dynamic>();
      map['action'] = _UPDATE_EMP_ACTION;
      map['emp_id'] = empId;
      map['first_name'] = firstName;
      map['last_name'] = lastName;
      final response = await http.post(ROOT, body: map);
      print('updateEmployee Response: ${response.body}');
      if (200 == response.statusCode) {
        return response.body;
      } else {
        return "error";
      }
    } catch (e) {
      return "error";
    }
  }

  // Method to Delete an Employee from Database...
  static Future<String> deleteEmployee(String empId) async {
    try {
      var map = Map<String, dynamic>();
      map['action'] = _DELETE_EMP_ACTION;
      map['emp_id'] = empId;
      final response = await http.post(ROOT, body: map);
      print('deleteEmployee Response: ${response.body}');
      if (200 == response.statusCode) {
        return response.body;
      } else {
        return "error";
      }
    } catch (e) {
      return "error"; // returning just an "error" string to keep this simple...
    }
  }
}

In the above code you can see that we are using the http package for service calls and the post parameters are sent in the form of a map.

To include the http package update your pubspec.yaml file like this

dependencies:
  flutter: 
    sdk: flutter

  http: "0.11.3+17" 
 ...

Let’s create the Main UI.

We will be displaying the employee list in a DataTable.

The below code will create a DataTable with columns ID, FIRST NAME, LAST NAME, DELETE (action).

 // Let's create a DataTable and show the employee list in it.
  SingleChildScrollView _dataBody() {
    // Both Vertical and Horozontal Scrollview for the DataTable to
    // scroll both Vertical and Horizontal...
    return SingleChildScrollView(
      scrollDirection: Axis.vertical,
      child: SingleChildScrollView(
        scrollDirection: Axis.horizontal,
        child: DataTable(
          columns: [
            DataColumn(
              label: Text('ID'),
            ),
            DataColumn(
              label: Text('FIRST NAME'),
            ),
            DataColumn(
              label: Text('LAST NAME'),
            ),
            // Lets add one more column to show a delete button
            DataColumn(
              label: Text('DELETE'),
            )
          ],
          rows: _employees
              .map(
                (employee) => DataRow(cells: [
                  DataCell(
                    Text(employee.id),
                    // Add tap in the row and populate the
                    // textfields with the corresponding values to update
                    onTap: () {
                      _showValues(employee);
                      // Set the Selected employee to Update
                      _selectedEmployee = employee;
                      setState(() {
                        _isUpdating = true;
                      });
                    },
                  ),
                  DataCell(
                    Text(
                      employee.firstName.toUpperCase(),
                    ),
                    onTap: () {
                      _showValues(employee);
                      // Set the Selected employee to Update
                      _selectedEmployee = employee;
                      // Set flag updating to true to indicate in Update Mode
                      setState(() {
                        _isUpdating = true;
                      });
                    },
                  ),
                  DataCell(
                    Text(
                      employee.lastName.toUpperCase(),
                    ),
                    onTap: () {
                      _showValues(employee);
                      // Set the Selected employee to Update
                      _selectedEmployee = employee;
                      setState(() {
                        _isUpdating = true;
                      });
                    },
                  ),
                  DataCell(IconButton(
                    icon: Icon(Icons.delete),
                    onPressed: () {
                      _deleteEmployee(employee);
                    },
                  ))
                ]),
              )
              .toList(),
        ),
      ),
    );
  }
  

The variable employees is initialized by getting employees from the service by calling the getEmployees() function in the Services class.The getEmployees() will return a json with fields id, first_name and last_name which will be mapped to the Employee object by using Employee.fromJson() method.

    _getEmployees() {
    _showProgress('Loading Employees...');
    Services.getEmployees().then((employees) {
      setState(() {
        _employees = employees;
      });
      _showProgress(widget.title); // Reset the title...
      print("Length ${employees.length}");
    });
  }
  

Similarly we can have Insert, Update and Delete actions.

The Complete UI will look like this…

import 'package:flutter/material.dart';
import 'Employee.dart';
import 'Services.dart';

class DataTableDemo extends StatefulWidget {
  //
  DataTableDemo() : super();

  final String title = 'Flutter Data Table';

  @override
  DataTableDemoState createState() => DataTableDemoState();
}

class DataTableDemoState extends State<DataTableDemo> {
  List<Employee> _employees;
  GlobalKey<ScaffoldState> _scaffoldKey;
  // controller for the First Name TextField we are going to create.
  TextEditingController _firstNameController;
  // controller for the Last Name TextField we are going to create.
  TextEditingController _lastNameController;
  Employee _selectedEmployee;
  bool _isUpdating;
  String _titleProgress;

  @override
  void initState() {
    super.initState();
    _employees = [];
    _isUpdating = false;
    _titleProgress = widget.title;
    _scaffoldKey = GlobalKey(); // key to get the context to show a SnackBar
    _firstNameController = TextEditingController();
    _lastNameController = TextEditingController();
    _getEmployees();
  }

  // Method to update title in the AppBar Title
  _showProgress(String message) {
    setState(() {
      _titleProgress = message;
    });
  }

  _showSnackBar(context, message) {
    _scaffoldKey.currentState.showSnackBar(
      SnackBar(
        content: Text(message),
      ),
    );
  }

  _createTable() {
    _showProgress('Creating Table...');
    Services.createTable().then((result) {
      if ('success' == result) {
        // Table is created successfully.
        _showSnackBar(context, result);
        _showProgress(widget.title);
      }
    });
  }

  // Now lets add an Employee
  _addEmployee() {
    if (_firstNameController.text.isEmpty || _lastNameController.text.isEmpty) {
      print('Empty Fields');
      return;
    }
    _showProgress('Adding Employee...');
    Services.addEmployee(_firstNameController.text, _lastNameController.text)
        .then((result) {
      if ('success' == result) {
        _getEmployees(); // Refresh the List after adding each employee...
        _clearValues();
      }
    });
  }

  _getEmployees() {
    _showProgress('Loading Employees...');
    Services.getEmployees().then((employees) {
      setState(() {
        _employees = employees;
      });
      _showProgress(widget.title); // Reset the title...
      print("Length ${employees.length}");
    });
  }

  _updateEmployee(Employee employee) {
    setState(() {
      _isUpdating = true;
    });
    _showProgress('Updating Employee...');
    Services.updateEmployee(
            employee.id, _firstNameController.text, _lastNameController.text)
        .then((result) {
      if ('success' == result) {
        _getEmployees(); // Refresh the list after update
        setState(() {
          _isUpdating = false;
        });
        _clearValues();
      }
    });
  }

  _deleteEmployee(Employee employee) {
    _showProgress('Deleting Employee...');
    Services.deleteEmployee(employee.id).then((result) {
      if ('success' == result) {
        _getEmployees(); // Refresh after delete...
      }
    });
  }

  // Method to clear TextField values
  _clearValues() {
    _firstNameController.text = '';
    _lastNameController.text = '';
  }

  _showValues(Employee employee) {
    _firstNameController.text = employee.firstName;
    _lastNameController.text = employee.lastName;
  }

  // Let's create a DataTable and show the employee list in it.
  SingleChildScrollView _dataBody() {
    // Both Vertical and Horozontal Scrollview for the DataTable to
    // scroll both Vertical and Horizontal...
    return SingleChildScrollView(
      scrollDirection: Axis.vertical,
      child: SingleChildScrollView(
        scrollDirection: Axis.horizontal,
        child: DataTable(
          columns: [
            DataColumn(
              label: Text('ID'),
            ),
            DataColumn(
              label: Text('FIRST NAME'),
            ),
            DataColumn(
              label: Text('LAST NAME'),
            ),
            // Lets add one more column to show a delete button
            DataColumn(
              label: Text('DELETE'),
            )
          ],
          rows: _employees
              .map(
                (employee) => DataRow(cells: [
                  DataCell(
                    Text(employee.id),
                    // Add tap in the row and populate the
                    // textfields with the corresponding values to update
                    onTap: () {
                      _showValues(employee);
                      // Set the Selected employee to Update
                      _selectedEmployee = employee;
                      setState(() {
                        _isUpdating = true;
                      });
                    },
                  ),
                  DataCell(
                    Text(
                      employee.firstName.toUpperCase(),
                    ),
                    onTap: () {
                      _showValues(employee);
                      // Set the Selected employee to Update
                      _selectedEmployee = employee;
                      // Set flag updating to true to indicate in Update Mode
                      setState(() {
                        _isUpdating = true;
                      });
                    },
                  ),
                  DataCell(
                    Text(
                      employee.lastName.toUpperCase(),
                    ),
                    onTap: () {
                      _showValues(employee);
                      // Set the Selected employee to Update
                      _selectedEmployee = employee;
                      setState(() {
                        _isUpdating = true;
                      });
                    },
                  ),
                  DataCell(IconButton(
                    icon: Icon(Icons.delete),
                    onPressed: () {
                      _deleteEmployee(employee);
                    },
                  ))
                ]),
              )
              .toList(),
        ),
      ),
    );
  }

  // UI
  @override
  Widget build(BuildContext context) {
    return Scaffold(
      key: _scaffoldKey,
      appBar: AppBar(
        title: Text(_titleProgress), // we show the progress in the title...
        actions: <Widget>[
          IconButton(
            icon: Icon(Icons.add),
            onPressed: () {
              _createTable();
            },
          ),
          IconButton(
            icon: Icon(Icons.refresh),
            onPressed: () {
              _getEmployees();
            },
          )
        ],
      ),
      body: Container(
        child: Column(
          mainAxisAlignment: MainAxisAlignment.center,
          children: <Widget>[
            Padding(
              padding: EdgeInsets.all(20.0),
              child: TextField(
                controller: _firstNameController,
                decoration: InputDecoration.collapsed(
                  hintText: 'First Name',
                ),
              ),
            ),
            Padding(
              padding: EdgeInsets.all(20.0),
              child: TextField(
                controller: _lastNameController,
                decoration: InputDecoration.collapsed(
                  hintText: 'Last Name',
                ),
              ),
            ),
            // Add an update button and a Cancel Button
            // show these buttons only when updating an employee
            _isUpdating
                ? Row(
                    children: <Widget>[
                      OutlineButton(
                        child: Text('UPDATE'),
                        onPressed: () {
                          _updateEmployee(_selectedEmployee);
                        },
                      ),
                      OutlineButton(
                        child: Text('CANCEL'),
                        onPressed: () {
                          setState(() {
                            _isUpdating = false;
                          });
                          _clearValues();
                        },
                      ),
                    ],
                  )
                : Container(),
            Expanded(
              child: _dataBody(),
            ),
          ],
        ),
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () {
          _addEmployee();
        },
        child: Icon(Icons.add),
      ),
    );
  }
}

Watch my youtube video to see all the above code in action.

35 thoughts on “Flutter DataTable + MySQL

  1. Sreejith

    Bro, Please upload nearest people user shower using lat, long by google map project.

    Reply
  2. Sreejith

    I am a beginner of the Android platform using flutter VSCode .two days before I have upgraded Flutter upgrade –force command. After successful upgrade, I have tried to build the app and its working in emulator but not working on my mobile phone. Error message in my phone is App Not Installed message. I am so stuck in this case. Please help me

    Reply
    1. James Post author

      Try uninstalling the app and reinstall after changing the package.

      Reply
  3. J.English

    Its a great tutorial, Please do a video on a continuous scrolling using php and mysql (using http requests).

    Thanks in advance.

    Reply
  4. harrylee

    why my getEmployee function doesn’t work?
    i tested it and it seems that “return parsed.map((json) => Employee.fromJson(json)).toList();” in ” parseResponse” doesn’t work either.

    Reply
    1. daniel

      hi
      I have The same problem .
      I’m new to Flutter and doesn’t know much about complex json parsing , please help me

      Reply
  5. bigmarn

    D/libc-netbsd(23007): getaddrinfo: localhost get result from proxy gai_error = 0
    It keeps showing me the error above when i click the add icon button for Create Table

    Reply
  6. Muhammad Adnan Ijaz

    Very nice but in this blog please add “Employee.dart” so that people will not get any errors. I am new in flutter but had experience in android development.
    Once again nice work.
    Note: For “Employee.dart” see the video and carefully write this class.

    Reply
  7. MohammedRais Shaikh

    Hi,

    I am using windows 10. I am trying to communicate the xamp server, but I got an error “Connection refuse”.

    What should I do to resolve this issue?

    Kindly reply as soon as possible.

    Reply
  8. Alex Muchau

    Hello, someone can help me ? my server side(php) have this error

    Notice: Undefined index: action in C:\xampp\htdocs\EmployeesDB\employee_actions.php on line 9

    I`ve tried everythink, but i coundn`t manage
    Kindly reply as soon as possible.

    Reply
    1. James Post author

      Make sure you are sending the parameters from Client side.

      Reply
      1. Alex Muchau

        What ? I’m a beginner at programmation. My server side code is the same as yours
        What can i do to fix ?

        Reply
  9. shaaban

    please

    1-
    error: Target of URI doesn’t exist: ‘package:http/http.dart’. (uri_does_not_exist at [flutterapp1] lib\Services.dart:2)

    2-
    error: The argument type ‘int’ can’t be assigned to the parameter type ‘String’. (argument_type_not_assignable at [flutterapp1] lib\DataTableDemo.dart:157) employee.id

    thanks

    Reply
    1. James Post author

      1. package:http/http.dart’ as http;
      2. Change your type of employee id from ‘String’ to ‘int’

      Reply
    1. James Post author

      employee_actions.php is the server side script which I am explaining in the youtube video. Please watch and let me know if you find any difficulties.
      Thanks.

      Reply
  10. Mikoyskie

    hello bro,
    below are the mediaquery error, i can’t fix it… hope it will be resolve πŸ™

    Installing build\app\outputs\apk\app.apk… 3.1s
    Syncing files to device Android SDK built for x86 64…
    I/flutter (11425): ══║ EXCEPTION CAUGHT BY WIDGETS LIBRARY β•žβ•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•β•
    I/flutter (11425): The following assertion was thrown building DataTableDemo(state: DataTableDemoState#fa534):
    I/flutter (11425): MediaQuery.of() called with a context that does not contain a MediaQuery.
    I/flutter (11425): No MediaQuery ancestor could be found starting from the context that was passed to MediaQuery.of().
    I/flutter (11425): This can happen because you do not have a WidgetsApp or MaterialApp widget (those widgets introduce
    I/flutter (11425): a MediaQuery), or it can happen if the context you use comes from a widget above those widgets.
    I/flutter (11425): The context used was:
    I/flutter (11425): Scaffold
    I/flutter (11425):
    I/flutter (11425): The relevant error-causing widget was:
    I/flutter (11425): DataTableDemo file:///C:/xampps/htdocs/EmployeesDB/employees_project/lib/main.dart:5:10
    I/flutter (11425):
    I/flutter (11425): When the exception was thrown, this was the stack:
    I/flutter (11425): #0 MediaQuery.of (package:flutter/src/widgets/media_query.dart:813:5)
    I/flutter (11425): #1 ScaffoldState.didChangeDependencies (package:flutter/src/material/scaffold.dart:2172:50)
    I/flutter (11425): #2 StatefulElement._firstBuild (package:flutter/src/widgets/framework.dart:4661:12)
    I/flutter (11425): #3 ComponentElement.mount (package:flutter/src/widgets/framework.dart:4476:5)
    I/flutter (11425): … Normal element mounting (9 frames)
    I/flutter (11425): #12 Element.inflateWidget (package:flutter/src/widgets/framework.dart:3446:14)
    I/flutter (11425): #13 Element.updateChild (package:flutter/src/widgets/framework.dart:3214:18)
    I/flutter (11425): #14 RenderObjectToWidgetElement._rebuild (package:flutter/src/widgets/binding.dart:1148:16)
    I/flutter (11425): #15 RenderObjectToWidgetElement.mount (package:flutter/src/widgets/binding.dart:1119:5)
    I/flutter (11425): #16 RenderObjectToWidgetAdapter.attachToRenderTree. (package:flutter/src/widgets/binding.dart:1061:17)
    I/flutter (11425): #17 BuildOwner.buildScope (package:flutter/src/widgets/framework.dart:2607:19)
    I/flutter (11425): #18 RenderObjectToWidgetAdapter.attachToRenderTree (package:flutter/src/widgets/binding.dart:1060:13)
    I/flutter (11425): #19 WidgetsBinding.attachRootWidget (package:flutter/src/widgets/binding.dart:941:7)
    I/flutter (11425): #20 WidgetsBinding.scheduleAttachRootWidget. (package:flutter/src/widgets/binding.dart:922:7)
    I/flutter (11425): (elided 11 frames from class _RawReceivePortImpl, class _Timer, dart:async, and dart:async-patch)
    I/flutter (11425):
    I/flutter (11425): ════════════════════════════════════════════════════════════════════════════════════════════════════
    Syncing files to device Android SDK built for x86 64… 5,133ms (!)

    Reply
    1. James Post author

      You are calling MediaQuery on a wrong context

      Reply
  11. Wandile

    Hi, I could login before but now it is giving me an Error.
    [ERROR:flutter/lib/ui/ui_dart_state.cc(157)] Unhandled Exception: FormatException: Unexpected character (at character 1).
    Please help

    Reply
    1. James Post author

      Updated the Article with Employee.dart. Sorry about that.

      Reply
  12. Niels

    Just wanna express my gratitude, this has been so unbelievably helpful! Very very good guide!

    Reply
  13. Asaad

    Hi, thanks for the video. Two questions. Flutter or Swift first, which is the best and why? Second, where is the link to download phpΒ΄s and code flutter? regards

    Reply
    1. James Post author

      Marcos, you can follow the same method to insert a list. Instead of sending strings, just send a json array string and insert in database.

      Reply
  14. Nadia

    Please,
    I got this error:

    The method ‘map’ was called on null.
    Receiver: null
    Tried calling: map(Closure: (Ticket) => DataRow)

    I tried implement in my own data. When i run my url in insomnia, i get this value. Can you help me, why i get this error? Thank you.

    Reply

Leave a Reply to James Cancel reply

Your email address will not be published. Required fields are marked *