#BLOG - 

How To Create Simple Pagination In PHP And MySQL

We all know Pagination is the best way to display a large set of data like search results, products list, blog article list, customer list etc. In this PHP pagination tutorial, I will show you how to show a large set of data grouped into pages and navigate the pages through navigational links. Using this simple pagination in PHP script, you can easily apply any kind of pagination. I have created a PHP pagination class. Using this class, you can easily show the results from MYSQL database with your limitation choice and also create advanced pagination navigation with next and previous. Data fetch limit technique is very useful to decrease page load time in spite of fetching all data at once.

5267
Last Modified on 2018-07-24
Ratings:   5.00 from 8 Reviews

How To Create Simple Pagination In PHP And MySQL image

Newsletter Subscribtion

Subscribe to our newsletter so that you can receive all updates.


Dreamy Priyanka

Why uses this Code


If you are looking for the ultimate pagination script in PHP and MySQL, then you should try this code at first.


You have to include the pagination class first, then just call two methods that’s it. To fetch and display the results you have to use getData() method and to create automatic navigation links just use createLinks() method.


I have explained all the process step by step. You do not have to search any other pagination script if you understand how to use this script. The user can also change the dataset limits from the results shown section.


Check out this pagination in PHP demo I have attached the bottom of this article.


How does it work?


As I told you that this is a responsive design so this pagination automatically adjusts in any device.


I have posted an article  How to create responsive CSS pagination design. After reading this article you can understand how responsive navigation links work. There is an option to change data limit. Using it the user can easily change the limit anytime.


There are two types of pagination navigation I have shown to you.


One is simple PHP pagination navigation and another one is advanced PHP pagination navigation links. You can view the working demo here and also download the full source code with the database from here. If you want to create simple pagination in php using ajax, then please check out my other PHP pagination tutorial section.

Check out other PHP pagination tutorials –


What you will need to create this PHP pagination

Steps to create PHP pagination with MYSQL


In this tutorial, I have used both MySQLI and PDO database connection. I will explain you now step by step how easily you can create PHP pagination with MYSQL.


1. Create Database table with dummy data


