如何对多重查询的结果进行排序？

ijxebb2r 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(272)

我用 mysqli_multi_query() 函数并以这样的方式获取两个查询的结果，即所有第一个查询结果都是按日期排序的第一个查询和之后的第二个查询。
以下是我的疑问：

$query = "SELECT 
            `b`.`class_name`, 
            `c`.`main_code`,`c`.`account_title`, 
            `a`.`ledger_entry`, `a`.`balance` AS `debit`, `a`.`date`, `a`.`v.no`, `a`.`interactive_person` 
         FROM `vouchers` AS `a`,`classes` AS `b`, `data` AS `c` 
         WHERE `a`.`post_status`='yes' 
           AND `a`.`cancel_status`='off' 
           AND `a`.`type`='CP' 
           AND `a`.`class_id` = `b`.`class_id` 
           AND `a`.`account_id`=`c`.`account_code` 
           AND `a`.`date` BETWEEN '$fromDate' AND '$toDate';";
$query .= "SELECT 
             `b`.`class_name`, 
             `c`.`main_code`,`c`.`account_title`, 
             `a`.`ledger_entry`, sum(`a`.`balance`) AS `credit`, `a`.`date`, `a`.`v.no`, `a`.`interactive_person` 
           FROM `vouchers` AS `a`,`classes` AS `b`,`data` AS `c` 
           WHERE `a`.`post_status`='yes' 
             AND `a`.`cancel_status`='off' 
             AND `a`.`type`='CP' 
             AND `a`.`class_id` = `b`.`class_id` 
             AND `a`.`interactive_person`=`c`.`main_code` 
             AND `a`.`date` BETWEEN '$fromDate' AND '$toDate' GROUP BY `v.no`;";

这是两个查询，现在让我向您展示如何使用multiquery函数：

