【CSS】顯示表格空白(empty-cells)、框線合併(border-collapse)

empty-cells

empty-cells:show -> 顯示空白表格

empty-cells:hide -> 不顯示空白表格 - 預設

empty-cells:inherit -> 繼承父屬性

1	2
3

border-collapse

border-collapse:collapse -> 框限線會合併

border-collapse:separate -> 框線分開

1	2
3

<style>
#wsxrtgvbuikm14010082 .t1{
	color:#1A6811;
	font-family: "微軟正黑體";
	font-size:24px;
	padding: 0 10px;
}

#wsxrtgvbuikm14010082 .t2{
	color:#1F1C97;
	font-family: "微軟正黑體";
	font-size:16px;
	padding: 0 10px;
	margin-top:8px;
	margin-bottom:8px;
}

#wsxrtgvbuikm14010082 .t3{
	color:#000;
	font-family: "微軟正黑體";
	font-size:16px;
	padding: 0 5px;	
}

#wsxrtgvbuikm14010082 .table-1 {
	width:300px;
	margin-left:10px;
}

#wsxrtgvbuikm14010082 .table-1 td{
	border:1px solid #0088dd;
	padding:15px;
	empty-cells:hide;
	
}

#wsxrtgvbuikm14010082 .table-2 {
	width:300px;
	border-collapse:collapse;
	margin-left:10px;
}

#wsxrtgvbuikm14010082 .table-2 td{
	border:1px solid #0088dd;
	padding:15px;
	empty-cells:hide;
	
}
</style>
<div id="wsxrtgvbuikm14010082">
  <div class="t1">empty-cells</div>
  <div class="t2">empty-cells:show -> 顯示空白表格</div>
  <div class="t2">empty-cells:hide -> 不顯示空白表格 - 預設</div>
  <div class="t2">empty-cells:inherit -> 繼承父屬性</div>
  <div>
    <table width="100%" border="0" cellspacing="0" cellpadding="0" class="table-1">
      <tr>
        <td>1</td>
        <td>2</td>
      </tr>
      <tr>
        <td>3</td>
        <td></td>
      </tr>
    </table>
  </div>
  
  <div class="t1" style="margin-top:30px;">border-collapse</div>
  <div class="t2">border-collapse:collapse -> 框限線會合併</div>
  <div class="t2">border-collapse:separate -> 框線分開</div>
  <div>
    <table width="100%" border="1" cellspacing="0" cellpadding="0" class="table-2">
      <tr>
        <td>1</td>
        <td>2</td>
      </tr>
      <tr>
        <td>3</td>
        <td></td>
      </tr>
    </table>
  </div>
  

【CSS】list-style-position

項目符號的位置:list-style-position

list-style-position:outside -> 不縮排

list-style-position:inherit -> 如果有第二排會縮排 預設值

SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC

SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC

SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC

• SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
• SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
• SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC

  
// CSS
.demo{
 width:300px;
 margin:0px;
 padding-left:30px;
 list-style-position:inside;
}

.demo li{
 margin-top:10px;
 list-style:circle;
}

ul.demo2{ /* 一定要用ul的樣子 */
 width:300px;
 list-style-position:outside;
 margin:0px;
 padding-left:30px;
 padding-top:30px;
}

.demo2 li{
 margin-top:10px;
 list-style:circle;
}

// HTML
<div class="demo">
  <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
  <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
  <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
</div>

<ul class="demo2">
  <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
  <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
  <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
</ul>
  
  

【SQL】if else

基本

PHP

   if ($score >= 90)
     $str = 'A';
    elseif ($score >= 70 && $score < 90) 
     $str = 'B';
    elseif ($score >= 60 && $score < 70) 
     $str = 'C';  
    else{
     $str = 'D';  
    }
     
  

SQL

   CASE  
     WHEN score >= 90 THEN 'A'
      WHEN score >= 90 THEN 'B'
      WHEN score >= 90 THEN 'C'
      ELSE 'D'
    END AS str
  

欄位值

Name	Answer
張一	1
王二	2
李三	3

    SELECT Name, 
     case Answer
       when 1 then '喜歡'
       when 2 then '不喜歡'
       when 3 then '一般'
      END
    FROM table;
    
    OR
    
    SELECT Name, 
     case
       when Answer=1 then '喜歡'
       when Answer=2 then '不喜歡'
       when Answer=3 then '一般'
      END AS Answer
      FROM table;
  

【MySQL】LIKE 搜尋

一、一般

1.name LIKE '豬%' -> 找豬開頭的文字後面不論字數，例如:豬八戒、豬頭
2.name LIKE '%豬%' -> 只有出現豬就算找到，如豬、神豬、豬頭
3.name LIKE '豬_' -> 限定字數只找豬開頭兩個字，底線在那邊就一定要有字
4.name LIKE '%豬%戒%' -> 只要字串包含順序有豬跟界就符合

二、應用搜尋

// 將字切開並加上%
$arr = $this->utf8_str_split($keywords);
$keywordSplit = implode('%',$arr);

// 這樣比 '%$keywords%' 搜尋到更多結果
$sql = "SELECT * FROM user WHERE name LIKE '%$keywordSplit%'";  
$rs = $pdo->query($sql);

// 用UTF8切文字  
function utf8_str_split($str, $split_len = 1){
 if (!preg_match('/^[0-9]+$/', $split_len) || $split_len < 1)
   return FALSE;

 $len = mb_strlen($str, 'UTF-8');
 if ($len <= $split_len)
   return array($str);

 preg_match_all('/.{'.$split_len.'}|[^\x00]{1,'.$split_len.'}$/us', $str, $ar);

 return $ar[0];
}  
  

【PHP】縮圖不失真

最近要做縮圖的工作，試了幾個function，
發現縮圖後的圖都失真的，變得比較模糊，
最後終於找不到不失真的function。

/*  Convert image size. true color*/ 
//$src        來源檔案 
//$dest        目的檔案 
//$maxWidth    縮圖寬度 
//$maxHeight    縮圖高度 
//$quality    JPEG品質 
function ImageCopyResizedTrue($src,$dest,$maxWidth,$maxHeight,$quality=100) { 
  //檢查檔案是否存在 
  if (file_exists($src)  && isset($dest)) { 
      // 目錄是否存在
      $dir = dirname($dest);
      if (!is_dir($dir)){
        mkdir($dir,0777);
      }
      
      $destInfo  = pathinfo($dest); 
      $srcSize   = getimagesize($src); //圖檔大小 
      $srcRatio  = $srcSize[0]/$srcSize[1]; // 計算寬/高 
      $destRatio = $maxWidth/$maxHeight; 
      if ($destRatio > $srcRatio) { 
          $destSize[1] = $maxHeight; 
          $destSize[0] = $maxHeight*$srcRatio; 
      } 
      else { 
          $destSize[0] = $maxWidth; 
          $destSize[1] = $maxWidth/$srcRatio; 
      } 

      //GIF 檔不支援輸出，因此將GIF轉成JPEG 
      if ($destInfo['extension'] == "gif") $dest = substr_replace($dest, 'jpg', -3); 

      //建立一個 True Color 的影像 
      $destImage = imagecreatetruecolor($destSize[0],$destSize[1]); 

      //根據副檔名讀取圖檔 
      switch ($srcSize[2]) { 
          case 1: $srcImage = imageCreateFromGif($src); break; 
          case 2: $srcImage = imageCreateFromJpeg($src); break; 
          case 3: $srcImage = imageCreateFromPng($src); break; 
          default: return false; break; 
      } 

      //取樣縮圖 
      imagecopyresampled($destImage, $srcImage, 0, 0, 0, 0,$destSize[0],$destSize[1], 
                          $srcSize[0],$srcSize[1]); 

      //輸出圖檔 
      switch ($srcSize[2]) { 
          case 1: case 2: imagejpeg($destImage,$dest,$quality); break; 
          case 3: imagepng($destImage,$dest); break; 
      } 
      return true; 
  } 
  else { 
      return false; 
  } 
} 
  

將它寫成Class方便之後使用

此class是可將一個目錄裡所有的圖片縮圖，
並存到另一個目錄，且可設定多個size。

// 使用方法
set_time_limit(0);
$imgPath = $_SERVER['DOCUMENT_ROOT'].'/data/images'; // 原始目錄

$thumb_150 = $_SERVER['DOCUMENT_ROOT'].'/data/images_150'; // 縮圖目錄
$thumb_110 = $_SERVER['DOCUMENT_ROOT'].'/data/images_110';

$igtb = new ImgThumb();
$igtb->oriPath = $imgPath;
$igtb->target = array(
         array($thumb_150,150,150),
         array($thumb_110,110,110), // 縮圖種類及大小
        );
$igtb->startThumb();

      
// @author - wsxrtgvbuikm@gmail.com
class ImgThumb {

 public $oriPath, // 要複製的路徑
     $target = array(
           array('路徑1',150,150) // 可多個 @parameter 路徑,width,height
         ),
     $imgArr;

 public function __construct(){
  $this->imgArr = array('jpg','jpeg','png','gif','bmp');
 }

 public function startThumb(){
  $this->img_thumb($this->oriPath);
  
 }

