Build ChatBot with PHP, MySQL and AJAX

In our previous tutorial, we have explained how to How To Use ChatGPT with PHP. In this tutorial, we will explain How To Make Chatbot with PHP, MySQL and AJAX.

Have you ever interacted with CahtBot? Yes, definitly while making queries to customer support in ECommerce websites, web hosting services etc.

A ChatBot is a software application, used to caried out human-like online conversation with users. The ChatBots are mostly used with customer support system or enquiry systems to make initial level conversation with its customers.

So here in this tutorial, we are going to build a ChatBot that provides real-time response to some common questions.

So let’s proceed to make a ChatBot with PHP, MySQL and AJAX.

Create MySQL Database Tables

We will create following MySQL tables for our ChatBot.

We weill create chabtbot_questions table to store questions details.

CREATE TABLE `chabtbot_questions` (
  `id` int(30) NOT NULL,
  `question` text DEFAULT NULL,
  `response_id` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `chabtbot_questions`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

We weill create chabtbot_responses table to store responses details.

CREATE TABLE `chabtbot_responses` (
  `id` int(30) NOT NULL,
  `response_message` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `chabtbot_responses`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

We weill create frequent_asks table to store frequently asked questions details.

CREATE TABLE `chabtbot_frequent_asks` (
  `id` int(30) NOT NULL,
  `question_id` int(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `chabtbot_frequent_asks`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=90;

We weill create chabtbot_unanswered table to store unaswered questions details.

CREATE TABLE `chabtbot_unanswered` (
  `id` int(30) NOT NULL,
  `question` text DEFAULT NULL,
  `no_asks` int(30) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `chabtbot_unanswered`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

Create ChatBot Page

We will create chatbot.php file and create html to design chatbot page with textraea and and for listing conversation.

<div class="container-fluid">
	<div class="">
		<div class="">
			<div class="card direct-chat direct-chat-primary" id="chat_convo">
              <div class="card-header ui-sortable-handle" style="cursor: move;">
                <h3 class="card-title">You Can Ask Me</h3>
                <div class="card-tools">
                  <button type="button" class="btn btn-tool" data-card-widget="collapse">
                    <i class="fas fa-minus"></i>
                  </button>
                </div>
              </div>              
              <div class="card-body">                
                <div class="direct-chat-messages">                  
                  <div class="direct-chat-msg mr-4">
                    <img class="direct-chat-img border-1 border-primary" src="http://localhost/chatbot/dist/img/no-image-available.png" alt="message user image">                    
                    <div class="direct-chat-text">
                      <?php echo $_settings->info('intro') ?>
                    </div>                    
                  </div>                      
                </div>
                <div class="end-convo"></div>                
              </div>              
              <div class="card-footer">
                <form id="send_chat" method="post">
                  <div class="input-group">
                    <textarea type="text" name="message" placeholder="Type Message ..." class="form-control" required=""></textarea>
                    <span class="input-group-append">
                      <button type="submit" class="btn btn-primary">Send</button>
                    </span>
                  </div>
                </form>
              </div>              
            </div>
		</div>
	</div>
</div>

Handle ChatBot Conversation

In chatbot.js file, we will handle conversation between ChatBot and user. We will handle form submit to send message via Ajax request to action.php and display responses.

$('#chatbotForm').submit(function(e){
	e.preventDefault();
	var message = $('[name="message"]').val();
	if(message == '' || message == null) return false;
	var uchat = $('#user_chat').clone();
	uchat.find('.direct-chat-text').html(message);
	$('#chat_convo .direct-chat-messages').append(uchat.html());
	$('[name="message"]').val('')
	$("#chat_convo .card-body").animate({ scrollTop: $("#chat_convo .card-body").prop('scrollHeight') }, "fast");

	$.ajax({
		url:_base_url_+"action.php?f=get_response",
		method:'POST',
		data:{message:message},
		error: err=>{
			console.log(err)
			alert_toast("An error occured.",'error');
			end_loader();
		},
		success:function(resp){
			if(resp){
				resp = JSON.parse(resp)
				if(resp.status == 'success'){
					var bot_chat = $('#bot_chat').clone();
					bot_chat.find('.direct-chat-text').html(resp.message);
					$('#chat_convo .direct-chat-messages').append(bot_chat.html());
					$("#chat_convo .card-body").animate({ scrollTop: $("#chat_convo .card-body").prop('scrollHeight') }, "fast");
				}
			}
		}
	})
});

in action.php file , we will check for action get_conversation and call method getConversation() to get conversation response data.

require_once('classes/ChatBot.php');
$chatbot = new ChatBot();
$action = !isset($_GET['f']) ? 'none' : strtolower($_GET['f']);
switch ($action) {	
	case 'get_conversation':
		echo $chatbot->getConversation();
	break;
}

We will imlement method getConversation() to get conversation response data from database according to user query and return as json data.

public function getConversation(){
	extract($_POST);
	$message = str_replace(array("?"), '', $message);
	$questions = array("what", "what is","who","who is", "where");
	if(in_array($message,$questions)){
		$response['status'] = "success";
		$response['message'] = $this->settings->info('no_result');
		return json_encode($response);
		exit;
	}
	$sql = "SELECT r.response_message,q.id from `chabtbot_questions` q inner join `chabtbot_responses` r on q.response_id = r.id where q.question Like '%{$message}%' ";
	$qry = $this->conn->query($sql);
	if($qry->num_rows > 0){
		$result = $qry->fetch_array();			
		$response['status'] = "success";
		$response['message'] = $result['response_message'];
		$response['sql'] = $sql;
		$this->conn->query("INSERT INTO `chabtbot_frequent_asks` set question_id = '{$result['id']}' ");
		return json_encode($response);
	}else{
		$response['status'] = "success";
		$response['message'] = $this->settings->info('no_result');
		$answered = $this->conn->query("SELECT * FROM `chabtbot_unanswered` where `question` = '{$message}' ");
		if($answered->num_rows > 0){
			$this->conn->query("UPDATE `chabtbot_unanswered` set no_asks = no_asks + 1 ");
		}else{
			$this->conn->query("INSERT INTO `chabtbot_unanswered` set question = '{$message}' ");
		}
		return json_encode($response);
	}
}	

This is a fron-end of our ChatBot handling conversation with between user and Chatbot. We will soo update this tutorial with Admin section to manage questions and responses for our ChatBot system.

Demo Download