Autocomplete with Suggestion Input using PHP, Vue.js, and MYSQL Database Tutorial

Submitted by oretnom23 on Wednesday, November 23, 2022 - 17:06.

Introduction

In this tutorial, you will learn how to create an Autocomplete with suggestions input using PHP, Vue (Vue.js), and MySQL Database. The tutorial aims to provide IT/CS students or new programmers a reference for improving their web applications' client-side functionalities using Vue. Here, snippets are provided and a sample source code that demonstrates the main goal of this tutorial is also provided and is free to download.

What is Autocomplete with Suggestions Input?

In web applications, inputs with autocomplete with suggestions are commonly part of it to give the en-users a better experience while using the application and make it more efficient. This kind of input is mostly used for search boxes and input that queries options.

How to Autocomplete with Suggestions Input using Vue, PHP, and MySQL?

Autocomplete with Suggestions Input using Vue, PHP, and MySQL can be achieved in a few JavaScript and PHP lines of code. Like my past posted tutorial entitled (Live Search using PHP and Vue.JS Tutorial), suggestions data will be retrieved using the Fetch API and list it below the input.

Database

For this tutorial, let's assume that we have a database named dummy_db and have a MySQL Schema and table data using the snippet below.

CREATE TABLE `languages` (
    `id` int(30) NOT NULL,
    `name` varchar(250) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
    INSERT INTO `languages` (`id`, `name`) VALUES
    (1, 'PHP Programming'),
    (2, 'HTML Programming'),
    (3, 'CSS Programming'),
    (4, 'JavaScript Programming'),
    (5, 'Python Programming'),
    (6, 'C++ Programming'),
    (7, 'C# Programming'),
    (8, '.NET Programming'),
    (9, 'VB Programming'),
    (10, 'ASP.NET Programming'),
    (11, 'Java Programming');
 
    ALTER TABLE `languages`
    ADD PRIMARY KEY (`id`);
 
    ALTER TABLE `languages`
    MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

Database Connection

Create a PHP file in your source code folder and save it as db-connect.php. This file contains the PHP Script for connecting the application to the database. See the following snippet.

<?php
$host = "localhost";
$username = "root";
$pw = "";
$db_name = "dummy_db";
 
$conn = new mysqli($host, $username, $pw, $db_name);
 
if(!$conn){
    die("Database Connection Failed");
}<?php
$host = "localhost";
$username = "root";
$pw = "";
$db_name = "dummy_db";
 
$conn = new mysqli($host, $username, $pw, $db_name);
 
if(!$conn){
    die("Database Connection Failed");
}

Suggestions Query Script

Next, create another PHP File and save it as getLanguages.php. This file contains the PHP code that queries the suggested languages/data from the database table. This is a simple PHP API where the Fetch API will request the suggested data.

<?php 
require_once('db-connect.php');
/**
    * Search Language where name is like the search term
    */
$swhere = "";
 
if(isset($_POST['search']) && !empty($_POST['search'])){
    $swhere = " WHERE `name` LIKE '%{$_POST['search']}%' ";
}
$sql = "SELECT * FROM `languages` {$swhere} order by `name` asc";
$query = $conn->query($sql);
$languages = [] ;
if($query->num_rows > 0){
    $languages = array_column($query->fetch_all(MYSQLI_ASSOC), 'name');
}
 
echo json_encode($languages);
$conn->close();

Interface

Next, create the page interface where you will display your autocomplete with suggestions input. The snippet I provided below uses CDNs for loading some external libraries for the design and Vue. Make sure to be connected to the internet when using the snippet to run it properly.

index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Auto Complete | PHP & Vue.JS</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css" integrity="sha512-xh6O/CkQoPOWDdYTDqeRdPCVd1SpvCA9XXcUnZS2FmJNp1coAFzvtCN9BmamE+4aHK8yyUHUSCcJHgXloTyT2A==" crossorigin="anonymous" referrerpolicy="no-referrer" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/js/all.min.js" integrity="sha512-naukR7I+Nk6gp7p5TMA4ycgfxaZBJ7MO5iC3Fp6ySQyKFHOGfpkSZkYVWV5R7u7cfAicxanwYQ5D1e17EfJcMA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3" crossorigin="anonymous"></script>
    <script src="https://unpkg.com/vue@3/dist/vue.global.js"></script>
    <style>
        html, body{
            min-height:100%;
            width:100%;
        }
        tbody:empty:after{
            content:'No records found'
        }
        #suggestion-field>.position-absolute {
            height: 30vh;
            overflow: auto;
        }
    </style>
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient">
        <div class="container">
            <a class="navbar-brand" href="./">Auto Complete</a>
            <div>
                <a href="https://sourcecodester.com" class="text-light fw-bolder h6 text-decoration-none" target="_blank">SourceCodester</a>
            </div>
        </div>
    </nav>
    <div class="container-fluid px-5 my-3" id="SampleApp">
        <div class="col-lg-6 col-md-8 col-sm-12 mx-auto">
            <h3 class="text-center"><b>Auto Complete using PHP and Vue.js</b></h3>
            <div class="d-flex w-100 justify-content-center">
                <hr class="w-50">
            </div>
            <div class="card rounded-0 shadow mb-3">
                <div class="card-body">
                    <div class="container-fluid">
                        <div class="mb-3">
                            <div class="input-group flex-nowrap">
                                <input type="search" class="form-control rounded-0" placeholder="Search Prgramming Laguage" aria-label="Search" aria-describedby="addon-wrapping" v-model="search" @input="searchLanguages()">
                                <span class="input-group-text rounded-0" id="addon-wrapping"><i class="fa-solid fa-search"></i></span>
                            </div>
                            <div id="suggestion-field" class="position-relative w-100" v-show="suggestions">
                                <div class="position-absolute w-100 border">
                                    <div id="suggestion-lis" class="list-group">
                                        <a class="list-group-item list-group-item-action" href="#" v-for="language in languages" @click="getLanguage(language)">
                                            {{language}}
                                        </a>
                                        <div class="list-group-item text-center text-muted" v-if="languages.length <= 0"><i>No suggestion found</i></div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script src="app.js"></script>
