How to do Pagination while UNION is used in MySQL?

Question

I have a complex type of database which, unfortunately, I can not change for some reason.

I have to UNION compulsorily among the tables and have to show a table.

I want to do pagination for the table as it returns a big data table.

But it is not working.

When I do not use UNION and grab data from one table, then this code block works.

I need a solution. I am really in a big problem with this issue. Please someone help me.

<?php

conFunc($rootdb); // Connection Strings to Database

$btsid = trim($_POST['btsid']);
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];

?>

<b>From:</b> <?php echo $date1; ?> <br />
<b>To:</b> <?php echo $date2; ?><br /><br />

<?php
$btsdb = mysql_query("SELECT * FROM `rollout_tracker` WHERE `site_id` LIKE '%".$btsid."'");
$rows = mysql_fetch_array($btsdb);
?>

<?php //----------------------------- PAGINATION (CHECK ROW) -----------------------------//
// Don't Change [$result] Variable
$result = mysql_query("SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_january`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_february`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_march`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_april`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_may`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_june`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_july`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_august`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_september`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_october`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_november`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT COUNT(`user_id`), `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_december`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`");
//----------------------------- PAGINATION (CHECK ROW) -----------------------------// ?>

<?php //----------------------------- PAGINATION START 1 -----------------------------//

if (isset($_GET['pageno'])) 
{
   $pageno = $_GET['pageno'];
} 
else 
{
   $pageno = 1;
}

$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];

$rows_per_page = 1;
$lastpage = CEIL($numrows/$rows_per_page);

$pageno = (int)$pageno;
if ($pageno < 1) 
{
   $pageno = 1;
} 
elseif ($pageno > $lastpage) 
{
   $pageno = $lastpage;
}

$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;

//----------------------------- PAGINATION END 1 -----------------------------// ?>

<?php //----------------------------- PAGINATION (GET DATA) -----------------------------//

