使用 SQL 和 php 创建网站使用情况的每周和每月图表

发布于 2024-12-15 06:14:05 字数 2005 浏览 2 评论 0原文

因此，我正在为我正在为工作而构建的网站构建一个统计部分（经理要求）。我有一个“24 小时”图表的功能代码。我的图形代码工作完美，但我无法为 mysql 获取正确的代码来根据天而不是小时提取数据

下方的每小时代码

function get_hourly_graph() {
    // First lets create a daily graph for experiment then we can build on that
    date_default_timezone_set('Australia/Sydney');
    $current_hour = date('H');

    // SQL Query to get data we want
    $sql = mysql_query("SELECT hour( time_hit ) AS hour , count( hour( time_hit ) ) AS hits FROM hits WHERE time_hit > now( ) - INTERVAL 1 DAY GROUP BY hour( time_hit );")
        or die(mysql_error());

    // Remove an hour off current time for array and the loop that will put the data into the array
    $array_hour = $current_hour - 1;

    // Add a 0 if the timer is under 10. just so this is compatable with the SQL data
    if ($array_hour < 10) {
        $array_hour = 0 . $array_hour;
    }

    // initialise the array so we can set up the indexes and data in the next couple of loops
    $hit_data = array();

    // Work back the clock 24 hours and create the array indexes
    for ($i = 0; $i < 24; $i++) {
        $new_hour = $current_hour;

        if ($new_hour < 1) {
            $new_hour = 24;
            $hit_data[$new_hour] = "0";
        }
        else {
            $hit_data[$new_hour] = "0";
        }

        $current_hour = $new_hour - 1;
    }

    // Loop through  the rest of the array and replace null with 0s
    while ($results = mysql_fetch_array($sql, MYSQL_ASSOC)) {
        foreach ($hit_data as $hour => $hits) {
            if ($results['hour'] == $hour) {
                $hit_data[$hour] = $results['hits'];
            }
        }
    }

    $hit_data = array_reverse($hit_data, true);

    // Generate the graph for the 24 hour data
    generate_graph ($hit_data, '24 Hour Report', 'hourly_graph.png', 'Time (Hours)', 'Hits');
}

“点击”表基本上是 3 个字段“hit_id”“ip_address”“timestamp”位于 'YYYY-MM-DD hh:mm:ss' = "2011-08-04 12:40:34" 下

如何更改 sql 代码下面以天而不是小时来工作？

原文

So i'm building an statistics section for a website i'm building for work (managers request). i have a functioning code for a '24 hour' graph. The graph code i have works perfectly but i'm having troubles getting the right code for mysql to pull out data based on days rather then hours

Hourly code below

function get_hourly_graph() {
    // First lets create a daily graph for experiment then we can build on that
    date_default_timezone_set('Australia/Sydney');
    $current_hour = date('H');

    // SQL Query to get data we want
    $sql = mysql_query("SELECT hour( time_hit ) AS hour , count( hour( time_hit ) ) AS hits FROM hits WHERE time_hit > now( ) - INTERVAL 1 DAY GROUP BY hour( time_hit );")
        or die(mysql_error());

    // Remove an hour off current time for array and the loop that will put the data into the array
    $array_hour = $current_hour - 1;

    // Add a 0 if the timer is under 10. just so this is compatable with the SQL data
    if ($array_hour < 10) {
        $array_hour = 0 . $array_hour;
    }

    // initialise the array so we can set up the indexes and data in the next couple of loops
    $hit_data = array();

    // Work back the clock 24 hours and create the array indexes
    for ($i = 0; $i < 24; $i++) {
        $new_hour = $current_hour;

        if ($new_hour < 1) {
            $new_hour = 24;
            $hit_data[$new_hour] = "0";
        }
        else {
            $hit_data[$new_hour] = "0";
        }

        $current_hour = $new_hour - 1;
    }

    // Loop through  the rest of the array and replace null with 0s
    while ($results = mysql_fetch_array($sql, MYSQL_ASSOC)) {
        foreach ($hit_data as $hour => $hits) {
            if ($results['hour'] == $hour) {
                $hit_data[$hour] = $results['hits'];
            }
        }
    }

    $hit_data = array_reverse($hit_data, true);

    // Generate the graph for the 24 hour data
    generate_graph ($hit_data, '24 Hour Report', 'hourly_graph.png', 'Time (Hours)', 'Hits');
}

the 'hits' table is basically 3 fields 'hit_id' 'ip_address' 'timestamp' which is under 'YYYY-MM-DD hh:mm:ss' = "2011-08-04 12:40:34"

How do i change the sql code below to work with days instead of hours?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柏拉图鍀咏恒 2024-12-22 06:14:05

SELECT DATE_FORMAT('date_column', '%Y-%m-%e') as day ...... group by day

http://dev.mysql .com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

SELECT DATE_FORMAT('date_column', '%Y-%m-%e') as day ...... group by day

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

回复收藏 0 原文

呆萌少年 2024-12-22 06:14:05

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <!--Script Reference[1]-->

  <script src="https://cdn.zingchart.com/zingchart.min.js"></script>
</head>
<body>
  <!--Chart Placement[2]-->
  <div id="chartDiv"></div>
  <script>
    var chartData = {
      type: "bar",  // Specify your chart type here.
      title: {
        text: "My First Chart" // Adds a title to your chart
      },
      legend: {}, // Creates an interactive legend
      series: [  // Insert your series data here.
          { values: [10, 42, 67, 89,70]},
      
      ]
    };
    zingchart.render({ // Render Method[3]
      id: "chartDiv",
      data: chartData,
      height: 400,
      width: 400
    });
  </script>
</body>
</html>

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <!--Script Reference[1]-->

  <script src="https://cdn.zingchart.com/zingchart.min.js"></script>
</head>
<body>
  <!--Chart Placement[2]-->
  <div id="chartDiv"></div>
  <script>
    var chartData = {
      type: "bar",  // Specify your chart type here.
      title: {
        text: "My First Chart" // Adds a title to your chart
      },
      legend: {}, // Creates an interactive legend
      series: [  // Insert your series data here.
          { values: [10, 42, 67, 89,70]},
      
      ]
    };
    zingchart.render({ // Render Method[3]
      id: "chartDiv",
      data: chartData,
      height: 400,
      width: 400
    });
  </script>
</body>
</html>

回复收藏 0 原文

~没有更多了~