RestFul Web Services with PHP Mysqli. In my last post about web service i explain how to create web service using PHP and test your service using REST Client, but MySQL is deprecated in current version so make update and show how to create your web service in MySQli. Before start please read my last post about service to get idea how to user Advance REST client.
Build your first web service with PHP, JSON and MySql
Before start i hope you have good command on oops concept. Now lets get started.
In this application i show you how to perform CRUD operation on database using PHP MySQLI.
Step 1. Create DB in your phpmyadmin. Now paste this code in query box.
|
|
CREATE TABLE IF NOT EXISTS `user` (
`id` int(10) NOT NULL,
`name` text NOT NULL,
`email` text NOT NULL,
`status` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;
|
Step 2. Now create connection file to connect with database
|
|
<?php
$connection = new mysqli("localhost", "root", "", "service") or die(mysqli_error());
?>
|
Now once your connection with database is created we are ready to perform CRUD operation.
Now in this post i explain you two different method.
1. SEND DATA IN NORMAL FORMAT LIKE $_POST[‘FNAME’] = ‘ANEH’; $_POST[‘LNAME’] = ‘THAKUR’;.
2. SEND DATA IN JSON OBJECT LIKE $_POST[‘KEYS’] = “{‘FNAME’:’ANEH’, ‘LNAME’:’THAKUR’}”;
Step 3. Send data in simple format.
3.1 Insert data to database. First include connection file in your insert.php or copy/paste below code.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<?php
include 'conn.php';
$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];
$sql = "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name', '$email', '$status');";
if ($connection->query($sql)) {
$msg = array("status" =>1 , "msg" => "Your record inserted successfully");
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($connection);
}
$json = $msg;
header('content-type: application/json');
echo json_encode($json);
@mysqli_close($conn);
?>
|
3.2. Retrieve data from database and send data in json webservice Create select.php and call this file from REST Client.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
<?php
include 'conn.php';
//Select data from database
$getData = "select * from user";
$qur = $connection->query($getData);
while($r = mysqli_fetch_assoc($qur)){
$msg[] = array("name" => $r['name'], "email" => $r['email'], "status" => $r['status']);
}
$json = $msg;
header('content-type: application/json');
echo json_encode($json);
@mysqli_close($conn);
?>
|
After click on submit button get you get all data from database in JSON format
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
|
[
{
"name": "Jatin",
"email": "jatin@trirnity.com",
"status": "active"
},
{
"name": "Aneh",
"email": "aneh@zapponomics.com",
"status": "active"
},
{
"name": "Anil",
"email": "Anil@trirnity.com",
"status": "active"
},
{
"name": "Shubahm",
"email": "shubham@trirnity.com",
"status": "active"
},
{
"name": "Pankaj",
"email": "pankaj@trirnity.com",
"status": "active"
},
{
"name": "aneh",
"email": "aneh@trinitytuts.com",
"status": "active"
}
]
|
3.3. Now create new file to perform delete operation. I named my file delete.php and paste bellow code.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<?php
include 'conn.php';
//Delete record from database
$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];
$query = "DELETE FROM `user` WHERE `name`='$name' && email = '$email'";
if ($connection->query($query)) {
$msg = array("status" =>1 , "msg" => "Record Deleted successfully");
} else {
echo "Error: " . $query . "<br>" . mysqli_error($connection);
}
$json = $msg;
header('content-type: application/json');
echo json_encode($json);
@mysqli_close($conn);
|
Now add delete link to your rest client and pass parameter to perform delete operation.
3.4. Create new file to perform updae operation. I named my file update.php and paste bellow code to update data and get response.
update.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
<?php
include 'conn.php';
//Update record in database
$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];
$id = $_POST['id'];
$query = "UPDATE `user` SET `name`='$name' ,`email`='$email' WHERE `id`='$id'";
if ($connection->query($query)) {
$msg = array("status" =>1 , "msg" => "Record Updated successfully");
}else {
echo "Error: " . $query . "<br>" . mysqli_error($connention);
}
$json = $msg;
header('content-type: application/json');
echo json_encode($json);
@mysqli_close($conn);
|
3.5. A very new and good feature of MySQLI is to perform MultiQuery operation which improve speed and save no of line of code.create file multiquery.php and paste below code to perform operation.
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
|
<?php
include 'conn.php';
//Insert Multyple record into database
$name = $_POST['name'];
$email = $_POST['email'];
$status = $_POST['status'];
$name1 = $_POST['name1'];
$email1 = $_POST['email1'];
$status1 = $_POST['status1'];
$name2 = $_POST['name2'];
$email2 = $_POST['email2'];
$status2 = $_POST['status2'];
$query = "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name', '$email', '$status');";
$query .= "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name1', '$email1', '$status1');";
$query .= "INSERT INTO `service`.`user` (`id`, `name`, `email`, `status`) VALUES (NULL, '$name2', '$email2', '$status2');";
if ($connection->multi_query($query)) {
$msg = array("status" =>1 , "msg" => "All Records Inserted successfully");
} else {
echo "Error: " . $query . "<br>" . mysqli_error($connention);
}
$json = $msg;
header('content-type: application/json');
echo json_encode($json);
@mysqli_close($conn);
|
Step 4. Now send data in JSONFormat and perform CURD operation. This is very simple and easy to use the only thing you need to know about JSON.
JSON, OR JAVASCRIPT OBJECT NOTATION, IS A MINIMAL, READABLE FORMAT FOR STRUCTURING DATA. IT IS USED PRIMARILY TO TRANSMIT DATA BETWEEN A SERVER AND WEB APPLICATION, AS AN ALTERNATIVE TO XML.
Now when you need to send data to server create a post variable and add your json data in it.
|
|
{
"name": "Jatin",
"email": "jatin@trirnity.com",
"status": "active"
}
|
Now we bind above json data in $_POST variable and after you send your data to server you need to decode json data also we need stripslashes() to remove slashes from request.
|
|
$get = json_decode(stripcslashes($_POST['r']));
$name = $get->name;
$email = $get->email;
$status = $get->status;
|
Once you read data from json request save data in variable and remaining part are same.
Hope this will help you in creating web service :-).
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment