MySQL select result inside JS function

Question

I found a simple JS script that works as a chatbot. in the script itself the results of lastUserMessage are predefined inline like

if (lastUserMessage === 'name') {
   botMessage = 'My name is ' + botName;
}

What I am trying to achieve is if the JS to search for lastUserMessage within a db and to provide botMessage from there.

I am sure it should't be that hard, but I cant figure out how to do it.

Here is the JS code:

nlp = window.nlp_compromise;
var messages = [], //array that hold the record of each string in chat
  lastUserMessage = "", //keeps track of the most recent input string from the user
  botMessage = "", //var keeps track of what the chatbot is going to say
  botName = 'Bot Name', //name of the chatbot
  talking = true; //when false the speach function doesn't work

//edit this function to change what the chatbot says
function chatbotResponse() {
  talking = true;
  botMessage = "Ops... didn't get this"; //the default message

  if (lastUserMessage === 'name') {
    botMessage = 'My name is ' + botName;
  }

}

//this runs each time enter is pressed.
//It controls the overall input and output
function newEntry() {
  //if the message from the user isn't empty then run 
  if (document.getElementById("chatbox").value != "") {
    //pulls the value from the chatbox ands sets it to lastUserMessage
    lastUserMessage = document.getElementById("chatbox").value;
    //sets the chat box to be clear
    document.getElementById("chatbox").value = "";
    //adds the value of the chatbox to the array messages
    messages.push(lastUserMessage);
    //Speech(lastUserMessage);  //says what the user typed outloud
    //sets the variable botMessage in response to lastUserMessage
    chatbotResponse();
    //add the chatbot's name and message to the array messages
    messages.push("<b>" + botName + ":</b> " + botMessage);
    // says the message using the text to speech function written below
    Speech(botMessage);
    //outputs the last few array elements of messages to html
    for (var i = 1; i < 8; i++) {
      if (messages[messages.length - i])
        document.getElementById("chatlog" + i).innerHTML = messages[messages.length - i];
    }
  }
}

//runs the keypress() function when a key is pressed
document.onkeypress = keyPress;
//if the key pressed is 'enter' runs the function newEntry()
function keyPress(e) {
  var x = e || window.event;
  var key = (x.keyCode || x.which);
  if (key == 13 || key == 3) {
    //runs this function when enter is pressed
    newEntry();
  }
  if (key == 38) {
    console.log('hi')
      //document.getElementById("chatbox").value = lastUserMessage;
  }
}

//clears the placeholder text ion the chatbox
//this function is set to run when the users brings focus to the chatbox, by clicking on it
function placeHolder() {
  document.getElementById("chatbox").placeholder = "";
}

Here is the HTML code

<div id='bodybox'>
  <div id='chatborder'>
    <p id="chatlog2" class="chatlog">&nbsp;</p>
    <p id="chatlog1" class="chatlog">&nbsp;</p>
    <input type="text" name="chat" id="chatbox" placeholder="Hi there! Type here to talk to me." onfocus="placeHolder()">
  </div>

What need to happen?

Ideally, the script should take the values "lastUserMessage" and "botMessage" from a db that has 2 columns "lastUserMessage" and "botMessage".

What I tried to to do is following Ghost's comment below... but didn't work.

    nlp = window.nlp_compromise;
var messages = [], //array that hold the record of each string in chat
  lastUserMessage = "", //keeps track of the most recent input string from the user
  botMessage = "", //var keeps track of what the chatbot is going to say
  botName = 'Bot Name', //name of the chatbot
  talking = true; //when false the speach function doesn't work

//edit this function to change what the chatbot says
function chatbotResponse() {
  talking = true;
  botMessage = "Ops... didn't get this"; //the default message

      $.ajax({                                      
  url: 'db_query.php',         
  data: "lastUserMessag=lastUserMessag", 
  dataType: 'json',                 
  success: function(data)          
  {
    var lastUserMessage_db = data[0]; 
    var botMessage_db= data[1];

if (lastUserMessage === lastUserMessage_db) {
    botMessage = botMessage_db;
  }
  } 
});

}

