0226(三)-網頁前端設計與開發運用培訓班
===
###### tags: `德鍵`

一、建立商品管理
1. 程式名稱：prod.php
2. 資料表：prods、files、kinds
3. 建立一個mysql.sql：把資料表的結構記錄
4. users

CREATE TABLE `users` (
  `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '會員編號',
  `uname` varchar(255) NOT NULL COMMENT '帳號',
  `pass` varchar(255) NOT NULL COMMENT '密碼',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `tel` varchar(255) NOT NULL COMMENT '電話',
  `email` varchar(255) NOT NULL COMMENT '信箱',
  `kind` enum('0','1') NOT NULL DEFAULT '0' COMMENT '會員類別',
  `token` varchar(255) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='會員資料表';

5. prods

CREATE TABLE `prods` (
    `sn` int(10) unsigned NOT NULL auto_increment COMMENT 'prods_sn',
    `kind_sn` smallint(5) unsigned NOT NULL default 0 COMMENT '分類',
    `title` varchar(255) NOT NULL default '' COMMENT '名稱',
    `content` text NULL COMMENT '內容',
    `price` int(10) unsigned NOT NULL COMMENT '價格',
    `enable` enum('1','0') NOT NULL default '1' COMMENT '狀態',
    `date` int(10) unsigned NOT NULL default 0 COMMENT '建立日期',
    `sort` smallint(5) unsigned NOT NULL default 0 COMMENT '排序',
    `counter` int(10) unsigned NOT NULL default 0 COMMENT '人氣',
    PRIMARY KEY  (`sn`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='商品資料表';

6. kinds

CREATE TABLE `kinds` (
    `sn` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'kinds_sn',
    `ofsn` smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT '父類別',
    `kind` varchar(255) NOT NULL DEFAULT '' COMMENT '分類',
    `title` varchar(255) NOT NULL DEFAULT '' COMMENT '標題',
    `sort` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
    `enable` enum('1','0') NOT NULL DEFAULT '1' COMMENT '狀態',
    `url` varchar(255) NOT NULL DEFAULT '' COMMENT '網址',
    `target` enum('1','0') NOT NULL DEFAULT '0' COMMENT '外連',
    `col_sn` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'col_sn',
    `content` text NULL COMMENT '內容',
    PRIMARY KEY (`sn`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='類別資料表';

7. files

CREATE TABLE `files` (
    `sn` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'files_sn',
    `kind` varchar(255) NOT NULL DEFAULT '' COMMENT '分類',
    `col_sn` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '欄位編號',
    `sort` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
    `file_kind` enum('img','file') NOT NULL DEFAULT 'img' COMMENT '上傳檔案種類',
    `file_name` varchar(255) NOT NULL DEFAULT '' COMMENT '上傳檔案名稱',
    `file_type` varchar(255) NOT NULL DEFAULT '' COMMENT '上傳檔案類型',
    `file_size` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上傳檔案大小',
    `description` text  NULL COMMENT '檔案說明',
    `counter` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '下載人次',
    `name` varchar(255) NOT NULL DEFAULT '' COMMENT '檔案名稱',
    `download_name` varchar(255) NOT NULL DEFAULT '' COMMENT '下載檔案名稱',
    `sub_dir` varchar(255) NOT NULL DEFAULT '' COMMENT '檔案子路徑',
    PRIMARY KEY (`sn`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='上傳檔案資料表';

二、程式
1. 複製 user.php 另存 prod.php
2. 複製 user.tpl 另存 user.tpl
3. 在後台建立程式程令
admin.tpl

              <li class="list-group-item">
                <a href="user.php" class="btn-block">會員管理</a>
              </li>
              <li class="list-group-item">
                <a href="prod.php" class="btn-block">商品管理</a>
              </li>

樣板控制
admin.tpl

          <{elseif  $WEB.file_name == "prod.php"}>
            <{include file="tpl/prod.tpl"}>

4. 建立指令：prod.php?op=op_form
把功能換成

<a href="?op=op_form" class="btn btn-primary btn-sm">新增</a>

5. of_form(\$sn="")

function op_form($sn=""){
  global $smarty,$db;

  if($sn){
    #取得單筆
    $sql="SELECT *
          FROM `prods`
          WHERE `sn` = '{$sn}'
    ";//die($sql);
    
    $result = $db->query($sql) or die($db->error() . $sql);
    $row = $result->fetch_assoc(); 
  }

  #初始值
  $row['sn'] = isset($row['sn']) ? $row['sn'] : "";
  $row['kind_sn'] = isset($row['kind_sn']) ? $row['kind_sn'] : "1";
  $row['title'] = isset($row['title']) ? $row['title'] : "";
  $row['content'] = isset($row['content']) ? $row['content'] : "";
  $row['price'] = isset($row['price']) ? $row['price'] : "";
  $row['enable'] = isset($row['enable']) ? $row['enable'] : "1";

  $row['date'] = isset($row['date']) ? $row['date'] : strtotime("now");  
  $row['date'] = date("Y-m-d H:i",$row['date']);//格式化

  $row['sort'] = isset($row['sort']) ? $row['sort'] : "0";
  $row['counter'] = isset($row['counter']) ? $row['counter'] : "0";

  #送變數至樣板
  $smarty->assign("row",$row);
}

* [strtotime](https://www.php.net/manual/zh/function.strtotime.php)
* [date](https://www.php.net/manual/zh/function.date.php)
* [小月曆](https://hackmd.io/@ulXUSG7WRnandb_4ZXbzlw/HJKgwqfVI/edit)
* php.ini
date.timezone=Asia/Taipei
6.

三、樣板

<{if $op=="op_form"}>
	<script type='text/javascript' src='<{$xoAppUrl}>class/My97DatePicker/WdatePicker.js'></script>	
    
	<div>
		<h1 class="text-center">商品表單</h1>
			
		<form action="user.php" method="post" id="myForm" class="mb-2" enctype="multipart/form-data">
			
			<div class="row">         
				<!--標題-->              
				<div class="col-sm-6">
					<div class="form-group">
					<label>標題<span class="text-danger">*</span></label>
					<input type="text" class="form-control" name="title" id="title" value="<{$row.title}>">
					</div>
				</div>         
				<!--日期-->              
				<div class="col-sm-3">
					<div class="form-group">
					<label>日期<span class="text-danger">*</span></label>
					<input type="text" class="form-control" name="date" id="date" value="<{$row.date}>" onClick="WdatePicker({dateFmt:'yyyy-MM-dd HH:mm'})">
					</div>
				</div> 
				<!-- 商品狀態  -->
				<div class="col-sm-3">
					<div class="form-group">
					<label style="display:block;">商品狀態</label>
					<input type="radio" name="enable" id="enable_1" value="1" <{if $row.enable=='1'}>checked<{/if}>>
					<label for="enable_1" style="display:inline;">啟用</label>&nbsp;&nbsp;
					<input type="radio" name="enable" id="enable_0" value="0" <{if $row.enable=='0'}>checked<{/if}>>
					<label for="enable_0" style="display:inline;">停用</label>
					</div>
				</div> 
				<!-- 類別  -->              
				<div class="col-sm-4">
					<div class="form-group">
						<label class="d-block">類別</label>
						<select name="kind_sn" id="kind_sn" class="form-control" >
							<option value="1" <{if $row.kind_sn == "1"}> selected<{/if}> >類別1</option>
							<option value="2" <{if $row.kind_sn == "2"}> selected<{/if}> >類別2</option>
						</select>
					</div> 
				</div> 							

				<!--價格-->              
				<div class="col-sm-4">
					<div class="form-group">
						<label>價格</label>
						<input type="text" class="form-control text-right" name="price" id="price" value="<{$row.price}>">
					</div>
				</div> 

				<!--排序-->              
				<div class="col-sm-2">
					<div class="form-group">
						<label>排序</label>
						<input type="text" class="form-control text-right" name="sort" id="sort" value="<{$row.sort}>">
					</div>
				</div>  

				<!--計數器-->              
				<div class="col-sm-2">
					<div class="form-group">
						<label>計數器</label>
						<input type="text" class="form-control text-right" name="counter" id="counter" value="<{$row.counter}>">
					</div>
				</div> 
				<!--商品圖-->              
				<div class="col-sm-4">
					<div class="form-group">
						<label>商品圖</label>
						<input type="file" class="form-control" name="prod" id="prod" >
					</div>
				</div> 
			</div>
			<div class="row">
				<div class="col-sm-12">  
					<!-- 內容 -->
					<div class="form-group">
						<label class="control-label">內容</label>
						<textarea class="form-control" rows="4" id="content" name="content"><{$row.content}></textarea>
					</div>
				</div>
			</div>

			<div class="text-center pb-20">
			<input type="hidden" name="op" value="<{$row.op}>">
			<input type="hidden" name="sn" value="<{$row.sn}>">
			<button type="submit" class="btn btn-primary">送出</button>
			</div>
		
		</form>
		<!-- 表單驗證 -->
		<script src="https://cdn.jsdelivr.net/npm/jquery-validation@1.19.1/dist/jquery.validate.min.js"></script>
		<!-- 調用方法 -->
		<style>
				.error{
				color:red;
				}
		</style>
		<script>
			$(function(){
				$("#myForm").validate({
					submitHandler: function(form) {
							form.submit();
					},
					rules: {
						'title' : {
							required: true
						}
					},
					messages: {
						'title' : {
							required: "必填"
						}
					}
				});

			});
		</script>
			
	</div>

<{/if}>

四、決定下個流程
1. prod.php
2. op=op_insert
3. 流程

  case "op_insert" :
    $msg = op_insert();
    redirect_header("prod.php", $msg, 3000);
    exit;

4. op_insert(\$sn="")
* 取得一個5碼亂數

      $rand = substr(md5(uniqid(mt_rand(), 1)), 0, 5);//取得一個5碼亂數

* 取得上傳檔案的副檔名

      #//取得上傳檔案的副檔名
      $ext = pathinfo($_FILES["prod"]["name"], PATHINFO_EXTENSION); 
      $ext = strtolower($ext);//轉小寫

* 檢查上傳資料夾

      mk_dir(_WEB_PATH."/uploads");
      mk_dir(_WEB_PATH."/uploads/{$sub_dir}");

* [strtolower](https://www.php.net/manual/zh/function.strtolower.php)：轉小寫
* [pathinfo](https://vector.cool/php-pathinfo-get-dirname-basename-extension-filename/)：取得檔案資訊

function op_insert($sn=""){
  global $db;
   
  $_POST['title'] = db_filter($_POST['title'], '標題');
  $_POST['date'] = db_filter($_POST['date'], '');//日期
  $_POST['date'] = strtotime($_POST['date']);

  $_POST['enable'] = db_filter($_POST['enable'], '狀態');
  $_POST['kind_sn'] = db_filter($_POST['kind_sn'], '類別');
  $_POST['price'] = db_filter($_POST['price'], '');//價格
  $_POST['sort'] = db_filter($_POST['sort'], '');//排序
  $_POST['counter'] = db_filter($_POST['counter'], '');//計數器  
  $_POST['content'] = db_filter($_POST['content'], '');//內容
  
  if($sn){

  }else{
    $sql="INSERT INTO `prods` 
          (`kind_sn`, `title`, `content`, `price`, `enable`, `date`, `sort`, `counter`)
          VALUES 
          ('{$_POST['kind_sn']}', '{$_POST['title']}', '{$_POST['content']}', '{$_POST['price']}', '{$_POST['enable']}', '{$_POST['date']}', '{$_POST['sort']}', '{$_POST['counter']}') 
    ";//die($sql);

    $db->query($sql) or die($db->error() . $sql);
    $sn = $db->insert_id;

    #上傳檔案
    if ($_FILES['prod']['error'] === UPLOAD_ERR_OK){
      $_FILES['prod']['name'] = db_filter($_FILES['prod']['name'], 'name');
      $_FILES['prod']['type'] = db_filter($_FILES['prod']['type'], 'type');
      $_FILES['prod']['size'] = db_filter($_FILES['prod']['size'], 'size');
      //$_FILES['prod']['tmp_name']= db_filter($_FILES['prod']['tmp_name'], 'tmp_name');
      
      $rand = substr(md5(uniqid(mt_rand(), 1)), 0, 5);//取得一個5碼亂數
      #//取得上傳檔案的副檔名
      $ext = pathinfo($_FILES["prod"]["name"], PATHINFO_EXTENSION); 
      $ext = strtolower($ext);//轉小寫
      
      //判斷檔案種類
      if ($ext == "jpg" or $ext == "jpeg" or $ext == "png" or $ext == "gif") {
        $file_kind = "img";
      } else {
        $file_kind = "file";
      }      
      
      $sub_dir = "prod";
      $new_filename = $rand ."_". $sn .".".$ext;      
      
      #檢查上傳資料夾
      mk_dir(_WEB_PATH."/uploads");
      mk_dir(_WEB_PATH."/uploads/{$sub_dir}");

      # 將檔案移至指定位置
      $uploaded = move_uploaded_file($_FILES['prod']['tmp_name'], _WEB_PATH . "/uploads/{$sub_dir}/" . $new_filename );

      if($uploaded){
        $sql="INSERT INTO `files` 
              (`kind`, `col_sn`, `sort`, `file_kind`, `file_name`, `file_type`, `file_size`, `description`, `counter`, `name`, `download_name`, `sub_dir`)
              VALUES 
              ('prod', '{$sn}', '1', '{$file_kind}', '{$_FILES['prod']['name']}', '{$_FILES['prod']['type']}', '{$_FILES['prod']['size']}', '', '0', '{$new_filename}', '','{$sub_dir}');
        ";//die($sql);    
        $db->query($sql) or die($db->error() . $sql);
      }       
    } else {
      die("檔案上傳失敗：" . $_FILES['prod']['error']);
    }
    return "商品資料新增成功";
  }

}

五、處理op_list

六、處理op_form(\$sn)
1. 得到商品圖