PHP 프로그래밍

본문 바로가기
사이트 내 전체검색
Web Programming >> PHP Programming
[목차]
제8장 카운터 프로그램(MySQL)

    1. MySQL을 이용한 카운터 프로그램

1. 카운터용 DB 및 테이블 만들기

파일명 : counter.sql

CREATE TABLE counter (
  date int(10) unsigned NOT NULL default '0',
  cnt int(10) unsigned NOT NULL default '0',
  remote_addr char(15) default NULL,
  PRIMARY KEY  (date)
);


# mysql -u root -p counter_db < counter.sql
와 같이 복구한다.

 
2. 카운터 프로그램
파일명 : counter.html
<?
/* 이것은 카운터 프로그램입니다. 
사용자의 접속 수를 카운터하고 화면으로 보여주는 기능을 합니다. */ 

function error($errcode) {
   echo("<script language=\"javascript\"> 
   <!--
   alert('$errcode');
   history.back();
   //-->   
   </script>");
   exit;
}

$dbconn = mysql_connect("localhost","leelab",
"leelab") or die("데이터베이스 연결에 실패하였습니다.");

$status = mysql_select_db("counter_db");
if (!$status) {
   error("DB_ERROR");
   exit;
}

$remote_addr = $REMOTE_ADDR;
$today = time();
$year = date("Y",$today);
$mon = date("m",$today);
$day = date("d",$today);
$hour = date("H",$today);
$curdate = mktime($hour,1,1,$mon,$day,$year);

$result = mysql_query("SELECT cnt,remote_addr FROM counter 
WHERE  date = $curdate");
if (!$result) {
   error("QUERY_ERROR");
   exit;
}
$rows = mysql_num_rows($result);

if($rows) { 
    $count = intval(mysql_result($result,0,0));
    $prev_addr = mysql_result($result,0,1);
} 
else { 
	$count =  0; 
}


if(strcmp($prev_addr ,$remote_addr)) {
	$count = $count + 1; 
	$prev_addr = $remote_addr;
	if($rows) {
		$query  = "UPDATE counter SET cnt=$count,
		remote_addr='$remote_addr' WHERE date = '$curdate'"; 
	   $result = mysql_query($query);
	   if(!$result) {
		  error("QUERY_ERROR");
		  exit;
	   }
		
	}
	else {
		$query = "INSERT INTO counter 
		VALUES ($curdate,$count,'$remote_addr')";
		$result = mysql_query($query);
		if (!$result) {      
		   error("QUERY_ERROR");
		   exit;
		}
	}
}

// 전체 방문수
$result = mysql_query("SELECT sum(cnt) FROM counter");
if (!$result) {
   error("QUERY_ERROR");
   exit;
}
$total_count = mysql_result($result,0,0);

// 오늘 방문수
$sdate = mktime(0,1,1,$mon,$day,$year);
$edate = mktime(24,1,1,$mon,$day,$year);

$result = mysql_query("SELECT sum(cnt) FROM counter 
where date >= $sdate and date < $edate");
if (!$result) {
   error("QUERY_ERROR");
   exit;
}
$today_count = mysql_result($result,0,0);

// 어제 방문수
$sdate = mktime(0,1,1,$mon,$day-1,$year);
$edate = mktime(24,1,1,$mon,$day-1,$year);

$result = mysql_query("SELECT sum(cnt) FROM counter 
where date >= $sdate and date < $edate");
if (!$result) {
   error("QUERY_ERROR");
   exit;
}
$yesterday_count = mysql_result($result,0,0);
?> 
<table width=150 align=center>
<tr><td colspan=2>
<hr size=1>
</td></tr>
<tr><td align=left>
<font color=blue><span style="font-size:9pt;">&nbsp;오늘 :</span></font>
</td><td align=right>
<font color=blue><span style="font-size:9pt;"><?echo "
$today_count 번";?>&nbsp;</span></font>
</td></tr>
<tr><td align=left>
<font color=blue><span style="font-size:9pt;">&nbsp;어제 :</span></font>
</td><td align=right>
<font color=blue><span style="font-size:9pt;"><?echo "
$yesterday_count 번";?>&nbsp;</span></font>
</td></tr>
<tr><td align=left>
<font color=blue><span style="font-size:9pt;">&nbsp;총합 :</span></font>
</td><td align=right>
<font color=blue><span style="font-size:9pt;"><?echo "
$total_count 번";?>&nbsp;</span></font>
</td></tr>
<tr><td colspan=2 align=center>
<hr size=1>
<span style="font-size:8pt;">Today : 
<?echo $year;?>년 <?echo $mon;?>월 <?echo 
$day;?>일</span></font>
</td></tr></table>

 
3. 통계보기
파일명 : status.html

<?

function error($errcode) {
   echo("<script language=\"javascript\"> 
   <!--
   alert('$errcode');
   history.back();
   //-->   
   </script>");
   exit;
}

$dbconn = mysql_connect("localhost","leelab",
"leelab") or die("데이터베이스 연결에 실패하였습니다.");

$status = mysql_select_db("counter_db");
if (!$status) {
   error("DB_ERROR");
   exit;
}

// 날자

if(!$year) $year=date("Y");

if(!$month) $month=date("m");

if(!$day) $day=date("d");

 

if(!$no) $no=2;

 

//전체

$result = mysql_query("SELECT sum(cnt) FROM counter");

if (!$result) {

   error("QUERY_ERROR");

   exit;

}

$total_count = mysql_result($result,0,0);

 

//오늘

$sdate = mktime(0,1,1,$month,$day,$year);

$edate = mktime(24,1,1,$month,$day,$year);

 

$result = mysql_query("SELECT sum(cnt) FROM counter where date >= $sdate and date < $edate");

if (!$result) {

   error("QUERY_ERROR");

   exit;

}

$today_count = mysql_result($result,0,0);

 

// 어제

$sdate = mktime(0,1,1,$month,$day,$year)-(60*60*24);

$edate = mktime(24,1,1,$month,$day,$year)-(60*60*24);

 

$result = mysql_query("SELECT sum(cnt) FROM counter where date >= $sdate and date < $edate");

if (!$result) {

   error("QUERY_ERROR");

   exit;

}

$yesterday_count = mysql_result($result,0,0);

 

 

?>

<HTML>

<HEAD>

<TITLE>카운터 보기 - Lee LAB</TITLE>

<STYLE TYPE="text/css">

<!--

td      { font-family:굴림,굴림체; font-size:10pt}

a:link  { font-family:굴림,굴림체; font-size:10pt;color:#003388; text-decoration:none; }

a:visited  { font-family:굴림,굴림체; font-size:10pt; text-decoration: none; }

a:hover  { font-family:굴림,굴림체; font-size:10pt; color:#ff0000; text-decoration: underline ; }

-->

</STYLE>

 

</HEAD>

 

<BODY BGCOLOR="#FFFFFF">

<form method=post action=<? echo $PHP_SELF; ?>>

<input type=hidden name=no value=<? echo $no; ?>>

<table border=0 cellpading=0 cellspacing=0>

<tr>

   <td align=left width=100% valign=bottom>

<?    echo"<a href=$PHP_SELF?no=1&year=$year&month=$month&day=$day>전체</a><font size=1>&nbsp;</font>";?>

<?    echo"<a href=$PHP_SELF?no=2&year=$year&month=$month&day=$day>오늘</a><font size=1>&nbsp;</font>";?>

<?    echo"<a href=$PHP_SELF?no=3&year=$year&month=$month&day=$day>월간</a><font size=1>&nbsp;</font>";?>

   </td>

</tr>

<tr>

    <td align=center width=417 height=31>

        Year : <input type=text name=year value=<? echo $year; ?> size=4 maxlength=4 style='background-color:eeeeee; border:1 solid black;height:16'> &nbsp;&nbsp;

        Month : <input type=text name=month value=<? echo $month; ?> size=2 maxlength=2 style='background-color:eeeeee; border:1 solid black;height:16'> &nbsp;&nbsp;

        Day : <input type=text name=day value=<? echo $day; ?> size=2 maxlength=2 style='background-color:eeeeee; border:1 solid black;height:16'> &nbsp;&nbsp;

        <input type="submit"  value="전송"></td>

</tr>

 

<?

//전체 카운터

if($no == "1") {

  echo("

<tr>

           <td height=30><b>▶ 전체 통계 보기</td>

</tr>

");

 

$result = mysql_query("SELECT date FROM counter");

if (!$result) {

   error("QUERY_ERROR");

   exit;

}

$total_date = mysql_num_rows($result);

 

$max_count=1;

$min_count=1;

for($i=0;$i<$total_date;$i++)  {

        $cur_date = mysql_result($result,$i,0);

 

    $c_year=date("Y",$cur_date);

    $c_month=date("m",$cur_date);

    $c_day=date("d",$cur_date);

 

    $time1=mktime(0,0,1,$c_month,$c_day,$c_year);

    $time2=mktime(0,0,1,$c_month,$c_day,$c_year) + (60*60*24);

        if($time1 != $b_time) {

                $result1 = mysql_query("SELECT sum(cnt) FROM counter where date >= $time1 and date < $time2");

                if (!$result1) {

                   error("QUERY_ERROR");

                   exit;

                }

                $cur_count = mysql_result($result1,0,0);

                if($max<$cur_count) {

                        $max_count=$cur_count;

                        $max_date=$time1;

                }

                if($min > $cur_count) {

                        $min_count=$cur_count;

                        $min_date=$time1;

                }

                $b_time = $time1;

        }

 

}

  echo"

       <tr>

          <td height=40>

             &nbsp; <li> 전체 방문자수 : $total_count &nbsp;&nbsp;&nbsp;

          </td>

       </tr>

       <tr>

          <td height=40>

             &nbsp; <li> 오늘 방문자수 : $today_count &nbsp;&nbsp;&nbsp;

          </td>

       </tr>

       <tr>

          <td height=40>

             &nbsp; <li> 어제 방문자수 : $yesterday_count &nbsp;&nbsp;&nbsp;

          </td>

       </tr>

       <tr>

          <td height=40>

             &nbsp; <li> 최고 방문자수 : $max_count &nbsp;&nbsp;&nbsp;

          </td>

       </tr>

       <tr>

          <td height=40>

             &nbsp; <li> 최저 방문자수 : $min_count &nbsp;&nbsp;&nbsp;

          </td>

       </tr>";

}

// 오늘 카운터

elseif($no=="2")

{

  echo("

<tr>

           <td height=30><b>▶ 시간대별 통계 보기</td>

</tr>

");

 

  echo"

       <tr>

           <td height=25>

               &nbsp; <li> $month 월 $day 일 방문자수 : $today_count

           </td>

       </tr>

       <tr>

           <td height=30 align=center>

 

           <table width=380 border=0 cellpadding=1 cellspacing=0>";

  

  $max=1;

  for($i=0;$i<24;$i++)

  {

   $time1=mktime($i,1,1,$month,$day,$year);

   $temp=mysql_fetch_array(mysql_query("select cnt from counter where date='$time1'"));

   $time_count[$i]=$temp[cnt];

   if($max<$time_count[$i]) $max=$time_count[$i];

  }

 

  for($i=0;$i<24;$i++)

  {

   $per1=(int)($time_count[$i]/$max*100);

   if($per1>100)$per1=99;

   $cur_count=intval($time_count[$i]);

   echo"

         <tr>

            <td width=50>- $i 시 </td>

            <td align=left><img src=images/bar.gif border=0 width=$per1% height=10 alt='$i시 방문자수 : $time_count[$i]'></td>

            <td width=80>&nbsp; <font color=blue>Unique $cur_count </td>

         </tr>";

  }

 

  echo"

         </table>

           </td>

        </tr>

        ";

}

// 월간 카운터

elseif($no=="3")

{

  echo("

<tr>

           <td height=30><b>▶ 월간 통계 보기</td>

</tr>

");

 

$sdate = mktime(0,1,1,$month,1,$year);

$edate = mktime(0,1,1,$month+1,1,$year);

 

$result = mysql_query("SELECT sum(cnt) FROM counter where date >= $sdate and date < $edate");

if (!$result) {

   error("QUERY_ERROR");

   exit;

}

$month_count = intval(mysql_result($result,0,0));

 

  echo"

       <tr>

           <td height=25>

               &nbsp; <li> $month 월 방문자수 : $month_count

           </td>

       </tr>

       <tr>

           <td height=30 align=center>

 

           <table width=380 border=0 cellpadding=1 cellspacing=0>";

if($month_count) {

  $end_day = date("t",mktime(0,0,0,$month,1,$year));

  for($i=1;$i<=$end_day;$i++) {

   $sdate=mktime(0,1,1,$month,$i,$year);

   $edate=mktime(24,1,1,$month,$i,$year);

   $result = mysql_query("select sum(cnt) from counter where date >= $sdate and date < $edate");

   $cur_count=intval(mysql_result($result,0,0));

   $per1=(int)($cur_count/$month_count*100);

   if($per1>100)$per1=99;

 

   echo"

         <tr>

            <td width=50>- $i 일</td>

            <td align=left><img src=images/bar.gif border=0 width=$per1% height=10 alt='$i시 방문자수 : $time_count[$i]'></td>

            <td width=80>&nbsp; <font color=blue>Unique $cur_count </td>

         </tr>";

  }

}

  echo"

         </table>

           </td>

        </tr>

        ";

}

?>

        <tr>

            <td align=center height=30 >

                        <hr>

                        <a href=http://www.leelab.co.kr target=_blank><b>Created by Lee LAB.</b></a></td>

        </tr>

        </table></form>

</BODY>

</HTML>

 
[목차]
개인정보취급방침 서비스이용약관 모바일 버전으로 보기 상단으로

TEL. 063-469-4551 FAX. 063-469-4560 전북 군산시 대학로 558
군산대학교 컴퓨터정보공학과

Copyright © www.leelab.co.kr. All rights reserved.