if (mysqli_multi_query($con, $query)) {
    do {
        // Store first result set
        if ($result = mysqli_store_result($con)) {
            // Fetch one and one row
            while ($row = mysqli_fetch_assoc($result)) {
                if ($y > 700) {
                    $y = 30;
                    $pdf->AddPage();
                    $x = 36;
                }
                $y = $y + 18;
                $x = 10;

                $debit = 0;
                $credit = $row['credit'];
                if($credit==""){$credit = 0;}
                $debit = $row['debit'];
                if($debit==""){$debit = 0;}

                $pdf->SetFont('Times', 'B', 12, '', true);
                $pdf->MultiCell(70, 18, $row['date'], 1, 'L', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
                $x = $x + 70;
                $pdf->SetFont('Times', '', 12, '', true);
                $pdf->MultiCell(50, 18, $row['v.no'], 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
                $x = $x + 50;
                $pdf->MultiCell(100, 18, $row['main_code'], 1, 'L', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
                $x = $x + 70;
                $pdf->MultiCell(100, 18, $row['account_title'], 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
                $x = $x + 100;
                $pdf->MultiCell(100, 18, $row['class_name'], 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
                $x = $x + 100;
                $pdf->MultiCell(120, 18,$credit , 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
                $x = $x + 120;
                $pdf->MultiCell(80, 18, $debit , 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);

            }
            // Free result set
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($con));
}

一切都很好地工作，我得到pdf格式的输出：

所以现在我想做的是排序的细节，我得到了秩序的“参考号”或“日期”。
我该怎么做？我可以用任何php函数吗？或者我应该改变从数据库获取数据的技术吗？

sql mysql php Function

来源：https://stackoverflow.com/questions/51477160/how-to-sort-the-result-of-multiquery

3条答案

按热度按时间

aiazj4mn1#

执行查询后，将数据存储在数组中，然后对其进行排序，然后按照我希望的方式工作，因此答案是

$pdf->SetFont('Times', 'B', 12, '', true);

$y = $y + 20;
$pdf->MultiCell(200, 18, 'Cash Payment Vouchers', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$y = $y + 20;
$pdf->MultiCell(70, 18, 'Date', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$x = $x + 70;
$pdf->MultiCell(50, 18, 'Ref.No', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$x = $x + 50;
$pdf->MultiCell(70, 18, 'Code', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$x = $x + 70;
$pdf->MultiCell(100, 18, 'Account Title', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$x = $x + 100;
$pdf->MultiCell(100, 18, 'Class', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$x = $x + 100;
$pdf->MultiCell(120, 18, 'Credit', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
$x = $x + 120;
$pdf->MultiCell(80, 18, 'Debit', 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
if (mysqli_multi_query($con, $query)) {
    do {
        // Store first result set
        if ($result = mysqli_store_result($con)) {
            // Fetch one and one row

            while ($row = mysqli_fetch_assoc($result)) {
                $a[] = $row;
                $vNo[] = $row['date'];

                array_multisort($a, SORT_ASC, $vNo);
            }
            // Free result set
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($con));
    array_multisort($a, SORT_ASC, $vNo);
    foreach ($a as $ro) {
        if ($y > 700) {
        $y = 30;
        $pdf->AddPage();
        $x = 10;
    }
        $debit = 0;
        $credit = $ro['credit'];
        if ($credit == "") {
            $credit = 0;
        }
        $debit = $ro['debit'];
        if ($debit == "") {
            $debit = 0;
        }
        $y = $y + 18;
        $x = 10;
        $pdf->SetFont('Times', 'B', 12, '', true);
        $pdf->MultiCell(70, 18, $ro['date'], 1, 'L', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $x = $x + 70;
        $pdf->SetFont('Times', '', 12, '', true);
        $pdf->MultiCell(50, 18, $ro['v.no'], 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $x = $x + 50;
        $pdf->MultiCell(100, 18, $ro['main_code'], 1, 'L', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $x = $x + 70;
        $pdf->MultiCell(100, 18, $ro['account_title'], 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $x = $x + 100;
        $pdf->MultiCell(100, 18, $ro['class_name'], 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $x = $x + 100;
        $pdf->MultiCell(120, 18, $credit, 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $x = $x + 120;
        $pdf->MultiCell(80, 18, $debit, 1, 'C', true, 1, $x, $y, true, 0, false, true, 0, 'T', false);
        $sum += $credit;
    }
}
$y = $y + 20;
$pdf->MultiCell(390, 18, 'Total :', 1, 'R', true, 1, '10', $y, true, 0, false, true, 0, 'T', false);
$pdf->MultiCell(120, 18, $sum, 1, 'C', true, 1, '400', $y, true, 0, false, true, 0, 'T', false);
$pdf->MultiCell(80, 18, $sum, 1, 'C', true, 1, '520', $y, true, 0, false, true, 0, 'T', false);
unset($a);
unset($vNo);
$sum = 0;

}
解决我问题的主要代码是

if (mysqli_multi_query($con, $query)) {
    do {
        // Store first result set
        if ($result = mysqli_store_result($con)) {
            // Fetch one and one row

            while ($row = mysqli_fetch_assoc($result)) {
                $a[] = $row;
                $vNo[] = $row['date'];

                array_multisort($a, SORT_ASC, $vNo);
            }
            // Free result set
            mysqli_free_result($result);
        }
    } while (mysqli_next_result($con));

然后使用foreach循环从名为$a的存储数组中获取数据

赞(0）回复(0）举报 2021-06-21

uidvcgyl2#

直接对数组排序

while ($row = mysqli_fetch_assoc($result)) {

    foreach ($row as $r) {
        $dates[] = $r['date'];
    }

    array_multisort($row, SORT_ASC, $dates);

......

}

赞(0）回复(0）举报 2021-06-21

yh2wf1be3#

您可以修改查询语句。只需附加“订购方式” ref_number 例如，在每个sql查询的末尾。
asc代表升序（从小到大），desc代表降序（从大到小）

赞(0）回复(0）举报 2021-06-21