PHP web Service Restfull with Slim MySQL
From w3cyberlearnings
Contents |
Table and Record
CREATE TABLE score ( id INT NOT NULL AUTO_INCREMENT, grade_c CHAR(1) NOT NULL, score double NOT NULL, PRIMARY KEY(id) );
Insert Sample Record
INSERT INTO score (grade_c,score)
VALUES('A',99),
('B',88),
('C',77),
('A',98),
('A',98),
('A',93),
('B',83),
('C',78);
Resttest.php
<?php
require 'Slim/Slim.php';
$app = new Slim();
$app->get('/score/getScores', 'getScores');
$app->get('/score/getScore/:id', 'getScore');
$app->post('/score/addScore', 'addScore');
$app->get('/score/search/:grade', 'searchByGrade');
$app->delete('/score/delete/:id', 'deleteScore');
$app->post('/score/update', 'updateScore');
$app->run();
//function to connect to MySQL using PDO
function conn() {
$db_conn = new PDO(
'mysql:host=localhost;dbname=studentscore',
'root',
'caojiang');
return $db_conn;
}
function getScores() {
$db_conn = conn();
$sql = 'SELECT * FROM score ORDER BY grade_c';
$record = $db_conn->prepare($sql);
$row = $record->execute();
$rs = $record->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rs);
}
function getScore($id) {
$db_conn = conn();
$sql = 'SELECT * FROM score WHERE id=:id ORDER BY grade_c';
$record = $db_conn->prepare($sql);
$row = $record->execute(array('id' => $id));
$rs = $record->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($rs);
}
function addScore() {
$db_conn = conn();
$return_a = array();
$app_r = Slim::getInstance()->request();
$grade = $app_r->post('grade');
$score = $app_r->post('score');
$sql_st = "INSERT INTO score(grade_c, score) VALUES(?,?)";
$sql = $db_conn->prepare($sql_st);
if ($sql->execute(array($grade, $score))) {
$return_a = array('id' => $db_conn->lastInsertId());
} else {
$return_a = array('add' => 'failed');
}
echo json_encode($return_a);
}
function updateScore() {
$db_conn = conn();
$reqt = Slim::getInstance()->request();
$id = $reqt->post('id');
$score = $reqt->post('score');
$grade = $reqt->post('grade');
$sql_st = 'Update score set grade_c=:grade_c, score=:score WHERE id=:id';
$update_array = array('id' => $id, 'grade_c' => $grade, 'score' => $score);
$sql = $db_conn->prepare($sql_st);
if ($sql->execute($update_array)) {
echo json_encode($update_array);
}
}
function deleteScore($id) {
$db_conn = conn();
$sql_st = 'DELETE FROM score WHERE id=?';
$sql = $db_conn->prepare($sql_st);
if ($sql->execute(array($id))) {
$aa = array('delete' => 'success');
echo json_encode($aa);
}
}
function searchByGrade($grade) {
$db_conn = conn();
$sql = 'SELECT * FROM score WHERE grade_c LIKE :grade ORDER BY id';
$record = $db_conn->prepare($sql);
$aa_search = array(':grade' => "%{$grade}%");
$row = $record->execute($aa_search);
if (!$row) {
$rs = array('error' => 'connection');
} else {
$rs = $record->fetchAll(PDO::FETCH_ASSOC);
}
echo json_encode($rs);
}
?>
Test
root@ubuntu:~# curl -i -X GET http://localhost/Resttest.php/score/getScores root@ubuntu:~# curl -i -X GET http://localhost/Resttest.php/score/getScore/4 root@ubuntu:~# curl -i -X GET http://localhost/Resttest.php/score/search/a root@ubuntu:~# curl -i -X DELETE http://localhost/Resttest.php/score/delete/4 root@ubuntu:~# curl -i -X POST -d "score=40&grade=d" http://localhost/Resttest.php/score/addScore root@ubuntu:~# curl -i -X POST -d "score=50&grade=c&id=50" http://localhost/Resttest.php/score/update
Add New Score Using HTML form
- Create A HTML FORM
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Add Score</title>
</head>
<body>
<form name="frmAdd" method="post" action="http://location/Resttest.php/score/addScore">
Score:
<input typ="number" name="score" id="score"/><br/>
Grade:
<select name="grade" id="grade">
<option value="a">A</option>
<option value="b">B</option>
<option value="c">C</option>
</select><br/>
<input type="submit" value="Add"/>
</form>
</body>
</html>
List all score using jQuery
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Add Score</title>
<script type="text/javascript"
src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js">
</script>
<script type="text/javascript">
$(document).ready(function(){
var rootURL = "http://localhost/Resttest.php/score/getScores";
$.getJSON(rootURL,{},function(data){
var tbl='<table>';
tbl+='<tr><td>Grade</td><td>Score</td></tr>';
$.each(data,function(i,item){
tbl +='<tr><td><i style="text-transform:uppercase">'+
data[i].grade_c +
'</i></td></tr>';
});
tbl +='</table>';
$('div#score_list').html(tbl);
});
})
</script>
</head>
<body>
<div id="score_list"></div>
</body>
</html>
