Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pagination of data from an UNION query

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

  • #2
    New Coder
    Join Date
    Feb 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 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>";
            }
    }
    ?>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •