How to populate a html form with PHP MySQL and JQuery

We need to populate a form with data from back end database given a specific user’s id.

User Story

  • As a user I will get a HTML form.
  • As a user I will also be able to type in a User ID on the form
  • As a user I will click on a submit button.
  • As a user I will be able to view all form data get populated based on user id I provided.

Sandbox

  • OS : Windows 7
  • Code Editor : Notepad++
  • Development Environment : XAMPP
  • RDBMS : MySQL
  • Server Side : PHP
  • Front : HTML
  • Javascript Library : JQuery
  • Web Server : Apache


Discussion & Steps

We will use HTML, JQuery, PHP, and MySQL to solve this problem. Let us take bottom up approach. We will first build our database, then middleware and then the front end.

Prepare MySQL Database with Test Data Tables

Connect to MySQL command prompt

1
C:\xampp\mysql\bin>mysql --user=root mysql

Create a User named ‘usertutor’

1
2
3
4
mysql> create user 'usertutor'@'localhost' identified by 'mypassword';
mysql> grant all privileges on *.* to 'usertutor'@'localhost' with grant option;
mysql> create user 'usertutor'@'%' identified by 'mypassword';
mysql> grant all privileges on *.* to 'usertutor'@'%' with grant option;

Quit mysql command prompt

Connect back to “test” database schema using user ‘usertutor’

1
C:\servers\xampp\mysql\bin>mysql --user=usertutor --password=mypassword test

Create a ‘users’ table with ‘id’, ‘lname’ and ‘fname’ field

mysql>create table users (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname varchar(60), lastname varchar(60), email varchar(60), telephone varchar(60));

Populate ‘persons’ table with the following data.

1
2
3
4
5
insert into users (id,firstname,lastname,email,telephone ) values (null,"John","Doe","jdoe@yahoo.com","512-334-5567");
insert into users (id,firstname,lastname,email,telephone ) values (null,"Mo","Joseph","moe@gmail.com","645-908-9090");
insert into users (id,firstname,lastname,email,telephone ) values (null,"Michael","Johnson","michael.jon@mjon.com","718-554-5507");
insert into users (id,firstname,lastname,email,telephone ) values (null,"Maria","Mamma","mariam@yahoo.com","809-100-5533");
insert into users (id,firstname,lastname,email,telephone ) values (null,"Idrisi","Midrisi","i.mid@agni.com","214-1908-2309");

We will use the USERS table. The table will contain data as shown below

1
2
3
4
5
6
7
8
9
+----+-----------+----------+----------------------+---------------+
| id | firstname | lastname | email                | telephone     |
+----+-----------+----------+----------------------+---------------+
|  1 | John      | Doe      | jdoe@yahoo.com       | 512-334-5567  |
|  3 | Michael   | Johnson  | michael.jon@mjon.com | 718-554-5507  |
|  4 | Maria     | Mamma    | mariam@yahoo.com     | 809-100-5533  |
|  5 | Idrisi    | Midrisi  | i.mid@agni.com       | 214-1908-2309 |
|  6 | Mo        | Joseph   | moe@gmail.com        | 645-908-9090  |
+----+-----------+----------+----------------------+---------------+

Solution 1: Single PHP File

Create a single PHP file to do it all.

1
put single page php here??????????????????

Solution 2: Use HTML, PHP, JQuery, AJAX and JSON

In this option we will use a decoupled design. HTML page will act as a front end. The html front end will invoke a server side PHP using AJAX. Data transfer between front end HTML and backend PHP will be done using JSON. Prepare the middleware with PHP. This middleware script will get invoked by a HTTP call. PHP will parse the call and extract out User ID. Extracted User Id will be used to query MySQL USERS table. Result coming back from Users table will be return back to callee HTML as JSON encoded data.

PHP to get MySQL Data

Write a PHP to make a query to MySQL ‘users’ table. The query query will fetch data and encode it in JSON and send the data back to a HTML front end.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!--?php 
    $db_host = "localhost";
    $db_user = "&lt; user id &gt;";
    $db_pass = "&lt; password &gt;";
    $db_name = "test";
    $mysqli = new MySQLi($db_host, $db_user, $db_pass, $db_name);
    $myQuery = "SELECT * FROM users";
    $result = $mysqli-&gt;query($myQuery) or die($mysqli-&gt;error);
    $data = array();
 
    while ( $row = $result-&gt;fetch_assoc() ){
        $data[] = ($row);
    }
 
    echo json_encode( $data );
?-->
HTML Front End

We will create a front end using HTML and JQuery. There will be two buttons that use JQuery .ajax to fetch json encoded data from backend.php and populate a few drop down list built using html select tag.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<script src="jquery-19.1.min.js"></script><script>// <![CDATA[
$(document).ready(function(){
 
    $("#buttonFirstNameLastName").click(function(){
        $.ajax({url:"backend.php",success:function(result){
            var obj1 = $.parseJSON(result);
 
            //Populate first name drop down
            var options = '';
            for (var i = 0; i < obj1.length; i++) {
                options += '
<option value="' + obj1[i].id + '">' + obj1[i].firstname + '</option>';
            }
            $("#firstNameList").html(options);
 
            //Populate last name drop down
            var options = '';
            for (var i = 0; i < obj1.length; i++) {
                options += '
<option value="' + obj1[i].id + '">' + obj1[i].lastname + '</option>';
            }
            $("#lastNameList").html(options);
        }});
    });
 
    $("#buttonEmailTelephone").click(function(){
        $.ajax({url:"backend.php",success:function(result){
            var obj2 = $.parseJSON(result);
 
            //Populate first name drop down
            var options = '';
            for (var i = 0; i < obj2.length; i++) {
                options += '
<option value="' + obj2[i].id + '">' + obj2[i].email + '</option>';
            }
            $("#emailList").html(options);
 
            //Populate last name drop down
            var options = '';
            for (var i = 0; i < obj2.length; i++) {
                options += '
<option value="' + obj2[i].id + '">' + obj2[i].telephone + '</option>';
            }
            $("#phoneList").html(options);
        }});
    });
 
});
// ]]></script>

Populate First Name and Last Name from BACKEND.php w/ JSON data

Drop down list with First Name

Populate eMail and Telephone from BACKEND.php w/ JSON data

Drop down list with First Name

6 Comments

  1. M Hakeem January 7, 2014 Reply
  2. Chris February 11, 2014 Reply
  3. tom June 17, 2014 Reply
  4. Scott March 25, 2015 Reply

Add a Comment

Your email address will not be published. Required fields are marked *