我有一个奇怪的问题:PHP调用一个从有序视图中选择的MariaDB存储过程。与通过命令行运行存储过程或直接从视图中选择相比,PHP结果按不同的日期顺序排序。
可能与PHP只能将日期作为字符串处理有关?(HTML5日期类型与所需浏览器不兼容)但我假设数据在返回到PHP之前已经排序。
谢谢你的阅读!
通过命令行存储过程的结果:

2017-12-01 | Attendance  |   4.00
2017-12-03 | Clay        |  18.00
2017-12-13 | Attendance  |   4.00
**2017-12-16 | Firing      |  12.00**

通过PHP调用存储过程的结果:
Date  Transaction Amount
2017-12-01    Attendance  $4.00
2017-12-03    Clay    $18.00
**2017-12-16  Firing  $12.00**
2017-12-13    Attendance  $4.00

以下是相关代码:
SQL顺序视图:
Create View
`view_invoices`
AS select `view_sum`.`Total` AS `Total`,
    `view_sum`.`Member_ID` AS `Member_ID`,
    `bpg`.`accounts`.`Date` AS `Date`,
    `bpg`.`accounts`.`Transaction` AS `Transaction`,
    `bpg`.`accounts`.`Amount` AS `Amount`,
    `bpg`.`members`.`First_name` AS `First_name`,
    `bpg`.`members`.`Email` AS `Email`,
    `view_member_names`.`Member_name` AS `Member_name`
from ((((select sum(`bpg`.`accounts`.`Amount`) AS `Total`,
        `bpg`.`accounts`.`Member_ID` AS `Member_ID`
        from `bpg`.`accounts`
group by `bpg`.`accounts`.`Member_ID`)
    `view_sum` join `bpg`.`accounts`)
    join `bpg`.`members`)
    join `bpg`.`view_member_names`)
where `view_sum`.`Member_ID` = `bpg`.`accounts`.`Member_ID`
and `view_sum`.`Member_ID` = `bpg`.`members`.`Member_ID`
and `view_sum`.`Member_ID` = `view_member_names`.`Member_ID`
order by `view_member_names`.`Member_name`, `accounts`.`Date`;

SQL存储过程:
DELIMITER $$

CREATE PROCEDURE `invoices`
    (parameter_start_date DATE
    , parameter_end_date DATE)

    NOT DETERMINISTIC
    MODIFIES SQL DATA

    BEGIN

    SELECT * FROM `view_invoices`
    WHERE `Date` >= parameter_start_date
    AND `Date` <= parameter_end_date
    ;
    END;
$$
DELIMITER ;

PHP调用:
<?php

