How to use SQL server in Kodular, Thunkable, Appybuilder or to other App Inventors

So, today I am gonna tell you how to connect to your own SQL server or database with your Kodular or other App Inventors’ apps. Usually we use Airtable Database or Firebase Database to our Apps cause they are very easy to implement. But connecting with SQL to your apps, gives you more features and more securities than Airtable.

Some Features

• From SQL you can create Tables from your App or Admin App with a simple query but this thing is not possible with Airtable, when you need to create a Table on Airtable you have to go to your Airtable Panel to create a Table manually
• From SQL you can create unlimited Rows, Columns and Tables (it depends on how much storage of SQL you get from your hosting providers) but in Airtable you can get upto 1200 Columns for Free, if you want to upgrade the column number then you must have to purchase
• From SQL you can send data from your App or Admin App in different languages but for Airtable you only can send data in English language
• From SQL you will get more quick response than from Airtable (it depends on your web server)

These are some great features of SQL

Get Started

To get started first you have to create a new SQL Database. You can use any free hosting or any paid hosting currently you are using to run your website.
If you are running a WordPress website or other Script or any other type of websites that is using your SQL database then please use different mySQL Database to be Safe, cause if you do something without any Backup, you may loose your website, so it is safe to create another mySQL database and then connect that database to your app. Now let’s create a new mySQL database, I am using cPanel so if you are using different Control Panels then the process may differ
So now Login to your Control Panel

Scroll down and Find For mySQL Database, click on that

Enter the name of your database (e.g. myapp) and click on Create Database

Now create a new User for the Database by filling Username and Password and then click on Create User

Now you can see the Database appears with no Privileged Users, so you have to connect with an User that we already created in Previous Step

Scroll down and search for Add User To Database and then Select an User and then Select the Database and click on Add to connect

Now click on ALL PRIVILEGES and click on Make Changes

Now you can see the Database is connected with the User

Now either you can start creating Table from phpMyAdmin or you can start integrate sql to your app and do the same from the app. Here I am not going to show you how to do that from phpMyAdmin, I will show you how to do that from Kodular Apps
You can use SQL by using Extension or without any extension. If you dont’ want to use any Extension then go to https://puravidaapps.com/mysql.php to get complete instructions about mySQL usage without any extension. If you want to use an extension then you can download DeepHost app from Google Play Store and download MySQL Database Extension and import it to your Kodular App, it will be so easy to use this extension. Also you can not get all features from this Extension but also there is a Run Query block from where you can do almost everything

Setup

So first you have to insert a PHP script into your website with your SQL Username, Password, Host and Database Name. So open your Website’s Filemanager and create a file sql.php(or other name that will use in MySql Database Extension) and paste the following PHP Script

<?php
/************************************CONFIG****************************************/
//Change Database Address, Username, Password and Name from your mySQL Database that is created by you in last step//
//DATABSE DETAILS//
$DB_ADDRESS="localhost";
$DB_USER="thetechs_techstudy";
$DB_PASS="password";
$DB_NAME="thetechs_myapp";

//SETTINGS//
//This code is something you set in the APP so random people cant use it.
$SQLKEY="secret";

/************************************CONFIG****************************************/

//these are just in case setting headers forcing it to always expire 
header('Cache-Control: no-cache, must-revalidate');

error_log(print_r($_POST,TRUE));

if( isset($_POST['query']) && isset($_POST['key']) ){                                   //checks if the tag post is there and if its been a proper form post
  //set content type to CSV (to be set here to be able to access this page also with a browser)
  header('Content-type: text/csv');

  if($_POST['key']==$SQLKEY){                                                           //validates the SQL key
    $query=urldecode($_POST['query']);
    if(get_magic_quotes_gpc()){     //check if the worthless pile of crap magic quotes is enabled and if it is, strip the slashes from the query
      $query=stripslashes($query);
    }
    $conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME);    //connect

    if($conn->connect_error){                                                           //checks connection
      header("HTTP/1.0 400 Bad Request");
      echo "ERROR Database Connection Failed: " . $conn->connect_error, E_USER_ERROR;   //reports a DB connection failure
    } else {
      $result=$conn->query($query);                                                     //runs the posted query
      if($result === false){
        header("HTTP/1.0 400 Bad Request");                                             //sends back a bad request error
        echo "Wrong SQL: " . $query . " Error: " . $conn->error, E_USER_ERROR;          //errors if the query is bad and spits the error back to the client
      } else {
        if (strlen(stristr($query,"SELECT"))>0) {                                       //tests if it's a SELECT statement
          $csv = '';                                                                    // bug fix Undefined variable: csv
          while ($fieldinfo = $result->fetch_field()) {
            $csv .= $fieldinfo->name.",";
          }
          $csv = rtrim($csv, ",")."\n";
          echo $csv;                                                                    //prints header row
          $csv = '';

          $result->data_seek(0);
          while($row = $result->fetch_assoc()){
            foreach ($row as $key => $value) {
              $csv .= $value.",";
            }
            $csv = rtrim($csv, ",")."\n";
          }
          echo $csv;                                                                    //prints all data rows
        } else {
          header("HTTP/1.0 201 Rows");
          echo "AFFECTED ROWS: " . $conn->affected_rows;       //if the query is anything but a SELECT, it will return the number of affected rows
        }
      }
      $conn->close();                                          //closes the DB
    }
  } else {
     header("HTTP/1.0 400 Bad Request");
     echo "Bad Request";                                       //reports if the secret key was bad
  }
} else {
        header("HTTP/1.0 400 Bad Request");
        echo "Bad Request";
}
?>

Change all to your SQL Username, Database Name, Password, Host Name and SQL Key

Now go to Kodular and select mySQL Database Extension and Insert URL of SQL file and insert SQL Key that is set in PHP Script

Now SQL Server is connected with your App, now you can Run any sql query in your app like Get data, Delete data etc.

To Run any query you can read any tutorial Online. In this tutorial I just let you know how to connect your app with SQL Database. In my next tutorial I will also share some useful queries with example