在MySQL中使用UNION时如何进行分页? [英] How to do Pagination while UNION is used in MySQL?
本文介绍了在MySQL中使用UNION时如何进行分页?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个复杂的数据库类型，不幸的是，由于某种原因我无法更改
I have a complex type of database which, unfortunately, I can not change for some reason.
我必须在各表之间强制执行UNION，并必须显示一个表.
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.
但是它不起作用.
当我不使用UNION并从一个表中获取数据时，此代码块将起作用.
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>
注意:未定义的索引:第16行的D:\ XAMPP \ htdocs \ soft \ bts_usage_result.php中的btsid
Notice: Undefined index: btsid in D:\XAMPP\htdocs\soft\bts_usage_result.php on line 16
注意:未定义的索引:第17行的D:\ XAMPP \ htdocs \ soft \ bts_usage_result.php中的date1
Notice: Undefined index: date1 in D:\XAMPP\htdocs\soft\bts_usage_result.php on line 17
注意:未定义的索引:第18行的D:\ XAMPP \ htdocs \ soft \ bts_usage_result.php中的date2
Notice: Undefined index: date2 in D:\XAMPP\htdocs\soft\bts_usage_result.php on line 18
发件人:
收件人:
推荐答案
查询末尾会有一个额外的')'(否则它将出现).删除它，然后重试.
You have an extra ')' at the end of the query (or so it would appear). Remove this and try again.
更重要的是-在尝试使用数据之前，请执行@Bad Wolf所说的并检查结果(以及错误消息).
More importantly - do what @Bad Wolf said and check out the result (and error msgs) before you try to use the data.
这篇关于在MySQL中使用UNION时如何进行分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
