PHP - 无法从 MySQL 数据库查询中按月份过滤

Question

所以我正在本地的大学做我的学生工作，我们必须根据给定的主题创建整个网站。我选择“ parking 场”主题，然后卡住了。陷入困境，不知道去哪里寻找问题。我需要做的是从数据库中过滤给定的年份和月份数据，如下所示:

我的查询，对该表中的数据进行排序，如下所示:

SELECT COUNT(parking_time) AS "Amont", SUM(sum) AS "Sum" 

    FROM parking_info

    GROUP BY MONTH(parking_time), YEAR(parking_time).

以及此查询的输出:

如您所见，用户应该从下拉列表中选择年份和月份，以选择特定月份来过滤 MySQL 查询中的数据。例如:如果用户从下拉列表 10 月中选择年 2013 和月，则它应该只为用户输出特定月份的信息，即金额 4/Sum 170。但就我而言，它不会输出任何内容(正如您在我的网页中看到的那样)。

基本上我有 functions.php 文件，其中包含所有计数、过滤器和 SQL 查询。此特定页面的 functions.php 文件如下所示:

<?php 



function cleanFilterArray($filters) {

    $new_filters = array();

    if ($filters) {

        foreach ($filters as $field => $data) {

            if ($data != '%%') {

                $new_filters[$field] = $data;

            }

        }

        return $new_filters;

    } else {

        return false;

    }

}



function get_months_in_LT()

{

    $months = array();

    $months[] = array(1, 'january');

    $months[] = array(2, 'february');

    $months[] = array(3, 'march');

    $months[] = array(4, 'april');

    $months[] = array(5, 'may');

    $months[] = array(6, 'june');

    $months[] = array(7, 'july');

    $months[] = array(8, 'august');

    $months[] = array(9, 'september');

    $months[] = array(10, 'october');

    $months[] = array(11, 'november');

    $months[] = array(12, 'december');



    return $months;

}

function get_month_in_LT($num)

{

    $months = get_months_in_LT();

    return $months[$num][1];

}

function get_month_report($filters) {



    $where = null;



    if ($filters) {

            $filters = cleanFilterArray($filters);



            foreach ($filters as $field => $data) {

                if (!empty($data)) {

                    $where .= $field.' \''.$data.'\' AND ';

                }

            }

            if ($where) {

                $where = 'WHERE '.substr($where, 0, -5); //removes last 'AND' and adds a 'WHERE' to the begining

            }

    }

    var_dump($where);

    var_dump($filters);

    $result = mysql_query('

    SELECT COUNT(parking_time) AS "Amont", SUM(sum) AS "Sum" 

    FROM parking_info

    GROUP BY MONTH(parking_time), YEAR(parking_time)

    '.$where.'

    ');



    $rows = array();

    while ($row = mysql_fetch_row($result))

    {       

        $rows[] = $row; 

    }       



    return $rows;   

}

?>

我的实际页面包含所有 HTML 和所有其他内容，如下所示:

<?php 



include_once 'functions.php';



if (isset($_GET['submit']))

{

  $year = $_GET['year'];

  $month = $_GET['month'];

}

else

{

  $year = date('Y');

  $month = date('m');

}





if (isset($_GET['submit']))

{

  if (isset($year) && $year > (int) date('Y'))

  {

    $error = 'this years has not yet come!';

  }

  if (isset($month) && $month > (int) date('m'))

  {

    $error = 'this month has not yet come';

  }

  elseif (isset($year) && $year < 0)

  {

    $error = 'date cannot be negative';    

  }

  elseif (isset($month) && ($month < 1 || $month > 12))

  {

    $error = 'not a month';

  }    


}



if (isset($_GET['submit']))

{

  $year = $_GET['year'];

  $month = $_GET['month'];



  $filters = array(

    "parking_time LIKE" => '%'.$year.'%',

    "parking_time LIKE" => '%'.$month.'%'

  );



}


include_once 'mysql.php';

if (isset($_GET['submit']) && !isset($error)){

  $infos = get_month_report($filters);  

}

$months = get_months_in_LT();



var_dump($infos);

?>



<?php include_once 'header.php' ?>

    <h1>Month Report</h1>

    <div id="main">

      <p>How much per month was observed cars and what is the total amount of money collected</p>

      <?php 

        if (isset($error))

        {

          echo "<p class=\"error\">Klaida: $error</p>";

        }    

      ?>

      <form method="post">

        <table border="1"> 

          <tr>

            <th>Year<span style="color:red">*</span></th>

            <td><input type="text" name="year" value="<?php if (isset($year)) echo $year ?>" /></td>

          </tr>

          <tr>

            <th>Month</th>

            <td>

              <select name="month" style="width:100%">

                <?php 

                  foreach ($months as $montha)

                  {

                    if (isset($month) && $month == $montha[0])

                    {

                      echo "<option value=\"$montha[0]\" selected=\"selected\">$montha[1]</option>";

                    }

                    else

                    {

                      echo "<option value=\"$montha[0]\">$montha[1]</option>";

                    }

                  }

                ?>

              </select> 

            </td>

          </tr>

          <tr>

            <td colspan="2" class="search"><button type="submit" name="submit" style="width:100%">Search</button></td>

          </tr>

        </table>

      </form>

      <br />

      <br />

      <?php if (isset($_GET['submit']) && sizeof($error) == 0): ?>

      <table border="1"> 

        <tr>

          <th>Amount</th>

          <th>Sum, LT</th>

        </tr>

        <?php

          foreach ($infos as $info)

          {

            echo "<tr>";

            echo "<td>$info[0]</td>";

            echo "<td>$info[1]</td>";      

            echo "</tr>";

          }

          if (count($infos) == 0)

          {

            echo "<tr><td colspan=\"2\">Rezultatų nėra.</td></tr>";        

          }

        ?>

      </table>  

      <?php endif; ?>

      <br/>

      <br/>

      <br/>

      <br/>

    </div>

<?php include_once 'footer.php' ?>  

希望有人能理解我真正需要什么，因为我只是卡住了，不知道为什么这个页面不输出任何内容。谢谢。

Answer 1

在查询中，您可以使用表列中的月份和年份，如下所示。

 Month(parking_time) = $month   and YEAR(parking_time) = $year

现在，在您要创建查询的文件中添加这些内容，而不是使用 like 进行比较。