0
\$\begingroup\$

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.

\$\endgroup\$
  • 3
    \$\begingroup\$ 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? \$\endgroup\$ – mickmackusa Jan 20 at 8:27
  • 3
    \$\begingroup\$ 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! \$\endgroup\$ – Toby Speight Jan 20 at 8:29
1
\$\begingroup\$

You can select multiple counts from the table based on a condition using a CASE WHEN statement, and when writing SQL or something where formatting helps readability it's normally a good idea to use heredoc.

$getSQL = <<<SQL
SELECT
    COUNT(DISTINCT guid) AS allNum,
    COUNT(DISTINCT CASE WHEN chk='Y' THEN guid END) AS openNum
FROM `{$table}`
WHERE
    1=1
    {$addSQL}
SQL;

$listData = $sql->Select($getSQL);
$arr = $listData['data'][0];

$arr['closeNum'] = $arr['allNum'] - $arr['openNum'];

echo json_encode($arr); 
exit;
|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – mickmackusa Jan 22 at 11:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.