FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Pagination of data from an UNION query





welshsteve
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.


Code:

<?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>&nbsp;" . $row['Name'] . "</td><td>&nbsp;" . $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
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.

Code:

<?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;'>&nbsp;" . $row['Name'] . "</td><td style='text-align:left;border: 1px solid #006001;'>&nbsp;" . $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>";
        }
}
?>

jmraker
The group by returns multiple rows

I would use this instead as $rec[0] is the count of the 1st record (which is 21)

Code:

$numRows = mysql_num_rows($q);
$pages = floor(($numRows + $perpage - 1) / $perpage);


I'm sure there's a better sql command that'll do the job. Probably involving a subselect

and it looks like you can simplify the $lynx with
Code:
for ($k=1; $k<=$pages; $k++) {
    $lynx .= ' <span style="font-size:1.2em;font-weight:bold;">';
    if ($k != $_REQUEST['page'])
         $lynx .= "<a href=\"$PHP_SELF?page=$k\">$k</a>";
    else
         $lynx .= "-- $k --";
    $lynx .= '</span>';
}
kv
Your below query to count the number of records is wrong

Code:
$q = mysql_query("(SELECT COUNT(`Break`) FROM `Breaks_Prem`)
UNION
(SELECT COUNT(`Break`) FROM `Breaks_A`)
UNION
(SELECT COUNT(`Break`) FROM `Breaks_Comp`)");


The above query returns 3 rows from different queries unioned together (values not added). Probably you are using only the first row.

You should instead
1. break it into 3 different queries and add it or
2. use subqueries in the query which returns single count or
3. add the count of all 3 rows in the result for total count
Aredon
kv wrote:

You should instead
1. break it into 3 different queries and add it or
2. use subqueries in the query which returns single count or
3. add the count of all 3 rows in the result for total count


You could also do a standard query:
"SELECT * FROM `database_name`"
However, instead of using MySQL to count the number of records you can pull to an array and count the number of entries with php.
Code:
$database_array=mysql_fetch_array(mysql_query($sql),MYSQL_ASSOC);
$count=count($database_array);

You could then theoretically count any other queries with arrays, and add them together pretty easily. In addition you'd have much easier data to work with since you could call any entry in the database from your array. (ie $database_array['cake']). It is also then much easier to check the data. For instance, here's a function I wrote a little while back to check to make sure that all entries were unique.
Code:
function isUniqueArray($array){
   if(is_array($array)){
      $unique_array=array_unique($array);
      $count_array=count(array_filter($array));
      $count_unique=count(array_filter($unique_array));
      return($count_array==$count_unique);
   }
}
Related topics
MSN Admits to Sharing Search Data
insert data to mySQL database problem
Delete data from MySQL database problem!!
mySQL Query/PHP code - get the highest value...
help with pagination problem
possible with query? or must use PHP organize this
[Edit] mySQL Count
Pagination
whats wrong with this mysql query?
Very basic pagination
MySQL Query help
execute php string
A Bit of Data Size Terminology
PHP and Javascript Issue handling data from a mysql query
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.