 public function img_thumb($dir){
  
  $dirArr = scandir($dir);
  foreach($dirArr as $val){
   if ($val == '.' || $val == '..') continue;
   
   $file = $dir.'/'.$val;
   $ext = strtolower(pathinfo($val, PATHINFO_EXTENSION));
   
   if (in_array($ext,$this->imgArr)){ 
    $extend = str_replace($this->oriPath,'',dirname($file));
    foreach($this->target as $arr){
     $target = $arr[0].$extend;
     if (!is_file($target.'/'.basename($file))) // 沒圖縮圖再跑

      $this->ImageCopyResizedTrue($file,$target.'/'.basename($file),$arr[1],$arr[2]);

    }
   }
   
   if (is_dir($file)){ // 是目錄就繼續找
    $this->img_thumb($file);
   }
   
  } // end foreach
  
 }
 
 
 /*  Convert image size. true color*/ 
 //$src        來源檔案 
 //$dest        目的檔案 
 //$maxWidth    縮圖寬度 
 //$maxHeight    縮圖高度 
  //$quality    JPEG品質 
  public function ImageCopyResizedTrue($src,$dest,$maxWidth,$maxHeight,$quality=100) { 
   //檢查檔案是否存在 
   if (file_exists($src)  && isset($dest)) { 
     // 目錄是否存在
     $dir = dirname($dest);
     if (!is_dir($dir)){
      mkdir($dir,0777);
     }
     
     $destInfo  = pathinfo($dest); 
     $srcSize   = getimagesize($src); //圖檔大小 
     $srcRatio  = $srcSize[0]/$srcSize[1]; // 計算寬/高 
     $destRatio = $maxWidth/$maxHeight; 
     if ($destRatio > $srcRatio) { 
       $destSize[1] = $maxHeight; 
       $destSize[0] = $maxHeight*$srcRatio; 
     } 
     else { 
       $destSize[0] = $maxWidth; 
       $destSize[1] = $maxWidth/$srcRatio; 
     } 

     //GIF 檔不支援輸出，因此將GIF轉成JPEG 
     if ($destInfo['extension'] == "gif") $dest = substr_replace($dest, 'jpg', -3); 

     //建立一個 True Color 的影像 
     $destImage = imagecreatetruecolor($destSize[0],$destSize[1]); 

     //根據副檔名讀取圖檔 
     switch ($srcSize[2]) { 
       case 1: $srcImage = imageCreateFromGif($src); break; 
       case 2: $srcImage = imageCreateFromJpeg($src); break; 
       case 3: $srcImage = imageCreateFromPng($src); break; 
       default: return false; break; 
     } 

     //取樣縮圖 
     imagecopyresampled($destImage, $srcImage, 0, 0, 0, 0,$destSize[0],$destSize[1], 
               $srcSize[0],$srcSize[1]); 

     //輸出圖檔 
     switch ($srcSize[2]) { 
       case 1: case 2: imagejpeg($destImage,$dest,$quality); break; 
       case 3: imagepng($destImage,$dest); break; 
     } 
     return true; 
   } 
   else { 
     return false; 
   } 
 } 
}
  

【PHP】JSON 使用

json(JavaScript Object Notation)：JSON 是個以純文字來儲存資料，
更好用的是將陣列或物件轉換成文字。

規則

陣列用[]，如：
array('php','javacript','jquery') 會變成：
["php","javacript","jquery"]

物件用{}，如
arry('name'=>'steven',tel=>'1234') 變成：
{"name":"steven,"tel":1234}

jquery 使用json

使用： $.parseJSON() 將json解譯
範例

  var str = '{"prodcut_id":"1234",
        "name":"鉛筆",
              "spec":["紅","綠","藍"]
              }';
  var result = $.pasrseJSON(str);
  alert(result.prodcut_id); // 印出1234
  alert(result.spec[0]); // 印出 紅
  

用$.post時可以指定回傳格視為json

  $.post("test.php",{name:$("#name").val()},function (result){
   alert(result.prodcut_id);
  },json);
  

PHP 使用json

編譯解譯分別為：json_encode、json_decode，
但為了操作方便可將物件解譯成array，
此時就要用json_decode($str,true);

  $arr = array("product"=>"1234","name"=>"鉛筆","spec"=>array("紅","綠","藍"));
  $str = json_encode($arr);
  
  $obj = json_decode($str); // 此時會變成 stdClass Object ( [product] => 1234 [name] => 鉛筆 [spec] => Array ( [0] => 紅 [1] => 綠 [2] => 藍 ) )
  $arr = json_decode($str,true); // 結果：Array ( [product] => 1234 [name] => 鉛筆 [spec] => Array ( [0] => 紅 [1] => 綠 [2] => 藍 ) )
  

【CSS】Reset CSS 將CSS歸零，符合各樣瀏覽器

