当前位置： 文江博客话题详情

使用 mysql / php 根据日期时间格式化 fetchall （制作一个简单的统计计数器）

发布于 2024-11-06 14:00:25 字数 7862 浏览 1 评论 0原文

你好，我在这里有点困惑。假设我们正在制作一个简单的页面统计数据。这是我们得到的数据。

假设我们现在没有任何来宾 uid、主机 uid 主机 pid。下面只是一个例子。

  0 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:53:20' (length=19)
  1 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:54:29' (length=19)
  2 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:57:39' (length=19)
  3 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:59:10' (length=19)
  4 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:59:49' (length=19)
  5 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:00:02' (length=19)
  6 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:00:09' (length=19)
  7 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:01:36' (length=19)
  8 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:01:49' (length=19)
  9 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:03:25' (length=19)
  10 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:05:54' (length=19)
  11 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:06:06' (length=19)
  12 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:06:45' (length=19)
  13 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:07:00' (length=19)
  14 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:07:52' (length=19)
  15 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:09:34' (length=19)
  16 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:09:57' (length=19)
  17 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:10:55' (length=19)
  18 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:11:02' (length=19)
  19 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => string '47' (length=2)
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/netcoid' (length=24)
      'timecreate' => string '2011-05-13 15:32:05' (length=19)
  20 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/product?id=47' (length=30)
      'timecreate' => string '2011-05-14 15:32:47' (length=19)
  21 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => string '22' (length=2)
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/netcoid' (length=24)
      'timecreate' => string '2011-05-14 15:32:50' (length=19)

我们如何将其转换为 mysql 或 PHP？

  0 => 
    array
      'totalview' => 19 ( from 0 - 19 array at date 2011-05-13 )
      'timecreate' => string '2011-05-13' (length=19)
      'UNIQview' => 1 ( using mysql distinct(IP) or something )
   1 => 
    array
      'totalview' => 2 ( from 20 - 21 array at date 2011-05-14 )
      'timecreate' => string '2011-05-14' (length=19)
      'UNIQview' => 1 ( using mysql distinct(IP) or something )

任何性能建议（假设我们有 1000 条数据。可以吗？ ）

最后一个问题，如何获取一周的数据？

*编辑 我设法得到类似的信息，

    function getPageViews($date_start = '0000-00-00' ,$date_end = '9999-99-99'){
    $data = $this->fetchAll ( "SELECT COUNT(AID) AS views, LEFT(timecreate, 10) AS date FROM analytics WHERE 
    LEFT(timecreate, 10) BETWEEN $date_start and $date_end GROUP BY(LEFT(timecreate, 10))" );
    return $data;
}

我在为 uniqpage 视图选择不同的 IP 时仍然遇到一些问题。

非常感谢您的关注。Adam

Ramadhan

原文

hello im a bit confused here. lets say we are making a simple page stat. heres the data that we get.

lets say we dont have any guest uid, host uid host pid for now. below is just a example.

  0 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:53:20' (length=19)
  1 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:54:29' (length=19)
  2 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:57:39' (length=19)
  3 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:59:10' (length=19)
  4 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:59:49' (length=19)
  5 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:00:02' (length=19)
  6 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:00:09' (length=19)
  7 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:01:36' (length=19)
  8 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:01:49' (length=19)
  9 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:03:25' (length=19)
  10 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:05:54' (length=19)
  11 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:06:06' (length=19)
  12 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:06:45' (length=19)
  13 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:07:00' (length=19)
  14 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:07:52' (length=19)
  15 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:09:34' (length=19)
  16 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:09:57' (length=19)
  17 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:10:55' (length=19)
  18 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:11:02' (length=19)
  19 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => string '47' (length=2)
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/netcoid' (length=24)
      'timecreate' => string '2011-05-13 15:32:05' (length=19)
  20 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/product?id=47' (length=30)
      'timecreate' => string '2011-05-14 15:32:47' (length=19)
  21 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => string '22' (length=2)
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/netcoid' (length=24)
      'timecreate' => string '2011-05-14 15:32:50' (length=19)

how can we turn it into in the mysql or with php?

  0 => 
    array
      'totalview' => 19 ( from 0 - 19 array at date 2011-05-13 )
      'timecreate' => string '2011-05-13' (length=19)
      'UNIQview' => 1 ( using mysql distinct(IP) or something )
   1 => 
    array
      'totalview' => 2 ( from 20 - 21 array at date 2011-05-14 )
      'timecreate' => string '2011-05-14' (length=19)
      'UNIQview' => 1 ( using mysql distinct(IP) or something )

any suggestion for performance ( lets say we have 1000 of data. is it ok ? )

last question, how do you just fetch one week data?

*edit
ive manage to get something like

    function getPageViews($date_start = '0000-00-00' ,$date_end = '9999-99-99'){
    $data = $this->fetchAll ( "SELECT COUNT(AID) AS views, LEFT(timecreate, 10) AS date FROM analytics WHERE 
    LEFT(timecreate, 10) BETWEEN $date_start and $date_end GROUP BY(LEFT(timecreate, 10))" );
    return $data;
}

i still have some problem on selecting a distinct IP for uniqpage view..

Thankyou very much for looking in.

Adam Ramadhan

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

扫码二维码加入Web技术交流群

发布评论

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

评论（1）

っ左 2024-11-13 14:00:25
SELECT DATE_FORMAT(timecreate,"%Y-%m-%d") as roundDate, 
              COUNT(1) as totalViews, COUNT(DISTINCT IP) as uniqueIPs
         FROM analytics
         GROUP BY roundDate;

请参阅 MySQL DATE_FORMAT有关格式化日期值的更多信息，在这种情况下实际上没有必要使用字符串操作。

SELECT DATE_FORMAT(timecreate,"%Y-%m-%d") as roundDate, 
              COUNT(1) as totalViews, COUNT(DISTINCT IP) as uniqueIPs
         FROM analytics
         GROUP BY roundDate;

See MySQL DATE_FORMAT for more information about formatting date values, there's really no need in using string manipulations in such cases.

回复 原文
~没有更多了~

关于作者

淡看悲欢离合

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

丶视觉

文章 0 评论 0

蓝礼

文章 0 评论 0

birdxs

文章 0 评论 0

foonlee

文章 0 评论 0

微信用户

文章 0 评论 0

っ〆星空下的拥抱

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文