# Get 3 counts from a table [closed]

I am trying to show the following results to the user

All:8000 enabled:8000 disabled:0

What can I do to improve this code?

$Val = explode(",", urldecode($_POST['Val']));
$table = mysqliChkData($Val[0]);

$addSQL = ''; if (!empty($Val[2])) {
// parrent.guid
$addSQL.= " and up_cls='".mysqliChkData($Val[2])."'";
}

$getSQL = "Select guid From ".$table." Where 1=1".$addSQL." GROUP BY guid";$listData = $sql->Select($getSQL);
$arr['allNum'] =$listData['num'];

$getSQL = "Select guid From ".$table." Where chk='Y'".$addSQL." GROUP BY guid";$listData = $sql->Select($getSQL);
$arr['openNum'] =$listData['num'];
$arr['closeNum'] =$arr['allNum'] - $arr['openNum']; echo json_encode($arr);
exit;


Same records but different languages will have same guid.

Child tables have up_cls column.

Few tables don't have chk column because no need to disable.

CREATE TABLE tablee (
id int(11) NOT NULL AUTO_INCREMENT,
lang varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
chk varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
date datetime DEFAULT NULL,
guid varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
modify_date timestamp NOT NULL DEFAULT '1970-12-31 16:00:00' ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id,guid) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT;


method Select

function Select($sql) { global$mysqli;
$reData = array();$reData['data'] = array();

$rs = mysqli_query($mysqli, $sql); if ($rs) {
$thisNum =$rs->num_rows;
}
else {
$thisNum = 0; }$reData['num'] = $thisNum; if ($thisNum != 0) {
while ($row =$rs->fetch_assoc()) {
$reData['data'][] =$row;
}

}
return $reData; }  =============== Why are you mentioning child tables in this question?$addSQL.= " and up_cls='".mysqliChkData($Val[2])."'"; Are you comfortable with scrapping your Select function ...because it is insecure. Why are you mixing procedural with object-oriented mysqli syntax? It passed acunetix last year, I don't know it's insecure. Could you give me some advice about this. Is there a reason that you cannot query once, then use php to filter the result set based on chk? How about a conditional count within the SELECT clause? It is because some tables don't have chk . If the query has chk it will broken. • Why are you mentioning child tables in this question? Are you comfortable with scrapping your Select function ...because it is insecure. Why are you mixing procedural with object-oriented mysqli syntax? Why do you pass $act to your function if you never use it? Is there a reason that you cannot query once, then use php to filter the result set based on chk? How about a conditional count within the SELECT clause? – mickmackusa Jan 20 '20 at 8:27
• Please edit your question so that the title describes the purpose of the code, rather than its mechanism. We really need to understand the motivational context to give good reviews. Thanks! – Toby Speight Jan 20 '20 at 8:29

$getSQL = <<<SQL SELECT COUNT(DISTINCT guid) AS allNum, COUNT(DISTINCT CASE WHEN chk='Y' THEN guid END) AS openNum FROM {$table}
{$addSQL} SQL;$listData = $sql->Select($getSQL);
$arr =$listData['data'][0];
$arr['closeNum'] =$arr['allNum'] - $arr['openNum']; echo json_encode($arr);

• 1. $listData['data'][0]? 2. How does heredoc syntax, in this case, improve readability in a way that double quotes does not? 3. exit() can receive json_encode($arr) as a parameter to remove the need for echo. – mickmackusa Jan 22 '20 at 11:32