Mysql Archives - W3school.info

Pagination code using php and mysql

Hi Geeks,
We have gone through some user requests for pagination code for php mysql project.So we have created a simple class where you can easily create pagination for any grid or listing. following are the simple steps for making a pagination system in php.

Step 1: Create a class file ‘Paginator.class.php’ in your project with following code snippet

<?php
 
class Paginator {
 
     private $_conn;
        private $_limit;
        private $_page;
        private $_query;
        private $_total;
		
	 public function __construct( $conn, $query ) {
		 
		$this->_conn = $conn;
		$this->_query = $query;
	 
		$rs= $this->_conn->query( $this->_query );
		$this->_total = $rs->num_rows;
		 
	}
	public function getData( $limit = 10, $page = 1 ) {
     
    $this->_limit   = $limit;
    $this->_page    = $page;
   
 
    if ( $this->_limit == 'all' ) {
        $query      = $this->_query;
    } else {
		$offset=( ( $this->_page - 1 ) * $this->_limit );
        $query      = $this->_query . " LIMIT " . $offset . ", $this->_limit";
    }
    $rs             = $this->_conn->query( $query );
	 
   $results=array();
    while ( $row = $rs->fetch_assoc() ) {
        $results[]  = $row;
    }
 
    $result         = new stdClass();
    $result->page   = $this->_page;
    $result->limit  = $this->_limit;
    $result->total  = $this->_total;
    $result->data   = $results;
 
    return $result;
	}
	
	public function append_existing_query_string($qstring)
	{
		if(isset($_GET))
		{
			foreach($_GET as $k=>$v)
			{
				$qstring.="&".$k."=".$v;
			}
		}
		return $qstring;
	}
	
	public function createLinks( $links, $list_class ) {
    if ( $this->_limit == 'all' ) {
        return '';
    }
 
    $last       = ceil( $this->_total / $this->_limit );
 
    $start      = ( ( $this->_page - $links ) > 0 ) ? $this->_page - $links : 1;
    $end        = ( ( $this->_page + $links ) < $last ) ? $this->_page + $links : $last;
 
    $html       = '<ul class="' . $list_class . '">';
 
    $class      = ( $this->_page == 1 ) ? "disabled" : "";
	    
	//
	$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page - 1 );
	$qstring=$this->append_existing_query_string($qstring);
	//
	
    $html       .= '<li class="' . $class . '"><a href="'.$qstring.'">&laquo;</a></li>';
 
    if ( $start > 1 ) {
		
		//
		$qstring='?limit=' . $this->_limit . '&page=1';
		$qstring=$this->append_existing_query_string($qstring);
		//
        $html   .= '<li><a href="'.$qstring.'">1</a></li>';
        $html   .= '<li class="disabled"><span>...</span></li>';
    }
 
    for ( $i = $start ; $i <= $end; $i++ ) {
        $class  = ( $this->_page == $i ) ? "active" : "";
		//
		$qstring='?limit=' . $this->_limit . '&page=' . $i;
		$qstring=$this->append_existing_query_string($qstring);
		//
        $html   .= '<li class="' . $class . '"><a href="'.$qstring.'">' . $i . '</a></li>';
    }
 
    if ( $end < $last ) {
        $html   .= '<li class="disabled"><span>...</span></li>';
		//
		$qstring='?limit=' . $this->_limit . '&page=' . $last;
		$qstring=$this->append_existing_query_string($qstring);
		//
        $html   .= '<li><a href="'.$qstring.'">' . $last . '</a></li>';
    }
 
    $class      = ( $this->_page == $last ) ? "disabled" : "";
	
	$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page + 1 );
	$qstring=$this->append_existing_query_string($qstring);
	
    $html       .= '<li class="' . $class . '"><a href="'.$qstring.'">&raquo;</a></li>';
 
    $html       .= '</ul><span style="float:right;color:blue">Total records : '.$this->_total.'</span>';
 
    return $html;
	}

}
?>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

