PHP 数据库查询而不是使用 0 和 1 使用 true 或 false/paid 或 unpaid

Question

新查询

$query = "SELECT COUNT(*) FROM payments $sCriteria";
$result         = mysql_query($query) or die("Error encountered on retrieving logs.");
$query_data     = mysql_fetch_row($result);
$numrows        = $query_data[0];
$rows_per_page  = 30;
$lastpage       = ceil($numrows/$rows_per_page);
$pageno         = (int)$pageno;

if ($pageno > $lastpage) {
    $pageno = $lastpage;
}
if ($pageno < 1) {
    $pageno = 1;
}

$limit          = "LIMIT ".($pageno - 1) * $rows_per_page .",".$rows_per_page;
$rs             = mysql_query("SELECT * FROM payments $sCriteria ORDER BY pay_date DESC $limit") or die("Error encountered on retrieving logs.".mysql_error());
$current_rows   = mysql_num_rows($rs);

if ($current_rows) {
    while ($rows = mysql_fetch_array($rs)) {
        echo "<tr>
                <td>".date("m/d/Y n:H:s",strtotime($rows["pay_date"]))."</td>
                <td>".$rows["po_no"]."</td>
                <td>".$rows["or_no"]."</td>
                <td>".$rows["sold_to"]."</td>
                <td>Php ".number_format($rows["amt_to_pay"],2,".",",")."</td>
                <td>Php ".number_format($rows["amt_paid"],2,".",",")."</td>
                <td>".$rows["pay_status"]."</td>
                **<td>".$rows["verified"]."</td>**
                <td><a href='pay_preview.php?id=$iPayID' class='action preview' title='Print Preview'><img src='images/preview.png' class='action_img' /></a></td>

旧查询

$query          = "SELECT COUNT(*) FROM payments";
$result         = mysql_query($query) or die("Error encountered on retrieving payment details.");
$query_data     = mysql_fetch_row($result);
$numrows        = $query_data[0];
$rows_per_page  = 10;
$lastpage       = ceil($numrows/$rows_per_page);
$pageno         = (int)$pageno;

if ($pageno > $lastpage) {
    $pageno = $lastpage;
}
if ($pageno < 1) {
    $pageno = 1;
}
$limit          = "LIMIT ".($pageno - 1) * $rows_per_page .",".$rows_per_page;
$rs             = mysql_query("SELECT * FROM payments ORDER BY pay_date DESC, po_id $limit") or die("Error encountered on retrieving payment details.");
$current_rows   = mysql_num_rows($rs);

if ($current_rows) {
    while ($rows = mysql_fetch_array($rs)) {
        $iPayID     = $rows["pay_id"];
        $iPoID      = $rows["po_id"];
        $dSubTotal  = get_po_subtotal($iPoID);
        $sPoNo      = get_value("po","po_no","WHERE po_id=$iPoID");
        $iDis       = get_value("po","discount","WHERE po_id=$iPoID");
        $dNet       = $dSubTotal - ($dSubTotal * ($iDis/100));
        $sStat      = $rows["pay_status"];

        switch($sStat) {
            case "Paid":    $sColor = "class='blue'";break;
            default:        $sColor = "";break;
        }

        if ($rows["verified"]) {
            $sVerified = "Yes";
        }
        else {
            $sVerified = "No <a href='payment_verification.php?id=$iPayID' class='action verify' title='Verify' id='$iPayID'>(verify)</a>";
        }
        echo "<tr>

                <td>".date('Y-m-d',strtotime($rows["pay_date"]))."</td>
                <td>".$rows["or_no"]."</td>
                <td>$sPoNo</td>
                <td>".$rows["sold_to"]."</td>
                <td class='right'>Php ".number_format($rows["amt_to_pay"],2,".",",")."</td>
                <td class='right'>Php ".number_format($rows["amt_paid"],2,".",",")."</td>
                <td $sColor>".$rows["pay_status"]."</td>
                <td>$sVerified</td>
                <td><a href='pay_preview.php?id=$iPayID' class='action preview' title='Print Preview'><img src='images/preview.png' class='action_img' /></a></td>
              </tr>\n";
    }
}

我的问题是，在我的新查询中，我无法将已验证的输出更改为付费或未付费，它保持为 0 和 1 ** 我也复制了它并将其与我的旧查询和新查询进行了比较，只是这样它没有用，有人可以帮我吗？非常感谢。

Answer 1

我建议以在 mysql 中使用条件为例:我的 SQL 语句指定了列并且不使用 SELECT *

SELECT IF(Field > 0,'TRUE','FALSE') AS verified FROM Table

您也可以添加一个字段将其更改为paid 或unpaid

SELECT IF(Field > 0,'Paid','Unpaid') AS verified FROM Table

作为例子。您可以添加任何条件和结果输出

还有

在你的代码中，我无法确定 $sVerified 在哪里赋值？