15 regels van database tonen en kunnen doorklikken naar de volgende 15
hoe doe je dat?
Dan voeg je aan je query toe :
LIMIT *offset , *length
Dus paar voorbeelden :
pagina 1 LIMIT 0,15
pagina 2 LIMIT 15,15
pagina 5 LIMIT 60,15
Het internet is hierover ook weer (zoals altijd) verdeeld in twee kampen waarbij de ene partij deze voorkeur deelt, en een partij die zegt dat je net zo goed dezelfde query (zonder limit) nog een keer zou kunnen uitvoeren. En beide partijen claimen dat "hun" oplossing efficienter is :s.
En ja, die paginering zal je dus in PHP of een andere (scripting)taal moeten bouwen.
Ik zal eens een voorbeeldje zoeken / maken.
EDIT: zoiets dus:
Code (php)
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
<?php
// assumptions
// - you have a local database running under database, user and password "test"
// - you have a config table you read entries from:
/*
CREATE TABLE `config` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`_key` varchar(255) NOT NULL,
`_value` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `_key` (`_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
// debugging
error_reporting(E_ALL);
ini_set('display_errors', 'stdout');
// helper functions
function escape($in) {
return htmlspecialchars($in, ENT_QUOTES, 'UTF-8');
}
function isIndex($in) {
// note: don't forget to trim or typecast (note that this is warranted when function returns true)
return preg_match('#^[1-9][0-9]*$#', $in) === 1;
}
// connect
$db = new mysqli('127.0.0.1', 'test', 'test', 'test');
$db->set_charset('utf8');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>paging test</title>
<style type="text/css">
div.pagination { }
div.pagination > div { float: left; display: block; width: 50px; height: 50px; text-align: center; line-height: 50px; background-color: #ffcccc; margin: 5px; }
div.pagination > div.active { background-color: #ff6666; }
div.pagination > div a { display: block; width: 100%; height: 100%; text-decoration: none; color: #000000; }
div.pagination > div a:hover { background-color: #ffaaaa; }
div.clear { clear: both; }
</style>
</head>
<body><?php
// determine page - starting from page zero
$currentPage = isset($_GET['p']) && isIndex($_GET['p']) ? (int) $_GET['p'] : 0;
// limit items per page
$limit = 5;
// determine item offset
$offset = $currentPage * $limit;
// get items of current page, notice the SQL_CALC_FOUND_ROWS flag
$results = $db->query(
'SELECT SQL_CALC_FOUND_ROWS id, _key, _value
FROM config
ORDER BY id
LIMIT '.$offset.', '.$limit
);
// determine total # items and calculate # pages
$tmp = $db->query('SELECT FOUND_ROWS()')->fetch_row();
$totalItems = $tmp[0];
$totalPages = ceil($totalItems / $limit);
// print navigation - only when there is something to print
if ($totalPages > 1) {
?><div class="pagination"><?php
// previous page?
if ($currentPage > 0) {
?><div><a href="?p=<?php echo escape($currentPage - 1) ?>"><</a></div><?php
} else {
?><div> </div><?php
}
for ($p = 0; $p < $totalPages; $p++) {
$currentActive = $p == $currentPage;
?><div<?php echo ($currentActive ? ' class="active"' : '') ?>><?php
if ($currentActive) {
// display "natural" page numbers, starting from 1
?><strong><?php echo escape($p + 1) ?></strong><?php
} else {
?><a href="?p=<?php echo escape($p) ?>"><?php echo escape($p + 1) ?></a><?php
}
?></div><?php
}
// next page?
if ($currentPage + 1 < $totalPages) {
?><div><a href="?p=<?php echo escape($currentPage + 1) ?>">></a></div><?php
} else {
?><div> </div><?php
}
?></div>
<div class="clear"><!-- clear --></div><?php
}
// print items
if ($results->num_rows) {
?><ul><?php
while ($row = $results->fetch_assoc()) {
?><li><strong><?php echo escape($row['_key']) ?></strong> - <?php echo escape($row['_value']) ?></li><?php
}
?></ul><?php
} else {
?><p>No results (for this page)</p><?php
}
$results->free();
?></body>
</html>
// assumptions
// - you have a local database running under database, user and password "test"
// - you have a config table you read entries from:
/*
CREATE TABLE `config` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`_key` varchar(255) NOT NULL,
`_value` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `_key` (`_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
// debugging
error_reporting(E_ALL);
ini_set('display_errors', 'stdout');
// helper functions
function escape($in) {
return htmlspecialchars($in, ENT_QUOTES, 'UTF-8');
}
function isIndex($in) {
// note: don't forget to trim or typecast (note that this is warranted when function returns true)
return preg_match('#^[1-9][0-9]*$#', $in) === 1;
}
// connect
$db = new mysqli('127.0.0.1', 'test', 'test', 'test');
$db->set_charset('utf8');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>paging test</title>
<style type="text/css">
div.pagination { }
div.pagination > div { float: left; display: block; width: 50px; height: 50px; text-align: center; line-height: 50px; background-color: #ffcccc; margin: 5px; }
div.pagination > div.active { background-color: #ff6666; }
div.pagination > div a { display: block; width: 100%; height: 100%; text-decoration: none; color: #000000; }
div.pagination > div a:hover { background-color: #ffaaaa; }
div.clear { clear: both; }
</style>
</head>
<body><?php
// determine page - starting from page zero
$currentPage = isset($_GET['p']) && isIndex($_GET['p']) ? (int) $_GET['p'] : 0;
// limit items per page
$limit = 5;
// determine item offset
$offset = $currentPage * $limit;
// get items of current page, notice the SQL_CALC_FOUND_ROWS flag
$results = $db->query(
'SELECT SQL_CALC_FOUND_ROWS id, _key, _value
FROM config
ORDER BY id
LIMIT '.$offset.', '.$limit
);
// determine total # items and calculate # pages
$tmp = $db->query('SELECT FOUND_ROWS()')->fetch_row();
$totalItems = $tmp[0];
$totalPages = ceil($totalItems / $limit);
// print navigation - only when there is something to print
if ($totalPages > 1) {
?><div class="pagination"><?php
// previous page?
if ($currentPage > 0) {
?><div><a href="?p=<?php echo escape($currentPage - 1) ?>"><</a></div><?php
} else {
?><div> </div><?php
}
for ($p = 0; $p < $totalPages; $p++) {
$currentActive = $p == $currentPage;
?><div<?php echo ($currentActive ? ' class="active"' : '') ?>><?php
if ($currentActive) {
// display "natural" page numbers, starting from 1
?><strong><?php echo escape($p + 1) ?></strong><?php
} else {
?><a href="?p=<?php echo escape($p) ?>"><?php echo escape($p + 1) ?></a><?php
}
?></div><?php
}
// next page?
if ($currentPage + 1 < $totalPages) {
?><div><a href="?p=<?php echo escape($currentPage + 1) ?>">></a></div><?php
} else {
?><div> </div><?php
}
?></div>
<div class="clear"><!-- clear --></div><?php
}
// print items
if ($results->num_rows) {
?><ul><?php
while ($row = $results->fetch_assoc()) {
?><li><strong><?php echo escape($row['_key']) ?></strong> - <?php echo escape($row['_value']) ?></li><?php
}
?></ul><?php
} else {
?><p>No results (for this page)</p><?php
}
$results->free();
?></body>
</html>
Gewijzigd op 14/08/2015 20:50:51 door Thomas van den Heuvel
Thomas van den Heuvel op 14/08/2015 13:28:39:
MySQL heeft ook een handige voorziening waarbij je de LIMIT-query kunt voorzien van een extra "flag" die ervoor zorgt dat je met een tweede vereenvoudigde query snel kunt opvragen hoeveel resultaten er in totaal zouden zijn zonder de limitering.
Een van scripts zoals maarten dat zei, heet DataTables. Ik gebruik het zelf ook.