<?php

class Paginator {

private $_conn;

private $_limit;

private $_page;

private $_query;

private $_total;

public function __construct( $conn, $query ) {

$this->_conn = $conn;

$this->_query = $query;

$rs= $this->_conn->query( $this->_query );

$this->_total = $rs->num_rows;

}

public function getData( $limit = 10, $page = 1 ) {

$this->_limit = $limit;

$this->_page = $page;

if ( $this->_limit == 'all' ) {

$query = $this->_query;

} else {

$offset=( ( $this->_page - 1 ) * $this->_limit );

$query = $this->_query . " LIMIT " . $offset . ", $this->_limit";

}

$rs = $this->_conn->query( $query );

$results=array();

while ( $row = $rs->fetch_assoc() ) {

$results[] = $row;

}

$result = new stdClass();

$result->page = $this->_page;

$result->limit = $this->_limit;

$result->total = $this->_total;

$result->data = $results;

return $result;

}

public function append_existing_query_string($qstring)

{

if(isset($_GET))

{

foreach($_GET as $k=>$v)

{

$qstring.="&".$k."=".$v;

}

return $qstring;

}

public function createLinks( $links, $list_class ) {

if ( $this->_limit == 'all' ) {

return '';

}

$last = ceil( $this->_total / $this->_limit );

$start = ( ( $this->_page - $links ) > 0 ) ? $this->_page - $links : 1;

$end = ( ( $this->_page + $links ) < $last ) ? $this->_page + $links : $last;

$html = '<ul class="' . $list_class . '">';

$class = ( $this->_page == 1 ) ? "disabled" : "";

$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page - 1 );

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li class="' . $class . '"><a href="'.$qstring.'">«</a></li>';

if ( $start > 1 ) {

$qstring='?limit=' . $this->_limit . '&page=1';

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li><a href="'.$qstring.'">1</a></li>';

$html .= '<li class="disabled"><span>...</span></li>';

}

for ( $i = $start ; $i <= $end; $i++ ) {

$class = ( $this->_page == $i ) ? "active" : "";

$qstring='?limit=' . $this->_limit . '&page=' . $i;

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li class="' . $class . '"><a href="'.$qstring.'">' . $i . '</a></li>';

}

if ( $end < $last ) {

$html .= '<li class="disabled"><span>...</span></li>';

$qstring='?limit=' . $this->_limit . '&page=' . $last;

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li><a href="'.$qstring.'">' . $last . '</a></li>';

}

$class = ( $this->_page == $last ) ? "disabled" : "";

$qstring='?limit=' . $this->_limit . '&page=' . ( $this->_page + 1 );

$qstring=$this->append_existing_query_string($qstring);

$html .= '<li class="' . $class . '"><a href="'.$qstring.'">»</a></li>';

$html .= '</ul><span style="float:right;color:blue">Total records : '.$this->_total.'</span>';

return $html;

}

Step 2: Include the paging class before extracting data to show

<?php

include 'Paginator.class.php';

?>

<?php

include 'Paginator.class.php';

Step 3: Now extract the data for any page as following

<?php
//create a connection to mysql
	$conn       = new mysqli('SERVER HOST NAME','MYSQL USER NAME', 'MY SQL USER PASSWORD','PROJECT DATABASE NAME' );
	
	//DEFINE LIMIT for PER PAGE now 25 is page limit
    $limit      = ( isset( $_GET['limit'] ) ) ? $_GET['limit'] : 25;
	
	//DEFAULT PAGE NUMBER if No page in url
    $page       = ( isset( $_GET['page'] ) ) ? $_GET['page'] : 1;
	
	//Number of frequency links to show at one time ; 
    $links      = ( isset( $_GET['links'] ) ) ? $_GET['links'] : 7;
   
   //your query here in query varriable
    $query  = "Select * from customers  ";
 
 //create a paging class object with connection and query parameters
    $Paginator  = new Paginator( $conn, $query );
 