CREATE TABLE `customers` (
`customerNumber` int(11) NOT NULL,
`customerName` varchar(50) NOT NULL,
`contactLastName` varchar(50) NOT NULL,
`contactFirstName` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`salesRepEmployeeNumber` int(11) DEFAULT NULL,
`creditLimit` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`customerNumber`),
KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `customers` (`customerNumber`, `customerName`, `contactLastName`, `contactFirstName`, `phone`, `addressLine1`, `addressLine2`, `city`, `state`, `postalCode`, `country`, `salesRepEmployeeNumber`, `creditLimit`) VALUES
(103, 'Atelier graphique', 'Schmitt', 'Carine ', '40.32.2555', '54, rue Royale', '', 'Nantes', '', '44000', 'France', 1370, '21000.00'),
(112, 'Signal Gift Stores', 'King', 'Jean', '7025551838', '8489 Strong St.', '', 'Las Vegas', 'NV', '83030', 'USA', 1166, '71800.00'),
(114, 'Australian Collectors, Co.', 'Ferguson', 'Peter', '03 9520 4555', '636 St Kilda Road', 'Level 3', 'Melbourne', 'Victoria', '3004', 'Australia', 1611, '117300.00'),
(119, 'La Rochelle Gifts', 'Labrune', 'Janine ', '40.67.8555', '67, rue des Cinquante Otages', '', 'Nantes', '', '44000', 'France', 1370, '118200.00'),
(121, 'Baane Mini Imports', 'Bergulfsen', 'Jonas ', '07-98 9555', 'Erling Skakkes gate 78', '', 'Stavern', '', '4110', 'Norway', 1504, '81700.00'),
(124, 'Mini Gifts Distributors Ltd.', 'Nelson', 'Susan', '4155551450', '5677 Strong St.', '', 'San Rafael', 'CA', '97562', 'USA', 1165, '210500.00'),
(125, 'Havel & Zbyszek Co', 'Piestrzeniewicz', 'Zbyszek ', '(26) 642-7555', 'ul. Filtrowa 68', '', 'Warszawa', '', '01-012', 'Poland', , '0.00'),
(128, 'Blauer See Auto, Co.', 'Keitel', 'Roland', '+49 69 66 90 2555', 'Lyonerstr. 34', '', 'Frankfurt', '', '60528', 'Germany', 1504, '59700.00'),
(129, 'Mini Wheels Co.', 'Murphy', 'Julie', '6505555787', '5557 North Pendale Street', '', 'San Francisco', 'CA', '94217', 'USA', 1165, '64600.00'),
(131, 'Land of Toys Inc.', 'Lee', 'Kwai', '2125557818', '897 Long Airport Avenue', '', 'NYC', 'NY', '10022', 'USA', 1323, '114900.00'),
(141, 'Euro+ Shopping Channel', 'Freyre', 'Diego ', '(91) 555 94 44', 'C/ Moralzarzal, 86', '', 'Madrid', '', '28034', 'Spain', 1370, '227600.00'),
(144, 'Volvo Model Replicas, Co', 'Berglund', 'Christina ', '0921-12 3555', 'Berguvsvägen 8', '', 'Luleå', '', 'S-958 22', 'Sweden', 1504, '53100.00'),
(145, 'Danish Wholesale Imports', 'Petersen', 'Jytte ', '31 12 3555', 'Vinbæltet 34', '', 'Kobenhavn', '', '1734', 'Denmark', 1401, '83400.00'),
(146, 'Saveley & Henriot, Co.', 'Saveley', 'Mary ', '78.32.5555', '2, rue du Commerce', '', 'Lyon', '', '69004', 'France', 1337, '123900.00'),
(148, 'Dragon Souveniers, Ltd.', 'Natividad', 'Eric', '+65 221 7555', 'Bronz Sok.', 'Bronz Apt. 3/6 Tesvikiye', 'Singapore', '', '079903', 'Singapore', 1621, '103800.00'),
(151, 'Muscle Machine Inc', 'Young', 'Jeff', '2125557413', '4092 Furth Circle', 'Suite 400', 'NYC', 'NY', '10022', 'USA', 1286, '138500.00'),
(157, 'Diecast Classics Inc.', 'Leong', 'Kelvin', '2155551555', '7586 Pompton St.', '', 'Allentown', 'PA', '70267', 'USA', 1216, '100600.00'),
(161, 'Technics Stores Inc.', 'Hashimoto', 'Juri', '6505556809', '9408 Furth Circle', '', 'Burlingame', 'CA', '94217', 'USA', 1165, '84600.00'),
(166, 'Handji Gifts& Co', 'Victorino', 'Wendy', '+65 224 1555', '106 Linden Road Sandown', '2nd Floor', 'Singapore', '', '069045', 'Singapore', 1612, '97900.00'),
(167, 'Herkku Gifts', 'Oeztan', 'Veysel', '+47 2267 3215', 'Brehmen St. 121', 'PR 334 Sentrum', 'Bergen', '', 'N 5804', 'Norway ', 1504, '96800.00'),
(168, 'American Souvenirs Inc', 'Franco', 'Keith', '2035557845', '149 Spinnaker Dr.', 'Suite 101', 'New Haven', 'CT', '97823', 'USA', 1286, '0.00'),
(169, 'Porto Imports Co.', 'de Castro', 'Isabel ', '(1) 356-5555', 'Estrada da saúde n. 58', '', 'Lisboa', '', '1756', 'Portugal', , '0.00'),
(171, 'Daedalus Designs Imports', 'Rancé', 'Martine ', '20.16.1555', '184, chaussée de Tournai', '', 'Lille', '', '59000', 'France', 1370, '82900.00'),
(172, 'La Corne D'abondance, Co.', 'Bertrand', 'Marie', '(1) 42.34.2555', '265, boulevard Charonne', '', 'Paris', '', '75012', 'France', 1337, '84300.00'),
(173, 'Cambridge Collectables Co.', 'Tseng', 'Jerry', '6175555555', '4658 Baden Av.', '', 'Cambridge', 'MA', '51247', 'USA', 1188, '43400.00'),
(175, 'Gift Depot Inc.', 'King', 'Julie', '2035552570', '25593 South Bay Ln.', '', 'Bridgewater', 'CT', '97562', 'USA', 1323, '84300.00'),
(177, 'Osaka Souveniers Co.', 'Kentary', 'Mory', '+81 06 6342 5555', '1-6-20 Dojima', '', 'Kita-ku', 'Osaka', ' 530-0003', 'Japan', 1621, '81200.00'),
(181, 'Vitachrome Inc.', 'Frick', 'Michael', '2125551500', '2678 Kingston Rd.', 'Suite 101', 'NYC', 'NY', '10022', 'USA', 1286, '76400.00'),
(186, 'Toys of Finland, Co.', 'Karttunen', 'Matti', '90-224 8555', 'Keskuskatu 45', '', 'Helsinki', '', '21240', 'Finland', 1501, '96500.00'),
(187, 'AV Stores, Co.', 'Ashworth', 'Rachel', '(171) 555-1555', 'Fauntleroy Circus', '', 'Manchester', '', 'EC2 5NT', 'UK', 1501, '136800.00'),
(189, 'Clover Collections, Co.', 'Cassidy', 'Dean', '+353 1862 1555', '25 Maiden Lane', 'Floor No. 4', 'Dublin', '', '2', 'Ireland', 1504, '69400.00'),
(198, 'Auto-Moto Classics Inc.', 'Taylor', 'Leslie', '6175558428', '16780 Pompton St.', '', 'Brickhaven', 'MA', '58339', 'USA', 1216, '23000.00'),
(201, 'UK Collectables, Ltd.', 'Devon', 'Elizabeth', '(171) 555-2282', '12, Berkeley Gardens Blvd', '', 'Liverpool', '', 'WX1 6LT', 'UK', 1501, '92700.00'),
(202, 'Canadian Gift Exchange Network', 'Tamuri', 'Yoshi ', '(604) 555-3392', '1900 Oak St.', '', 'Vancouver', 'BC', 'V3F 2K1', 'Canada', 1323, '90300.00'),
(204, 'Online Mini Collectables', 'Barajas', 'Miguel', '6175557555', '7635 Spinnaker Dr.', '', 'Brickhaven', 'MA', '58339', 'USA', 1188, '68700.00'),
(205, 'Toys4GrownUps.com', 'Young', 'Julie', '6265557265', '78934 Hillside Dr.', '', 'Pasadena', 'CA', '90003', 'USA', 1166, '90700.00'),
(206, 'Asian Shopping Network, Co', 'Walker', 'Brydey', '+612 9411 1555', 'Suntec Tower Three', '8 Temasek', 'Singapore', '', '038988', 'Singapore', , '0.00'),
(209, 'Mini Caravy', 'Citeaux', 'Frédérique ', '88.60.1555', '24, place Kléber', '', 'Strasbourg', '', '67000', 'France', 1370, '53800.00'),
(211, 'King Kong Collectables, Co.', 'Gao', 'Mike', '+852 2251 1555', 'Bank of China Tower', '1 Garden Road', 'Central Hong Kong', '', '', 'Hong Kong', 1621, '58600.00'),
(216, 'Enaco Distributors', 'Saavedra', 'Eduardo ', '(93) 203 4555', 'Rambla de Cataluña, 23', '', 'Barcelona', '', '08022', 'Spain', 1702, '60300.00'),
(219, 'Boards & Toys Co.', 'Young', 'Mary', '3105552373', '4097 Douglas Av.', '', 'Glendale', 'CA', '92561', 'USA', 1166, '11000.00'),
(223, 'Natürlich Autos', 'Kloss', 'Horst ', '0372-555188', 'Taucherstraße 10', '', 'Cunewalde', '', '01307', 'Germany', , '0.00'),
(227, 'Heintze Collectables', 'Ibsen', 'Palle', '86 21 3555', 'Smagsloget 45', '', 'Århus', '', '8200', 'Denmark', 1401, '120800.00'),
(233, 'Québec Home Shopping Network', 'Fresnière', 'Jean ', '(514) 555-8054', '43 rue St. Laurent', '', 'Montréal', 'Québec', 'H1J 1C3', 'Canada', 1286, '48700.00'),
(237, 'ANG Resellers', 'Camino', 'Alejandra ', '(91) 745 6555', 'Gran Vía, 1', '', 'Madrid', '', '28001', 'Spain', , '0.00'),
(239, 'Collectable Mini Designs Co.', 'Thompson', 'Valarie', '7605558146', '361 Furth Circle', '', 'San Diego', 'CA', '91217', 'USA', 1166, '105000.00'),
(240, 'giftsbymail.co.uk', 'Bennett', 'Helen ', '(198) 555-8888', 'Garden House', 'Crowther Way 23', 'Cowes', 'Isle of Wight', 'PO31 7PJ', 'UK', 1501, '93900.00'),
(242, 'Alpha Cognac', 'Roulet', 'Annette ', '61.77.6555', '1 rue Alsace-Lorraine', '', 'Toulouse', '', '31000', 'France', 1370, '61100.00'),
(247, 'Messner Shopping Network', 'Messner', 'Renate ', '069-0555984', 'Magazinweg 7', '', 'Frankfurt', '', '60528', 'Germany', , '0.00'),
(249, 'Amica Models & Co.', 'Accorti', 'Paolo ', '011-4988555', 'Via Monte Bianco 34', '', 'Torino', '', '10100', 'Italy', 1401, '113000.00'),
(250, 'Lyon Souveniers', 'Da Silva', 'Daniel', '+33 1 46 62 7555', '27 rue du Colonel Pierre Avia', '', 'Paris', '', '75508', 'France', 1337, '68100.00'),
(256, 'Auto Associés & Cie.', 'Tonini', 'Daniel ', '30.59.8555', '67, avenue de l'Europe', '', 'Versailles', '', '78000', 'France', 1370, '77900.00'),
(259, 'Toms Spezialitäten, Ltd', 'Pfalzheim', 'Henriette ', '0221-5554327', 'Mehrheimerstr. 369', '', 'Köln', '', '50739', 'Germany', 1504, '120400.00'),
(260, 'Royal Canadian Collectables, Ltd.', 'Lincoln', 'Elizabeth ', '(604) 555-4555', '23 Tsawassen Blvd.', '', 'Tsawassen', 'BC', 'T2F 8M4', 'Canada', 1323, '89600.00'),
(273, 'Franken Gifts, Co', 'Franken', 'Peter ', '089-0877555', 'Berliner Platz 43', '', 'München', '', '80805', 'Germany', , '0.00'),
(276, 'Anna's Decorations, Ltd', 'O'Hara', 'Anna', '02 9936 8555', '201 Miller Street', 'Level 15', 'North Sydney', 'NSW', '2060', 'Australia', 1611, '107800.00'),
(278, 'Rovelli Gifts', 'Rovelli', 'Giovanni ', '035-640555', 'Via Ludovico il Moro 22', '', 'Bergamo', '', '24100', 'Italy', 1401, '119600.00'),
(282, 'Souveniers And Things Co.', 'Huxley', 'Adrian', '+61 2 9495 8555', 'Monitor Money Building', '815 Pacific Hwy', 'Chatswood', 'NSW', '2067', 'Australia', 1611, '93300.00'),
(286, 'Marta's Replicas Co.', 'Hernandez', 'Marta', '6175558555', '39323 Spinnaker Dr.', '', 'Cambridge', 'MA', '51247', 'USA', 1216, '123700.00'),
(293, 'BG&E Collectables', 'Harrison', 'Ed', '+41 26 425 50 01', 'Rte des Arsenaux 41 ', '', 'Fribourg', '', '1700', 'Switzerland', , '0.00'),
(298, 'Vida Sport, Ltd', 'Holz', 'Mihael', '0897-034555', 'Grenzacherweg 237', '', 'Genève', '', '1203', 'Switzerland', 1702, '141300.00'),
(299, 'Norway Gifts By Mail, Co.', 'Klaeboe', 'Jan', '+47 2212 1555', 'Drammensveien 126A', 'PB 211 Sentrum', 'Oslo', '', 'N 0106', 'Norway ', 1504, '95100.00'),
(303, 'Schuyler Imports', 'Schuyler', 'Bradley', '+31 20 491 9555', 'Kingsfordweg 151', '', 'Amsterdam', '', '1043 GR', 'Netherlands', , '0.00'),
(307, 'Der Hund Imports', 'Andersen', 'Mel', '030-0074555', 'Obere Str. 57', '', 'Berlin', '', '12209', 'Germany', , '0.00'),
(311, 'Oulu Toy Supplies, Inc.', 'Koskitalo', 'Pirkko', '981-443655', 'Torikatu 38', '', 'Oulu', '', '90110', 'Finland', 1501, '90500.00'),
(314, 'Petit Auto', 'Dewey', 'Catherine ', '(02) 5554 67', 'Rue Joseph-Bens 532', '', 'Bruxelles', '', 'B-1180', 'Belgium', 1401, '79900.00'),
(319, 'Mini Classics', 'Frick', 'Steve', '9145554562', '3758 North Pendale Street', '', 'White Plains', 'NY', '24067', 'USA', 1323, '102700.00'),
(320, 'Mini Creations Ltd.', 'Huang', 'Wing', '5085559555', '4575 Hillside Dr.', '', 'New Bedford', 'MA', '50553', 'USA', 1188, '94500.00'),
(321, 'Corporate Gift Ideas Co.', 'Brown', 'Julie', '6505551386', '7734 Strong St.', '', 'San Francisco', 'CA', '94217', 'USA', 1165, '105000.00'),
(323, 'Down Under Souveniers, Inc', 'Graham', 'Mike', '+64 9 312 5555', '162-164 Grafton Road', 'Level 2', 'Auckland ', '', '', 'New Zealand', 1612, '88000.00'),
(324, 'Stylish Desk Decors, Co.', 'Brown', 'Ann ', '(171) 555-0297', '35 King George', '', 'London', '', 'WX3 6FW', 'UK', 1501, '77000.00'),
(328, 'Tekni Collectables Inc.', 'Brown', 'William', '2015559350', '7476 Moss Rd.', '', 'Newark', 'NJ', '94019', 'USA', 1323, '43000.00'),
(333, 'Australian Gift Network, Co', 'Calaghan', 'Ben', '61-7-3844-6555', '31 Duncan St. West End', '', 'South Brisbane', 'Queensland', '4101', 'Australia', 1611, '51600.00'),
(334, 'Suominen Souveniers', 'Suominen', 'Kalle', '+358 9 8045 555', 'Software Engineering Center', 'SEC Oy', 'Espoo', '', 'FIN-02271', 'Finland', 1501, '98800.00'),
(335, 'Cramer Spezialitäten, Ltd', 'Cramer', 'Philip ', '0555-09555', 'Maubelstr. 90', '', 'Brandenburg', '', '14776', 'Germany', , '0.00'),
(339, 'Classic Gift Ideas, Inc', 'Cervantes', 'Francisca', '2155554695', '782 First Street', '', 'Philadelphia', 'PA', '71270', 'USA', 1188, '81100.00'),
(344, 'CAF Imports', 'Fernandez', 'Jesus', '+34 913 728 555', 'Merchants House', '27-30 Merchant's Quay', 'Madrid', '', '28023', 'Spain', 1702, '59600.00'),
(347, 'Men 'R' US Retailers, Ltd.', 'Chandler', 'Brian', '2155554369', '6047 Douglas Av.', '', 'Los Angeles', 'CA', '91003', 'USA', 1166, '57700.00'),
(348, 'Asian Treasures, Inc.', 'McKenna', 'Patricia ', '2967 555', '8 Johnstown Road', '', 'Cork', 'Co. Cork', '', 'Ireland', , '0.00'),
(350, 'Marseille Mini Autos', 'Lebihan', 'Laurence ', '91.24.4555', '12, rue des Bouchers', '', 'Marseille', '', '13008', 'France', 1337, '65000.00'),
(353, 'Reims Collectables', 'Henriot', 'Paul ', '26.47.1555', '59 rue de l'Abbaye', '', 'Reims', '', '51100', 'France', 1337, '81100.00'),
(356, 'SAR Distributors, Co', 'Kuger', 'Armand', '+27 21 550 3555', '1250 Pretorius Street', '', 'Hatfield', 'Pretoria', '0028', 'South Africa', , '0.00'),
(357, 'GiftsForHim.com', 'MacKinlay', 'Wales', '64-9-3763555', '199 Great North Road', '', 'Auckland', '', '', 'New Zealand', 1612, '77700.00'),
(361, 'Kommission Auto', 'Josephs', 'Karin', '0251-555259', 'Luisenstr. 48', '', 'Münster', '', '44087', 'Germany', , '0.00'),
(362, 'Gifts4AllAges.com', 'Yoshido', 'Juri', '6175559555', '8616 Spinnaker Dr.', '', 'Boston', 'MA', '51003', 'USA', 1216, '41900.00'),
(363, 'Online Diecast Creations Co.', 'Young', 'Dorothy', '6035558647', '2304 Long Airport Avenue', '', 'Nashua', 'NH', '62005', 'USA', 1216, '114200.00'),
(369, 'Lisboa Souveniers, Inc', 'Rodriguez', 'Lino ', '(1) 354-2555', 'Jardim das rosas n. 32', '', 'Lisboa', '', '1675', 'Portugal', , '0.00'),
(376, 'Precious Collectables', 'Urs', 'Braun', '0452-076555', 'Hauptstr. 29', '', 'Bern', '', '3012', 'Switzerland', 1702, '0.00'),
(379, 'Collectables For Less Inc.', 'Nelson', 'Allen', '6175558555', '7825 Douglas Av.', '', 'Brickhaven', 'MA', '58339', 'USA', 1188, '70700.00'),
(381, 'Royale Belge', 'Cartrain', 'Pascale ', '(071) 23 67 2555', 'Boulevard Tirou, 255', '', 'Charleroi', '', 'B-6000', 'Belgium', 1401, '23500.00'),
(382, 'Salzburg Collectables', 'Pipps', 'Georg ', '6562-9555', 'Geislweg 14', '', 'Salzburg', '', '5020', 'Austria', 1401, '71700.00'),
(385, 'Cruz & Sons Co.', 'Cruz', 'Arnold', '+63 2 555 3587', '15 McCallum Street', 'NatWest Center #13-03', 'Makati City', '', '1227 MM', 'Philippines', 1621, '81500.00'),
(386, 'L'ordine Souveniers', 'Moroni', 'Maurizio ', '0522-556555', 'Strada Provinciale 124', '', 'Reggio Emilia', '', '42100', 'Italy', 1401, '121400.00'),
(398, 'Tokyo Collectables, Ltd', 'Shimamura', 'Akiko', '+81 3 3584 0555', '2-2-8 Roppongi', '', 'Minato-ku', 'Tokyo', '106-0032', 'Japan', 1621, '94400.00'),
(406, 'Auto Canal+ Petit', 'Perrier', 'Dominique', '(1) 47.55.6555', '25, rue Lauriston', '', 'Paris', '', '75016', 'France', 1337, '95000.00'),
(409, 'Stuttgart Collectable Exchange', 'Müller', 'Rita ', '0711-555361', 'Adenauerallee 900', '', 'Stuttgart', '', '70563', 'Germany', , '0.00'),
(412, 'Extreme Desk Decorations, Ltd', 'McRoy', 'Sarah', '04 499 9555', '101 Lambton Quay', 'Level 11', 'Wellington', '', '', 'New Zealand', 1612, '86800.00'),
(415, 'Bavarian Collectables Imports, Co.', 'Donnermeyer', 'Michael', ' +49 89 61 08 9555', 'Hansastr. 15', '', 'Munich', '', '80686', 'Germany', 1504, '77000.00'),
(424, 'Classic Legends Inc.', 'Hernandez', 'Maria', '2125558493', '5905 Pompton St.', 'Suite 750', 'NYC', 'NY', '10022', 'USA', 1286, '67500.00'),
(443, 'Feuer Online Stores, Inc', 'Feuer', 'Alexander ', '0342-555176', 'Heerstr. 22', '', 'Leipzig', '', '04179', 'Germany', , '0.00'),
(447, 'Gift Ideas Corp.', 'Lewis', 'Dan', '2035554407', '2440 Pompton St.', '', 'Glendale', 'CT', '97561', 'USA', 1323, '49700.00'),
(448, 'Scandinavian Gift Ideas', 'Larsson', 'Martha', '0695-34 6555', 'Åkergatan 24', '', 'Bräcke', '', 'S-844 67', 'Sweden', 1504, '116400.00'),
(450, 'The Sharp Gifts Warehouse', 'Frick', 'Sue', '4085553659', '3086 Ingle Ln.', '', 'San Jose', 'CA', '94217', 'USA', 1165, '77600.00'),
(452, 'Mini Auto Werke', 'Mendel', 'Roland ', '7675-3555', 'Kirchgasse 6', '', 'Graz', '', '8010', 'Austria', 1401, '45300.00'),
(455, 'Super Scale Inc.', 'Murphy', 'Leslie', '2035559545', '567 North Pendale Street', '', 'New Haven', 'CT', '97823', 'USA', 1286, '95400.00'),
(456, 'Microscale Inc.', 'Choi', 'Yu', '2125551957', '5290 North Pendale Street', 'Suite 200', 'NYC', 'NY', '10022', 'USA', 1286, '39800.00'),
(458, 'Corrida Auto Replicas, Ltd', 'Sommer', 'Martín ', '(91) 555 22 82', 'C/ Araquil, 67', '', 'Madrid', '', '28023', 'Spain', 1702, '104600.00'),
(459, 'Warburg Exchange', 'Ottlieb', 'Sven ', '0241-039123', 'Walserweg 21', '', 'Aachen', '', '52066', 'Germany', , '0.00'),
(462, 'FunGiftIdeas.com', 'Benitez', 'Violeta', '5085552555', '1785 First Street', '', 'New Bedford', 'MA', '50553', 'USA', 1216, '85800.00'),
(465, 'Anton Designs, Ltd.', 'Anton', 'Carmen', '+34 913 728555', 'c/ Gobelas, 19-1 Urb. La Florida', '', 'Madrid', '', '28023', 'Spain', , '0.00'),
(471, 'Australian Collectables, Ltd', 'Clenahan', 'Sean', '61-9-3844-6555', '7 Allen Street', '', 'Glen Waverly', 'Victoria', '3150', 'Australia', 1611, '60300.00'),
(473, 'Frau da Collezione', 'Ricotti', 'Franco', '+39 022515555', '20093 Cologno Monzese', 'Alessandro Volta 16', 'Milan', '', '', 'Italy', 1401, '34800.00'),
(475, 'West Coast Collectables Co.', 'Thompson', 'Steve', '3105553722', '3675 Furth Circle', '', 'Burbank', 'CA', '94019', 'USA', 1166, '55400.00'),
(477, 'Mit Vergnügen & Co.', 'Moos', 'Hanna ', '0621-08555', 'Forsterstr. 57', '', 'Mannheim', '', '68306', 'Germany', , '0.00'),
(480, 'Kremlin Collectables, Co.', 'Semenov', 'Alexander ', '+7 812 293 0521', '2 Pobedy Square', '', 'Saint Petersburg', '', '196143', 'Russia', , '0.00'),
(481, 'Raanan Stores, Inc', 'Altagar,G M', 'Raanan', '+ 972 9 959 8555', '3 Hagalim Blv.', '', 'Herzlia', '', '47625', 'Israel', , '0.00'),
(484, 'Iberia Gift Imports, Corp.', 'Roel', 'José Pedro ', '(95) 555 82 82', 'C/ Romero, 33', '', 'Sevilla', '', '41101', 'Spain', 1702, '65700.00'),
(486, 'Motor Mint Distributors Inc.', 'Salazar', 'Rosa', '2155559857', '11328 Douglas Av.', '', 'Philadelphia', 'PA', '71270', 'USA', 1323, '72600.00'),
(487, 'Signal Collectibles Ltd.', 'Taylor', 'Sue', '4155554312', '2793 Furth Circle', '', 'Brisbane', 'CA', '94217', 'USA', 1165, '60300.00'),
(489, 'Double Decker Gift Stores, Ltd', 'Smith', 'Thomas ', '(171) 555-7555', '120 Hanover Sq.', '', 'London', '', 'WA1 1DP', 'UK', 1501, '43300.00'),
(495, 'Diecast Collectables', 'Franco', 'Valarie', '6175552555', '6251 Ingle Ln.', '', 'Boston', 'MA', '51003', 'USA', 1188, '85100.00'),
(496, 'Kelly's Gift Shop', 'Snowden', 'Tony', '+64 9 5555500', 'Arenales 1938 3'A'', '', 'Auckland ', '', '', 'New Zealand', 1612, '110000.00');

In this step, I have created a sample database and customers table to implement our pagination script. I have attached this sample database file in the download section below. You can easily import it into your MYSQL database.


2. Basic HTML and CSS (Index.php)  


In this step, I have created a basic structure to display our PHP pagination. At first, I have used Bootstrap for designing part and for navigation links I have used my own responsive pagination stylesheet. I have used a table to display results and navigation links. Here is the code –


<!DOCTYPE html>
<head>
<title>PHP Pagination</title>

<style type="text/css">
.pagination {
margin: 0;
padding: 0;
text-align: center;
width: 100%;
display: inline-block !important;
}
.pagination ul {
margin: 0;
padding: 0;
border: 0;
outline: 0;
vertical-align: baseline;
background: 0 0;
}
.pagination li {
display: inline;
margin: 0;
padding: 0;
}
.pagination li a {
border: 1px solid #c9c9c9;
color: #242424;
text-decoration: none;
display: inline-block;
transition: all .8s ease 0s;
height: 40px;
line-height: 40px;
margin: 0 10px 0 0;
padding: 0;
text-align: center;
width: 40px;
margin-top: 10px;
}
.pagination li a:hover {
background-color: #4CAF50;
color: white;
}
.pagination li.active a {
background-color: #4CAF50;
color: white;
}
.pagination li.arrows a {
background-color: #4C5BAF;
color: white;
font-size: 14px;
}

</style>
</head>
<body>
<div class="container-fluid">
<br />
<div class="col-sm-12 text-center">
<div class="row">
<a href="https://www.learningoceanteam.com/pagination-in-php" class="btn btn-success">Visit This Tutorial</a>
</div>
</div>
<div class="col-md-12 ">


<br /><br />
<div class="col-md-12">
<div class="row">

<div class="col-md-6">
<h1 style="font-size: 20px;font-weight:bold;">PHP Pagination</h1>
</div>
<div class="col-md-6">
<!-- Results Limit Change -->
</div>
</div>
</div>
<br />
<div class="table-responsive">
<table class="table table-striped table-bordered table-rounded">
<thead>
<tr>
<th>Sl no.</th>
<th>customerName</th>
<th width="20%">Phone</th>
<th width="20%">City</th>
<th width="25%">State</th>
</tr>
</thead>

<tbody>
<!-- Results -->
</tbody>
<tfoot>
<!-- Pagination Links-->
</tfoot>
</table>
</div>
</div>
</div>
</body>
</html>

3. Connecting to database using PHP (MYSQLI or PDO)


I have created both database connection option for MYSQLI and PDO users. If you are using MYSQLI connection then just set $connection_type to MySQLI or if you are using PDO connection then set $connection_type to PDO, that’s it.


$connection_type = 'mysqli';
if ($connection_type == 'mysqli') {
$con = mysqli_connect("localhost", "root", "", "pagination_table") or die(mysqli_connect_errno());
} else if ($connection_type == 'pdo') {
try {
$con = new PDO("mysql:host=localhost;dbname=pagination_table", "root", "");
} catch(PDOException $e) {
print "Error!" . $e -> getMessage();
die();
}
} else {
echo "No Database Connection!";
die();
}

4. Pagination Formula: Setting pagination limits, active page, navigation link gapping


Here I have used $limit to count per page result limits like 5 rows per page or 25 rows per page etc. $page used to get active page number to display results. $links used to display the number of additional navigation links besides of active link. It means if your active page is 5 and you want to display users navigation links 4, 5, 6 then you have to set $links to 1.


$limit = ( isset($_GET['limit'])) ? $_GET['limit'] : 5;
if (strtolower($limit) == 'all') {
$limit = 'all';
} else {
$limit = filter_var($limit, FILTER_SANITIZE_NUMBER_INT);
if (trim($limit) == '') {
$limit = 5;
}
}

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

$links = ( isset($_GET['links'])) ? $_GET['links'] : 1;
$links = filter_var($links, FILTER_SANITIZE_NUMBER_INT);

5. Include PHP Pagination class (paginator_class.php)


Load the pagination class and pass connection type variable, database connection variable and basic MySQL query string without any limitation.


require_once 'paginator_class.php';
$query = "SELECT * FROM customers";
$Paginator = new Paginator($connection_type, $con, $query);

6. Retrieving Results from database


After loading pagination class, it’s time to fetching data from database using one simple method of the class called getData(). You have to pass pagination limit count and active page number into method. If you pass limit = 10 and page = 4, then It means you are fetching the 10 rows from the database for the fourth page. Stored the results in $results variable.


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

7. Displaying Results


Now displaying the results using for loop. If $results count value is 0 then displaying No data found. To display data just use $results->data variable.


<tbody>


<?php

if(count( $results->data )>0)
{
if($limit!='all')
{
$vs= (($limit * $page) - $limit) + 1;
}
else {
$vs=1;
}

for( $i = 0; $i < count( $results->data ); $i++ ) { ?>
<tr>
<td><?php echo $vs; ?></td>
<td><?php echo $results -> data[$i]['customerName']; ?></td>
<td><?php echo $results -> data[$i]['phone']; ?></td>
<td><?php echo $results -> data[$i]['city']; ?></td>
<td><?php echo $results -> data[$i]['state']; ?></td>
</tr>
<?php
$vs++;
}
}
else {
echo "<tr><td colspan='5'>No data found</td></tr>";
}
?>
</tbody>

8. Displaying Pagination Links


To display pagination links, you have to call createPaginationLinks() method of the class. Here I have created both options for simple pagination links and advanced pagination links with next and previous options. You have to pass pagination links gapping count called $links and pagination mode like simple or advanced into this method. Here are both simple and advanced pagination links demo –


Simple pagination links


<tfoot>
<tr>
<th colspan="5">
<div class="pagination text-center">
<?php echo $Paginator -> createPaginationLinks($links, 'simple'); ?>
</div>
</th>
</tr>
</tfoot>

Output - 


Simple Pagination in PHP MySQL


Advanced pagination links


<tfoot>
<tr>
<th colspan="5">
<div class="pagination text-center">
<?php echo $Paginator -> createPaginationLinks($links, 'advanced'); ?>
</div>
</th>
</tr>
</tfoot>

Output -


Advanced pagination in PHP MySQL


9. The user can change the number of results per page


 Here I have created an additional option for users to flexible this PHP pagination script. User can change the number of display results per page from the drop-down menu. To display the drop-down menu, use this code –


<div class="col-md-6 text-right">
<b>Results Shown:</b> <select onchange="changelimit(this.value)" class="col-sm-2">
<?php
$limits = array(5, 10, 15, 20, 25, 50, 100, 'all');
foreach ($limits as $key => $value) {
if ($value == $limit) {
echo '<option selected="selected" value="' . $value . '">' . ucfirst($value) . '</option>';
} else {
echo '<option value="' . $value . '">' . ucfirst($value) . '</option>';
}
}
?>

</select>
</div>

To change the value of per page result, use this JavaScript code –


function changelimit(v) {
window.location.href = "index.php?limit=" + v + "&page=1";
return false;
}

Let's assemble all the codes in one page

<?php
$connection_type = 'mysqli';
if ($connection_type == 'mysqli') {
$con = mysqli_connect("localhost", "root", "", "pagination_table") or die(mysqli_connect_errno());
} else if ($connection_type == 'pdo') {
try {
$con = new PDO("mysql:host=localhost;dbname=pagination_table", "root", "");
} catch(PDOException $e) {
print "Error!" . $e -> getMessage();
die();
}
} else {
echo "No Database Connection!";
die();
}

$limit = ( isset($_GET['limit'])) ? $_GET['limit'] : 5;
if (strtolower($limit) == 'all') {
$limit = 'all';
} else {
$limit = filter_var($limit, FILTER_SANITIZE_NUMBER_INT);
if (trim($limit) == '') {
$limit = 5;
}
}

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

$links = ( isset($_GET['links'])) ? $_GET['links'] : 1;
$links = filter_var($links, FILTER_SANITIZE_NUMBER_INT);


require_once 'paginator_class.php';
$query = "SELECT * FROM customers";
$Paginator = new Paginator($connection_type, $con, $query);


$results = $Paginator -> getData($limit, $page);
?>
<!DOCTYPE html>
<head>
<title>PHP Pagination</title>

<style type="text/css">
.pagination {
margin: 0;
padding: 0;
text-align: center;
width: 100%;
display: inline-block !important;
}
.pagination ul {
margin: 0;
padding: 0;
border: 0;
outline: 0;
vertical-align: baseline;
background: 0 0;
}
.pagination li {
display: inline;
margin: 0;
padding: 0;
}
.pagination li a {
border: 1px solid #c9c9c9;
color: #242424;
text-decoration: none;
display: inline-block;
transition: all .8s ease 0s;
height: 40px;
line-height: 40px;
margin: 0 10px 0 0;
padding: 0;
text-align: center;
width: 40px;
margin-top: 10px;
}
.pagination li a:hover {
background-color: #4CAF50;
color: white;
}
.pagination li.active a {
background-color: #4CAF50;
color: white;
}
.pagination li.arrows a {
background-color: #4C5BAF;
color: white;
font-size: 14px;
}

</style>
<script>
function changelimit(v) {
window.location.href = "index.php?limit=" + v + "&page=1";
return false;
}
</script>
</head>
<body>
<div class="container-fluid">
<br />
<div class="col-sm-12 text-center">
<div class="row">
<a href="https://www.learningoceanteam.com/pagination-in-php" class="btn btn-success">Visit This Tutorial</a>
</div>
</div>
<div class="col-md-12 ">


<br /><br />
<div class="col-md-12">
<div class="row">

<div class="col-md-6">
<h1 style="font-size: 20px;font-weight:bold;">PHP Pagination</h1>
</div>
<div class="col-md-6 text-right">
<b>Results Shown:</b> <select onchange="changelimit(this.value)" class="col-sm-2">
<?php
$limits = array(5, 10, 15, 20, 25, 50, 100, 'all');
foreach ($limits as $key => $value) {
if ($value == $limit) {
echo '<option selected="selected" value="' . $value . '">' . ucfirst($value) . '</option>';
} else {
echo '<option value="' . $value . '">' . ucfirst($value) . '</option>';
}
}
?>

</select>
</div>
</div>
</div>
<br />
<div class="table-responsive">
<table class="table table-striped table-bordered table-rounded">
<thead>
<tr>
<th>Sl no.</th>
<th>customerName</th>
<th width="20%">Phone</th>
<th width="20%">City</th>
<th width="25%">State</th>
</tr>
</thead>

<tbody>


<?php

if(count( $results->data )>0)
{
if($limit!='all')
{
$vs= (($limit * $page) - $limit) + 1;
}
else {
$vs=1;
}

for( $i = 0; $i < count( $results->data ); $i++ ) { ?>
<tr>
<td><?php echo $vs; ?></td>
<td><?php echo $results -> data[$i]['customerName']; ?></td>
<td><?php echo $results -> data[$i]['phone']; ?></td>
<td><?php echo $results -> data[$i]['city']; ?></td>
<td><?php echo $results -> data[$i]['state']; ?></td>
</tr>
<?php
$vs++;
}
}
else {
echo "<tr><td colspan='5'>No data found</td></tr>";
}
?>
</tbody>
<tfoot>
<tr>
<th colspan="5">
<div class="pagination text-center">
<?php echo $Paginator -> createPaginationLinks($links, 'advanced'); ?>
</div>
</th>
</tr>
</tfoot>
</table>
</div>
</div>
</div>
</body>
</html>


My Words

By following the above steps, you can easily implement any kind of PHP pagination easily. I am using this pagination script into my every project that’s why I am sharing with you.


Please do not hesitate to leave questions or comments in the comment box below. And if you like this tutorial please share it with your friends via Email or Social Media.


If you need any help or more information you can easily contact me. I will definitely help you. To get faster response (within 1 hour) please send message on our official FB Page.







Download Code

Please send us a message on Our Facebook Page to get the source code of this tutorial






Share Now With Your Friends & Family:




Please Leave a ReplyCancel Reply


Name
Email
Website
Comment
Ratings
   

  • Ratings: 4/5

    Please where can I find the paginator class?
    reply
    • learningoceanteam.com logo   

      Hi Imieni, hope you are doing well. Please send a message on Facebook Page with you email. I will send you the full source code of this tutorial.
      reply
  • Ratings: 5/5

    Hey Souradeep really nice tutorial. Can you make another tutorial on Pagination with Datatable? I am developing a website and i want to implement datatable into that website. So please make another tutorial on that topic.
    reply
  • Ratings: 5/5

    This tutorial is really good, thanks Learningocean. Thanks a lot for sharing such good script.I am new to PHP. Please send me download script in mail.
    Thanks
    reply
  • Ratings: 5/5

    Nice Tutorial! You have included all the options. But I think it would be better if you use Jquery.
    reply
    • learningoceanteam.com logo   

      Hi Ishaan, Thanx for your feedback. Yes I have created a pagination script using jQuery.
      reply
  • Ratings: 5/5

    Great tutorial. The way you have explained is nice and waiting for next pagination tutorials.
    reply
    • learningoceanteam.com logo   

      Thank you Rohit. You can subscribe my newsletter to get upcoming tutorial alert.
      reply


Follow Us
About Me
author souradeep debnath

Hi, I am Souradeep Debnath and I am really happy that you are here. Here I share tutorial topics on technology.





Live Chat


souradeep debnath live facebook