Friday, September 11, 2015

Read Excel file and save details to database using PHP

How can you read and display excel file using php and save that details to the database? You can use this simple php class to do this.That is read row by row while reading column by column.While reading like this it gets each cells details.Look at this example that is simply describes this process and you can understand this better.
First download excel reader.php class file. You can download reader.php file here.
Also you must include this oleread.inc file of this reader.php file.Download oleread.inc.
Here is the sample.xls file format.
sample.xls-format

How to read excel file using php

<html>
  <head>
  </head>
  <body>
<?php
include 'reader.php';
    $excel = new Spreadsheet_Excel_Reader();
?>
Sheet 1:<br/>
    <table border="1">
<?php
        $excel->read('sample.xls'); // set the excel file name here   
        $x=1;
        while($x<=$excel->sheets[0]['numRows']) { // reading row by row 
          echo "\t<tr>\n";
          $y=1;
          while($y<=$excel->sheets[0]['numCols']) {// reading column by column 
            $cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
            echo "\t\t<td>$cell</td>\n";  // get each cells values
            $y++;
          }  
          echo "\t</tr>\n";
          $x++;
        }
        ?>    
    </table>
  </body>
</html>

Note:
This is reading sheet1 details.If you want to read sheet 2 details, then change the sheets[0] to sheets[1] in both while loops.
Example:
while($x<=$excel->sheets[0]['numRows']) {
Remember “sheets” is the array.Array begin from 0.You already know about it. ðŸ˜€
So,
Sheet1 -> sheets[0]
Sheet2 -> sheets[1]
Sheet3 -> sheets[2]

How to read and save details to the database using php

Table structure
DROP TABLE IF EXISTS `users_details`;
CREATE TABLE `users_details` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `job` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
<html>
  <head> 
  <title>Save Excel file details to the database</title>
  </head>
  <body>
<?php
include 'db_connection.php';
include 'reader.php';
    $excel = new Spreadsheet_Excel_Reader();
?>
   <table border="1">
<?php
            $excel->read('sample.xls');    
$x=2;
while($x<=$excel->sheets[0]['numRows']) {
$name = isset($excel->sheets[0]['cells'][$x][1]) ? $excel->sheets[0]['cells'][$x][1] : '';
$job = isset($excel->sheets[0]['cells'][$x][2]) ? $excel->sheets[0]['cells'][$x][2] : '';
$email = isset($excel->sheets[0]['cells'][$x][3]) ? $excel->sheets[0]['cells'][$x][3] : '';
// Save details
$sql_insert="INSERT INTO users_details (id,name,job,email) VALUES ('','$name','$job','$email')";
$result_insert = mysql_query($sql_insert) or die(mysql_error()); 
   $x++;
}
        ?>    
    </table>
  </body>

PHP Rest API in Codeigniter

Before starting REST API, I would like to explain what exactly an API is.
API is an abbreviation for Application Programming Interface. API is a set of instructions ( functions and routines) that accomplish a specific task or are allowed to interact with a specific software component like database or computer hardware.
REST stands for Representational State Transfer. REST is web standard-based architecture and uses HTTP Protocol for data communication. REST architecture works on the client-server model. The main idea behind using this is to use simple HTTP Protocol to make calls between machines instead of using complex mechanisms like CORBA, SOAP, or RPC.
We’re going to build a RESTful application which will use HTTP requests to perform CRUD(Create, Read, Update & Delete) operations. In REST API, everything is a resource.

php-rest-api

Features:

  • Platform-independent,
  • Language-independent,
  • Standards-based, i.e, runs on top of HTTP protocol, and
  • It can easily be used in the presence of firewalls.

HTTP Methods v/s CRUD Operations:

Now, let’s take a look at how HTTP methods can be mapped to CRUD operations.
HTTP MethodCRUD Operation
GETREAD
POSTINSERT / CREATE
PUTUPDATE
DELETEDELETE


Files to be needed:

We’re going to use Phil Sturgeon’s Rest Server and Rest Client. We’ll use codeigniterframework of PHP to develop Rest API.
These are the library files for our respective server and client files.
We need to include these files inside the library folder of the application folder of our project.

Process:

First of all we need to understand the process of REST API. Let’s take a look at it.
As we know that it’s a client-server model, we need to create 2 files. One will work at theclient end and other at the server end. These 2 files plays a very important role in REST architecture.

Client File:

All requests pass through this file. This file handles every type of request and calls the respective method and pass the data to the server.I’ll explain this in detail later. In this file, we have to mention the server address, login credentials with API key so that it will authenticate the user. Along with credentials, HTTP methods ( GET, POST, PUT, DELETE ) are defined. Use of these methods are just to collect data from the user from the front end and transfer it to the server using HTTP, CURL etc. We’re going to use CURL.

Server File:

The server receives the request and performs the respective operation. This file interacts with both the client file and database directly. So ultimately this file is going to handle everything behind the scenes.
Now, I’ll explain each file one by one.

Controller : Client.php ( Client File )

// Client File ( Client.php )
class Client extends CI_Controller
{
// User's Login Credentials
function __construct() {
parent::__construct();
$this->load->library('rest', array('server' => 'http://localhost/mb',
'api_key' => 'REST API',
'api_name' => 'X-API-KEY',
'http_user' => 'admin',
'http_pass' => '1234',
'http_auth' => 'basic',
));
}
// Client's Put Method
function put($id=0){
if($id==0){
$this->load->view('read');
}
$id = $this->uri->segment(3);
$this->rest->format('application/json');
$params = array(
'id' => $id,
'book_name' => $this->input->post('dname'),
'book_price' => $this->input->post('dprice'),
'book_author' => $this->input->post('dauthor')
);
$user = $this->rest->put('index.php/api/data/'.$id, $params,'');
$this->rest->debug();
}
// Client's Post Method
function post($id=0){
if($id==0){
$this->load->view('read');
}
$this->rest->format('application/json');
$params = $this->input->post(NULL,TRUE);
$user = $this->rest->post('index.php/api/data', $params,'');
$this->rest->debug();
}
// Client's Get Method
function get($id=0){
if($id==0){
$this->load->view('read');
}
$id = $this->uri->segment(3);
$this->rest->format('application/json');
$params = $this->input->get('id');
$user = $this->rest->get('index.php/api/data/'.$id, $params,'');
$this->rest->debug();
}
// Client's Delete Method
function delete($id=0){
if($id==0){
$this->load->view('read');
}
$id = $this->uri->segment(3);
$this->rest->format('application/json');
$user = $this->rest->delete('index.php/api/data/'.$id,'','');
$this->rest->debug();
}
}

In client file, I’ve passed parameters required for authentication and connection with the server to the client library. Client library then connects to the server using curl. Authentication details, server details, and other HTTP headers are passed using curl.
 // Details will be passed in an array :
array('server' => 'http://localhost/mb',
'api_key' => 'REST API',
'api_name' => 'X-API-KEY',
'http_user' => 'admin',
'http_pass' => '1234',
'http_auth' => 'basic' )

Once authentication has been done, the respective method will be called in the client file. Say, if we’re using get method. Then get method of client file will be called.
 // This method will get called in the client file
function get($id=0){
if($id==0){
$this->load->view('read');
}
$id = $this->uri->segment(3);
$this->rest->format('application/json');
$params = $this->input->get('id');
$user = $this->rest->get('index.php/api/data/'.$id, $params,'');
$this->rest->debug();
}

It further call another get method inside it, along with parameters (URL, Data, and Format) whose definition is in the rest library.
$user = $this->rest->get('index.php/api/data/'.$id, $params,'');
It then passed to _call method which resides in the rest library and is going to pass data to server file through curl.

Controller : Api.php ( Server File )

 // Server File ( Api.php )
defined('BASEPATH') OR exit('No direct script access allowed');
// Including Phil Sturgeon's Rest Server Library in our Server file.
require APPPATH . '/libraries/REST_Controller.php';
class API extends REST_Controller{
// Load model in constructor
public function __construct() {
parent::__construct();
$this->load->model('API_model');
}
// Server's Get Method
public function data_get($id_param = NULL){
$id = $this->input->get('id');
if($id===NULL){
$id = $id_param;
}
if ($id === NULL)
{
$data = $this->API_model->read($id);
if ($data)
{
$this->response($data, REST_Controller::HTTP_OK);
}
else
{
$this->response([
'status' => FALSE,
'error' => 'No books were found'
], REST_Controller::HTTP_NOT_FOUND);
}
}
$data = $this->API_model->read($id);
if ($data)
{
$this->set_response($data, REST_Controller::HTTP_OK);
}
else
{
$this->set_response([
'status' => FALSE,
'error' => 'Record could not be found'
], REST_Controller::HTTP_NOT_FOUND);
}
}
// Server's Post Method
public function data_post(){
$data = array('book_name' => $this->input->post('dname'),
'book_price' => $this->input->post('dprice'),
'book_author' => $this->input->post('dauthor')
);
$this->API_model->insert($data);
$message = [
'Book Name' => $data['book_name'],
'Book Price' => $data['book_price'],
'Book Author' => $data['book_author'],
'message' => 'Added a resource'
];
$this->set_response($message, REST_Controller::HTTP_CREATED);
}
// Server's Put Method
public function data_put(){
$data = $this->input->input_stream();
$this->API_model->update($data);
$message = [
'id' => $data['id'],
'Book Name' => $data['book_name'],
'Book Price' => $data['book_price'],
'Book Author' => $data['book_author'],
'message' => 'Added a resource'
];
$this->set_response($message, REST_Controller::HTTP_CREATED);
}
// Server's Delete Method
public function data_delete(){
$id = $this->uri->segment(3);
if($id===NULL){
$this->set_response([
'status' => FALSE,
'error' => 'ID cannot be empty'
], REST_Controller::HTTP_NOT_FOUND);
}
$data = $this->API_model->delete($id);
if ($data)
{
$this->set_response($data, REST_Controller::HTTP_OK);
}
else
{
$this->set_response([
'status' => FALSE,
'error' => 'Record could not be found'
], REST_Controller::HTTP_NOT_FOUND);
}
}
}

In server file, first thing that is going to call is the constructor of the server file. As we’ve inherited REST_controller class, constructor of REST_controller will also get called and it will call appropriate functions for various purposes (like detect_method, authentication, parsing get method to data_get method, etc.) and then calls the data_get method in server file.
 // Server's data_get method
public function data_get($id_param = NULL){
$id = $this->input->get('id');
if($id===NULL){
$id = $id_param;
}
if ($id === NULL)
{
$data = $this->API_model->read($id);
if ($data)
{
$this->response($data, REST_Controller::HTTP_OK);
}
else
{
$this->response([
'status' => FALSE,
'error' => 'No books were found'
], REST_Controller::HTTP_NOT_FOUND);
}
}
$data = $this->API_model->read($id);
if ($data)
{
$this->set_response($data, REST_Controller::HTTP_OK);
}
else
{
$this->set_response([
'status' => FALSE,
'error' => 'Record could not be found'
], REST_Controller::HTTP_NOT_FOUND);
}
}
In this data_get method database operations will take place. As we are using CodeIgniter, all the queries are written in the model file, i.e., API_model in our case.

Model : API_model.php

 //Model File ( API_model )
defined('BASEPATH') OR exit('No direct script access allowed');
class API_model extends CI_Model{
// Read Query
public function read($id){
if($id===NULL){
$replace = "" ;
}
else{
$replace = "=$id";
}
$query = $this->db->query("select * from books where id".$replace);
return $query->result_array();
}
// Insert/Create Query
public function insert($data){
$this->db->insert('books', $data);
return TRUE;
}
// Delete Query
public function delete($id){
$query = $this->db->query("delete from books where id=$id");
return TRUE;
}
// Update Query
public function update($data){
$id= $data['id'];
$this->db->where('id',$id);
$this->db->update('books',$data);
}
}

Htaccess file

Apart from this process, you may need to edit your htaccess file present in your application folder.
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ api.php/$1 [L]
</IfModule>

Following line meant that all the requests coming from the client must be forwarded to api.php, i.e., our server file.
RewriteRule ^(.*)$ api.php/$1 [L]
So, this is the complete process for implementing Rest API. You may need to make changes in the library files. You can also create your own authentication method/algorithm if you’d like to.

Database

First of all create a database with a name ‘books’ with the following query :
CREATE DATABASE books;
Now, create a table inside this database with the following query :
CREATE TABLE `books`.
( `id` INT NOT NULL AUTO_INCREMENT ,
`book_name`VARCHAR(255) NOT NULL ,
`book_price` VARCHAR(255) NOT NULL ,
`book_author`VARCHAR(255) NOT NULL ,
PRIMARY KEY (`id`)) ENGINE = InnoDB;

Procedure to run this project

You can easily run this project with following url’s for respective operation.
For each operation you need to edit the type of method in the read.php file.
For example : put in this case and method will be post.
<form action="http://localhost/mb/index.php/client/put/50" method="post">
For Put & Post, Method will be post & for Get & Delete, Method will be get.
For Read Operation :
You may or may not need to mention a particular id you want to fetch in the end of the URL like this :
http://localhost/mb/index.php/client/get/47      (say id = 47) 
For Create/Insert Operation :
You need to insert data in the form.
http://localhost/mb/index.php/client/post
For Update Operation :
You need to insert data you want to update in the form along with the id at which you want to update this information.
http://localhost/mb/index.php/client/put/23     (say id = 23) 
For Delete Operation :
You may or may not need to mention a particular id you want to fetch in the end of the URL like this :
http://localhost/mb/index.php/client/delete/26     (say id = 26) 

Conclusion:

So, this is how we can create Rest API in PHP. I hope it was helpful for you. Keep visiting our website.

How to backup and download Database using PHP

< ?php $mysqlUserName = 'databaseusername' ; $mysqlPassword = 'databasepassword' ; $mysqlHostNa...