我根据我的表创建了一个饼图，然后将它们按4个部分进行分组 Escalate, Undet, Supported, Not Supported 使用此查询：
$query = "select 'Undet' as trendx , COUNT(*) as counter
from jeremy_table_trend WHERE trendx LIKE '%Undet%'
union all
select 'Escalate', COUNT(*) as counter
from jeremy_table_trend WHERE trendx LIKE '%Escalate%'
union all
select 'Not Supported', COUNT(*) as counter
from jeremy_table_trend WHERE trendx LIKE '%Not Supported%'
union all
select 'Supported', COUNT(*) as counter
from jeremy_table_trend
WHERE trendx NOT LIKE '%Not Supported%' OR '%Undet%' OR '%Escalate%'";这个输出
但是我希望我的饼图输出我当前的会话当我筛选它时，我尝试了这个查询，但是它没有根据我的会话显示正确的输出：
$query = "select 'Undet' as trendx , COUNT(*) as counter from jeremy_table_trend $_SESSION[current_query] WHERE trendx LIKE '%Undet%' union all select 'Escalate', COUNT(*) as counter from jeremy_table_trend WHERE trendx LIKE '%Escalate%' union all select 'Not Supported', COUNT(*) as counter from jeremy_table_trend WHERE trendx LIKE '%Not Supported%' union all select 'Supported', COUNT(*) as counter from jeremy_table_trend WHERE trendx NOT LIKE '%Not Supported%' OR '%Undet%' OR '%Escalate%'";看看我的代码，这是我的筛选页面，要输出筛选表，我希望我的饼图根据我当前的\u会话显示项目：
<?php
$rpp = 10;
$page = 1;
if (isset($_GET['rpp'])) {
$_SESSION['rpp'] = $_GET['rpp'];
}
if (isset($_SESSION['rpp'])) {
$rpp = $_SESSION['rpp'];
}
if (isset($_GET['page'])) {
$page = $_GET['page'];
}
$conn = mysqli_connect("localhost", "root", "", "jeremy_db");
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$_SESSION['sourced_from'] = $_POST['sourced_from'];
$_SESSION['sourced_to'] = $_POST['sourced_to'];
$_SESSION['vsdt'] = $_POST['vsdt'];
$_SESSION['sha1'] = $_POST['sha1'];
$_SESSION['trendx'] = $_POST['trendx'];
$_SESSION['notes'] = $_POST['notes'];
$_SESSION['trendx_eq'] = $_POST['trendx_eq'];
$_SESSION['vsdt_eq'] = $_POST['vsdt_eq'];
}
if ($_SESSION['sourced_from'] or $_SESSION['sourced_to'] or $_SESSION['vsdt'] or $_SESSION['sha1'] or $_SESSION['trendx'] or $_SESSION['notes']) {
$_SESSION['filter_query'] = "";
$first = True;
$and = "";
$_SESSION['filter_query'] .= "WHERE ";
if ($_SESSION['sourced_from'] or $_SESSION['sourced_to']) {
if (!$first) $and = " AND ";
else $first = False;
$date1 = $_SESSION['sourced_from'];
$date2 = $_SESSION['sourced_to'];
if($_SESSION['sourced_from'] and $_SESSION['sourced_to']) $_SESSION['filter_query'] .= $and . " date_sourced BETWEEN '$date1' AND '$date2'";
elseif($_SESSION['sourced_from']) $_SESSION['filter_query'] .= $and . " date_sourced >= '$date1'";
elseif($_SESSION['sourced_to']) $_SESSION['filter_query'] .= $and . " date_sourced <= '$date2'";
}
if ($_SESSION['sha1']) {
if (!$first) $and = " AND ";
else $first = False;
$_SESSION['filter_query'] .= $and . " sha1 = '" . $_SESSION['sha1'] . "'";
}
if ($_SESSION['vsdt']) {
if (!$first) $and = " AND ";
else $first = False;
if($_SESSION['vsdt_eq'] == "eq") {
$_SESSION['filter_query'] .= $and . " vsdt = ";
if($_SESSION['vsdt'] == 'None') $_SESSION['filter_query'] .= " '' ";
else $_SESSION['filter_query'] .= " '" . $_SESSION['vsdt'] . "' ";
} elseif($_SESSION['vsdt_eq'] == "neq") {
$_SESSION['filter_query'] .= $and . " vsdt != ";
if($_SESSION['vsdt'] == 'None') $_SESSION['filter_query'] .= " '' ";
else $_SESSION['filter_query'] .= " '" . $_SESSION['vsdt'] . "' ";
} else $_SESSION['filter_query'] .= $and . " vsdt LIKE '%" . $_SESSION['vsdt'] . "%'";
}
if ($_SESSION['trendx']) {
if (!$first) $and = " AND ";
else $first = False;
if($_SESSION['trendx_eq'] == "eq") {
$_SESSION['filter_query'] .= $and . " trendx = ";
if($_SESSION['trendx'] == 'None') $_SESSION['filter_query'] .= " '' ";
else $_SESSION['filter_query'] .= " '" . $_SESSION['trendx'] . "' ";
} elseif($_SESSION['trendx_eq'] == "neq") {
$_SESSION['filter_query'] .= $and . " trendx != ";
if($_SESSION['trendx'] == 'None') $_SESSION['filter_query'] .= " '' ";
else $_SESSION['filter_query'] .= " '" . $_SESSION['trendx'] . "' ";
} else $_SESSION['filter_query'] .= $and . " trendx LIKE '%" . $_SESSION['trendx'] . "%'";
}
if ($_SESSION['notes']) {
if (!$first) $and = " AND ";
else $first = False;
$_SESSION['filter_query'] .= $and . " notes LIKE '%" . $_SESSION['notes'] . "%'";
}
} else {
$_SESSION['filter_query'] = "";
$first = True;
$and = "";
}
$temp = ($page-1)*$rpp;
$query = " SELECT * FROM jeremy_table_trend " . $_SESSION['filter_query'] . " ORDER by id desc LIMIT $temp, $rpp ";
// echo $query;
$page_result = mysqli_query($conn, $query);
$total = " SELECT * FROM jeremy_table_trend " . $_SESSION['filter_query'];
$_SESSION['current_query'] = $_SESSION['filter_query'];
$total = mysqli_query($conn, $total);
$total = mysqli_num_rows($total);
if (isset($_GET['update_id'])) {
$update_id = $_GET['update_id'];
$update_id = " SELECT * FROM jeremy_table_trend WHERE id='$update_id' ";
$update_id = mysqli_query($conn, $update_id);
$update_id = mysqli_fetch_assoc($update_id);
}
?>
1条答案
按热度按时间6xfqseft1#
您当前的sql查询可能不正确，而且肯定可以改进。我建议：
因为这是一个单一的查询，所以插入required where子句会容易得多。
注：我认为现在这个不正确的原因是你没有否定决赛中的所有条件
应为：
但使用
case expression如上图所示。顺便说一句，在php中没有必要将sql查询写成单行，如果您开始使用格式化的sql块，这将有助于提高可读性。