Pagination of data from an UNION query [Archive]

Welshsteve

05-21-2009, 02:52 PM

Hi everyone,

I have the following SQL pulling data down from a MySQL database.

(SELECT * FROM Breaks_Prem)
UNION
(SELECT * FROM Breaks_A)
UNION
(SELECT * FROM Breaks_Comp)
ORDER BY Break DESC , Name , Club

I also have a pagination php script to split the displayed records on my website into manageable pages. However, for this particular SQL I'm not sure how to get the pagination script to work.

Basically, I need to count the number of records produced by the UNION statement. How do you do this? This is what I have tried but it doesn't work.

<?php
mysql_connect("SERVER","USER","PASSWORD");
mysql_select_db("DATABASE");

$perpage = 20;
$lynx = $html = "";
$startat = $_REQUEST[page] * $perpage;

$q = mysql_query("(SELECT COUNT(`Break`) FROM `Breaks_Prem`)
UNION
(SELECT COUNT(`Break`) FROM `Breaks_A`)
UNION
(SELECT COUNT(`Break`) FROM `Breaks_Comp`)");
$row = mysql_fetch_array($q);
$pages = ($row[0] + $perpage - 1) / $perpage;

$q = mysql_query("(SELECT * FROM Breaks_Prem)
UNION
(SELECT * FROM Breaks_A)
UNION
(SELECT * FROM Breaks_Comp)
ORDER BY Break DESC , Name , Club $startat,$perpage");

while ($row = mysql_fetch_assoc($q)) {
$text = strip_tags($row[entry_text]);
$text = substr($text,0,300);
$html .= "<tr><td> " . $row['Name'] . "</td><td> " . $row['Club'] . "</td><td>" . $row['Break'] . "</td><td>" . $row['Comp'] . "</td></tr>";
};

for ($k=0; $k<$pages; $k++) {
if ($k != $_REQUEST[page]) {
$lynx .= " <a href=$PHP_SELF"."?page=$k>".($k+1)."</a>";
} else {
$lynx .= " --".($k+1)."--";
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Breaks List</title>
<link rel="stylesheet" type="text/css" href="css/css_main.css" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<body>
<h1>Snooker - Breaks - Overall: Page <?= $_REQUEST[page]+1 ?></h1>
<p>Page: <?= $lynx ?></p>
<table>
<tr>
<th>Name</th>
<th>Club</th>
<th>Break</th>
<th>Competition</th>
</tr>
<?= $html ?>
</table>
<p>Page: <?= $lynx ?></p>
</body>
</html>
Is anybody able to help? The page in question is http://www.ldbsa.co.uk/s_breaks_o.php

Welshsteve

05-21-2009, 04:48 PM

UPDATE

OK, I have moved the data into one table now and put a flag for the competition. But now I have another problem.

I also have a page that shows which players have made the most breaks.

http://www.ldbsa.co.uk/s_breaks_top.php

This works fine. But as soon as I introduce the pagination script to this, it only shows the top 40 records, i.e. only 2 pages.

http://www.ldbsa.co.uk/s_breaks_top2.php

The URL's created by each "Page" link are as follows:

Page 1 - http://www.ldbsa.co.uk/s_breaks_top2.php?page=0
Page 2 - http://www.ldbsa.co.uk/s_breaks_top2.php?page=1

etc.

But only pages 1 and 2 work with this. I can however, alter the url manually to get the next page.

Page 3 - http://www.ldbsa.co.uk/s_breaks_top2.php?page=2
Page 4 - http://www.ldbsa.co.uk/s_breaks_top2.php?page=3

and so on.

Can anybody tell me why? Here's the pagination script for this particular page.

<?php

mysql_connect("SERVER","USERNAME","PASSWORD");
mysql_select_db("DATABASE");

$perpage = 20;
$lynx = $html = "";
$startat = $_REQUEST[page] * $perpage;

$q = mysql_query("select count(Break) from Breaks_Snooker GROUP BY Name");
$row = mysql_fetch_array($q);
$pages = ($row[0] + $perpage - 1) / $perpage;

$q = mysql_query("SELECT Name,Club,COUNT(Break) as Breaks FROM Breaks_Snooker GROUP BY Name ORDER BY Breaks DESC, Name limit $startat,$perpage");

while ($row = mysql_fetch_assoc($q)) {
$text = strip_tags($row[entry_text]);
$text = substr($text,0,300);
$html .= "<tr><td style='text-align:left;border: 1px solid #006001;'> " . $row['Name'] . "</td><td style='text-align:left;border: 1px solid #006001;'> " . $row['Club'] . "</td><td style='text-align:center;border: 1px solid #006001;'>" . $row['Breaks'] . "</td></tr>";
};

for ($k=0; $k<$pages; $k++) {
if ($k != $_REQUEST[page]) {
$lynx .= " <span style='font-size:1.2em;font-weight:bold;'><a href=$PHP_SELF"."?page=$k>".($k+1)."</a></span>";
} else {
$lynx .= " <span style='font-size:1.2em;font-weight:bold;'>--".($k+1)."--</span>";
}
}
?>