De PHPhulp kroeg
OOP pagination & PDO/SQL
Ik ben nu bezig met een pagination class die de results uit een database haalt. In het verleden heb ik een pagination-functie geschreven en toen haalde ik het AANTAL results gewoon d.m.v. het uitvoeren van de SQL en dan m.b.v. mysql_num_rows() het aantal results bepalen.
Ik ben er achter gekomen dat dit traag kan zijn, als er VEEL results zijn. Nu dacht ik: ik verander de SQL met een 'COUNT(*) AS paginationCount' erbij. Hierdoor komt er maar één result binnen die het aantal result bevat: precies wat ik nodig heb. Het aantal results heb ik nodig om het aantal pagina's te bepalen.
Dus bij het aanmaken van de class, geef je een SQL-statement mee (uiteraard een SELECT-SQL-statement). Deze word dan aangepast met 'COUNT(*) AS paginationCount'.
Ik gebruik voor het eerst PDO en vroeg me af of hier toevallig een betere optie voor is. Zo niet, vroeg ik me af of ik dit veilig kan gebruiken:
Code (php)
1
2
3
4
2
3
4
<?php
preg_match('#^SELECT (.*?) FROM (.*?)$#is', $sql, $match);
$count_sql = 'SELECT '.$match[1].', COUNT(*) AS paginationCount FROM '.$match[2];
?>
preg_match('#^SELECT (.*?) FROM (.*?)$#is', $sql, $match);
$count_sql = 'SELECT '.$match[1].', COUNT(*) AS paginationCount FROM '.$match[2];
?>
Het werkt bij de tests die ik heb uitgevoerd, maar ik was benieuwd naar jullie mening. Vergeet ik niet iets?
Voorbeeld:
Code (php)
1
2
3
4
2
3
4
<?php
$sql = 'SELECT name, id, face FROM users'; // Zo gaat hij erin
$count_sql = 'SELECT name, id, face, COUNT(*) AS paginationCount FROM users'; // Zo komt hij eruit
?>
$sql = 'SELECT name, id, face FROM users'; // Zo gaat hij erin
$count_sql = 'SELECT name, id, face, COUNT(*) AS paginationCount FROM users'; // Zo komt hij eruit
?>
Dus eigenlijk de vragen die ik hierover heb zijn:
- Is hier een betere manier voor?
- Zo niet, is dit veilig te gebruiken (werkt dit bij elk indenkbaar SQL-scenario?)
Alvast bedankt,
Mark L.
Gewijzigd op 10/06/2010 13:32:09 door Mark L
Iets als: http://www.phpclasses.org/package/2813-PHP-Dynamically-build-SQL-queries.html
Wanneer je gebruik maakt van de genoemde klasse krijg je iets als:
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
function paginate($table, array $fields = array('*'), $start = 0, $pageCount = 10, $order = null) {
$sql = new SqlQueryBuilder('select');
$sql->setTable($table);
foreach($fields as $field) $sql->addColumn($field);
if(!is_null($order)) $sql->setOrderBy($order);
$sql->setLimit($start . ', '. $start + $pageCount;
return $sql->buildQuery();
}
?>
function paginate($table, array $fields = array('*'), $start = 0, $pageCount = 10, $order = null) {
$sql = new SqlQueryBuilder('select');
$sql->setTable($table);
foreach($fields as $field) $sql->addColumn($field);
if(!is_null($order)) $sql->setOrderBy($order);
$sql->setLimit($start . ', '. $start + $pageCount;
return $sql->buildQuery();
}
?>
Gewijzigd op 10/06/2010 14:33:28 door Pim -
Misschien werkt zoiets ook goed met MySQL:
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
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
<?php
class PaginatedResultSet
{
private $query, $pageSize, $page;
public function __construct($query, $pageSize = 60, $page = 0)
{
$this->query = $query;
$this->pageSize = $pageSize;
$this->page = $page;
}
public function count()
{
$result = mysql_query(sprintf('SELECT COUNT(*) FROM (%s)', $this->query));
return $result
? mysql_result($result, 0)
: false;
}
public function fetchAll()
{
$result = mysql_query(sprintf('SELECT * FROM (%s) LIMIT %d OFFSET %d',
$this->query, $this->pageSize, $this->page * $this->pageSize));
if(!$result)
return false;
$rows = array();
while($row = mysql_fetch_assoc($result))
$rows[] = $row;
return $rows;
}
}
?>
class PaginatedResultSet
{
private $query, $pageSize, $page;
public function __construct($query, $pageSize = 60, $page = 0)
{
$this->query = $query;
$this->pageSize = $pageSize;
$this->page = $page;
}
public function count()
{
$result = mysql_query(sprintf('SELECT COUNT(*) FROM (%s)', $this->query));
return $result
? mysql_result($result, 0)
: false;
}
public function fetchAll()
{
$result = mysql_query(sprintf('SELECT * FROM (%s) LIMIT %d OFFSET %d',
$this->query, $this->pageSize, $this->page * $this->pageSize));
if(!$result)
return false;
$rows = array();
while($row = mysql_fetch_assoc($result))
$rows[] = $row;
return $rows;
}
}
?>
Gewijzigd op 10/06/2010 22:49:05 door Jelmer -
Bedankt Pim, maar ik schrijf liever mijn querys dan dat ik ze laat genereren.
Maar nu ben ik op het idee VIEWs gekomen; ik was gewoon even mijn kennis over SQL aan 't vergroten, toen ik hierop stuitte. Nu kan ik niet echt een duidelijk beeld krijgen van VIEWs en hoe ik deze goed of slecht kan gebruiken.
Code (php)
1
2
3
4
5
2
3
4
5
<?php
$createview = 'CREATE VIEW paginationView AS '.$sql; // Create view
$query = 'SELECT COUNT(*) AS paginationCount FROM paginationView'; // Pak aantal van de view
$deleteview = 'DROP VIEW paginationView'; // Delete view
?>
$createview = 'CREATE VIEW paginationView AS '.$sql; // Create view
$query = 'SELECT COUNT(*) AS paginationCount FROM paginationView'; // Pak aantal van de view
$deleteview = 'DROP VIEW paginationView'; // Delete view
?>
Ik heb nog nooit met VIEWs gewerkt, en ik denk dat ik het eigenlijk verkeerd gebruik. Volgens mij moet een VIEW alleen gebruikt worden als je hier langer dan één query mee wil werken. Wat zijn de voor- en nadelen van deze aanpak? Kan ik voor mijn doel VIEWs gebruiken, of toch beter niet?
Alvast bedankt,
Mark
Gewijzigd op 12/06/2010 12:05:40 door Mark L
Als je de views goed weet op te bouwen en te gebruiken dan heeft vele voordelen.
SELECT * is zo ie zo al niet duidelijk en vaak haal je dan ook data op waarmee je niets doet. Benoem dus gewoon de velden waarin de data staat waarmee je iets gaat doen.
In feite kan je simpelweg dit doen:
SELECT u.name
, u.id
, u.face
, pc.paginationCount
FROM users U
, (SELECT COUNT(*) paginationCount
FROM users) pc
en als je er dan kijkt naar view
1ste view wordt dan
SELECT u.name
, u.id
, u.face
FROM users u
2de view wordt dan
SELECT COUNT(*) paginationCount
FROM users
3de view
SELECT u.name
, u.id
, u.face
, pc.paginationCount
FROM usersvw u
, pageingcountvw pc
uiteraad kan je ook 1 view maken
SELECT u.name
, u.id
, u.face
, pc.paginationCount
FROM users U
, (SELECT COUNT(*) paginationCount
FROM users) pc
Mijn voorkeur gaat dan uit naar de 3 views, want die views kan je dan ook nog gebruiken om andere views samen te stellen
Nadeel views in mysql speciale aandacht nodig hebben, je zult altijd met explain moeten blijven kijken voor optimalisatie, dus dan kan het zijn dat je een tijdje kwijt bent met zoeken welke view niet goed in elkaar steekt als je views nest.