How to retrieve data from MySQL database using PHP and display it using jQuery and Ajax

This is a quick summary of how to retrieve data from MySQL database using PHP and display it using jQuery and Ajax.

Step 1 - Prepare MySQL database and data table.

The SQL query below creates a database and data table and fills the table with sample data.

CREATE DATABASE IF NOT EXISTS question;
 
CREATE TABLE IF NOT EXISTS q_TABLE (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    position varchar(20) NOT NULL,
    salary varchar(20) NOT NULL,
    start_date varchar(20) NOT NULL,
    office varchar(20) NOT NULL,
    extn varchar(20) NOT NULL,    
    PRIMARY KEY (id)
);

-- Insert data into the newly created "q_TABLE"

INSERT INTO q_TABLE (name, position, salary, start_date, office, extn)
VALUES
        ('Tiger Nixon','System Architect','$320,800','2011/04/25','Edinburgh','5421'),
        ('Garrett Winters','Accountant','$170,750','2011/07/25','Tokyo','8422'),
        ('Ashton Cox','Junior Technical Author','$86,000','2009/01/12','San Francisco','1562'),
        ('Cedric Kelly','Senior Javascript Developer','$433,060','2012/03/29','Edinburgh','6224'),
        ('Airi Satou','Accountant','$162,700','2008/11/28','Tokyo','5407'),
        ('Brielle Williamson','Integration Specialist','$372,000','2012/12/02','New York','4804'),
        ('Herrod Chandler','Sales Assistant','$137,500','2012/08/06','San Francisco','9608'),
        ('Rhona Davidson','Integration Specialist','$327,900','2010/10/14','Tokyo','6200'),
        ('Colleen Hurst','Javascript Developer','$205,500','2009/09/15','San Francisco','2360'),
        ('Sonya Frost','Software Engineer','$103,600','2008/12/13','Edinburgh','1667'),
        ('Jena Gaines','Office Manager','$90,560','2008/12/19','London','3814'),
        ('Quinn Flynn','Support Lead','$342,000','2013/03/03','Edinburgh','9497'),
        ('Charde Marshall','Regional Director','$470,600','2008/10/16','San Francisco','6741'),
        ('Haley Kennedy','Senior Marketing Designer','$313,500','2012/12/18','London','3597'),
        ('Tatyana Fitzpatrick','Regional Director','$385,750','2010/03/17','London','1965'),
        ('Michael Silva','Marketing Designer','$198,500','2012/11/27','London','1581'),
        ('Paul Byrd','Chief Financial Officer (CFO)','$725,000','2010/06/09','New York','3059'),
        ('Gloria Little','Systems Administrator','$237,500','2009/04/10','New York','1721'),
        ('Bradley Greer','Software Engineer','$132,000','2012/10/13','London','2558'),
        ('Dai Rios','Personnel Lead','$217,500','2012/09/26','Edinburgh','2290'),
        ('Jenette Caldwell','Development Lead','$345,000','2011/09/03','New York','1937'),
        ('Yuri Berry','Chief Marketing Officer (CMO)','$675,000','2009/06/25','New York','6154'),
        ('Caesar Vance','Pre-Sales Support','$106,450','2011/12/12','New York','8330'),
        ('Doris Wilder','Sales Assistant','$85,600','2010/09/20','Sidney','3023'),
        ('Angelica Ramos','Chief Executive Officer (CEO)','$1,200,000','2009/10/09','London','5797'),
        ('Gavin Joyce','Developer','$92,575','2010/12/22','Edinburgh','8822'),
        ('Jennifer Chang','Regional Director','$357,650','2010/11/14','Singapore','9239'),
        ('Brenden Wagner','Software Engineer','$206,850','2011/06/07','San Francisco','1314'),
        ('Fiona Green','Chief Operating Officer (COO)','$850,000','2010/03/11','San Francisco','2947'),
        ('Shou Itou','Regional Marketing','$163,000','2011/08/14','Tokyo','8899'),
        ('Michelle House','Integration Specialist','$95,400','2011/06/02','Sidney','2769'),
        ('Suki Burks','Developer','$114,500','2009/10/22','London','6832'),
        ('Prescott Bartlett','Technical Author','$145,000','2011/05/07','London','3606'),
        ('Gavin Cortez','Team Leader','$235,500','2008/10/26','San Francisco','2860'),
        ('Martena Mccray','Post-Sales support','$324,050','2011/03/09','Edinburgh','8240'),
        ('Unity Butler','Marketing Designer','$85,675','2009/12/09','San Francisco','5384'),
        ('Howard Hatfield','Office Manager','$164,500','2008/12/16','San Francisco','7031'),
        ('Hope Fuentes','Secretary','$109,850','2010/02/12','San Francisco','6318'),
        ('Vivian Harrell','Financial Controller','$452,500','2009/02/14','San Francisco','9422'),
        ('Timothy Mooney','Office Manager','$136,200','2008/12/11','London','7580'),
        ('Jackson Bradshaw','Director','$645,750','2008/09/26','New York','1042'),
        ('Olivia Liang','Support Engineer','$234,500','2011/02/03','Singapore','2120'),
        ('Bruno Nash','Software Engineer','$163,500','2011/05/03','London','6222'),
        ('Sakura Yamamoto','Support Engineer','$139,575','2009/08/19','Tokyo','9383'),
        ('Thor Walton','Developer','$98,540','2013/08/11','New York','8327'),
        ('Finn Camacho','Support Engineer','$87,500','2009/07/07','San Francisco','2927'),
        ('Serge Baldwin','Data Coordinator','$138,575','2012/04/09','Singapore','8352'),
        ('Zenaida Frank','Software Engineer','$125,250','2010/01/04','New York','7439'),
        ('Zorita Serrano','Software Engineer','$115,000','2012/06/01','San Francisco','4389'),
        ('Jennifer Acosta','Junior Javascript Developer','$75,650','2013/02/01','Edinburgh','3431'),
        ('Cara Stevens','Sales Assistant','$145,600','2011/12/06','New York','3990'),
        ('Hermione Butler','Regional Director','$356,250','2011/03/21','London','1016'),
        ('Lael Greer','Systems Administrator','$103,500','2009/02/27','London','6733'),
        ('Jonas Alexander','Developer','$86,500','2010/07/14','San Francisco','8196'),
        ('Shad Decker','Regional Director','$183,000','2008/11/13','Edinburgh','6373'),
        ('Michael Bruce','Javascript Developer','$183,000','2011/06/27','Singapore','5384'),
        ('Donna Snider','Customer Support','$112,000','2011/01/25','New York','4226');

