HTML --홈쇼핑 테이블

정보처리산업기사_2차 평가문제(공개용).pdf

0.42MB

 

 

 

1.  오토셋에 테이블 2개 추가하기 (member / money)

 

Create table member_tbl_02 (
 custno int(6)  not  null primary key ,
 custname varchar(40),
 phone varchar(26),
 address varchar(120),
 joindate  date,
 grade  char(1),
 city  char(2)
);


insert  into  member_tbl_02 
values(100001,'김행복','010-1111-2222','서울 동대문구 휘경1동','20151202','A','01'); 

insert  into  member_tbl_02 
values(100002,'이축복','010-1111-3333','서울 동대문구 휘경2동','20151206','B','01'); 

insert  into  member_tbl_02 
values(100003,'장믿음','010-1111-4444','울릉군 울릉읍','20151001','B','30'); 

insert  into  member_tbl_02 
values(100004,'최사랑','010-1111-5555','울릉군 울릉읍 독도2리','20151113','A','30'); 

insert  into  member_tbl_02 
values(100005,'진평화','010-1111-6666','제주도 제주시 외나무골','20151225','B','60'); 

insert  into  member_tbl_02 
values(100006,'차공단','010-1111-7777','제조도 제주시 감나무골','20151211','C','60'); 

select  *  from member_tbl_02;


Create table money_tbl_02 (
 custno int(6)  not  null  ,
 salenol int(8)  not  null,
 pcost  int(8),
 amount int(4),
 price  int(8),
 pcode  varchar(4),
 sdate  date,
 constraint pk_money_tbl_02 primary key (custno,salenol)
);

insert  into  money_tbl_02 values (100001, 20160001, 500, 5, 2500, 'A001','20160101' );
insert  into  money_tbl_02 values (100001, 20160002, 1000, 4, 4000, 'A002','20160101' );
insert  into  money_tbl_02 values (100001, 20160003, 500, 3, 1500, 'A008','20160101' );
insert  into  money_tbl_02 values (100002, 20160004, 2000, 1, 2000, 'A004','20160102' );
insert  into  money_tbl_02 values (100002, 20160005, 500, 1, 500, 'A001','20160103' );
insert  into  money_tbl_02 values (100003, 20160006, 1500, 2, 3000, 'A003','20160103' );
insert  into  money_tbl_02 values (100004, 20160007, 500, 2, 1000, 'A001','20160104' );
insert  into  money_tbl_02 values (100004, 20160008, 300, 1, 300, 'A005','20160104' );
insert  into  money_tbl_02 values (100004, 20160009, 600, 1, 600, 'A006','20160104' );
insert  into  money_tbl_02 values (100004, 20160010, 3000, 1, 3000, 'A007','20160106' );

select  *  from money_tbl_02;

 

2. top.php에 추가하고 새 파일 생성하기

     [ <a href="<?=$path?>/A_school/member_list.php">회원목록보기</a>&nbsp; 
      <a href="<?=$path?>/A_school/money_list.php">매출조회 ] </a>&emsp;&emsp;

 

3. member_list.php 작성하고 grade를 

   A -> VIP  등등 바꿔주기

 

 if ($row['grade'] == "A"){
        $row['grade'] = "VIP"; 
    } else if (  $row['grade'] == "B"){
        $row['grade'] = "직원"; 
    } else if ( $row['grade'] == "C") {
        $row['grade'] = "일반"; 
    }

 

4. 가입일자를 년/월/일 로 바꿔주기

substr

 

<td><?=substr($row['joindate'],0,4)?>년<?=substr($row['joindate'],5,2)?>월<?=substr($row['joindate'],8,2)?>일</td>

 

5. money_list.php

join을 사용하는 테이블 

매출이란 ,, 한 사람이 산 총 가격

<? include $_SERVER["DOCUMENT_ROOT"]."/D_include/top.php"?>

<?
$conn = new mysqli("localhost","root","autoset","korea");

$SQL = " select b.custno, custname, grade, sum(price) 
 from member_tbl_02 b join money_tbl_02 y on b.custno = y.custno
 group by b.custno , custname, grade   " ;
$result =  $conn -> query($SQL);

?>                                                    
<section>
<br><br>    
<div align=center> <font size=4> <b> 회원 매출 조회 </b></font>  </div>    
<div align=center>
   <br>
   <table border=1 width=300px>
    <tr>
        <td>회원번호</td><td>회원성명</td><td>고객등급</td><td>매출</td>
    </tr>
