Paginanummering
Het onderstaande script gebruik ik op een website voor paginanummering.
Echter is het volgens mij niet mogelijk om de query met GROUP BY uit te voeren. Heeft iemand een oplossing hoe dit wel gaat werken?
Bedankt.
--------------------------------------------------------------------------------
PagedQuery.class.php
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
<?php
/**
* Used for building query's witch need pagination.
*/
class PagedQuery
{
/** @var integer Total number of records */
var $total;
/** @var integer Record length of a page */
var $pageSize;
/** @var integer Total pages */
var $pages;
/** @var string Current requested page */
var $currentPage;
/** the variable in the url to indicate the current page. Defaul = 'page' */
var $pageVar;
/**
* Construct a PagedQuery. The $query variable will be replaced by a new
* query wich will allow pagination.
* @param string $query Sql select query
* @param integer $totalRows Number of rows to show on a page
* @param integer $pageVar (optional) The used variable for pagination
*/
function __construct(&$query, $totalRows, $pageVar = "page")
{
// set pageVar
$this->pageVar = $pageVar;
// Get the current page
if (isset($_GET[$pageVar]) && is_numeric($_GET[$pageVar]))
{
$currentPage = $_GET[$pageVar];
}
else
{
$currentPage = 1;
}
$this->pageSize = $totalRows;
$this->currentPage = $currentPage;
$query = strtolower($query);
// Split the query to create a new count query
list($queryStart, $queryEnd) = explode(" from ", $query, 2);
// Create the count query
if (substr_count($queryEnd,"group by ",1) == 1)
$query = "select count(*) from (select count(*) from " . $queryEnd . ") as t1";
else
$query = "select count(*) from " . $queryEnd;
// Get the total rows
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$this->total = $row[0];
// bereken het aantal pagina's
$this->pages = ceil($this->total / $totalRows);
// Create the new query
$offset = ($currentPage - 1) * $totalRows;
$query = $queryStart . " from " . $queryEnd . " limit " . $offset . "," . $totalRows . " ";
}
/**
* Get the base link from the requested url current
* Used to build the page links
*/
public function getBaseUrl()
{
if (!isset($_GET[$this->pageVar]))
{
if ($_SERVER['QUERY_STRING'] == "")
return "?" . $this->pageVar . "=";
else
return "?" . $_SERVER['QUERY_STRING'] . "&" . $this->pageVar . "=";
}
else
{
// replace and append the query string
if (sizeOf($_GET) <= 1)
{
return "?" . $this->pageVar . "=";
}
else
{
// try to replace the var
$newQueryString = str_replace("&" . $this->pageVar . "=" . $_GET[$this->pageVar], "", $_SERVER['QUERY_STRING']);
// try to replace the first var
$newQueryString = str_replace($this->pageVar . "=" . $_GET[$this->pageVar], "", $newQueryString);
// correct & if this is the first char
if(substr($newQueryString,0,1) == "&")
{
$newQueryString = substr($newQueryString,1,strlen($newQueryString)-1);
}
return "?" . $newQueryString . "&" . $this->pageVar . "=";
}
}
}
/**
* Get the url for the first page,
* without the page infor in the url.
* This will prevent urls with duplicate content
* for search engine optimization.
*/
public function getFirstUrl()
{
if (isset($_GET[$this->pageVar]))
{
// try to replace the var
$newQueryString = str_replace("&" . $this->pageVar . "=" . $_GET[$this->pageVar], "", $_SERVER['QUERY_STRING']);
// try to replace the first var
$newQueryString = str_replace($this->pageVar . "=" . $_GET[$this->pageVar], "", $newQueryString);
// strip off last character for correct url
return "?" . $newQueryString;
}
else
{
if ($_SERVER['QUERY_STRING'] != "")
{
return "?" . $_SERVER['QUERY_STRING'];
}
else
return ".";
}
}
/**
* Shorcut function for calling the static
* method pageLinks from class Pagination
*/
public function pageLinks()
{
return Pagination::pageLinks($this);
}
/**
* Shorcut function for calling the static
* method simplePageLinks from class Pagination
*/
public function simplePageLinks()
{
return Pagination::simplePageLinks($this);
}
}
?>
/**
* Used for building query's witch need pagination.
*/
class PagedQuery
{
/** @var integer Total number of records */
var $total;
/** @var integer Record length of a page */
var $pageSize;
/** @var integer Total pages */
var $pages;
/** @var string Current requested page */
var $currentPage;
/** the variable in the url to indicate the current page. Defaul = 'page' */
var $pageVar;
/**
* Construct a PagedQuery. The $query variable will be replaced by a new
* query wich will allow pagination.
* @param string $query Sql select query
* @param integer $totalRows Number of rows to show on a page
* @param integer $pageVar (optional) The used variable for pagination
*/
function __construct(&$query, $totalRows, $pageVar = "page")
{
// set pageVar
$this->pageVar = $pageVar;
// Get the current page
if (isset($_GET[$pageVar]) && is_numeric($_GET[$pageVar]))
{
$currentPage = $_GET[$pageVar];
}
else
{
$currentPage = 1;
}
$this->pageSize = $totalRows;
$this->currentPage = $currentPage;
$query = strtolower($query);
// Split the query to create a new count query
list($queryStart, $queryEnd) = explode(" from ", $query, 2);
// Create the count query
if (substr_count($queryEnd,"group by ",1) == 1)
$query = "select count(*) from (select count(*) from " . $queryEnd . ") as t1";
else
$query = "select count(*) from " . $queryEnd;
// Get the total rows
$result = mysql_query($query);
$row = mysql_fetch_row($result);
$this->total = $row[0];
// bereken het aantal pagina's
$this->pages = ceil($this->total / $totalRows);
// Create the new query
$offset = ($currentPage - 1) * $totalRows;
$query = $queryStart . " from " . $queryEnd . " limit " . $offset . "," . $totalRows . " ";
}
/**
* Get the base link from the requested url current
* Used to build the page links
*/
public function getBaseUrl()
{
if (!isset($_GET[$this->pageVar]))
{
if ($_SERVER['QUERY_STRING'] == "")
return "?" . $this->pageVar . "=";
else
return "?" . $_SERVER['QUERY_STRING'] . "&" . $this->pageVar . "=";
}
else
{
// replace and append the query string
if (sizeOf($_GET) <= 1)
{
return "?" . $this->pageVar . "=";
}
else
{
// try to replace the var
$newQueryString = str_replace("&" . $this->pageVar . "=" . $_GET[$this->pageVar], "", $_SERVER['QUERY_STRING']);
// try to replace the first var
$newQueryString = str_replace($this->pageVar . "=" . $_GET[$this->pageVar], "", $newQueryString);
// correct & if this is the first char
if(substr($newQueryString,0,1) == "&")
{
$newQueryString = substr($newQueryString,1,strlen($newQueryString)-1);
}
return "?" . $newQueryString . "&" . $this->pageVar . "=";
}
}
}
/**
* Get the url for the first page,
* without the page infor in the url.
* This will prevent urls with duplicate content
* for search engine optimization.
*/
public function getFirstUrl()
{
if (isset($_GET[$this->pageVar]))
{
// try to replace the var
$newQueryString = str_replace("&" . $this->pageVar . "=" . $_GET[$this->pageVar], "", $_SERVER['QUERY_STRING']);
// try to replace the first var
$newQueryString = str_replace($this->pageVar . "=" . $_GET[$this->pageVar], "", $newQueryString);
// strip off last character for correct url
return "?" . $newQueryString;
}
else
{
if ($_SERVER['QUERY_STRING'] != "")
{
return "?" . $_SERVER['QUERY_STRING'];
}
else
return ".";
}
}
/**
* Shorcut function for calling the static
* method pageLinks from class Pagination
*/
public function pageLinks()
{
return Pagination::pageLinks($this);
}
/**
* Shorcut function for calling the static
* method simplePageLinks from class Pagination
*/
public function simplePageLinks()
{
return Pagination::simplePageLinks($this);
}
}
?>
Pagination.class.php
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
<?php
/**
* Used for building query's witch need pagination.
*/
class Pagination
{
/**
* Simpel layout for page numbering
*/
public static function simplePageLinks($pagedQuery)
{
$return = "";
$baseLink = $pagedQuery->getBaseUrl();
if ($pagedQuery->pages > 1)
{
for ($i=1;$i<$pagedQuery->pages + 1;$i++)
{
if ($i==1)
$return .= " <a class=\"page\" href=\"" . $pagedQuery->getFirstUrl() . "\">";
else
$return .= " <a class=\"page\" href=\"" . $baseLink . $i . "\">";
if ($i == $pagedQuery->currentPage)
$return .= "<b>$i</b>";
else
$return .= "$i";
$return .= "</a> ";
}
}
return $return;
}
/**
* Simpel layout for page numbering
*/
function pageLinks($pagedQuery)
{
$iconLocation = "images/icons";
$baseLink = $pagedQuery->getBaseUrl();
$firstLink = $pagedQuery->getFirstUrl();
$return = "";
$return .= "<table class=\"pagination\"><tr>";
if ($pagedQuery->currentPage > $pagedQuery->pages)
return "";
$count = 10; // max 10 links
if ($pagedQuery->currentPage != 1)
{
$return .= "<td><a class=\"no_pagination\" href=\"" . $firstLink . "\"><img src='$iconLocation/page-first.gif'></a></td>";
$return .= "<td><a class=\"no_pagination\" href=\"";
if ($pagedQuery->currentPage - 1 == 1)
{
$return .= $firstLink;
}
else
{
$return .= $baseLink . ($pagedQuery->currentPage - 1);
}
$return .= "\"><img src='$iconLocation/page-prev.gif'></a></td>";
}
else
{
$return .= "<td><img src='$iconLocation/page-first-disabled.gif'></td>";
$return .= "<td><img src='$iconLocation/page-prev-disabled.gif'></td>";
}
// more then standard 5 left
if ($pagedQuery->currentPage + 4 > $pagedQuery->pages)
$leftCalcCount = 9 - ($pagedQuery->pages - $pagedQuery->currentPage);
else
$leftCalcCount = 5;
// Linker links
for ($i=$pagedQuery->currentPage - $leftCalcCount;$i<$pagedQuery->currentPage + 1;$i++)
{
if ($i < 1)
$i = 1;
$return .= "<td><a ";
// add class
if ($i == $pagedQuery->currentPage)
$return .= "class=\"current\" ";
if ($i == 1)
{
$return .= "href=\"" . $firstLink . "\">";
}
else
{
$return .= "href=\"" . $baseLink . $i . "\">";
}
if ($i == $pagedQuery->currentPage)
$return .= "<b>$i</b>";
else
$return .= "$i";
$return .= "</a></td>";
$count--;
}
// Rechter links
for ($i=$pagedQuery->currentPage + 1;$i< $pagedQuery->currentPage + $count + 1;$i++)
{
if ($i < 1)
$i = 1;
if ($i > $pagedQuery->pages)
break;
$return .= "<td><a href=\"" . $baseLink . $i . "\">";
if ($i == $pagedQuery->currentPage)
$return .= "<b>$i</b>";
else
$return .= "$i";
$return .= "</a></td>";
}
if ($pagedQuery->currentPage != $pagedQuery->pages)
{
$return .= "<td><a class=\"no_pagination\" href=\"" . $baseLink . ($pagedQuery->currentPage + 1) . "\"><img src='$iconLocation/page-next.gif'></a></td>";
$return .= "<td><a class=\"no_pagination\" href=\"" . $baseLink . $pagedQuery->pages . "\"><img src='$iconLocation/page-last.gif'></a></td></td>";
}
else
{
$return .= "<td><img src='$iconLocation/page-next-disabled.gif'></td>";
$return .= "<td><img src='$iconLocation/page-last-disabled.gif'></td>";
}
$return .= "</tr></table>";
return $return;
}
}
?>
/**
* Used for building query's witch need pagination.
*/
class Pagination
{
/**
* Simpel layout for page numbering
*/
public static function simplePageLinks($pagedQuery)
{
$return = "";
$baseLink = $pagedQuery->getBaseUrl();
if ($pagedQuery->pages > 1)
{
for ($i=1;$i<$pagedQuery->pages + 1;$i++)
{
if ($i==1)
$return .= " <a class=\"page\" href=\"" . $pagedQuery->getFirstUrl() . "\">";
else
$return .= " <a class=\"page\" href=\"" . $baseLink . $i . "\">";
if ($i == $pagedQuery->currentPage)
$return .= "<b>$i</b>";
else
$return .= "$i";
$return .= "</a> ";
}
}
return $return;
}
/**
* Simpel layout for page numbering
*/
function pageLinks($pagedQuery)
{
$iconLocation = "images/icons";
$baseLink = $pagedQuery->getBaseUrl();
$firstLink = $pagedQuery->getFirstUrl();
$return = "";
$return .= "<table class=\"pagination\"><tr>";
if ($pagedQuery->currentPage > $pagedQuery->pages)
return "";
$count = 10; // max 10 links
if ($pagedQuery->currentPage != 1)
{
$return .= "<td><a class=\"no_pagination\" href=\"" . $firstLink . "\"><img src='$iconLocation/page-first.gif'></a></td>";
$return .= "<td><a class=\"no_pagination\" href=\"";
if ($pagedQuery->currentPage - 1 == 1)
{
$return .= $firstLink;
}
else
{
$return .= $baseLink . ($pagedQuery->currentPage - 1);
}
$return .= "\"><img src='$iconLocation/page-prev.gif'></a></td>";
}
else
{
$return .= "<td><img src='$iconLocation/page-first-disabled.gif'></td>";
$return .= "<td><img src='$iconLocation/page-prev-disabled.gif'></td>";
}
// more then standard 5 left
if ($pagedQuery->currentPage + 4 > $pagedQuery->pages)
$leftCalcCount = 9 - ($pagedQuery->pages - $pagedQuery->currentPage);
else
$leftCalcCount = 5;
// Linker links
for ($i=$pagedQuery->currentPage - $leftCalcCount;$i<$pagedQuery->currentPage + 1;$i++)
{
if ($i < 1)
$i = 1;
$return .= "<td><a ";
// add class
if ($i == $pagedQuery->currentPage)
$return .= "class=\"current\" ";
if ($i == 1)
{
$return .= "href=\"" . $firstLink . "\">";
}
else
{
$return .= "href=\"" . $baseLink . $i . "\">";
}
if ($i == $pagedQuery->currentPage)
$return .= "<b>$i</b>";
else
$return .= "$i";
$return .= "</a></td>";
$count--;
}
// Rechter links
for ($i=$pagedQuery->currentPage + 1;$i< $pagedQuery->currentPage + $count + 1;$i++)
{
if ($i < 1)
$i = 1;
if ($i > $pagedQuery->pages)
break;
$return .= "<td><a href=\"" . $baseLink . $i . "\">";
if ($i == $pagedQuery->currentPage)
$return .= "<b>$i</b>";
else
$return .= "$i";
$return .= "</a></td>";
}
if ($pagedQuery->currentPage != $pagedQuery->pages)
{
$return .= "<td><a class=\"no_pagination\" href=\"" . $baseLink . ($pagedQuery->currentPage + 1) . "\"><img src='$iconLocation/page-next.gif'></a></td>";
$return .= "<td><a class=\"no_pagination\" href=\"" . $baseLink . $pagedQuery->pages . "\"><img src='$iconLocation/page-last.gif'></a></td></td>";
}
else
{
$return .= "<td><img src='$iconLocation/page-next-disabled.gif'></td>";
$return .= "<td><img src='$iconLocation/page-last-disabled.gif'></td>";
}
$return .= "</tr></table>";
return $return;
}
}
?>
Het script
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$query = "SELECT * FROM TABEL";
$totalRows = 9; // Max rows on a page
$PagedQuery = new PagedQuery($query, $totalRows);
$result = mysql_query($query);
while($row = mysql_fetch_row($result))
{
echo $row[];
}
$PagedQuery->pageLinks();
?>
$query = "SELECT * FROM TABEL";
$totalRows = 9; // Max rows on a page
$PagedQuery = new PagedQuery($query, $totalRows);
$result = mysql_query($query);
while($row = mysql_fetch_row($result))
{
echo $row[];
}
$PagedQuery->pageLinks();
?>
Gewijzigd op 26/06/2012 11:07:59 door Jan Jaap van der Sluijs
Wat gaat er fout, wat is de foutmelding?
In de classes kun je zien dat de query gesplitst word, vervolgens werkt GROUP BY niet.
Gewijzigd op 26/06/2012 14:22:36 door Jan Jaap van der Sluijs