//this runs each time enter is pressed.
//It controls the overall input and output
function newEntry() {
  //if the message from the user isn't empty then run 
  if (document.getElementById("chatbox").value != "") {
    //pulls the value from the chatbox ands sets it to lastUserMessage
    lastUserMessage = document.getElementById("chatbox").value;
    //sets the chat box to be clear
    document.getElementById("chatbox").value = "";
    //adds the value of the chatbox to the array messages
    messages.push(lastUserMessage);
    //Speech(lastUserMessage);  //says what the user typed outloud
    //sets the variable botMessage in response to lastUserMessage
    chatbotResponse();
    //add the chatbot's name and message to the array messages
    messages.push("<b>" + botName + ":</b> " + botMessage);
    // says the message using the text to speech function written below
    Speech(botMessage);
    //outputs the last few array elements of messages to html
    for (var i = 1; i < 8; i++) {
      if (messages[messages.length - i])
        document.getElementById("chatlog" + i).innerHTML = messages[messages.length - i];
    }
  }
}

//runs the keypress() function when a key is pressed
document.onkeypress = keyPress;
//if the key pressed is 'enter' runs the function newEntry()
function keyPress(e) {
  var x = e || window.event;
  var key = (x.keyCode || x.which);
  if (key == 13 || key == 3) {
    //runs this function when enter is pressed
    newEntry();
  }
  if (key == 38) {
    console.log('hi')
      //document.getElementById("chatbox").value = lastUserMessage;
  }
}

//clears the placeholder text ion the chatbox
//this function is set to run when the users brings focus to the chatbox, by clicking on it
function placeHolder() {
  document.getElementById("chatbox").placeholder = "";
}

And in DB_query.php I have

$p = $_GET['lastUserMessag']; 
      $query=mysql_query("SELECT lastUserMessag, botMessage FROM `aiml` WHERE lastUserMessag='$p'");
      $array = mysql_fetch_row($query);  
    echo json_encode($array);

I suppose you're using client-side JS so what does the server-side app look like? Client-side JS executes HTTP requests to a server and the server looks into the database with some query. I cannot see where JS issues MySQL queries directly. — Ewald Benes
– Ewald Benes, Commented Nov 14, 2017 at 14:28
It is on a windows server. The JS does not issues MySQL queries.... I believe this is what I need to happen. — lStoilov
– lStoilov, Commented Nov 14, 2017 at 15:58
You would need to GET/POST your server that accesses the MySQL database. Otherwise you could use local storage although I’m not sure that’s what you want and if all browsers support it. Read this: html5rocks.com/en/tutorials/offline/storage — Cole
– Cole, Commented Nov 16, 2017 at 19:13
So you want SO users to write the server-side code for you? Did you try to search and see how you can achieve that? — Christos Lytras
– Christos Lytras, Commented Nov 16, 2017 at 20:08

user8574318 · Accepted Answer · 2017-11-18 17:04:18Z

6

The Javascript you are executing currently runs in the browser but is not connected to any Database that you could fetch data from.

To do so you'd have to send a POST or GET request to your backend server (assuming you have one) that, I assume, is written in JS, using NodeJS.

Note that you should NEVER give the user access (e.g. connect the database to the code you are using right now, which executes in a browser) to said database as they are then able to do whatever they want with it.

If you want to look into NodeJS and how to handle requests, I suggest you look into ExpressJS and this great MySQL + NodeJS tutorial on W3Schools depending on your level of expertise

answered Nov 18, 2017 at 17:04

user8574318

Sign up to request clarification or add additional context in comments.

3 Comments

lStoilov Over a year ago

Thanks, I have tried to do it... but for some reason it doesn't want to trigger the request to the database

Kulvar Over a year ago

If using NodeJS, instead of AJAX request, I would suggest using websocket

Ricardo Ribeiro Over a year ago

Suggesting ExpressJS for this is overkill. If you only want to access database and return the data to the client, why would you use a full-featured framework? Or any framework at all? It may be easier to start, but in my opinion it's not a good way to learn.

Rick James · Accepted Answer · 2017-11-16 22:28:19Z

4

Javascript runs in the Client's browser. MySQL runs in the Server. So, something else is needed to connect them.

I use PHP and AJAX as the conduit between them. JS issues an AJAX call. (Note: "A" stands for "Asynchronous".) Then JS gets the results via a "callback". Meanwhile, the target of the AJAX 'call' is a PHP program (or Java or VB or ...) which connects to the database (MySQL) and performs the SELECT and builds the JSON to deliver back to JS.

answered Nov 16, 2017 at 22:28

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

Stamos · Accepted Answer · 2017-11-23 16:49:12Z

1

+50

have you confirmed that the db_query.php works?

Try to use POSTMAN or Broswer like Chrome to hit the url ex. localhost/db_query.php or the correct one and see if you get the result you are expecting.

If you get the expected result then something is wrong with you JavaScript.

$.ajax is asynchronous so it will be called and the javascript execution will continue.

I try explain it. You call chatbotResponse() witch does a $.ajax call that on success sets the data

var lastUserMessage_db = data[0]; 
var botMessage_db= data[1];

BUT $.ajax is asynchronous so javascript execution will continue SO this javascript code will run before success is called

    //add the chatbot's name and message to the array messages
    messages.push("<b>" + botName + ":</b> " + botMessage);
    // says the message using the text to speech function written below
    Speech(botMessage);
    //outputs the last few array elements of messages to html
    for (var i = 1; i < 8; i++) {
      if (messages[messages.length - i])
        document.getElementById("chatlog" + i).innerHTML = messages[messages.length - i];
    }

so the problem is that the above code will run before you get the lastUserMessage from db_query.php

Solution

Easy solution is change ajax to synchronous call async:false, this is not best practice.

$.ajax({                                      
  url: 'db_query.php?lastUserMessage='+lastUserMessage,         
  type: "GET",
  async: false,               
  success: function(data)          
  {
    var lastUserMessage_db = data[0]; 
    var botMessage_db= data[1];

if (lastUserMessage === lastUserMessage_db) {
    botMessage = botMessage_db;
  }
  } 
});

Better way is to utilize the success callback

Example:

$.ajax({
      url: 'db_query.php?lastUserMessage='+lastUserMessage,         
      type: "GET",
      success: function(data) {
        var lastUserMessage_db = data[0];
        var botMessage_db = data[1];

        if (lastUserMessage === lastUserMessage_db) {
          botMessage = botMessage_db;
        }

        //add the chatbot's name and message to the array messages
        messages.push("<b>" + botName + ":</b> " + botMessage);
        // says the message using the text to speech function written below
        Speech(botMessage);
        //outputs the last few array elements of messages to html
        for (var i = 1; i < 8; i++) {
          if (messages[messages.length - i])
            document.getElementById("chatlog" + i).innerHTML = messages[messages.length - i];
        }

      }
    });

edited Nov 23, 2017 at 16:49

answered Nov 23, 2017 at 14:38

Stamos

4,0181 gold badge25 silver badges48 bronze badges

9 Comments

lStoilov Over a year ago

I have tried using both examples, but for some reason it doesn't want to execute the query. When I try to execute db_query.php it does return both lastUserMessage and botMessage. One thing that I am thinking is: How does the Ajax understand if it should use POST or GET? or dataType: 'json' is enough? in the db_query.php I use $p = $_GET['lastUserMessage']; to get the value from the url.

Stamos Over a year ago

Fixed ajax calls check it out.

Stamos Over a year ago

Please check your code you have some possible typos. I would suggest to use some developer tools like chromes to debug your code.

lStoilov Over a year ago

I had one typo in lastUserMessag=lastUserMessag...fixed. Tried the new code... but still doesn't work.

lStoilov Over a year ago

one other thing that I have noticed is that when I try to alert the lastUserMessage_db or botMessage_db inside the ajax, it returns only the first letter

|

lStoilov · Accepted Answer · 2017-11-24 14:19:10Z

This is how I make it work with Stamos' help

nlp = window.nlp_compromise;

var messages = [], //array that hold the record of each string in chat
  lastUserMessage = "", //keeps track of the most recent input string from the user
  botMessage = "", //var keeps track of what the chatbot is going to say
  botName = 'Bot Name', //name of the chatbot
  talking = true; //when false the speach function doesn't work
//

//****************************************************************
//edit this function to change what the chatbot says
function chatbotResponse() {
  talking = true;
  botMessage = "Sorry, Didnt get that"; //the default message

  $.ajax({
    url: "appi.php?lastUserMessage=" + lastUserMessage,
    type: "GET",
    async: false,
    success: function(data) {

      var obj = JSON.parse(data);

      if (lastUserMessage === obj.lastUserMessage) {
        botMessage = obj.botMessage;
      }
    }
  });

}

//****************************************************************

