Objective: To develop a one-page application that integrates jQuery, AJAX, PHP API, and MySQL. The application allows adding a new student record and reloading all records without reloading the page.
Expected outcome (example screenshot):
Key features:
Click on the “Add Record” button: to add a new record to students table in the MySQL database and shows a status message about the number of recorded successfully inserted;
Click on the “Reload All Student Records” button: to query the database and update the page with all existing records in the students table;
Again, this is one-page application, meaning you stay on the page without reloading and all client/server communication is through AJAX behind the scene.
A. MySQL database tasks
From the previous assignment, you should have had a students table with id, name, program_id, and start_year field. If you have a few student records in the table, you should be good to go.
B. PHP (server API) tasks
PHP debugging hints: Code your PHP in small iterations. Every time you’ve added some code, please save, upload and test it with your browser. Add the following code at the top of your PHP code to help you debug:
error_reporting(E_ALL);
ini_set('display_errors', 'On');
If your program does not work as expected, add echo statements in your PHP code to help trace where the error/problem is. Add echo statements earlier (near the top of page) if nothing shows up on browser.
1. Create a “lab4” folder in your project. All files in the following steps should be created in the lab3 folder.
2. Create a addStudent.php file in lab4/ and add related code to do the following.
a. Add the following database connection code:
a. Accepts GET parameters: $_GET['name'], $_GET['program_id'], $_GET['start_year']
b. Assign the GET parameters to $name, $program_id, $start_year
c. Call $db->exec() method to execute the following SQL statement:
$sql = "insert into students (name,program_id,start_year) values ('$name',$program_id,$year)";
e. Return the above function to $n, which is the number of successfully inserted record;
f. At the end:
header("Content-Type: text/plain");
echo $n;
g. Upload your page and test it from your browser with:
(If it shows a number such as 1, mostly likely it works well.)
Hint: Compare your code to addProduct.php code in AdvExample_AJAX.pptx
3. Create a getAllStudent.php page with the following code (3 of 6 points):
a. Add the same database connection code (as in addProduct.php);
b. Call $db->query() method to run the following query:
$sql = "select id, name, program_id, start_year from students";
c. Return the above call (result) to $data;
d. At the end:
header("Content-Type: application/json");
echo json_encode($data->fetchAll(PDO::FETCH_ASSOC));
e. Upload page and test it from browser:
If you see content like the following, you are good to go:
C. HTML, CSS, and jQuery/JS (client-side script) tasks
1. Create a onepage.html page with the following elements:
a. In the head section, link to a css file, jQuery, and a js file:
<link rel="stylesheet" type="text/css" href="css/main.css" />
<script src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script src="js/main.js"></script>
b. Create three input elements:
i. An input with id="name";
ii. A select element with id="program_id" and three program options;
iii. Another input with id="start_year";
c. Create a button with id="btnAdd" to add student record;
d. Create a <span id="status"></span> to show status message
e. Create a button with id="btnReload" to reload student records from database;
f. Create a ul element (unordered list) with id="records"
2. Create a main.css in the css/ subdirectory and add sufficient CSS code.
3. Create a main.js in the js/ subdirectory and add the following JS code
a. Here is a the basic structure of your js code:
$(document).ready(function() {
$("#btnAdd").click(function() {
// TODO when the Add Record button is clicked
});
$("#btnReload").click(function(){
// TODO when the Reload button is clicked
});
});
b. In the $("#btnAdd") click function part, add code to:
i. Get values from the inputs by calling $("#name").val(), $("#program_id").val(), $("#start_year").val();
ii. Assign the values to name, program_id, and start_year variables respectively;
iii. Use $.get() method to send a GET request to the following URL:
"addStudent.php?name=" + name + "&program_id="+program_id + "&start_year="+start_year
iv. In the callback function, show status message by:
$("#status").text(data + " record(s), " + name + ", has been added to database.");
c. In the $("btnReload") click function part, add code to:
i. Clear existing records in the list by:
$("#records").empty();
ii. Use $.get() method to send a GET request to "getAllStudents.php";
iii. In the callback, loop over the returned JSON data by:
$.each(data, function(index, record){
$("#records").append("<li>" + record['id'] + ": "
+ record['name'] + ", " + record['program_id']
+ ", " + record['start_year'] + "</li>");
E. Bonus
In onepage.html, change the hardcoded select options (drop down menu) for program_id to load records from a programs table in MySQL
We are also providing other PHP related help, if you need any other PHP project help then share your requirement details at:
And get instant help with our dedicated professionals and expert.