function invoices($start_date, $end_date) {
        require LIB_DIR . '\pdo.php';

        // in development
        ini_set('display_errors',1);
        error_reporting(E_ALL);

    $prev = NULL;
    $intro = "<body style='background-color:#f0f2f4;font-family:Calibri,Verdana,Arial;'>";
    $intro .= "<ul style='float:left;list-style-type:none;width:100%;background-color:#d3d9de;'>";
    $intro .=    "<li style='display:inline;float:left;border-width:10px;border-color:#d3d9de;padding:5px;margin:5px;background-color:#FEFEFE;align-self:center;'>";
    $intro .= "<img src='PATH\public\images\BPG_logo_big.png' height='166' width='173'></li>";
    $intro .= "<li style='display:inline;float:left;border:5px;border-color:#d3d9de;margin:5px;padding:5px;background-color:#FEFEFE;width:250px;height:166px;align-self:center;text-indent:5pxeach-line;'>";
    $intro .= "<h2>Invoice</h2>";
    $intro .= "<p style='margin-left:15px;margin-bottom:0px;'>39 Elizabeth Street<br>Cloverdale WA 6076<br>treasurerbpg@gmail.com</p></li></ul>";

    $outro = "<p style='margin-left:15px;margin-bottom:0px;'>You can pay by direct deposit, at your bank, or by cheque using the details below.</p>";
    $outro .= "<p style='font-weight:bold;margin-left:15px;margin-bottom:0px;'>Please use your name as the payment reference. </p>";

    $sql = "CALL invoices (:start_date, :end_date)";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':start_date', $start_date);
    $stmt->bindValue(':end_date', $end_date);
    $stmt->execute();
    $stmt->bindColumn('Total', $total);
    $stmt->bindColumn('Member_ID', $id);
    $stmt->bindColumn('Date', $date);
    $stmt->bindColumn('Transaction', $transaction);
    $stmt->bindColumn('Amount', $amount);
    $stmt->bindColumn('First_name', $fname);
    $stmt->bindColumn('Email', $email);
    $stmt->bindColumn('Member_name', $name);

    $message = NULL;
    $curr_date = date("Y.m.d");
    $due_date = strtotime("+7 day");
    $due_date = date('j F Y', $due_date);
    $curr_date_words = date('j F Y');
    $start_date_words = strtotime($start_date);
    $start_date_words = date('j F Y', $start_date_words);
    $end_date_words = strtotime($end_date);
    $end_date_words = date('j F Y', $end_date_words);
    $filename = NULL;
    $number = $stmt->rowCount();
    echo $number;

    while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {

        $number = --$number;
        $person = $id;

        if ($person != $prev) {    // if the current member is different from the previous member, print header and row

            if ($row > 0) {
                $message .= "</table>"; /*added line of code*/
                $message .= $outro;


                if($filename != NULL) {
                    $savedfile = fopen(WRITE_DIR . $filename, "w") or die("File BPG_invoice_" . $filename . ".html was not created.");
                    fwrite($savedfile, $message);
                    fclose($savedfile);
                    echo $filename . " was saved here: " . WRITE_DIR . "<br>";
                }

          }


    // file name

            $filename = "\BPG_invoice_" . $curr_date . "_" . $name . ".html";
    // message
            $message = $intro;

            $message .= "<br><br><p style='margin-left:15px;'>Member: " . $name . "<br>";
            $message .= "Date: " . $curr_date_words . "</p>";
            $message .= "<p style='margin-left:15px;margin-bottom:0px;'><br>Dear " . $fname . "</p>";
            $message .= "<p style='margin-left:15px;margin-bottom:0px;'>Please find your fees for the period " . $start_date_words . " to " . $end_date_words . " below. </p>";
            $message .= "<p style='margin-left:15px;margin-bottom:0px;font-size:1.6em;font-weight:bold;'>The total of your invoice is: $" . $total . "</p>";


        if($total <= 0 ) {
            $message .= "<p style='margin-left:15px;margin-bottom:0px;'>Your account is in credit and does not require payment.</p>";
        }else{
            $message .= "<p style='margin-left:15px;margin-bottom:0px;'>Please pay before: " . $due_date . "</p>";
        }

            $message .= "<br><table style='margin-left:15px;width:350px;border-width:1px;border:solid;border:black;'><tr><th  style='padding:5px;text-align:center;'>Date</th><th style='padding:5px;text-align:center;'>Transaction</th><th style='padding:5px;text-align:center;'>Amount</th></tr>";

    // table rows

            $message .= "<tr><td style='padding:5px;text-align:center;'>" . $date . "</td>";
            $message .= "<td style='padding:5px;text-align:center;'>" . $transaction . "</td>";
            $message .= "<td style='padding:5px;text-align:center;'>$" . $amount . "</td></tr>";

    // separate if statement because || or was not working - DUPLICATED
        if($number == 0) {
    //        last_row();

                $message .= "</table>";
                $message .= $outro;

                $savedfile = fopen(WRITE_DIR . $filename, "w") or die("File BPG_invoice_" . $filename . ".html was not created.");
                fwrite($savedfile, $message);
                fclose($savedfile);
                echo $filename . " was saved here: " . WRITE_DIR . "<br>";
       }


            $prev = $person;

        }else{

            $message .= "<tr><td style='padding:5px;text-align:center;'>" . $date . "</td>";
            $message .= "<td style='padding:5px;text-align:center;'>" . $transaction . "</td>";
            $message .= "<td style='padding:5px;text-align:center;'>$" . $amount . "</td></tr>";

            $prev = $person;

// separate if statement because || or was not working - DUPLICATED
        if($number == 0) {
        //    last_row();

                $message .= "</table>";
                $message .= $outro;

                $savedfile = fopen(WRITE_DIR . $filename, "w") or die("File BPG_invoice_" . $filename . ".html was not created.");
                fwrite($savedfile, $message);
                fclose($savedfile);
                echo $filename . " was saved here: " . WRITE_DIR . "<br>";
            }

        }

    }
    $message .= "</table>";
 }

?>

最佳答案

如果在返回结果的ORDER BY语句中没有SELECT语句,MySQL/MariaDB可以按任意顺序返回行并在规范内(服务器可以忽略视图定义中的ORDER BY子句)
至于PHP和命令行客户机之间的行为差异,这有点奇怪(假设我们连接到相同的数据库、相同的凭据、调用相同的过程)

10-08 05:31
查看更多