</body>
</html>

JavaScript

Lastly, create a new file and save it as app.js. The file contains the JavaScript codes that initiate the autocomplete with suggestions input using VueJS. It contains the method of retrieving the suggestions and selecting the input data.

const { createApp } = Vue
 
/**
    * Create Vue App to the selected Element
    */
var app = createApp({
    /**
        * App Data
        */
    data() {
        return {
            languages: [],
            search:'',
            suggestions:false
        }
 
    },
    /**
        * App Methods
        */
    methods:{
        searchLanguages(){
            /**
                * Query Suggestions
                */
            if(this.search == ""){
                this.languages = []
                this.suggestions = false
            }else{
                var formData = new FormData();
                formData.append('search', this.search)
                fetch('getLanguages.php',{
                    method:'POST',
                    body: formData
                })
                .then(response => {
                    return response.json()
                })
                .then(data => {
                    this.languages = Object.values(data)
                    this.suggestions = true
                } )
            }
 
        },
        getLanguage(language){
            /**
                * Write selected suggestion to the search input
                */
            this.search = language
            this.languages = []
            this.suggestions = false
        }
    }
}).mount('#SampleApp')

That's it! the suggestion list will be triggered upon inputting in the provided text field. The selected suggestion will be automatically written on the input when clicking the item.

Snapshots

The snippets that I have provided above will result in the following snapshots.

Main Page Interface

Autocomplete with Suggestions Input

I have provided also the source code zip file that I created for this tutorial. You can download it by clicking the Download Button below this article. Feel free to download and modify it the way you wanted.

That's the end of this tutorial. I hope this Autocomplete with Suggestions Input Tutorial using PHP, VueJS, and MySQL will help you with what you are looking for and that you'll find this useful for your current and future projects.

Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding:)

Add new comment

616 views