<?   
 while ($row = $result -> fetch_assoc()){ 
 
 if ($row['grade'] == "A"){
        $row['grade'] = "VIP"; 
    } else if (  $row['grade'] == "B"){
        $row['grade'] = "직원"; 
    } else if ( $row['grade'] == "C") {
        $row['grade'] = "일반"; 
    }

 ?>
<tr>
    <td><?=$row['custno']?></td>
    <td><?=$row['custname']?></td>
    <td><?=$row['grade']?></td> 
    <td><?=$row['sum(price)']?></td>
</tr>
 <?
 }
?>


</table>
</section>
<? include $_SERVER["DOCUMENT_ROOT"]."/D_include/bottom.php"?>

6. 매출 천단위에 콤마 찍기 

<td><?=number_format($row['sum(price)'])?></td>

7. 밑에 막대그래프 넣기

 

<? include  $_SERVER["DOCUMENT_ROOT"]."/D_include/top.php" ?>

<?
$servername = "localhost" ;
$username = "root";
$password ="autoset";
$dbname="korea";

$conn = new mysqli($servername, $username, $password, $dbname ); 
?>

<?
   $SQL = "select  m1.custno k1, custname k2 , grade k3, sum(price) k4
   from member_tbl_02 m1 join  money_tbl_02 m2
   on m1.custno = m2.custno
   group  by  m1.custno, custname, grade
   order by sum(price) desc " ;
   $result = $conn -> query($SQL);
   $result2 = $conn -> query($SQL);
?>

<style>
#tr1{
    background-color:#ffaaaa;
}
#tr2{
    background-color:#ffccaa;
}
tr{
    text-align:center;
}
</style>  

<script src="http://apps.bdimg.com/libs/jquery/2.1.4/jquery.min.js"></script>
<script src="http://code.highcharts.com/highcharts.js"></script> 
<script src="http://code.highcharts.com/modules/data.js"></script>     


<section>
<br><br>    
<div align=center> <font size=5> <b> 회원 매출 조회  </b></font>  </div>    
<div align=center>
   <br> 
 
   <table border=1  width=400  height=200>
     <tr> 
     <th> 회원번호 </th> <th> 회원성명 </th>
     <th> 고객등급 </th>  <th> 매출 </th>
 
     </tr> 
     
     <? 
     while( $row = $result -> fetch_assoc()) {
        if ($row['k3'] == "A") {
            $grade = "VIP" ;
        }else if ($row['k3'] == "B") {
            $grade = "일반" ;
        }else if ($row['k3'] == "C") {
            $grade = "직원" ;
        }

     ?>

     <tr  >

     <td> <?=$row['k1']?> </td>
     <td><?=$row['k2']?> </td> 
     <td><?=$grade?> </td>
     <td><?=$row['k4']?> </td>
     </tr>

     <? } ?>
    </table> 

    <div id=body1>
	 <div id = hidden style="display:none"> 
	 <!-- 표는 숨겨준다.!!  -->
		 <table border=1  width=400  id='datatable' >
		
		   <tr><td> 성명 </td><td> 회원성명 </td></tr> 
           <?  
           while( $row2 = $result2 -> fetch_assoc()) {
		   ?>
		   <tr><td> <?=$row2["k2"] ?> </td>
               <td> <?=$row2["k4"] ?> </td>  
           </tr>
		
		   <? 
		     }
		   ?>
		</table>
	</div>
</div>
<br>
<div id="container" style="width: 400px; height: 230px; margin: 0 auto;"></div>
<script language="JavaScript">
$(document).ready(function() { 
   var data = {
      table: 'datatable'
   };
   var chart = {
      type: 'column'
   };
   var title = {
      text: '회원 매출 조회'   
   };      
   var yAxis = {
      allowDecimals: false,
      title: {
         text: '매출액'
      }
   };
   var tooltip = {
      formatter: function () {
         return '<b>' + this.series.name + '</b><br/>' +
            this.point.y + ' ' + this.point.name.toLowerCase();
      }
   };
   var credits = {
      enabled: false
   };  
      
   var json = {};   
   json.chart = chart; 
   json.title = title; 
   json.data = data;
   json.yAxis = yAxis;
   json.credits = credits;  
   json.tooltip = tooltip;  
   $('#container').highcharts(json);
});
</script>
</div>
<br>
</div>
<br>
</section>
<? include $_SERVER["DOCUMENT_ROOT"]."/D_include/bottom.php" ?>