//this runs each time enter is pressed.
//It controls the overall input and output
function newEntry() {
  //if the message from the user isn't empty then run 
  if (document.getElementById("chatbox").value != "") {
    //pulls the value from the chatbox ands sets it to lastUserMessage
    lastUserMessage = document.getElementById("chatbox").value;
    //sets the chat box to be clear
    document.getElementById("chatbox").value = "";
    //adds the value of the chatbox to the array messages
    messages.push(lastUserMessage);
    //Speech(lastUserMessage);  //says what the user typed outloud
    //sets the variable botMessage in response to lastUserMessage
    chatbotResponse();
    //add the chatbot's name and message to the array messages
    messages.push("<b>" + botName + ":</b> " + botMessage);
    // says the message using the text to speech function written below
    Speech(botMessage);
    //outputs the last few array elements of messages to html
    for (var i = 1; i < 8; i++) {
      if (messages[messages.length - i])
        document.getElementById("chatlog" + i).innerHTML = messages[messages.length - i];
    }
  }
}

//text to Speech
//https://developers.google.com/web/updates/2014/01/Web-apps-that-talk-Introduction-to-the-Speech-Synthesis-API
function Speech(say) {
  //if ('speechSynthesis' in window && talking) {
  //  var utterance = new SpeechSynthesisUtterance(say);
  //msg.voice = voices[10]; // Note: some voices don't support altering params
  //msg.voiceURI = 'native';
  //utterance.volume = 1; // 0 to 1
  //utterance.rate = 0.1; // 0.1 to 10
  //utterance.pitch = 1; //0 to 2
  //utterance.text = 'Hello World';
  //utterance.lang = 'en-US';
  // speechSynthesis.speak(utterance);
  // }
}

//runs the keypress() function when a key is pressed
document.onkeypress = keyPress;
//if the key pressed is 'enter' runs the function newEntry()
function keyPress(e) {
  var x = e || window.event;
  var key = (x.keyCode || x.which);
  if (key == 13 || key == 3) {
    //runs this function when enter is pressed
    newEntry();
  }
  if (key == 38) {
    console.log('hi')
    //document.getElementById("chatbox").value = lastUserMessage;
  }
}

//clears the placeholder text ion the chatbox
//this function is set to run when the users brings focus to the chatbox, by clicking on it
function placeHolder() {
  document.getElementById("chatbox").placeholder = "";
}

body {
  font: 15px arial, sans-serif;
  background-color: #fff;
  padding-top: 15px;
  padding-bottom: 15px;
}

#bodybox {
  margin: auto;
  max-width: 550px;
  font: 15px arial, sans-serif;
  background-color: lightgrey;
  border-style: solid;
  border-width: 1px;
  padding-top: 20px;
  padding-bottom: 25px;
  padding-right: 25px;
  padding-left: 25px;
  box-shadow: 5px 5px 5px grey;
  border-radius: 15px;
}

#chatborder {
  border-style: solid;
  background-color: #f6f9f6;
  border-width: 3px;
  margin-top: 20px;
  margin-bottom: 20px;
  margin-left: 20px;
  margin-right: 20px;
  padding-top: 10px;
  padding-bottom: 15px;
  padding-right: 20px;
  padding-left: 15px;
  border-radius: 15px;
}

.chatlog {
  font: 15px arial, sans-serif;
}

#chatbox {
  font: 17px arial, sans-serif;
  //height: 22px;
  width: 100%;
}

h1 {
  margin: auto;
}

pre {
  background-color: #f0f0f0;
  margin-left: 20px;
}

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
</script>
<div id='bodybox'>
  <h1>Virtual Assistant</h1>
  <div id='chatborder' class="form-group">
    <p id="chatlog7" class="chatlog">&nbsp;</p>
    <p id="chatlog6" class="chatlog">&nbsp;</p>
    <p id="chatlog5" class="chatlog">&nbsp;</p>
    <p id="chatlog4" class="chatlog">&nbsp;</p>
    <p id="chatlog3" class="chatlog">&nbsp;</p>
    <p id="chatlog2" class="chatlog">&nbsp;</p>
    <p id="chatlog1" class="chatlog">&nbsp;</p>
    <input type="text" name="chat" id="chatbox" placeholder="Hi there! Type here to talk to me." onfocus="placeHolder()" class="form-control">
  </div>

and in the appi.php I have this code

$p = $_GET['lastUserMessage'];
//open connection to mysql db
$connection = mysqli_connect("localhost","username","password","dbname");

//fetch table rows from mysql db
$sql = "select lastUserMessage, botMessage FROM `table_name` WHERE lastUserMessage='$p'";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array

$emparray = $array;
while($row =mysqli_fetch_assoc($result))
{
    $emparray = $row;
}
echo json_encode($emparray);

//close the db connection
mysqli_close($connection);

Collectives™ on Stack Overflow

MySQL select result inside JS function

4 Answers 4

3 Comments

Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related