css(Cascading Style Sheets)是用來設定網頁的樣式
但在不同瀏覽器有時相同語法呈現方式卻會不同
對於css有時候真的會很麻煩
這時可以用 Reset CSS 將css歸零
在css開始前加上：

   /* http://meyerweb.com/eric/tools/css/reset/ 
   v2.0 | 20110126
   License: none (public domain)
    */
    
    html, body, div, span, applet, object, iframe,
    h1, h2, h3, h4, h5, h6, p, blockquote, pre,
    a, abbr, acronym, address, big, cite, code,
    del, dfn, em, img, ins, kbd, q, s, samp,
    small, strike, strong, sub, sup, tt, var,
    b, u, i, center,
    dl, dt, dd, ol, ul, li,
    fieldset, form, label, legend,
    table, caption, tbody, tfoot, thead, tr, th, td,
    article, aside, canvas, details, embed, 
    figure, figcaption, footer, header, hgroup, 
    menu, nav, output, ruby, section, summary,
    time, mark, audio, video {
      margin: 0;
      padding: 0;
      border: 0;
      font-size: 100%;
      font: inherit;
      vertical-align: baseline;
    }
    /* HTML5 display-role reset for older browsers */
    article, aside, details, figcaption, figure, 
    footer, header, hgroup, menu, nav, section {
      display: block;
    }
    body {
      line-height: 1;
    }
    ol, ul {
      list-style: none;
    }
    blockquote, q {
      quotes: none;
    }
    blockquote:before, blockquote:after,
    q:before, q:after {
      content: '';
      content: none;
    }
    table {
      border-collapse: collapse;
      border-spacing: 0;
    }
  

來源：http://meyerweb.com/eric/tools/css/reset/

【GitHub】windows 入門

GitHub 是一個線上版本控制的服務，
版本管控簡單的說就是防止別人改到你的程式和做程式還原，
寫程式很怕越寫越糟，想找上一個版本或是還原到昨天寫的內容，
這時就要使用版本控制。

1.申請帳號

先到 https://github.com/ 申請一個帳號

2.建立一個 repository

要勾選Publiec，Private要收費

3.下載 GitHub windows 的工具

找到 Clone in Desktop 並下載安裝

4.開啟剛剛安裝的GitHub，輸入帳號密碼並連線

5.新增一個目錄並建立檔案

6.當有新增或修改檔案時要給予備註及上傳

【MSSQL】最近執行的SQL

查詢目前正在執行的SQL語法

SELECT TOP 200 qs.creation_time,last_execution_time-creation_time as time,text 
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY qs.creation_time DESC

SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
    ltrim(q.text),
    r.statement_start_offset/2+1,
    (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            -- q.text, /* 完整的 T-SQL 指令碼 */
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
   LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc

SELECT
A.SESSION_ID,DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,
A.START_TIME,A.COMMAND,A.CPU_TIME,
A.PERCENT_COMPLETE,A.ESTIMATED_COMPLETION_TIME,B.TEXT

FROM SYS.DM_EXEC_REQUESTS A
CROSS APPLY
SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B
WHERE SESSION_ID > 50

看哪個語法最花時間

SELECT TOP 10 total_worker_time, last_worker_time, 
max_worker_time, min_worker_time,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset 
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END 
- qs.statement_start_offset)/2) + 1) as statement_text
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
ORDER BY max_worker_time DESC   

SELECT TOP 50
 [Average CPU used] = total_worker_time / qs.execution_count,
 [Total CPU used] = total_worker_time,
 [Execution count] = qs.execution_count,
 [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
         (CASE WHEN qs.statement_end_offset = -1 
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
          ELSE qs.statement_end_offset END - 
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;


WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 
  SUM(total_worker_time) AS [CPU_Time_Ms]
 FROM sys.dm_exec_query_stats AS qs
 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
              FROM sys.dm_exec_plan_attributes(qs.plan_handle)
              WHERE attribute = N'dbid') AS F_DB
 GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
       DatabaseName, [CPU_Time_Ms], 
       CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
       OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);
SELECT 
substring(text,qs.statement_start_offset/2
,(CASE 
WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), 
text)) * 2 
ELSE qs.statement_end_offset 
END - qs.statement_start_offset)/2) 
,qs.plan_generation_num as recompiles
,qs.execution_count as execution_count
,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
,qs.total_worker_time as cpu_time
,qs.total_logical_reads as reads
,qs.total_logical_writes as writes
,last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
LEFT JOIN sys.dm_exec_requests r 
ON qs.sql_handle = r.sql_handle
ORDER BY total_wait_time DESC

【PHP、Jquery】廣告計數

計算一個廣告被被點擊次數有兩種狀況

1.站內廣告

原理：當點擊時用ajax計數，範例：

<a href="7311.tw" class="st-ad" adid="10"></a>

<script>
// 廣告計數
$("a.st-ad").click(function (e){
  e.preventDefault();
  $this = $(this);
  $.post("aj_ad.php",
    {mod:'ad_statistics',ad_id:$(this).attr('adid')},
    function (){
      window.location = $this.attr('href');
  });
});
</script>

2.站外廣告

原理：先連結到一頁專門計數的頁面，再轉到連結頁面