Create BOT terminal with Jquery#3 : Access database columns, Validation command, Get the previous command

Repository

https://github.com/jquery/jquery

What Will I Learn?

• Access database columns
• Validation command in backend and frontend
• Get the previous command

Requirements

• Html, CSS, javascript
• Install Jquery and basic Jquery
• Localserver (Xampp, Wampp, or etc)

Resources

• jQuery - https://github.com/jquery/jquery
• Keycode info - http://keycode.info/

Difficulty

Basic

Tutorial Content

This tutorial is an advanced tutorial from the jquery bot tutorial series, in the previous section we have taught our bot to recognize databases and interact with databases but our bot's ability is still fairly simple, in this section, we will make our bot recognize the columns in the database and make our codes become dynamic. for that, we need some changes to the code structure we have. let's start this tutorial.

Access database columns

In the previous tutorial, our bot only knows the columns in the database but does not have the ability to access or read data in that column. for that, we will teach it, to access the selected column. the following is an example.

Index.html

$('#terminal').on('keyup', '.input',function(e){
  if(e.keyCode == 13){
       var value = $(this).val().trim().split(' ');
        if( value == ''){
            doResponse("Command not recognized")
        }else{
                 if(value[0] == "open"){
                  openLink(value[1])
                }else if(value[0] == "db"){
                $.post("api/index.php", { command: value[0], table:value[1], column:value[2] },
                    function(response){
                        for (var i = 0; i < response.length; i++) {
                    doResponse(response[i].title);
                  }
                  commandBot();
                        });
                    }
                }
            
            }
});

When doing ajax $.post (), we will add another parameter, column column:value[2]. We will use these parameters to validate the third command in our command line bot.

• Create validation command in the backend

in the backend in index.php, we need to make changes when accepting the new JSON parameter when we do ajax. We will use the method call_user_func_array();the following is our backend file.

Index.php

<?php
header('Content-Type: application/json');
$conn = mysqli_connect('localhost','root', '',  'data_bot');
//get the json key
$command  = $_POST['command'];
$table  = $_POST['table'];
$column  = $_POST['column'];
call_user_func_array($command, $table);
// create a function
function db($table){
  global $conn;
  $sql = "SELECT * FROM $table where title = $column";
  $result = mysqli_query($conn,$sql);
  echo json_encode(mysqli_fetch_all($result, MYSQLI_ASSOC));
}

• $column = $_POST['column'];: We can get the value of the third parameter that is passed on the frontend in the same way $column = $_POST['column'];.

• call_user_func_array($command, $table);: This function has two parameters that make this function dynamic. The first parameter is the name of the function that we will run and The second parameter is the parameters that we will pass to our function. In the code above the first parameter that we pass is $command that comes from theJSON key that we pass when doing ajax and the second parameter is the parameter that we function which we call that is $table. the second parameter is in array() form so you can pass more than one parameter.

• If you have seen my tutorial before our function name is named () but in this tutorial, I changed it to function db() so that the function is more dynamic.

Before you run our project, make sure your local server is running. then we run our code the results will be as below:

• Add dynamic function

We have created a dynamic function using call_user_func_array();. to see its usefulness we will create a function that can be called and executed dynamically based on what we ordered in the command line bot. The following is an example of its function.

<?php
header('Content-Type: application/json');
$conn = mysqli_connect('localhost','root', '',  'data_bot');
//get the json key
$command  = $_POST['command'];
$table  = $_POST['table'];
$column  = $_POST['column'];
call_user_func_array($command, array($table, $column));
// create a function
function db($table){
  global $conn;
  $sql = "SELECT * FROM $table where title = $column";
  $result = mysqli_query($conn,$sql);
  echo json_encode(mysqli_fetch_all($result, MYSQLI_ASSOC));
}
//table
function table($table, $column){
  global $conn;
  $sql = "SELECT * FROM $table where $column = $column";
  $result = mysqli_query($conn,$sql);
  echo json_encode(mysqli_fetch_all($result, MYSQLI_ASSOC));
}

• The function function table($table, $column) is used to read the table based on the command we type in the command line. This function has two parameters, the first is the table name$table and the second is the name of the column we are going to $column.

• We need to make changes to make our queries also dynamic by adding parameters to be part of a query that can be replaced according to the parameters that are operated as follows"SELECT * FROM $table where title = '$column'";

• In the frontend section(index.html) I will do the response in a form like the following and if we run it the result will be like the picture below:

doResponse('title:' + response [i] .title + 'Description:' + response [i] .description);

• Add validations column

We have managed to make the function dynamic by using a function called based on the command line in our bot. Now we will make a simple validation to check whether the name of the column we entered exists. the following is an example:

$.post("api/index.php", { command: value[0], table:value[1], column:value[2] },
    function(response){
          if(response.length ==0){
              doResponse('Column not found');
             }else{
                    for (var i = 0; i < response.length; i++) {
                      doResponse('title : '+response[i].title+' Description : '+ response[i].description);
                    }  
                  }                         
   commandBot();
});

• We will check whether the result of the backend contained in the response has an array length equal to 0 response.length ==0, if the conditions are met, it can be ascertained that data is not found and we can pass messages like doResponse('Column not found');. If there is no error then we will see the results as shown below:

Get the command input

If we look at our terminal BOT, there are things that are not pleasant when we type our command, the bot does not recognize the command that we entered earlier. so we have to retype our commands, of course, this is a less pleasant thing, for that we will create a function to display the commands before and after.

• Get the previous input

We will create a function to retrieve the previous command using Jquery, we can see an example like this.

$('#terminal').keydown(function(e){
        if(e.keyCode==38){
          var data = $('.input').eq(-2).val();
          $('.input').last().val(data);
        }
      });

• Use keydown: We will use the keydown() function to detect whether our keyboard is pressing arrow down. The code from arrow down is 38, You can check in http://keycode.info/.

• Get the previous value: to take the previous value from the input, we can use eq(), Because we will take the previous value then we can add -2 to the eq(), $('.input').eq(-2).val(); our input has class = 'input' so we can use $('.input').

• Set the previous value: After we get the previous value, we can set this value in our command line bot input as follows $('.input').last().val(data);
  If there is no error then we can see the results as shown below:

We have finished this tutorial, we have learned how to make our bot dynamic from the backend side and from the frontend side, we have also validated the backend and frontend, the BOT that we make is still lacking, so I hope you can develop it get better, hopefully this tutorial can help you understand how the terminal works at the command prompt that we often use, thanks

Curriculum

Bot Jquery#1 Preparation, input and output
Bot Jquery#2 Create database, Ajax process, Display data

Proof of workdone

https://github.com/milleaduski/botJquery