简单分页php,PHP：简单分页

This article shows how to create a simple pagination in PHP and MySQL.

In the example code present in this article:

– We will be fetching data from a MySQL database table.

– We will be showing certain rows of data in first page and other rows will be displayed in next pages.

– We will show the page numbers at the bottom of the page.

Let’s suppose our database is test. Let’s create a MySQL database table named users inside database test.

1

2

3

4

5

6

7

8

9

10

11

--

-- Table structure for table `users`

--

CREATETABLEIF NOT EXISTS`users`(

`id`int(11)NOT NULLAUTO_INCREMENT,

`name`varchar(100)NOT NULL,

`age`int(3)NOT NULL,

`email`varchar(100)NOT NULL,

PRIMARY KEY(`id`)

)ENGINE=InnoDBDEFAULTCHARSET=latin1AUTO_INCREMENT=20;

Let’s add/insert some data in table users

1

2

3

4

5

6

7

8

9

10

11

12

--

-- Inserting data for table `users`

--

INSERTINTO`users`(`id`,`name`,`age`,`email`)VALUES

(1,'Anuradha Koirala',66,'anuradha@example.com'),

(2,'Appa Sherpa',55,'apa.sherpa@example.com'),

(3,'Mahabir Pun',54,'mahabir@example.com'),

(4,'Pushpa Basnet',25,'pushpa@example.com'),

(5,'Laxmi Prasad Devkota',88,'laxmi@example.com'),

(6,'Narayan Gopal',68,'gopal@example.com'),

(7,'Paras Khadka',29,'pkhadka@example.com')

Here are the steps to be followed in the PHP code:

1. Connect to the MySQL database table

2. Set a row limit for each page, i.e. number of rows to show on each page

3. Identify page number and offset

4. Count the total number of rows in database table

5. Determine the total number of pages to be displayed

6. Fetch row data from database table using the offset and limit as determined in step 2 and 3 above.

7. Display/Show data

8. Display/Show page numbers using number of pages as determined in step 5 above.

Here’s the full source 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

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

// DATABASE CONNECTION

$databaseHost='localhost';

$databaseName='test';

$databaseUsername='root';

$databasePassword='root';

$mysqli=mysqli_connect($databaseHost,$databaseUsername,$databasePassword,$databaseName);

// NUMBER OF ROWS TO SHOW PER PAGE

$limit=3;

// GET PAGE AND OFFSET VALUE

if(isset($_GET['page'])){

$page=$_GET['page']-1;

$offset=$page*$limit;

}else{

$page=0;

$offset=0;

}

// COUNT TOTAL NUMBER OF ROWS IN TABLE

$sql="SELECT count(id) FROM users";

$result=mysqli_query($mysqli,$sql);

$row=mysqli_fetch_array($result);

$total_rows=$row[0];

// DETERMINE NUMBER OF PAGES

if($total_rows>$limit){

$number_of_pages=ceil($total_rows/$limit);

}else{

$pages=1;

$number_of_pages=1;

}

// FETCH DATA USING OFFSET AND LIMIT

$result=mysqli_query($mysqli,"SELECT * FROM users ORDER BY id DESC LIMIT $offset, $limit");

?>

HomepageNameAgeEmail

while($res=mysqli_fetch_array($result)){

echo"

";

echo"

".$res['name']."";

echo"

".$res['age']."";

echo"

".$res['email']."";

}

?>

// SHOW PAGE NUMBERS

if($page){

echo"First ";

}

for($i=1;$i<=$number_of_pages;$i++){

echo"".$i." ";

}

if(($page+1)!=$number_of_pages){

echo"Last ";

}

?>

Hope this helps. Thanks.

Share this: