如何将count()group by从mysql保存到php变量中

eivnm1vs 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(461)

我需要将count（activity\ type）group by user\ posted\ to保存到mysql查询的一个变量中

$query = "
        SELECT user_posted_to, COUNT(*)
       FROM activity_log_table 
       WHERE 
           post_type = 'discussion' 
          AND activity_type = 'Like' ";
$query .= "AND activity_timestamp >= CURRENT_DATE - INTERVAL 7 DAY AND activity_type <= CURRENT_DATE ";
$query .= "GROUP BY user_posted_to ORDER BY activity_timestamp DESC LIMIT 25";
$result = mysqli_query($connection, $query);

 while ($row = mysqli_fetch_assoc($result)) {
            $user_posted_to = (int)$row['user_posted_to'];
            $timestamp = time();
            // I need to insert the count into this table for number_assert
            $query2 = "INSERT INTO top_weekly (user_id, number_assert, timestamp) VALUES ($user_posted_to, $timestamp)";
            $result2 = mysqli_query($connection, $query2);
            $confirm_query2 = confirm_query($result2);

        if($confirm_query2) {
            echo "success query 2";
        } else {
            echo "failed query 2";
        }
    }

我希望将count（）group by保存到一个php变量中，以便以后在页面上使用它

mysql php

来源：https://stackoverflow.com/questions/53972733/how-to-save-the-count-group-by-from-mysql-into-a-php-variable

2条答案

按热度按时间

km0tfn4u1#

将和作为标签添加到count（*）之后，您将能够读出您得到的行中的标签。

$query = "SELECT user_posted_to, COUNT(*) AS varCount FROM activity_log_table WHERE post_type = 'discussion' AND activity_type = 'Like' ";

$nummerOfCount = $row['varCount']

赞(0）回复(0）举报 2021-06-18

fzsnzjdm2#

你想给« 计数（*） » 去别的地方，比如« 碳纳米管 ». 然后，您可以在获取后按名称访问该列，如下所示：

$query = "
    SELECT 
        user_posted_to, 
        COUNT(*) as cnt
    FROM activity_log_table 
    WHERE 
        post_type = 'discussion' 
        AND activity_type = 'Like'
       AND activity_timestamp >= CURRENT_DATE - INTERVAL 7 DAY
        AND activity_type <= CURRENT_DATE
    GROUP BY user_posted_to 
    ORDER BY activity_timestamp DESC 
    LIMIT 25";

$result = mysqli_query($connection, $query);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "user_posted_to: " . $row["user_posted_to"]. " - Count: " . $row["cnt"] . "<br>";
    } 
} else {
    echo "0 results";
}

赞(0）回复(0）举报 2021-06-17

我来回答

如何将count()group by从mysql保存到php变量中

2条答案

相关问题

热门标签

最新问答