Step 2 - Prepare the PHP code that fetches data from the database.

data_fetch.php

<?php

require "includes/dbh.inc.php";

$query = "SELECT * FROM q_TABLE";

$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_array($result)){
    $data[] = $row;
}

echo json_encode($data);

?>

Below is the output result.

Step 3 - Prepare the HTML and the jQuery and the Ajax code that displays the fetched data in console.log.

Ref.:
Save php json object into jquery variables
https://stackoverflow.com/questions/36251127/save-php-json-object-into-jquery-variables

<!DOCTYPE html>
<html>
<head>
    <meta name="description" content="Add Select and input to html table">
    <script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>

    <link href="https://nightly.datatables.net/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
    <script src="https://nightly.datatables.net/js/jquery.dataTables.js"></script>

    <link href="https://cdn.datatables.net/select/1.2.1/css/select.dataTables.min.css" rel="stylesheet" type="text/css" />
    <script src="https://cdn.datatables.net/select/1.2.1/js/dataTables.select.min.js" type="text/javascript" ></script>
    
    <link href="https://cdn.datatables.net/buttons/1.2.4/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css"/>
    <script src="https://cdn.datatables.net/buttons/1.2.4/js/dataTables.buttons.min.js" type="text/javascript"></script>
    <script type="text/javascript" language="javascript" src="//cdn.datatables.net/buttons/1.3.1/js/buttons.print.min.js"></script>       
        
    <script type="text/javascript"  src="//cdn.datatables.net/plug-ins/1.10.15/sorting/custom-data-source/dom-text.js"></script>

 <meta charset=utf-8 />
 <title>A Simple Test</title>
</head>

</html>

<script type="text/javascript" language="javascript">

// ref.: 
// https://stackoverflow.com/questions/35402370/send-php-array-to-jquery-ajax-and-make-a-each-loop-from-that-array
// https://stackoverflow.com/questions/21488145/why-isnt-this-very-simple-jquery-working

$(document).ready(function() {
    //console.log('I am in!');
    //alert('Hi.');

    $.ajax({
        url: "data_fetch.php",
        dataType: 'json',
        cache: false,
        success: function(response)
        {
             for (var i = 0; i < response.length; i++)
             {
                 //console.log(i, response[i].name);
                 console.log(response[i].name);
             }
        }
    });
  });

</script>

The Result