如何使用MySQL和PHP按日期对行进行计数 [英] How to count rows by date using MySQL and PHP

问题描述

好吧， 我正在开发Vote系统，在其中使用以下代码从数据库中选择数据:

Well, I am developing Vote system where, i am selecting data from database using following code:

// Select info about vote
        $select_information = mysql_query("SELECT * FROM `Vote` WHERE `VoteID` = '$VoteID';");
            $take = mysql_fetch_array($select_information);
            $createdBy = $take["Username"];
            $InstaCredit = $take["InstaCredit"];
            $activeVote = $take["Available"];
            $select_last_vote = mysql_query("SELECT `Date` FROM `voteslist` WHERE `IP` = '$ip' AND `VoteID` = '$VoteID' order by `Date` DESC LIMIT 1;");
            $select_last_vote = mysql_fetch_array($select_last_vote);
            $dbdate = $select_last_vote["Date"];

## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## 
// Here i am select date. retrieve just the date part
            $takeResult = mysql_query("SELECT DATE_FORMAT(STR_TO_DATE('$dbdate', '%d/%m/%Y' ), '%d/%m/%Y') as Date ;");
            $Result_date = mysql_fetch_array($takeResult);
            $result_date = $Result_date["Date"];
            $today = date("d/m/Y");
            $thanks_to = $createdBy;

// here my problem. I want count all votes from today.
            $today_voting_query = "SELECT DATE_FORMAT(STR_TO_DATE('$dbdate', '%d/%m/%Y' ), '%d/%m/%Y') as Date COUNT(`ID`) FROM `voteslist` WHERE `VoteID` = '$VoteID' AND `Date` = '$result_date';";
            $today_voting = mysql_fetch_array($today_voting_query);
            $today_voting = $today_voting[0];
## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ## ##

            //now compare your dates
            if($result_date == $today)
            {
                echo "<span class='thanks-for-voting voted'>
                error <strong>{$thanks_to}</strong>
                </span><br />
                <span class='note-vote voted'>You can vote one time in 24 hours</span>";
            }
            else {
                echo "
                <span class='thanks-for-voting'>
                Thanks for vote to <strong>{$thanks_to}</strong>!
                </span>
                ";
            }
    ?>
        <div class="statics">
        <span id="static-title">Statics <?php echo $thanks_to; ?></span><hr />
        <span id="static-q">Today votes:</span> <span id="static-a"><?php echo $today_voting; ?></span><br />
        <span id="static-q">Votes:</span> <span id="static-a">19</span><br />
        </div>

我需要通过以下变量来计数和打印今日投票"的值: $today_voting

I need to count and print the value of Today's votes by the following variable: $today_voting

请参阅: 使用PHP MySql从我的数据库中选择日期而无需几秒钟

推荐答案

将逗号放在Date& COUNT(ID)

Put comma between Date & COUNT(ID)

$today_voting_query = "SELECT 
DATE_FORMAT(STR_TO_DATE('$dbdate', '%d/%m/%Y' ), '%d/%m/%Y') as Date, 
COUNT(ID) AS countID FROM `voteslist` WHERE `VoteID` = '$VoteID' AND `Date` = '$result_date';";

这篇关于如何使用MySQL和PHP按日期对行进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！