Autocomplete MySql PHP

Autocomplete MySql PHP

aungkoheinaungkohein Posts: 38Questions: 5Answers: 0
edited February 2019 in Free community support

Hi!

Could you provide me with the syntax for loading autocomplete suggestion from MySQL table? Here is my example code and I could not understand how to convert it:

// The below is using local data variables. I do not want to hard code here but load from database.
var editor;

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.cars.php',
        table: '#cargoes',
        fields: [
            {
                "label": "Date:",
                "name": "date",
                "type": "datetime",
                "format": "DD\/MM\/YY"
            },
            {
                "label": "Status:",
                "name": "status",
                "type": "autoComplete",
                "opts": {
                    "source": 
                            [
                                "Open",
                                "Home",
                                "Parked",
                                
                            ]
                }
            },

Answers

  • aungkoheinaungkohein Posts: 38Questions: 5Answers: 0
    edited February 2019

    Found the Solution!

    Follow this video: https://www.youtube.com/watch?v=UBs6wKVSCmU

    Step 1: Create a data.php

    <?php
        //connection
        $dbhost = 'localhost';
        $dbname = 'example';
        $dbuser = 'root';
        $dbpass = '';
    
        try{
    
            $dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
            $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
        }catch(PDOException $ex){
            
            die($ex->getMessage());
            
        }
        $stmt=$dbcon->prepare('SELECT example_name FROM example');
        $stmt->execute();
        $array = array();
        while($row=$stmt->fetch(PDO::FETCH_ASSOC))
        {
            extract($row);
            $array[]= $example;
        }
        echo json_encode($array);
    
    ?>
    

    Step 2: Then change your table.example.js

    var editor;
    
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
            ajax: 'php/table.example.php',
            table: '#example',
            fields: [
                {
                    "label": "Status:",
                    "name": "status",
                    "type": "autoComplete",
                    "opts": {
                                           "source": "autocomplete/data.php" //redirect to your PHP file
                    }
                },
    
  • aungkoheinaungkohein Posts: 38Questions: 5Answers: 0
    edited February 2019

    Hi @allan / @colin,

    The above codes does not work!

    Case: I would like to retrieve the values of Autocomplete from the database(MySQL) using PHP.

    Iinline Editor jQuery UI Autocomplete

    data.php -- This gives me json format "

    ["qqq","qqq","qqq","qqq","555","TESTTESTTEST","5+4654"] "

    <?php 
    
    //connection 
    
    $dbhost = 'localhost'; 
    $dbname = 'cfd'; 
    $dbuser = 'root'; 
    $dbpass = 'root'; 
    
    try{ $dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass); 
        
    $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); }catch(PDOException $ex)
    { die($ex->getMessage()); } 
    
    $stmt=$dbcon->prepare('SELECT item_name FROM items'); 
    $stmt->execute(); 
    $array = array(); 
    
    while($row=$stmt->fetch(PDO::FETCH_ASSOC)) { 
        
        extract($row); 
        $array[]= $row['item_name'];
        
    } echo json_encode($array); 
    
    ?>
    

    table.items.js

                {
                    "label": "Item:",
                    "name": "item",
                    "type": "autoComplete",
                    "opts": {
                        source: function( request, response ) {
                            $.ajax({
                                dataType: "json",
                                type: 'POST',
                                url: '../autocomplete_data/data.php',
                                data: {
                                    term: request.term
                                },
                                success: function( data ) {
                                    response( data );
                                }
                            } );
                        } 
                    }
    
                },
    

    Result: No AutoComplete suggestion shown in the Editor cell.

    Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Can you link to your page please? Have you included the jQuery UI AutoComplete plug-in for Editor?

    From the jQuery UI documentation that should be all you need I think.

    Allan

This discussion has been closed.