{"id":588,"date":"2014-03-11T18:36:36","date_gmt":"2014-03-11T18:36:36","guid":{"rendered":"http:\/\/www.phpgang.com\/?p=588---9840ae01-dcc4-47d3-963e-70b96a9d7e13"},"modified":"2017-01-20T06:49:16","modified_gmt":"2017-01-20T06:49:16","slug":"how-to-create-restful-api-webservice-with-slim-php-and-mysql","status":"publish","type":"post","link":"https:\/\/www.phpgang.com\/how-to-create-restful-api-webservice-with-slim-php-and-mysql_588.html","title":{"rendered":"Simple PHP REST API with Slim, PHP & MySQL"},"content":{"rendered":"

I have received many requests<\/a> from readers and after lots of searching I found out?a light weight frame work to create PHP?RESTful API?Tutorial<\/strong>. There are a number of frame?works available and the one I chose is SLIM<\/a> (it is a micro framework that helps?you efficiently write powerful web services). This tutorial gives you complete?examples of creating full Restful API using multiple HTTP methods like GET<\/strong>, POST<\/strong>,?PUT<\/strong> and DELETE<\/strong>. You would get the output in JSON<\/a> and create a user data for all?options, download code available<\/strong>.<\/p>\n

\"How<\/p>\n

\n
Download the full code package
<\/div>\n
[purchase_link id=”1131″ style=”button” color=”blue” text=”Purchase” direct=”true”]<\/div>\n
DEMO<\/a><\/div>\n<\/div>\n
DEMO<\/a><\/div>\n

HTTP methods:<\/strong><\/p>\n

GET<\/strong>: Used to retrieve and search data.<\/p>\n

POST<\/strong>: Used to insert data.<\/p>\n

PUT<\/strong>: Used to update data.<\/p>\n

DELETE<\/strong>: Used to delete data.<\/p>\n

Add below .htaccess file in your api folder http:\/\/localhost\/api<\/em><\/p>\n

RewriteEngine On\r\n\r\nRewriteBase \/api # if hosting api files on root use only \/\r\nRewriteCond %{REQUEST_FILENAME} !-f\r\nRewriteRule ^(.*)$ index.php [QSA,L]<\/pre>\n
Database design and table:<\/strong>
\ndatabase name => phpgang
\ntable name =>?restAPI
\ncolumn names => id, name, email, ip, date
\ndb.sql<\/strong>
\nDatabase file run in your MySQL to create database and add data in table.<\/p>\n
-- \r\n-- Table structure for table `restAPI`\r\n-- \r\n\r\nCREATE TABLE `restAPI` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `name` varchar(240) NOT NULL,\r\n  `email` varchar(240) NOT NULL,\r\n  `password` varchar(240) NOT NULL,\r\n  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  `ip` varchar(20) NOT NULL,\r\n  PRIMARY KEY (`id`)\r\n);<\/pre>\n
Database configuration<\/strong><\/p>\n
Edit database name, user and password as per your configuration<\/p>\n
function getConnection() {\r\n    try {\r\n        $db_username = \"DATABASE_NAME\";\r\n        $db_password = \"********\";\r\n        $conn = new PDO('mysql:host=localhost;dbname=root', $db_username, $db_password);\r\n        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\r\n\r\n    } catch(PDOException $e) {\r\n        echo 'ERROR: ' . $e->getMessage();\r\n    }\r\n    return $conn;\r\n}<\/pre>\n
Used PDO connection you can use as per your ease and I have added this function in same methods file you can also manage them as per your projects.<\/p>\n
Implement API<\/strong><\/p>\n
We have created 6 API methods<\/p>\n
    \n
  1. getUsers<\/strong><\/li>\n
  2. getUser<\/strong><\/li>\n
  3. findByName<\/strong><\/li>\n
  4. addUser<\/strong><\/li>\n
  5. updateUser<\/strong><\/li>\n
  6. deleteUser<\/strong><\/li>\n<\/ol>\n
    Define HTTP routes:<\/strong><\/p>\n
    $app->get('\/users', 'getUsers'); \/\/ Using Get HTTP Method and process getUsers function\r\n$app->get('\/users\/:id',    'getUser'); \/\/ Using Get HTTP Method and process getUser function\r\n$app->get('\/users\/search\/:query', 'findByName'); \/\/ Using Get HTTP Method and process findByName function\r\n$app->post('\/users', 'addUser'); \/\/ Using Post HTTP Method and process addUser function\r\n$app->put('\/users\/:id', 'updateUser'); \/\/ Using Put HTTP Method and process updateUser function\r\n$app->delete('\/users\/:id',    'deleteUser'); \/\/ Using Delete HTTP Method and process deleteUser function\r\n$app->run();<\/pre>\n
    These all routes call individual function as defined above and in the last $app->run(); used to run Slim application.<\/p>\n
    let’s see functions:<\/strong><\/p>\n
    1.?getUsers:<\/strong>?$app->get(‘\/users’, ‘getUsers’);<\/p>\n
    function getUsers() {\r\n    $sql_query = \"select `name`,`email`,`date`,`ip` FROM restAPI ORDER BY name\";\r\n    try {\r\n        $dbCon = getConnection();\r\n        $stmt   = $dbCon->query($sql_query);\r\n        $users  = $stmt->fetchAll(PDO::FETCH_OBJ);\r\n        $dbCon = null;\r\n        echo '{\"users\": ' . json_encode($users) . '}';\r\n    }\r\n    catch(PDOException $e) {\r\n        echo '{\"error\":{\"text\":'. $e->getMessage() .'}}';\r\n    }    \r\n}<\/pre>\n
    This function simply return all users information as you can see in this query, to call this API use this URL http:\/\/localhost\/api\/users<\/em> this is it for your first API using get route.<\/p>\n
    2.?getUser:<\/strong>?$app->get(‘\/users\/:id’, ? ?’getUser’); In this route we are sending id.<\/p>\n
    function getUser($id) {\r\n    $sql = \"SELECT `name`,`email`,`date`,`ip` FROM restAPI WHERE id=:id\";\r\n    try {\r\n        $dbCon = getConnection();\r\n        $stmt = $dbCon->prepare($sql);  \r\n        $stmt->bindParam(\"id\", $id);\r\n        $stmt->execute();\r\n        $user = $stmt->fetchObject();  \r\n        $dbCon = null;\r\n        echo json_encode($user); \r\n    } catch(PDOException $e) {\r\n        echo '{\"error\":{\"text\":'. $e->getMessage() .'}}'; \r\n    }\r\n}<\/pre>\n
    This function check record of given id and return if found any thing,??to call this API use this URL http:\/\/localhost\/api\/users\/1<\/em>.<\/p>\n
    3. findByName:<\/strong>?$app->get(‘\/users\/search\/:query’, ‘findByName’); Route is used to search record with extra parameter and a search query with simple get method.<\/p>\n
    function findByName($query) {\r\n    $sql = \"SELECT * FROM restAPI WHERE UPPER(name) LIKE :query ORDER BY name\";\r\n    try {\r\n        $dbCon = getConnection();\r\n        $stmt = $dbCon->prepare($sql);\r\n        $query = \"%\".$query.\"%\";\r\n        $stmt->bindParam(\"query\", $query);\r\n        $stmt->execute();\r\n        $users = $stmt->fetchAll(PDO::FETCH_OBJ);\r\n        $dbCon = null;\r\n        echo '{\"user\": ' . json_encode($users) . '}';\r\n    } catch(PDOException $e) {\r\n        echo '{\"error\":{\"text\":'. $e->getMessage() .'}}'; \r\n    }\r\n}<\/pre>\n
    This function search in database for your given query,?to call this API use this URL http:\/\/localhost\/api\/users\/search\/phpgang<\/em>.<\/p>\n
    create PHP REST API<\/h1>\n
    4. addUser:<\/strong>?$app->post(‘\/users’, ‘addUser’); API used to add new record and accept post.<\/p>\n
    function addUser() {\r\n    global $app;\r\n    $req = $app->request(); \/\/ Getting parameter with names\r\n    $paramName = $req->params('name'); \/\/ Getting parameter with names\r\n    $paramEmail = $req->params('email'); \/\/ Getting parameter with names\r\n\r\n    $sql = \"INSERT INTO restAPI (`name`,`email`,`ip`) VALUES (:name, :email, :ip)\";\r\n    try {\r\n        $dbCon = getConnection();\r\n        $stmt = $dbCon->prepare($sql);  \r\n        $stmt->bindParam(\"name\", $paramName);\r\n        $stmt->bindParam(\"email\", $paramEmail);\r\n        $stmt->bindParam(\"ip\", $_SERVER['REMOTE_ADDR']);\r\n        $stmt->execute();\r\n        $user->id = $dbCon->lastInsertId();\r\n        $dbCon = null;\r\n        echo json_encode($user); \r\n    } catch(PDOException $e) {\r\n        echo '{\"error\":{\"text\":'. $e->getMessage() .'}}'; \r\n    }\r\n}<\/pre>\n
    This API accept post request and insert submitted data in your database as we received parameters in starting of that function. To call this API I have used cURL, you can use jQuery or any other technique.<\/p>\n
    <?php\r\nif($_POST){\r\n    echo post_to_url(\"http:\/\/localhost\/users\", $_POST);\r\n} else{\r\n ?>\r\nADD RECORD.\r\n<form action=\"\" method=\"post\">\r\n<input type=\"text\" name=\"name\" placeholder=\"Name\" \/><br>\r\n<input type=\"text\" name=\"email\" placeholder=\"Email\" \/><br>\r\n<input type=\"hidden\" name=\"_METHOD\" value=\"POST\" \/>\r\n<input type=\"submit\" value=\"A D D\" \/>\r\n<\/form>\r\n<?php \r\n}\r\n?><\/pre>\n
    This form will be submitted to your action file and there is a curl and that curl will post data as I have used a hidden input of _METHOD with POST value this is because some times your cURL don’t Post data to API (faced this so used this for post as well) and Slim give us this functionality to method override and as our modern browsers do not have native support to PUT and delete so we have to use method overriding like below.<\/p>\n
    PHP RESTful API<\/strong><\/h2>\n
    <input type=\"hidden\" name=\"_METHOD\" value=\"POST\" \/> <!-- POST data -->\r\n<input type=\"hidden\" name=\"_METHOD\" value=\"PUT\" \/> <!-- PUT data -->\r\n<input type=\"hidden\" name=\"_METHOD\" value=\"DELETE\" \/> <!-- DELETE data --><\/pre>\n
    cURL function to post data:<\/strong><\/p>\n
    function post_curl($_url, $_data) {\r\n   $mfields = '';\r\n   foreach($_data as $key => $val) { \r\n      $mfields .= $key . '=' . $val . '&'; \r\n   }\r\n   rtrim($mfields, '&');\r\n   $pst = curl_init();\r\n\r\n   curl_setopt($pst, CURLOPT_URL, $_url);\r\n   curl_setopt($pst, CURLOPT_POST, count($_data));\r\n   curl_setopt($pst, CURLOPT_POSTFIELDS, $mfields);\r\n   curl_setopt($pst, CURLOPT_RETURNTRANSFER, 1);\r\n\r\n   $res = curl_exec($pst);\r\n\r\n   curl_close($pst);\r\n   return $res;\r\n}<\/pre>\n
    5. updateUser:<\/strong>?$app->put(‘\/users\/:id’, ‘updateUser’); This route accept put HTTP method.<\/p>\n
    function updateUser($id) {\r\n    global $app;\r\n    $req = $app->request();\r\n    $paramName = $req->params('name');\r\n    $paramEmail = $req->params('email');\r\n\r\n    $sql = \"UPDATE restAPI SET name=:name, email=:email WHERE id=:id\";\r\n    try {\r\n        $dbCon = getConnection();\r\n        $stmt = $dbCon->prepare($sql);  \r\n        $stmt->bindParam(\"name\", $paramName);\r\n        $stmt->bindParam(\"email\", $paramEmail);\r\n        $stmt->bindParam(\"id\", $id);\r\n        $status = $stmt->execute();\r\n\r\n        $dbCon = null;\r\n        echo json_encode($status); \r\n    } catch(PDOException $e) {\r\n        echo '{\"error\":{\"text\":'. $e->getMessage() .'}}'; \r\n    }\r\n}<\/pre>\n
    This API function update your data by id, to call this API we need to again use cURL and HTML form.<\/p>\n
    <?php\r\nif($_POST){\r\n        echo post_to_url(\"http:\/\/localhost\/users\/\".$_POST['id'], $_POST); \/\/ add id after last slash which you want to edit.\r\n} else{\r\nUPDATE RECORD.\r\n<br>\r\n<form action=\"\" method=\"post\">\r\n<input type=\"text\" name=\"id\" placeholder=\"Id to update\" \/><br>\r\n<input type=\"text\" name=\"name\" placeholder=\"Name\" \/><br>\r\n<input type=\"text\" name=\"email\" placeholder=\"Email\" \/><br>\r\n<input type=\"hidden\" name=\"_METHOD\" value=\"PUT\" \/>\r\n<input type=\"submit\" value=\"U P D A T E\" \/>\r\n<\/form>\r\n<?php \r\n}\r\n?><\/pre>\n
    6.?deleteUser:<\/strong>?$app->delete(‘\/users\/:id’, ? ?’deleteUser’); Route used to delete specific ID.<\/p>\n
    function deleteUser($id) {\r\n    $sql = \"DELETE FROM restAPI WHERE id=:id\";\r\n    try {\r\n        $dbCon = getConnection();\r\n        $stmt = $dbCon->prepare($sql);  \r\n        $stmt->bindParam(\"id\", $id);\r\n        $status = $stmt->execute();\r\n        $dbCon = null;\r\n        echo json_encode($status);\r\n    } catch(PDOException $e) {\r\n        echo '{\"error\":{\"text\":'. $e->getMessage() .'}}'; \r\n    }\r\n}<\/pre>\n
    This API function accept HTTP delete request and to send HTTP delete method we have to used method overriding using our hidden field named _METHOD<\/em> and value will be DELETE<\/em>?form and cURL code given below.<\/p>\n
    <?php\r\nif($_POST){\r\n        echo post_to_url(\"http:\/\/localhost\/users\/\".$_POST['id'], $_POST);\r\n} else{\r\n ?>\r\nDELETE RECORD.\r\n<br>\r\n<form action=\"\" method=\"post\">\r\n<input type=\"text\" name=\"id\" placeholder=\"Id to delete\" \/><br>\r\n<input type=\"hidden\" name=\"_METHOD\" value=\"DELETE\" \/>\r\n<input type=\"submit\" value=\"D E L E T E\" \/>\r\n<\/form>\r\n <?php \r\n}\r\n?><\/pre>\n
    \n
    Download the full code package<\/div>\n
    [purchase_link id=”1131″ style=”button” color=”blue” text=”Purchase” direct=”true”]<\/p><\/div>\n
    Read more: Create a web service with PHP<\/a><\/p><\/blockquote>\n
    That’s all for our one of the biggest tutorial for RESTful API<\/strong>, I hope you like this tutorial and please don’t forget to give us your feedback and any issue you have faced in this tutorial please do comment we try our level best to solve your problems<\/strong>.<\/p>\n
    Facebook<\/a><\/blockquote><\/div><\/div>
    Tutorial Categories:<\/strong>
    \n        \"PHP\"<\/a><\/div>
    \n        \"Web<\/a><\/div><\/div>","protected":false},"excerpt":{"rendered":"
    I have received many requests<\/a> from readers and after lots of searching I found out\u00a0a light weight frame work to create RESTful Web services Tutorial<\/strong>. There are a number of frame\u00a0works available and the one I chose is SLIM<\/a> (it is a micro framework that helps\u00a0you efficiently write powerful web services). This tutorial gives you complete\u00a0examples of creating full Restful API using multiple HTTP methods like GET<\/strong>, POST<\/strong>,\u00a0PUT<\/strong> and DELETE<\/strong>. You would get the output in JSON<\/a> and create a user data for all\u00a0options, download code available<\/strong>.<\/p>\n
    \"How<\/p>\n","protected":false},"author":1,"featured_media":589,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"twitterCardType":"summary_large_image","cardImageID":0,"cardImage":"","cardTitle":"","cardDesc":"","cardImageAlt":"","cardPlayer":"","cardPlayerWidth":0,"cardPlayerHeight":0,"cardPlayerStream":"","cardPlayerCodec":"","footnotes":""},"categories":[164,3,13],"tags":[15,121,6,413,416,417,415,414],"_links":{"self":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts\/588"}],"collection":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/comments?post=588"}],"version-history":[{"count":0,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/posts\/588\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/media\/589"}],"wp:attachment":[{"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/media?parent=588"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/categories?post=588"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.phpgang.com\/wp-json\/wp\/v2\/tags?post=588"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}