$result = mysql_query("SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_january`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_february`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_march`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_april`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_may`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_june`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_july`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_august`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_september`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_october`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_november`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                UNION
                                SELECT `user_id`, `mac_add`, SUM(`duration`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
                                FROM `cdr_data_december`
                                WHERE (`bs_id_site` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
                                GROUP BY `user_id`
                                ORDER BY `user_id` $limit");

echo "<h3 style=\"font-family:Verdana;color:black;\">Summary Report:</h3><br />";

echo "<table class=\"imagetable\" width='100%' border='1'><tr><th>User ID</th>";
if($_SESSION["type"]=="1")
{
    echo "<th>MAC Address</th>";
}
echo "<th>Total Connection Time (Minute)</th><th>Total Upload (MB)</th><th>Total Download (MB)</th><th>Detailed Usage</th></tr>";

while($row = mysql_fetch_array($result))
{
    if(($row['user_id'] != '') && (strpos($row['user_id'],'@') == false))
    {
        echo "<tr align=\"center\">";
        echo "<td>";
        if($row["TotalUp"] >= ($row["TotalDown"] * (90/100)))
        {
            echo "<font color=\"red\">" . $row["user_id"] . "</font>";
        }
        else
        {
            echo $row["user_id"];
        }
        echo "</td>";
        if($_SESSION["type"]=="1")
        {
            echo "<td>";
                echo strtoupper($row["mac_add"]);
            echo "</td>";
        }
        echo "<td>";
            echo number_format($row["ConnTime"], 2, '.', ''); // $row["ConnTime"];
        echo "</td>";
        echo "<td>";
            echo number_format($row["TotalUp"], 2, '.', ''); // $row["TotalUp"];
        echo "</td>";
        echo "<td>";
            echo number_format($row["TotalDown"], 2, '.', ''); // $row["TotalDown"];
        echo "</td>";
        echo "<td>";
            echo "<a class='ajax' target=\"_blank\" title='[ Detailed Usage of the User ]' href='detailed_usage.php?sid=".$row['user_id']."&bid=".$btsid."&d1=".$date1."&d2=".$date2."&mac=".$row["mac_add"]."'><img height=\"12\" width=\"12\" src=\"gallery/file/edit-button.png\"></a>";
        echo "</td>";
        echo "</tr>";
    }
}
echo "</table><br/><br/>";
?>

<?php //----------------------------- PAGINATION START 2 -----------------------------//

if ($pageno == 1) 
{
   echo " FIRST PREV ";
} 
else
{
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
}

echo " ( Page $pageno of $lastpage ) ";

IF ($pageno == $lastpage) 
{
   echo " NEXT LAST ";
}
else
{
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
}

//----------------------------- PAGINATION END 2 -----------------------------// ?>

</p>

Notice: Undefined index: btsid in D:\XAMPP\htdocs\soft\bts_usage_result.php on line 16

Notice: Undefined index: date1 in D:\XAMPP\htdocs\soft\bts_usage_result.php on line 17

Notice: Undefined index: date2 in D:\XAMPP\htdocs\soft\bts_usage_result.php on line 18

From:

To:

Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in D:\XAMPP\htdocs\software\bts_usage_result.php on line 117 ------------- and ------------ Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\XAMPP\htdocs\software\bts_usage_result.php on line 209 — Nirjhor, Jun 24 '13 at 17:13
possible duplicate of [mysql\_fetch\_array() expects parameter 1 to be resource, boolean given in select](http://stackoverflow.com/questions/2973202/mysql-fetch-array-expects-parameter-1-to-be-resource-boolean-given-in-select) — hjpotter92, Jun 24 '13 at 17:31
No! I do not think so... And, that page is not answer of my question... — Nirjhor, Jun 24 '13 at 17:35
There is an error in your sql somewhere which is causing php to error when it tries to fetch the result from a failed query. Try adding `or die(mysql_error())` to the end of your `mysql_query()` calls to see what error sql is returning. — Bad Wolf, Jun 24 '13 at 17:41
This time, result is coming, " FIRST PREV ( Page 1 of 15 ) NEXT LAST " but When pressing NEXT it is showing the above error message and POST-ED variable goes erased. I also have tried request, no change... Please give me solution... — Nirjhor, Jun 25 '13 at 16:45
I have found a problem point. I am using GROUP BY, for this reason this page is showing 1 row (it is alright). But the handler page (code of which is given above) is showing " 1 - 15 " because there is actually 15 rows coming which is shown on this page as 1 row as I have used GROUP BY. But in such case of GROUPING what should I use to do pagination, I am not understanding... Is there any code to get row number after GROUPING (i.e. after "GROUP BY" is done)? Can anyone help? Please? — Nirjhor, Jun 25 '13 at 17:33

score 0 · Answer 1 · answered Jun 24 '13 at 20:50

0

You have an extra ')' at the end of the query (or so it would appear). Remove this and try again.

More importantly - do what @Bad Wolf said and check out the result (and error msgs) before you try to use the data.

answered Jun 24 '13 at 20:50

ethrbunny

10,379
9
69
131

I have removed ')' and also tried putting mysql_error() but no solution is found... – Nirjhor Jun 25 '13 at 16:35
What output do you get if you put the string (with appropriate substitutions) into the mysql cmd line? – ethrbunny Jun 25 '13 at 16:54
I have found a problem point. I am using GROUP BY, for this reason this page is showing 1 row (it is alright). But the handler page (code of which is given above) is showing " 1 - 15 " because there is actually 15 rows coming which is shown on this page as 1 row as I have used GROUP BY. But in such case of GROUPING what should I use to do pagination, I am not understanding... – Nirjhor Jun 25 '13 at 16:57
ethrbunny -> 15 | b8616f02045c | 1605.0500 | 612.8851 | 87.8155 – Nirjhor Jun 25 '13 at 17:00
Column Name -> COUNT(`user_id`) | mac_add | ConnTime | TotalDown | TotalUp – Nirjhor Jun 25 '13 at 17:00
Is there any code to get row number after GROUPING (i.e. after "GROUP BY" is done)? Can anyone help? Please? – Nirjhor Jun 25 '13 at 17:27

How to do Pagination while UNION is used in MySQL?

1 Answers1

Linked