 //get the results from paginator class
    $results    = $Paginator->getData( $limit, $page ); 
 ?>

<?php

//create a connection to mysql

$conn = new mysqli('SERVER HOST NAME','MYSQL USER NAME', 'MY SQL USER PASSWORD','PROJECT DATABASE NAME' );

//DEFINE LIMIT for PER PAGE now 25 is page limit

$limit = ( isset( $_GET['limit'] ) ) ? $_GET['limit'] : 25;

//DEFAULT PAGE NUMBER if No page in url

$page = ( isset( $_GET['page'] ) ) ? $_GET['page'] : 1;

//Number of frequency links to show at one time ;

$links = ( isset( $_GET['links'] ) ) ? $_GET['links'] : 7;

//your query here in query varriable

$query = "Select * from customers ";

//create a paging class object with connection and query parameters

$Paginator = new Paginator( $conn, $query );

//get the results from paginator class

$results = $Paginator->getData( $limit, $page );

Step 4 : Now we are ready with data. So just showing the data and links as following

> Show the data first and after it showing the pagination links as following:

<table class="table table-striped">
              <thead>
                <tr>
               
                  <th>Customer Id</th>
                  <th>Customer Name</th>
				  <th>Customer Email</th>
                  <th>Customer Address</th>
                 
                </tr>
              </thead>
              <tbody>
				<?php
			 
				if(isset($results) && count( $results->data ) > 0){
				 
				 for( $i = 0; $i < count( $results->data ); $i++ ) { ?>
				<tr>
                <td><?php echo $results->data[$i]['id']; ?></td>
                <td><?php echo $results->data[$i]['name']; ?></td>
                <td><?php echo $results->data[$i]['email']; ?></td>
                <td><?php echo $results->data[$i]['address']; ?></td>
               
				  
				</tr>
				<?php }
				} ?>
               
              </tbody>
            </table>

<thead>

<tr>

<th>Customer Id</th>

<th>Customer Name</th>

<th>Customer Email</th>

<th>Customer Address</th>

</tr>

</thead>

<tbody>

<?php

if(isset($results) && count( $results->data ) > 0){

for( $i = 0; $i < count( $results->data ); $i++ ) { ?>

<tr>

<td><?php echo $results->data[$i]['email']; ?></td>

<td><?php echo $results->data[$i]['address']; ?></td>

</tr>

<?php }

} ?>

</tbody>

</table>

> Show the paging links after the data

<p><?php echo $Paginator->createLinks( $links, $class="pagination"); ?></p>

1

<p><?php echo $Paginator->createLinks( $links, $class="pagination"); ?></p>

How to work with AngularJS as frontend and PHP MYSQL as backend

Hi Geeks,

In this tutorial you will learn that how to work with AngularJs as web frontend or in any hybrid app and get retrieve data from database using php and mysql. We have created restful webservice for return records to angular app.

Now steps for making a angularjs app with php and mysql –
Step 1) Create a database and table for fetching content from the table
Note: If you want to use existing database then select you db else create a new db with ‘news_db’ or whatever you want to take a db name.
Now Create a table named ‘news’ for getting data by following sql statement

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `permalink` varchar(255) NOT NULL,
  `details` text NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `category_id` int(12) NOT NULL,
  `source_id` int(12) NOT NULL,
  `datetime` int(12) NOT NULL,
  `day` int(2) NOT NULL,
  `month` int(2) NOT NULL,
  `year` int(4) NOT NULL,
  `hits` int(12) NOT NULL,
  `published` int(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `news` (

`id` int(12) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`permalink` varchar(255) NOT NULL,

`details` text NOT NULL,

`thumbnail` varchar(255) NOT NULL,

`category_id` int(12) NOT NULL,

`source_id` int(12) NOT NULL,

`datetime` int(12) NOT NULL,

`day` int(2) NOT NULL,

`month` int(2) NOT NULL,

`year` int(4) NOT NULL,

`hits` int(12) NOT NULL,

`published` int(1) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Now Dump some dummy records by following insert statement:

--
-- Dumping data for table `news`
--

INSERT INTO `news` (`id`, `title`, `permalink`, `details`, `thumbnail`, `category_id`, `source_id`, `datetime`, `day`, `month`, `year`, `hits`, `published`) VALUES
(1, 'MY NEWS TITLE 1', '', 'The standard chunk of Lorem Ipsum used since the 1500s is reproduced below for those interested. Sections 1.10.32 and 1.10.33 ', '11621455015936.jpg', 1, 0, 1455015936, 9, 2, 2016, 45, 1),
(2, 'MY NEWS TITLE 2', '', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. ', '76911455015952.jpg', 2, 0, 1455015952, 9, 2, 2016, 73, 0);

-- Dumping data for table `news`

INSERT INTO `news` (`id`, `title`, `permalink`, `details`, `thumbnail`, `category_id`, `source_id`, `datetime`, `day`, `month`, `year`, `hits`, `published`) VALUES

(1, 'MY NEWS TITLE 1', '', 'The standard chunk of Lorem Ipsum used since the 1500s is reproduced below for those interested. Sections 1.10.32 and 1.10.33 ', '11621455015936.jpg', 1, 0, 1455015936, 9, 2, 2016, 45, 1),

(2, 'MY NEWS TITLE 2', '', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. ', '76911455015952.jpg', 2, 0, 1455015952, 9, 2, 2016, 73, 0);

Step 2) Create a file named config.php with following code

<?php
$details=array();
$details['server_host']="localhost";//server host name
$details['mysql_name']="root";//your mysql user name
$details['mysql_password']="";//your mysql user name
$details['mysql_database']="news_db";//your database name
?>

<?php

$details=array();

$details['server_host']="localhost";//server host name

$details['mysql_name']="root";//your mysql user name

$details['mysql_password']="";//your mysql user name

$details['mysql_database']="news_db";//your database name

change credential according to your database and server host
Step 3) Now create a file for webservices named wsdl.php
Define Cross origin headers in this file by following code

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
?>

<?php

header("Access-Control-Allow-Origin: *");

header("Content-Type: application/json; charset=UTF-8");

Now code for getting data from database and provide in restful json format by following code

<?php
include 'config.php';//make the cofig file include
global $details;//make the connection vars global

if($_GET['method'] == "load_news")
{
	$conn = new mysqli($details['server_host'], $details['mysql_name'],$details['mysql_password'], $details['mysql_database']);	
	$result = $conn->query("SELECT title,details,hits FROM news");
	$data=array();
	while($rs = $result->fetch_array(MYSQLI_ASSOC)) {
		$row=array();
	   $row['title']=addslashes($rs["title"]);
	   $row['details']=addslashes($rs["details"]);
	   $row['hits']=addslashes($rs["hits"]);
	   
	   $data[]=$row;
		
	}
	$jsonData=array();
	$jsonData['records']=$data;

	$conn->close();
	echo json_encode($jsonData);

}
?>

<?php

include 'config.php';//make the cofig file include

global $details;//make the connection vars global

if($_GET['method'] == "load_news")

{

$conn = new mysqli($details['server_host'], $details['mysql_name'],$details['mysql_password'], $details['mysql_database']);

$result = $conn->query("SELECT title,details,hits FROM news");

$data=array();

while($rs = $result->fetch_array(MYSQLI_ASSOC)) {

$row=array();

$row['title']=addslashes($rs["title"]);

$row['details']=addslashes($rs["details"]);

$row['hits']=addslashes($rs["hits"]);

$data[]=$row;

}

$jsonData=array();

$jsonData['records']=$data;

$conn->close();

echo json_encode($jsonData);

}

So complete Code for wsdl.php is following

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
include 'config.php';//make the cofig file include
global $details;//make the connection vars global

if($_GET['method'] == "load_news")
{
	$conn = new mysqli($details['server_host'], $details['mysql_name'],$details['mysql_password'], $details['mysql_database']);	
	$result = $conn->query("SELECT title,details,hits FROM news");
	$data=array();
	while($rs = $result->fetch_array(MYSQLI_ASSOC)) {
		$row=array();
	   $row['title']=addslashes($rs["title"]);
	   $row['details']=addslashes($rs["details"]);
	   $row['hits']=addslashes($rs["hits"]);
	   
	   $data[]=$row;
		
	}
	$jsonData=array();
	$jsonData['records']=$data;

	$conn->close();
	echo json_encode($jsonData);

}
?>

<?php

header("Access-Control-Allow-Origin: *");

header("Content-Type: application/json; charset=UTF-8");

include 'config.php';//make the cofig file include

global $details;//make the connection vars global

if($_GET['method'] == "load_news")

{

$conn = new mysqli($details['server_host'], $details['mysql_name'],$details['mysql_password'], $details['mysql_database']);

$result = $conn->query("SELECT title,details,hits FROM news");

$data=array();

while($rs = $result->fetch_array(MYSQLI_ASSOC)) {

$row=array();

$row['title']=addslashes($rs["title"]);

$row['details']=addslashes($rs["details"]);

$row['hits']=addslashes($rs["hits"]);

$data[]=$row;

}

$jsonData=array();

$jsonData['records']=$data;

$conn->close();

echo json_encode($jsonData);

}

Step 4) Now Start with frontend
Step for Getting data from database using angularjs as frontend and php & mysql as backed
make a file named index.html
include the script which i have attached with this post

<script src="js/angular.1.4.8.min.js"></script>

1	<script src="js/angular.1.4.8.min.js"></script>

Now make a ng-app with following html

<div ng-app="myApp" ng-controller="newsCtrl"> 

</div>

</div>

make a html table format inside the div for getting data from datasource(php/mysql restful service) as following

<div ng-app="myApp" ng-controller="newsCtrl"> 
<table border="2">
  <tr ng-repeat="x in names">
    <td>{{ x.title }}</td>
    <td>{{ x.details }}</td>
    <td>{{ x.hits }}</td>
  </tr>
</table>
</div>

<td>{{ x.title }}</td>

<td>{{ x.details }}</td>

</tr>

</table>

</div>

Now put the script that call the restful web service and load data to the table

<script>
var app = angular.module('myApp', []);
app.controller('newsCtrl', function($scope, $http) {
    $http.get("http://localhost/angular/wsdl.php?method=load_news")
    .then(function (response) {$scope.names = response.data.records;});
});
</script>

var app = angular.module('myApp', []);

app.controller('newsCtrl', function($scope, $http) {

$http.get("http://localhost/angular/wsdl.php?method=load_news")

.then(function (response) {$scope.names = response.data.records;});

});

</script>

Now complete Code for index.html

<script src="js/angular.1.4.8.min.js"></script>
<div ng-app="myApp" ng-controller="newsCtrl"> 

<table border="2">
  <tr ng-repeat="x in names">
    <td>{{ x.title }}</td>
    <td>{{ x.details }}</td>
    <td>{{ x.hits }}</td>
  </tr>
</table>

</div>

<script>
var app = angular.module('myApp', []);
app.controller('newsCtrl', function($scope, $http) {
    $http.get("http://localhost/angular/wsdl.php?method=load_news")
    .then(function (response) {$scope.names = response.data.records;});
});
</script>

<td>{{ x.title }}</td>

<td>{{ x.details }}</td>

</tr>

</table>

</div>

var app = angular.module('myApp', []);

app.controller('newsCtrl', function($scope, $http) {

$http.get("http://localhost/angular/wsdl.php?method=load_news")

.then(function (response) {$scope.names = response.data.records;});

});

</script>

Congratulations you have completed with this tutorial please provide comments for this tutorial. [viraldownloader id=146 text=’DOWNLOAD COMPLETE CODE’]