php - 在 SUM 中调用或创建 session 变量

Question

我对 PHP 或 MySQL 不是很熟悉，但是在这里研究之后，我已经能够学习并非常接近我需要的东西，并构建了我的第一个 sum询问。我正在尝试读取数据库并根据几个变量求和值。

我需要预订表中的 reservation_pax，其中 reservation_time 是 8:00，其中 reservation_hidden = 0 和 reservation_date 是什么。我可以手动输入日期并且它有效。我现在正在尝试使用脚本中已有的 session 代码，或者找到一种方法来根据选定的日期动态添加。

这是我在没有动态方面或 session 的情况下工作的代码。

$result = mysql_query("SELECT SUM(reservation_pax)
                       FROM reservations
                       WHERE reservation_time = '8:00:00'
                       AND reservation_date = '2014-10-27'
                       AND reservation_hidden ='0'") ; 
if ($result === false) {
    die(mysql_error()); // TODO: better error handling
}

while($row = mysql_fetch_array($result)) {
   echo $row['SUM(reservation_pax)'];
}

这是我输入上述内容的页面的完整代码。谁能帮我弄清楚如何调用所选日期而不是手动输入。

<!-- Begin reservation table data -->
<br/>
<table class="global resv-table-small" cellpadding="0" cellspacing="0">
    <tbody>
        <tr>

        <?php
        echo "<td class='noprint'> </td>";
        echo "<td>Time</td>";
        echo "<td>Guests/Type</td>";
        echo "<td>Name</td>";
        echo "<td>Special Instructions/Notes</td>";
        echo "<td class='noprint'>Table</td>";
        echo "<td class='noprint'>Status</td>";
        echo "<td class='noprint'>Created</td>";
        echo "<td class='noprint'>Details/Delete</td>";

    echo "</tr>";

        // Clear reservation variable
        $reservations ='';

        if ($_SESSION['page'] == 1) {
            $reservations = querySQL('all_reservations');
        }else{
            $reservations = querySQL('reservations');
        }

        // reset total counters
        $tablesum = 0;
        $guestsum = 0;

        if ($reservations) {

            //start printing out reservation grid
            foreach($reservations as $row) {
                // reservation ID
                $id = $row->reservation_id;
                $_SESSION['reservation_guest_name'] = $row->reservation_guest_name;
                // check if reservation is tautologous
                $tautologous = querySQL('tautologous');

            echo "<tr id='res-".$id."'>";
            echo "<td";
            // daylight coloring
            if ($row->reservation_time > $daylight_evening){
                echo " class='evening noprint'";
            }else if ($row->reservation_time > $daylight_noon){
                echo " class='afternoon noprint'";
            }else if ($row->reservation_time < $daylight_noon){
                echo " class='morning noprint'";
            }

            echo " style='width:10px !important; padding:0px;'>&nbsp;</td>";
            echo "<td id='tb_time'";
            // reservation after maitre message
            if ($row->reservation_timestamp > $maitre['maitre_timestamp'] && $maitre['maitre_comment_day']!='') {
                echo " class='tautologous' title='"._sentence_13."' ";
            }
            echo ">";
            echo "<strong>".formatTime($row->reservation_time,$general['timeformat'])."</strong></td>";
            echo "<td id='tb_pax'><strong class='big'>".$row->reservation_pax."</strong>&nbsp;&nbsp;<span class='noprint'>";
            printType($row->reservation_hotelguest_yn);
                //echo "<img src='images/icons/user-silhouette.png' class='middle'/>";
            echo "</span></td><td style='width:10%' id='tb_name'><span class='noprint'>".printTitle($row->reservation_title)."</span><strong> <a id='detlbuttontrigger' href='ajax/guest_detail.php?id=".$id."'"; 
            // color guest name if tautologous
            if($tautologous>1){echo" class='tautologous tipsy' title='"._tautologous_booking."'";}
            echo ">".$row->reservation_guest_name."</a></strong>";

            // old reservations symbol
            if( (strtotime($row->reservation_timestamp) + $general['old_days']*86400) <= time() ){
                echo "<img src='images/icons/clock-bolt.png' class='help tipsyold middle smicon' title='"._sentence_11."' />";
            }
            // recurring symbol
            if ($row->repeat_id !=0) {
                echo "&nbsp;<img src='images/icons/loop-alt.png' alt='"._recurring.
                     "' title='"._recurring."' class='tipsy' border='0' >";
            }

            echo"</td><td style='width:10%' id='tb_note'>";
                if ($_SESSION['page'] == 1) {
                    echo $row->outlet_name;
                }else{
                    echo $row->reservation_notes;
                }
            echo "</td>";


            if($_SESSION['wait'] == 0){
                echo "<td class='big tb_nr' style='width:85px;' id='tb_table'><img src='images/icons/table_II.png' class='tipsy leftside noprint' title='"._table."' /><div id='reservation_table-".$id."' class='inlineedit'>".$row->reservation_table."</div></td>";
            }
            echo "<td class='noprint'><div>";
                getStatusList($id, $row->reservation_status);
            echo "</div></td>";
            echo "<td class='noprint'>";
            echo "<small>".$row->reservation_booker_name." | ".humanize($row->reservation_timestamp)."</small>";
            echo "</td>";
            echo "<td class='noprint'>";
            // MOVE BUTTON
            //  echo "<a href=''><img src='images/icons/arrow.png' alt='move' class='help' title='"._move_reservation_to."'/></a>";

            // WAITLIST ALLOW BUTTON
            if($_SESSION['wait'] == 1){
                $leftspace = leftSpace(substr($row->reservation_time,0,5), $availability);
                if($leftspace >= $row->reservation_pax && $_SESSION['outlet_max_tables']-$tbl_availability[substr($row->reservation_time,0,5)] >= 1){       
                    echo"&nbsp;<a href='#' name='".$id."' class='alwbtn'><img src='images/icons/check-alt.png' name='".$id."' alt='"._allow."' class='help' title='"._allow."'/></a>&nbsp;&nbsp;";
                }
            }
            // EDIT/DETAIL BUTTON
            echo "<a href='?p=102&resID=".$id."'><img src='images/icons/pen-fill.png' alt='"._detail."' class='help' title='"._detail."'/></a>&nbsp;&nbsp;";
            // DELETE BUTTON
            if ( current_user_can( 'Reservation-Delete' ) && $q!=3 ){
                echo"<a href='#modalsecurity' name='".$row->repeat_id."' id='".$id."' class='delbtn'>
                    <img src='images/icons/delete.png' alt='"._cancelled."' class='help' title='"._delete."'/></a>";
            }
        echo"</td></tr>";
        $tablesum ++;
        $guestsum += $row->reservation_pax;
            }
        }
        ?>
    </tbody>
    <tfoot>


        <tr style="border:1px #000;">
            <td class=" noprint"></td><td></td>
            <td colspan="2" class="bold"><?php echo $guestsum;?>&nbsp;&nbsp;<?php echo _guest_summary;?></td>
            <td></td>
            <td colspan="2" class="bold"><?php echo $tablesum;?>&nbsp;&nbsp;<?php echo _tables_summary;?></td>
            <td colspan="2" class="bold"><?php 
            $result = mysql_query("SELECT SUM(`reservation_pax`) FROM `reservations` WHERE `reservation_time` = '8:00:00' AND `reservation_date` = '{$_SESSION['selectedDate']}' AND `reservation_hidden` ='0'") ; 
if($result === FALSE) {
    die(mysql_error()); // TODO: better error handling
}

while($row = mysql_fetch_array($result))
{
   echo $row['SUM(reservation_pax)'];
}

?>

&nbsp;&nbsp;<?php echo '/ 40 ', _guest_summary, ' -8:00 AM';?></td>
            <td></td>
            <?php
            if($_SESSION['wait'] == 0){
                //echo "<td></td>";
            }
            ?>
        </tr>
    </tfoot>
</table>
<!-- End reservation table data -->

Answer 1

你可以使用alias字段，如:

$result = mysql_query("SELECT SUM(reservation_pax) AS reserve_sum
            FROM reservations WHERE reservation_time = '8:00:00' 
            AND reservation_date = '2014-10-27' 
            AND reservation_hidden ='0'"
        ) ; 
....

并以以下方式访问它:

while($row = mysql_fetch_array($result))
{
   echo $row['reserve_sum'];
}