DataTable: ดัดแปลง / คัดลอก ข้อมูล

แนวคิดคือ คัดลอกข้อมูลที่เลือกเอาไว้จาก DataTables ตัวหนึ่งไปยังอีกตัวหนึง

[code language=”html” title=”jQuery.DataTables/data.json.transferDataToAnotherTable.html”]<!doctype html>
<html>

<head>
<meta charset="utf-8">
<meta name="author" content="Pitt Phunsanit">
<title>DataTables: Transfer Data To Another Table</title>
<link href="../vendor/twbs/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<link href="../vendor/twbs/bootstrap/dist/css/bootstrap-theme.min.css" rel="stylesheet" type="text/css">
<link href="../vendor/datatables/datatables/media/css/dataTables.bootstrap.min.css" rel="stylesheet" type="text/css">
<link href="../assets/fronteed/icheck/skins/minimal/red.css" rel="stylesheet" type="text/css">
</head>

<body>
<table class="table table-bordered table-hover table-striped" id="filtersTableA" width="100%"></table>
<button class="btn btn-success" id="copyBtn" type="button">Copy Data To Table</button>
<table class="table table-bordered table-hover table-striped" id="tableA" width="100%"></table>
<script src="../vendor/components/jquery/jquery.min.js"></script>
<script src="../vendor/datatables/datatables/media/js/jquery.dataTables.min.js"></script>
<script src="../vendor/datatables/datatables/media/js/dataTables.bootstrap.min.js"></script>
<script src="../assets/fronteed/icheck/icheck.min.js"></script>

</html>[/code]

ส่วนที่เขียนเป็นฟังก์ชั่นกลางไว้ให้ไฟล์อื่นเรียกใช้[code language=”javascript” title=”jQuery.DataTables/DataTables.js”]function iCheckBulk(dataTableArea, dataTableObject) {
dataTableArea.on(‘ifChanged’, ‘.checkAll’, function(event) {

var datas = dataTableObject.data();
var inputs = $(‘input:checkbox, input:radio’, dataTableArea);

if (event.target.checked) {
var enable = ‘1’;
var state = ‘check’;
} else {
var enable = ‘0’;
var state = ‘uncheck’;
}

$.each(datas, function(index, value) {
value.enable = enable;

dataTableObject.row(index).data(value);
});

iCheckInit($(‘input:checkbox, input:radio’, dataTableArea));
});

}

/* change filtersTable data value on input name enables is change */
function iCheckChange(filtersTableArea, filtersTableObject, dataTableObject) {
$(‘tbody’, filtersTableArea).on(‘ifChanged’, ‘input[name="enables[]"]’, function(event) {

event.stopPropagation();

var row = $(this).closest(‘tr’);

var data = filtersTableObject.row(row).data();

if ($(this).is(‘:checked’)) {
$(this).attr(‘checked’, 1);
data.enable = true;
} else {
$(this).attr(‘checked’, 0);
data.enable = false;
}
filtersTableObject.row(row).data(data);

iCheckInit(row);
});
}

function iCheckCopy(dataTableObject, filtersTableObject, pkField) {
$(‘#copyBtn’).click(function() {

/* loop current data (pkField) in current dataTableObject */
var datas = dataTableObject.data();
var hasKeys = new Array();
$.each(datas, function(index, value) {
hasKeys.push(value[pkField]);
});

var datasChoose = filtersTableObject.data();

$.each(datasChoose, function(index, value) {
/* add row to filtersTableObject if input name enables[] is checked */
if (value.enable == true && hasKeys.indexOf(value[pkField]) == -1) {
dataTableObject
.row.add(value)
.draw()
.node();
}
});

});

}

function iCheckInit(selector) {
selector.iCheck({
checkboxClass: ‘icheckbox_minimal-red’,
radioClass: ‘iradio_minimal-red’,
});
}[/code]

ไฟล์ที่ทำหน้าที่ควบคุมการคัดลอกข้อมูล[code language=”javascript” title=”jQuery.DataTables/data.json.transferDataToAnotherTable.js”]$(function() {

dataTableA = $(‘#dataTableA’);
filtersTableA = $(‘#filtersTableA’);
tableA = $(‘#tableA’);

filtersTable = filtersTableA
.DataTable({
"ajax": {
"data": function(parameters) {},
"method": "POST",
"url": "data.json.php",
},
"columns": [{
"orderable": false,
"render": function(data, type, row, meta) {
return parseInt(meta.row) + parseInt(meta.settings._iDisplayStart) + 1;
},
"title": ‘No.’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
if (row.enable == ‘1’) {
var checked = ‘ checked’;
} else {
var checked = ”;
}

return ‘<input’ + checked + ‘ name="enables[]" type="checkbox" value="’ + row.DISTRICT_CODE + ‘">’;
},
"title": ‘<input class="checkAll" type="checkbox">’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
if (row.enable == ‘1’) {
return ‘<span class="glyphicon glyphicon-ok"></span>’;
} else {
return ‘<span class="glyphicon glyphicon-remove"></span>’;
}
},
"title": "Enable",
"width": "10px",
}, {
"data": "DISTRICT_CODE",
"title": "District Code",
"width": "90px",
}, {
"data": "DISTRICT_NAME",
"title": "District Name",
}, {
"data": "PROVINCE_NAME",
"title": "Province Name",
}
],
"processing": true,
"serverSide": true,
"stateSave": true
})
.on(‘draw’, function(event, settings, json, xhr) {
/* add style to checkbox, radio */
iCheckInit($(‘input:checkbox, input:radio’, settings.nTable));
});

iCheckBulk(filtersTableA, filtersTable);

dataTable = tableA
.DataTable({
"columns": [{
"orderable": false,
"render": function(data, type, row, meta) {
return parseInt(meta.row) + parseInt(meta.settings._iDisplayStart) + 1;
},
"title": ‘No.’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
if (row.enable == 1) {
var checked = ‘ checked’;
} else {
var checked = ”;
}

return ‘<input’ + checked + ‘ name="enables[]" type="checkbox" value="’ + row.DISTRICT_CODE + ‘">’;
},
"title": ‘<input class="checkAll" type="checkbox">’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
if (row.enable) {
return ‘<span class="glyphicon glyphicon-ok"></span>’;
} else {
return ‘<span class="glyphicon glyphicon-remove"></span>’;
}
},
"title": "Enable",
"width": "10px",
}, {
"data": "DISTRICT_CODE",
"title": "District Code",
"width": "90px",
}, {
"data": "DISTRICT_NAME",
"title": "District Name",
}, {
"data": "PROVINCE_NAME",
"title": "Province Name",
}
]
})
.on(‘draw’, function(event, settings, json, xhr) {
/* add style to checkbox, radio */
iCheckInit($(‘input:checkbox, input:radio’, settings.nTable));
});

iCheckBulk(tableA, dataTable);

iCheckChange(filtersTableA, filtersTable, dataTable);

iCheckCopy(dataTable, filtersTable, ‘DISTRICT_CODE’);

});[/code]

อธิบาย

มี 2 ตารางคือ id=”filtersTableA” เป็นต้นฉบับ และ id=”tableA” เป็นตารางที่จะรับค่าที่จะคัดลอก
เพราะว่าอาจจะแบ่งข้อมูลไว้หลายหน้าและ DataTable จะ render ที่ละหน้าเท่านั้น ถ้า user เลือกรายการโดยใช้ input enables[] แล้วเปลี่ยนไปหน้าอื่น input นั้นจะหายไป ทำให้ต้องใช้วิธี update กลับไปที่ Data ของตัว DataTable filtersTableA โดยเปลี่ยนค่าใน object data เช่น data.enable = true; หรือ data.enable = false;
เมือคลิก ปุ่ม id=”copyBtn” ให้คัดลอกข้อมูล เพื่อป้องกันการเก็บข้อมูลซ้ำจึงต้อง ดึงรายการที่เก็บข้อมูลเอาไว้แล้ว มาเทียบว่าที่จะเข้ามาใหม่มีรึยังโดยใช้ primary key คือ DISTRICT_CODE

อ่านเพิ่มเติม

DataTable: การค้นหาชั้นสูง

เราสามารถเพิ่มเงื่อนไขให้ DataTables ใช้ในการค้นหาได้ โดยส่วนที่เปลี่ยนไปจากเดิมคือใช้ “beforeSend” ในการรวมข้อมูลจาก form (formA) และเขียน validation ง่ายๆ โดยบังคับว่าถ้า advanceSearch ถูกติ๊กอยู่จะต้องเลือก geo_id ด้วย (จริงๆคือ ถึงไม่ติ๊ก advanceSearch ก็ใช้ geo_id search ได้เหมือนกัน)

[code language=”html” title=”jQuery.DataTables/data.json.external.search.html”]<!doctype html>
<html>

<head>
<meta charset="utf-8">
<meta name="author" content="Pitt Phunsanit">
<title>DataTables: external.search</title>
<link href="../vendor/twbs/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<link href="../vendor/twbs/bootstrap/dist/css/bootstrap-theme.min.css" rel="stylesheet" type="text/css">
<link href="../vendor/datatables/datatables/media/css/dataTables.bootstrap.min.css" rel="stylesheet" type="text/css">
</head>

<body>
<form action="DataTables.json.php" class="form-inline" id="formA" method="post">
<div class="form-group">
<input name="advanceSearch" type="checkbox">
<label for="advanceSearch">advance Search</label>
</div>
<div class="form-group">
<label for="geo_id">ภูมิภาค:</label>
<select class="form-control" name="geo_id">
<option selected="selected" value="">— Select —</option>
<option value="1">ภาคเหนือ</option>
<option value="2">ภาคกลาง</option>
<option value="3">ภาคตะวันออกเฉียงเหนือ</option>
<option value="4">ภาคตะวันตก</option>
<option value="5">ภาคตะวันออก</option>
<option value="6">ภาคใต้</option>
</select>
</div>
<button type="submit" class="btn btn-default">Search</button>
</form>
<table class="table table-bordered table-hover table-striped" id="tableA" width="100%"></table>
<script src="../vendor/components/jquery/jquery.min.js"></script>
<script src="../vendor/datatables/datatables/media/js/jquery.dataTables.min.js"></script>
<script src="../vendor/datatables/datatables/media/js/dataTables.bootstrap.min.js"></script>
<script src="data.json.external.search.js"></script>
</body>

</html>[/code][code language=”javascript” title=”jQuery.DataTables/data.json.external.search.js”]$(function() {

formA = $(‘#formA’);
tableA = $(‘#tableA’);

datatable = tableA.DataTable({
"ajax": {
"beforeSend": function(jqXHR, settings) {
/* add value form from to DataTable params */
settings.data = formA.serialize() + ‘&’ + settings.data;

/* validation */
var params = new URLSearchParams(settings.data);

/* user must selected region if enable advance search */
if (params.get(‘advanceSearch’) == ‘on’ && params.get(‘geo_id’) == ”) {
jqXHR.abort();
alert(‘กรุณาเลือกภูมิภาค’);
return false;
}

return true;
},
"dataSrc": function(json) {
alert(‘data back ‘ + json.data.length + ‘ items’);

return json.data;
},
"method": "POST",
"url": "data.json.php",
},
"columns": [{
"orderable": false,
"render": function(data, type, row, meta) {
return parseInt(meta.row) + parseInt(meta.settings._iDisplayStart) + 1;
},
"title": ‘No.’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
return ‘<input type="checkbox" value="’ + row.DISTRICT_CODE + ‘">’;
},
"title": ‘<input class="checkAll" type="checkbox">’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
if (row.enable == ‘1’) {
return ‘<span class="glyphicon glyphicon-ok"></span>’;
} else {
return ‘<span class="glyphicon glyphicon-remove"></span>’;
}
},
"title": "Enable",
"width": "10px",
}, {
"data": "DISTRICT_CODE",
"title": "District Code",
"width": "90px",
}, {
"data": "DISTRICT_NAME",
"title": "District Name",
}, {
"data": "PROVINCE_NAME",
"title": "Province Name",
}
],
/* default sort */
"order": [
[3, "asc"],
[4, "asc"],
],
"processing": true,
"serverSide": true,
"stateSave": true,
});

$(‘.checkAll’, tableA).click(function() {
$(‘input:checkbox’, tableA).not(this).prop(‘checked’, this.checked);
});

formA.submit(function(event) {
event.preventDefault();

datatable.ajax.reload();
});

});[/code]

ข้อมูลจากฟอร์ม [code language=”html”]id="formA"[/code] จะถูกรวมกับ data ที่ DataTable สร้างขึ้นมาเองโดย[code language=”javascript” title=”beforeSend”]"beforeSend": function(jqXHR, settings) {
settings.data = formA.serialize() + ‘&’ + settings.data;
},[/code]
สามารถทำ validation data ก่อนส่งข้อมูลออกไป ถ้าต้องการหยุดการทำงาน ก็ให้ใช้[code language=”javascript” title=”beforeSend”]"beforeSend": function(jqXHR, settings) {
jqXHR.abort();
},[/code]ไม่ให้ส่งข้อมูลกลับไป server อาจจะดัดแปลงให้ datatable ไม่ขอข้อมูลจาก server กรณีที่ไม่มีการเลือกตัว filter ก็ได้เช่นกัน
หลังได้ข้อมูลกลับมายังสามารถใช้[code language=”javascript” title=”dataSrc / success”]"dataSrc": function(json) {
alert(‘data back ‘ + json.data.length + ‘ items’);

return json.data;
},[/code]แก้ไขข้อมูลก่อนแสดงผลได้ แต่ไม่ใช่ “success” เหมือนใน jQuery ajax ตามปกตินะครับ เพราะจะทำให้ DataTable มันทำงานผิดปกติได้เลย
ถ้าต้องการให้ DataTable ดึงข้อมูลใหม่ให้ใช้รูปแบบ[code language=”javascript” title=”refresh datatable”]datatable.ajax.reload();[/code]

[code language=”php” title=”jQuery.DataTables/data.json.php”]<?php
/* https://datatables.net/manual/server-side */

$output = [
‘data’ => [],
‘debug’ => [
‘length’ => $_REQUEST[‘length’],
‘post’ => $_REQUEST,
‘sqlCount’ => ”,
‘sqlResult’ => ”,
‘start’ => $_REQUEST[‘start’],
],
‘draw’ => $_REQUEST[‘draw’],
‘recordsFiltered’ => $_REQUEST[‘length’],
‘recordsTotal’ => 0,
];

$dns = new PDO(‘mysql:host=localhost;dbname=snippets’, ‘root’, ”, [
//PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => ‘SET NAMES utf8’,
]);

$condition = [];
$from = ‘ FROM district AS d LEFT JOIN province AS p ON d.PROVINCE_ID = p.PROVINCE_ID’;
$parameters = [];
$where = ”;

if (isset($_REQUEST[‘filters’]) || isset($_REQUEST[‘search’][‘value’])) {

if (isset($_REQUEST[‘search’][‘value’])) {
if ($_REQUEST[‘search’][‘value’] != ”) {

$parameter = ‘:d_DISTRICT_NAME’;

$parameters[$parameter] = ‘%’ . $_REQUEST[‘search’][‘value’] . ‘%’;
array_push($condition, ‘d.DISTRICT_NAME LIKE ‘ . $parameter);
}
}

if (isset($_REQUEST[‘filters’])) {
foreach ($_REQUEST[‘filters’] as $tableAlias => $filter) {
foreach ($filter as $field => $value) {
if ($value != ”) {
$parameter = ‘:’ . $tableAlias . ‘_’ . $field;

$parameters[$parameter] = ‘%’ . $value . ‘%’;
array_push($condition, $tableAlias . ‘.’ . $field . ‘ LIKE ‘ . $parameter);
}
}
}
}

}

if (isset($_REQUEST[‘geo_id’]) && $_REQUEST[‘geo_id’] != ”) {
$parameter = ‘:d_geo_id’;

$parameters[$parameter] = $_REQUEST[‘geo_id’];
array_push($condition, ‘d.GEO_ID = ‘ . $parameter);
}

if (count($parameters)) {
$where = ‘ WHERE ‘ . implode("\n\t AND ", $condition);
}

if (isset($_REQUEST[‘order’]) && isset($_REQUEST[‘order’][0])) {
$columns = [
0 => ‘DISTRICT_NAME’,
3 => ‘DISTRICT_CODE’,
4 => ‘DISTRICT_NAME’,
5 => ‘PROVINCE_NAME’,
];

$order = ‘ ORDER BY ‘ . $columns[$_REQUEST[‘order’][0][‘column’]] . ‘ ‘ . strtoupper($_REQUEST[‘order’][0][‘dir’]);
} else {
$order = ‘ ORDER BY DISTRICT_NAME ASC’;
}

$output[‘debug’][‘parameters’] = $parameters;

/* Total records, before filtering */
$sql = ‘SELECT COUNT(d.DISTRICT_ID)’ . $from;
try {
$output[‘debug’][‘sqlCount’] = $sql;
$stmt = $dns->prepare($sql);
$stmt->execute($parameters);
$output[‘recordsTotal’] = (int) $stmt->fetchColumn(0);
} catch (PDOException $e) {
exit($e->getMessage());
}

/* Total records, after filtering */
$sql = ‘SELECT COUNT(d.DISTRICT_ID)’ . $from . $where;
try {
$output[‘debug’][‘sqlCount’] = $sql;
$stmt = $dns->prepare($sql);
$stmt->execute($parameters);
$output[‘recordsFiltered’] = (int) $stmt->fetchColumn(0);
} catch (PDOException $e) {
exit($e->getMessage());
}

/* data */
if ($output[‘recordsTotal’] > 0) {
$sql = ‘SELECT d.enable, d.DISTRICT_ID, d.DISTRICT_CODE, d.DISTRICT_NAME, p.PROVINCE_NAME’ . $from . $where . $order . " LIMIT " . $_REQUEST[‘start’] . ", " . $_REQUEST[‘length’] . ";";

try {
$output[‘debug’][‘sqlResult’] = $sql;
$stmt = $dns->prepare($sql);
$stmt->execute($parameters);
$output[‘data’] = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
exit($e->getMessage());
}
}

/* unset debug for security */
unset($output[‘debug’]);

header(‘Content-type: application/json; charset=utf-8’);
echo json_encode($output);[/code]

อ่านเพิ่มเติม

DataTable: ทำ ajax data grid table

DataTables เป็น grid ที่สามารถให้ฟรีได้โดยไม่มีเงื่อนไขจริงๆ การใช้ก็ไม่ยุ่งยากจนเกินไป

[code language=”html” title=”jQuery.DataTables/data.json.html”]<!doctype html>
<html>

<head>
<meta charset="utf-8">
<meta name="author" content="Pitt Phunsanit">
<title>DataTables: datas from ajax</title>
<link href="../vendor/twbs/bootstrap/dist/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<link href="../vendor/twbs/bootstrap/dist/css/bootstrap-theme.min.css" rel="stylesheet" type="text/css">
<link href="../vendor/datatables/datatables/media/css/dataTables.bootstrap.min.css" rel="stylesheet" type="text/css">
</head>

</html>[/code][code language=”javascript” title=”jQuery.DataTables/data.json.js”]$(function() {

tableA = $(‘#tableA’);

datatable = tableA.DataTable({
"ajax": {
"data": function(parameters) {},
"method": "POST",
"url": "data.json.php",
},
"columns": [{
"orderable": false,
"render": function(data, type, row, meta) {
return parseInt(meta.row) + parseInt(meta.settings._iDisplayStart) + 1;
},
"title": ‘No.’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
return ‘<input type="checkbox" value="’ + row.DISTRICT_CODE + ‘">’;
},
"title": ‘<input class="checkAll" type="checkbox">’,
"width": "10px",
},
{
"orderable": false,
"render": function(data, type, row, meta) {
if (row.enable == ‘1’) {
return ‘<span class="glyphicon glyphicon-ok"></span>’;
} else {
return ‘<span class="glyphicon glyphicon-remove"></span>’;
}
},
"title": "Enable",
"width": "10px",
}, {
"data": "DISTRICT_CODE",
"title": "District Code",
"width": "90px",
}, {
"data": "DISTRICT_NAME",
"title": "District Name",
}, {
"data": "PROVINCE_NAME",
"title": "Province Name",
}
],
/* default sort */
"order": [
[3, "asc"],
[4, "asc"],
],
"processing": true,
"serverSide": true,
"stateSave": true,
});

$(‘.checkAll’, tableA).click(function() {
$(‘input:checkbox’, tableA).not(this).prop(‘checked’, this.checked);
});

});[/code][code language=”php” title=”jQuery.DataTables/data.json.php”]<?php
/* https://datatables.net/manual/server-side */

$dns = new PDO(‘mysql:host=localhost;dbname=snippets’, ‘root’, ”, [
//PDO::ATTR_EMULATE_PREPARES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => ‘SET NAMES utf8’,
]);

$condition = [];
$from = ‘ FROM district AS d LEFT JOIN province AS p ON d.PROVINCE_ID = p.PROVINCE_ID’;
$parameters = [];
$where = ”;

if (isset($_REQUEST[‘filters’]) || isset($_REQUEST[‘search’][‘value’])) {

if (isset($_REQUEST[‘search’][‘value’])) {
if ($_REQUEST[‘search’][‘value’] != ”) {

$parameter = ‘:d_DISTRICT_NAME’;

$parameters[$parameter] = ‘%’ . $_REQUEST[‘search’][‘value’] . ‘%’;
array_push($condition, ‘d.DISTRICT_NAME LIKE ‘ . $parameter);
}
}

$parameters[$parameter] = ‘%’ . $value . ‘%’;
array_push($condition, $tableAlias . ‘.’ . $field . ‘ LIKE ‘ . $parameter);
}
}
}
}

}

if (isset($_REQUEST[‘geo_id’]) && $_REQUEST[‘geo_id’] != ”) {
$parameter = ‘:d_geo_id’;

$parameters[$parameter] = $_REQUEST[‘geo_id’];
array_push($condition, ‘d.GEO_ID = ‘ . $parameter);
}

if (count($parameters)) {
$where = ‘ WHERE ‘ . implode("\n\t AND ", $condition);
}

if (isset($_REQUEST[‘order’]) && isset($_REQUEST[‘order’][0])) {
$columns = [
0 => ‘DISTRICT_NAME’,
3 => ‘DISTRICT_CODE’,
4 => ‘DISTRICT_NAME’,
5 => ‘PROVINCE_NAME’,
];

$order = ‘ ORDER BY ‘ . $columns[$_REQUEST[‘order’][0][‘column’]] . ‘ ‘ . strtoupper($_REQUEST[‘order’][0][‘dir’]);
} else {
$order = ‘ ORDER BY DISTRICT_NAME ASC’;
}

$output[‘debug’][‘parameters’] = $parameters;

/* unset debug for security */
unset($output[‘debug’]);

header(‘Content-type: application/json; charset=utf-8’);
echo json_encode($output);[/code]json ที่ส่งกลับมาที่สำคัญคือ

data
: เป็นข้อมูลที่นำมาสร้างเป้นตัว body ใน table หรือคือ data ที่ต้องการนำมาแสดง
draw: เป็นรหัสอ้างอิงให้ DataTable รู้ว่า json ที่ส่งมาอันไหนใหม่กว่า จะเรียกว่าเป็นเลข version ของข้อมูลก็ได้
recordsFiltered: เป็นจำนวนชุดข้อมูลทั้งหมดที่ query ได้ (ไม่ใช่จำนวนจริงๆ ที่ return กลับมา เช่น query ได้ข้อมูลทั้งหมด 8,880 รายการ แต่ทำ paging ดึงข้อมูล data มาแค่ครั้งละ 10, 20, 50 และ 100 รายการ)
recordsTotal: เป็นจำนวนข้อมูลทั้งหมดที่มีใน table โดยที่ยังไม่ใส่เงื่อนไข query ซึ่งจริงๆแล้วคัดลอกค่ามากจาก recordsTotal ไปเลยก็ได้ จะได้ไม่เสียเวลา query

สร้าง Data Dictionary แค่คลิก

สร้าง Data Dictionary ใน sql server แบบง่ายๆ เพียงแค่คลิกเท่านั้น บทความนี้เป็นเวอร์ชั้นปรับปรุงของ สร้าง Data Dictionary แบบด่วนๆ ให้ทำได้ง่ายขั้น

เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query
copy[code language=”sql” title=”generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit”]
SET ANSI_PADDING OFF;
GO

SET NOCOUNT ON;
GO

SET TEXTSIZE 8192;
GO

PRINT ‘<!doctype html>’;
PRINT ‘<html lang="th">’;
PRINT ‘<head>’;
PRINT ‘<meta charset="utf-8" />’;
PRINT ‘<title>script generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit</title>’;
PRINT ‘<style>table {background: #7ba0cd;border-collapse: collapse;border-left: solid 1px #7ba0cd;border-right: solid 1px #7ba0cd;font-family: "Calibri", "sans-serif";font-size: 11pt;width: 100%;}table tr {background: #FFFFFF;border-bottom: solid 1px #7ba0cd;}table thead tr {background-color: #4e80bc;color: white;}table tbody tr:nth-of-type(odd) {background: #d2deed;border-bottom: solid 1px #FFFFFF;box-shadow: 0 2px 0 -1px #7ba0cd;}table tbody tr.case,table tfoot tr {background: #d7d7d7;}</style>’;
PRINT ‘</head>’;
PRINT ‘<body>’;
PRINT ‘<h1>’ + DB_NAME() + ‘</h1>’;
PRINT ‘<h3>create date : ‘ + convert(VARCHAR(10), GETDATE(), 111) + ‘</h3>’;

DECLARE @record CURSOR;
DECLARE @table VARCHAR(128);
DECLARE @object_id INT;
DECLARE @schema_id INT;

SET @record = CURSOR
FOR
SELECT [name] AS [table], [object_id], [schema_id]
FROM sys.tables AS t
WHERE type_desc = ‘USER_TABLE’

OPEN @record

FETCH NEXT
FROM @record
INTO @table, @object_id, @schema_id

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT CAST(‘<br><table><caption><b>’ + @table + ‘</b>’ + ISNULL(‘ : ‘+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</caption>’ AS NTEXT)
,CAST(‘<thead><tr>’ + ‘<td style="width:100px;"><b>Primary Key</b></td>’ + ‘<td style="width:200px;"><b>Foreign key</b></td>’ + ‘<td style="width:400px;"><b>Column Name</b></td>’ + ‘<td><b>Description</b></td>’ AS NTEXT)
,CAST(‘<td style="width:100px;"><b>Data Type</b></td>’ + ‘<td style="width:100px;"><b>Allow Nulls</b></td>’ + ‘<td style="width:100px;"><b>Identity</b></td>’ + ‘<td style="width:100px;"><b>Default Value</b></td>’ + ‘</tr></thead><tbody>’ AS NTEXT)
FROM sys.schemas AS s
— get description of table, if available
LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = @object_id
AND ep.minor_id = 0
AND ep.NAME = ‘MS_Description’
AND ep.class = 1
WHERE s.[schema_id] = @schema_id
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = @object_id
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT ” AS html
,'<tr><td>’ + CASE
WHEN pk.column_id IS NOT NULL
THEN ‘PK’
ELSE ”
END + ‘</td><td>’ + CASE
WHEN fk.primary_table IS NOT NULL
THEN fk.primary_table + ‘.’ + fk.primary_column
ELSE ”
END + ‘</td><td>’ + c.[name] + ‘</td><td>’ + ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</td><td>’ + CASE
WHEN uty.[name] IS NOT NULL
THEN uty.[name]
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘ (
‘
ELSE ”
END + CASE
WHEN sty.[name] IS NOT NULL
THEN sty.[name]
ELSE ”
END + CASE
WHEN sty.[name] IN (
‘CHAR’
,’NCHAR’
,’VARCHAR’
,’NVARCHAR’
,’BINARY’
,’VARBINARY’
)
THEN ‘ (
‘ + CASE
WHEN c.max_length = – 1
THEN ‘max’
ELSE CASE
WHEN sty.[name] IN (
‘NCHAR’
,’NVARCHAR’
)
THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
ELSE CAST(c.max_length AS VARCHAR(MAX))
END
END + ‘
) ‘
WHEN sty.[name] IN (
‘NUMERIC’
,’DECIMAL’
)
THEN ‘ (
‘ + CAST(c.precision AS VARCHAR(MAX)) + ‘
,’ + CAST(c.scale AS VARCHAR(MAX)) + ‘
) ‘
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘
) ‘
ELSE ”
END + ‘</td><td>’ + CASE
WHEN c.is_nullable = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ AS html1
,CASE
WHEN c.is_identity = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ + ISNULL(dc.[definition], ”) + ‘</td></tr>’ AS html2
FROM sys.columns AS c
INNER JOIN sys.schemas s ON s.[schema_id] = @schema_id
— get name of user data type
LEFT OUTER JOIN sys.types uty ON uty.system_type_id = c.system_type_id
AND uty.user_type_id = c.user_type_id
AND c.user_type_id <> c.system_type_id
— get name of system data type
LEFT OUTER JOIN sys.types sty ON sty.system_type_id = c.system_type_id
AND sty.user_type_id = c.system_type_id
— get description of column, if available
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = @object_id
AND ep.minor_id = c.column_id
AND ep.[name] = ‘MS_Description’
AND ep.[class] = 1
— get default’ s code TEXT
LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = @object_id
AND dc.parent_column_id = c.column_id
— check for inclusion in primary key
LEFT OUTER JOIN (
SELECT ic.column_id
,i.[object_id]
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id
AND ic.[object_id] = i.[object_id]
WHERE i.is_primary_key = 1
) pk ON pk.column_id = c.column_id
AND pk.[object_id] = @object_id
— check for inclusion in foreign key
LEFT OUTER JOIN (
SELECT CASE
WHEN s.[name] = ‘dbo’
THEN pk.[name]
ELSE s.[name] + ‘.’ + pk.[name]
END AS primary_table
,pkc.[name] AS primary_column
,fkc.parent_object_id
,fkc.parent_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.tables pk ON fk.referenced_object_id = pk.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = pk.[schema_id]
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
AND fkc.referenced_object_id = pk.[object_id]
INNER JOIN sys.columns pkc ON pkc.[object_id] = pk.[object_id]
AND pkc.column_id = fkc.referenced_column_id
) fk ON fk.parent_object_id = @object_id
AND fk.parent_column_id = c.column_id
WHERE c.object_id = @object_id
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = @object_id
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT ” AS html
,” AS html1
,'</tbody></table><br><hr><br>’ AS html2

FETCH NEXT
FROM @record
INTO @table, @object_id, @schema_id
END

CLOSE @record

DEALLOCATE @record

PRINT ‘<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit ([email protected])</a>’;
PRINT ‘</body>’;
PRINT ‘</html>’;
[/code]
คลิกปุ่ม Results to file หรือ Ctrl + Shift + F กด Excute หรือ F5 แล้วเซฟเป็นไฟล์ ที่ต้องการแต่ลงท้ายด้วย .html เพราะว่าผลลัพธ์ที่ได้จะเป็นเว็บครับ
เปิดไฟล์ที่ส่งออกออกมาแล้วคัดลอกใส่โปรแกรมเวิร์ดหรือเอ็กเซลได้เลยครับ

ตัวอย่างผลงานที่เร็วกว่ามาม่า

test

create date : 2016/07/14

—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-

**areas_cities**
Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK		city_id		int
		status		tinyint	Y
		geo_id	ภูมิภาค	int			((0))
		province_id		int			((0))
		amphur_code		varchar ( 4 )	Y

**areas_cities_lang**
Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK	areas_cities.city_id	city_id		int
PK		language		char ( 2 )
		name		nvarchar ( 600 )

PlusMagi.com by Pitt Phunsanit ([email protected])
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ

เวลานำไปให้ให้เครดิตผมด้วยนะครับ ถ้าหากเจอข้อบกพร่องแจ้งผมด้วยนะครับ จะได้ปรับปรุงให้คนอื่นใช้กันต่อไป “สังคมแห่งการแบ่งปันครับ”

สร้าง Data Dictionary แบบด่วนๆ

Updateอ่าน สร้าง Data Dictionary แค่คลิก แทนครับ

ต้องเขียน Data Dictionary ให้โครงการที่ทำอยู่ แต่มันมีหลายตารางมาก และที่สำคัญคือ แต่ละตารางจะมี relation ยุบยับเต็มไปหมด copy โครงสร้างมาแต่ละตารางมาวางในเอ็กส์เซล์ที่ละตัวก็ตาลาย พลาดได้ง่ายๆเลย

วิธีที่คิดออกคือ มันต้องมีโปรแกรมที่ช่วยงานนี้ได้ซิ เราไม่ใช่คนแรกที่ต้องเขียน Data Dictionary ซะหน่อย มีจริงๆ แต่แพง ไม่ก็ดูแปลกๆ เปลี่ยนไปใช้อีกวิธีคือ ทำไมไม่ให้ SQL ทำให้ละ search เจอ SQL Server Data Dictionary แต่มันก็ดูยาก เลยต้องเอามาเขียนใหม่ซะเอง

query ที่เขียนขึ้นมาใหม่คือ[code language=”sql”]
SET ANSI_DEFAULTS OFF;
GO

SET NOCOUNT ON;
GO

SET TEXTSIZE 8192;
GO

PRINT ‘<HTML><body>’
PRINT ‘<!doctype html>’
PRINT ‘<html>’
PRINT ‘<head>’
PRINT ‘<meta charset="utf-8">’
PRINT ‘<title>script generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit</title>’
PRINT ‘<style>table {background: #7ba0cd;border-collapse: collapse;border-left: solid 1px #7ba0cd;border-right: solid 1px #7ba0cd;font-family: "Calibri", "sans-serif";font-size: 11pt;width: 100%;}table tr {background: #FFFFFF;border-bottom: solid 1px #7ba0cd;}table thead tr {background-color: #4e80bc;color: white;}table tbody tr:nth-of-type(odd) {background: #d2deed;border-bottom: solid 1px #FFFFFF;box-shadow: 0 2px 0 -1px #7ba0cd;}table tbody tr.case,table tfoot tr {background: #d7d7d7;}</style>’
PRINT ‘</head>’
PRINT ‘<body>’
PRINT ‘<h1>’ + DB_NAME() + ‘</h1>’
PRINT ‘<h3>create date : ‘ + convert(VARCHAR(10), GETDATE(), 111) + ‘</h3>’

SELECT html
,html1
,html2
FROM (
SELECT s.[name] AS [schema]
,CASE
WHEN s.[name] = ‘dbo’
THEN t.[name]
ELSE s.[name] + ‘.’ + t.[name]
END AS [table]
,NULL AS column_id
,CAST(‘<table><cption><b>’ + t.[name] + ‘</b>’ + ISNULL(‘ : ‘+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</caption>’ AS NTEXT) AS html
,CAST(‘<thead>’ + ‘<td style="width:100px;"><b>Primary Key</b></td>’ + ‘<td style="width:200px;"><b>Foreign key</b></td>’ + ‘<td style="width:400px;"><b>Column Name</b></td>’ + ‘<td><b>Description</b></td>’ AS NTEXT) AS html1
,CAST(‘<td style="width:100px;"><b>Data Type</b></td>’ + ‘<td style="width:100px;"><b>Allow Nulls</b></td>’ + ‘<td style="width:100px;"><b>Identity</b></td>’ + ‘<td style="width:100px;"><b>Default Value</b></td>’ + ‘</thead><tbody>’ AS NTEXT) AS html2
FROM sys.tables t
INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
— get description of table, if available
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
AND ep.minor_id = 0
AND ep.NAME = ‘MS_Description’
AND ep.class = 1
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = t.[object_id]
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT s.[name] AS [schema]
,CASE
WHEN s.[name] = ‘dbo’
THEN t.[name]
ELSE s.[name] + ‘.’ + t.[name]
END AS [table]
,c.column_id
,” AS html
,'<tr><td>’ + CASE
WHEN pk.column_id IS NOT NULL
THEN ‘PK’
ELSE ”
END + ‘</td><td>’ + CASE
WHEN fk.primary_table IS NOT NULL
THEN fk.primary_table + ‘.’ + fk.primary_column
ELSE ”
END + ‘</td><td>’ + c.[name] + ‘</td><td>’ + ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</td><td>’ + CASE
WHEN uty.[name] IS NOT NULL
THEN uty.[name]
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘ (
‘
ELSE ”
END + CASE
WHEN sty.[name] IS NOT NULL
THEN sty.[name]
ELSE ”
END + CASE
WHEN sty.[name] IN (
‘CHAR’
,’NCHAR’
,’VARCHAR’
,’NVARCHAR’
,’BINARY’
,’VARBINARY’
)
THEN ‘ (
‘ + CASE
WHEN c.max_length = – 1
THEN ‘max’
ELSE CASE
WHEN sty.[name] IN (
‘NCHAR’
,’NVARCHAR’
)
THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
ELSE CAST(c.max_length AS VARCHAR(MAX))
END
END + ‘
) ‘
WHEN sty.[name] IN (
‘NUMERIC’
,’DECIMAL’
)
THEN ‘ (
‘ + CAST(c.precision AS VARCHAR(MAX)) + ‘
,’ + CAST(c.scale AS VARCHAR(MAX)) + ‘
) ‘
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘
) ‘
ELSE ”
END + ‘</td><td>’ + CASE
WHEN c.is_nullable = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ AS html1
,CASE
WHEN c.is_identity = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ + ISNULL(dc.[definition], ”) + ‘</td></tr>’ AS html2
FROM sys.columns c
INNER JOIN sys.tables t ON t.[object_id] = c.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
— get name of user data type
LEFT OUTER JOIN sys.types uty ON uty.system_type_id = c.system_type_id
AND uty.user_type_id = c.user_type_id
AND c.user_type_id <> c.system_type_id
— get name of system data type
LEFT OUTER JOIN sys.types sty ON sty.system_type_id = c.system_type_id
AND sty.user_type_id = c.system_type_id
— get description of column, if available
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
AND ep.minor_id = c.column_id
AND ep.[name] = ‘MS_Description’
AND ep.[class] = 1
— get default’ s code TEXT
LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = t.[object_id]
AND dc.parent_column_id = c.column_id
— check for inclusion in primary key
LEFT OUTER JOIN (
SELECT ic.column_id
,i.[object_id]
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id
AND ic.[object_id] = i.[object_id]
WHERE i.is_primary_key = 1
) pk ON pk.column_id = c.column_id
AND pk.[object_id] = t.[object_id]
— check for inclusion in foreign key
LEFT OUTER JOIN (
SELECT CASE
WHEN s.[name] = ‘dbo’
THEN pk.[name]
ELSE s.[name] + ‘.’ + pk.[name]
END AS primary_table
,pkc.[name] AS primary_column
,fkc.parent_object_id
,fkc.parent_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.tables pk ON fk.referenced_object_id = pk.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = pk.[schema_id]
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
AND fkc.referenced_object_id = pk.[object_id]
INNER JOIN sys.columns pkc ON pkc.[object_id] = pk.[object_id]
AND pkc.column_id = fkc.referenced_column_id
) fk ON fk.parent_object_id = t.[object_id]
AND fk.parent_column_id = c.column_id
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = t.[object_id]
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT s.[name] AS [schema]
,CASE
WHEN s.[name] = ‘dbo’
THEN t.[name]
ELSE s.[name] + ‘.’ + t.[name]
END AS [table]
,999999 AS column_id
,” AS html
,” AS html1
,'</tbody></table><br><hr><br>’ AS html2
FROM sys.tables t
INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = t.[object_id]
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)
) tmp
ORDER BY tmp.[schema]
,tmp.[table]
,tmp.column_id;

PRINT ‘<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit ([email protected])</a>’
PRINT ‘</body>’
PRINT ‘</html>’
[/code] query ตัวนี้มีอะไรพิเศษมากกว่าตัวอื่นๆ หลายอย่าง คือ

การเอาไปใช้ แทนที่จะดูผลลัพธ์ในรูปแบบ grid ต้องใช้มันในแบบ Results to Text โดยกด (Ctrl+T) แล้วเอาผลที่ได้ (เป็น code html) ให้เอาไป copy เป็นไฟล์ .html แล้วจะ copy ไปลง excel / word อีกต่อหนึ่งก็ได้ ถ้าใช้ grid ก็ได้ code เหมือนกันแต่จะไม่สวย เพราะพวกคำสั่ง print มันจะไม่ทำงาน
ผลลัพท์ต้องแบ่งเป็น column html, html1, html2 เพราะว่าถ้าผลลัพธ์ที่ออกมากมันยาว จะโดนตัดทิ้งไปเฉยๆ พยามแก้อยู่นาน นานกว่าเขียน code ของมันซะอีก โดยลองใช้ SET TEXTSIZE 8192; และ cast ร่วมกับตัวเลือก Maximum number of characters display in each column ก็ไม่ได้ผล เลยต้องซอย column ออกมาแทน
การเอา code ที่เอาไปใช้ ถ้ามีการจัด code ใหม่ต้องระวังเงื่อนไข อย่าง ‘CHAR’ ถ้าโดนจัดเป็น ‘ CHAR ‘ (มี space) มันจะไม่ทำงาน
การใช้งานถ้าจะให้ข้อมูลออกมาครบต้องทำ table relation และใส่ descriptions ให้ครบ ถึงจะออกมาสวยๆ ครบๆ เหมือนกัน
sub query ด้านล่างๆ จริงๆ คิดว่าถ้าให้มีประสิทธิภาพน่าจะเขียนวิธีอื่นๆ ได้ แต่ตอนนี้ขอใช้ quick and easy (dirty) ไว้ก่อน
ยังมีจุดที่จะแก้ในครั้งต่อไปอย่าง มันยังมี result header อยู่ทำให้มีเส้น ———————- กับ html html1 html2 เกินมาอยู่ แต่ลบเองไม่กี่วิก็ใช้ได้ละ เพราะงั้นปล่อยๆ มันไปก่อน
ใครแก้จุดไหนได้ รบกวนบอกผมด้วยละกันครับ

ตัวอย่างผลลัพธ์ที่ได้

DATABASE NAME

create date : 2016/05/30

areas_cities : อำเภอ

Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK		city_id		int
		status		tinyint	Y
		geo_id	ภูมิภาค	int			(‘0’)
		province_id		int			(‘0’)
		amphur_code		varchar (4)	Y

areas_cities_lang : อำเภอ (แปล)

Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK	areas_cities.city_id	city_id		int
PK		language		char (2)
		name		nvarchar (600)

จริงๆ มันมีสีสันด้วยนะ สวยจนเอาไปใช้ได้เลยไม่ต้องแต่งอะไรอีก

MySql ต่างกับ sql server

อ่านเจอคำถามนี้ใน facebook

อยากทราบว่าถ้าเราเปลี่ยน database จาก mysql เป็น sql server 2008 นอกจากส่วน connect แล้ว โค๊ดเก่าจากmysql จะสามารถใช้ได้เหมือนเดิมไหมครับ (ผมใช้ codeigniter เขียนครับ)

เลยลองยกตัวอย่างดู โดยใช้ PDO แทน function ของแต่ละ database จะได้เห็นได้ชัดเจน

schema_mysql.php สำหรับ mysql[code language=”php” title=”schema_mysql.php”]
<?php
$database = ‘yii2advanced’;

try {
$dbh = new PDO(
‘mysql:host=localhost;dbname=’ . $database,
‘root’,
”
);
} catch (PDOException $e) {
exit(‘Error!: ‘ . $e->getMessage());
}
$dbh->query(‘SET NAMES utf8’);

switch ($_GET[‘op’]) {
case ‘columns’:{
$table = $_GET[‘table’];

$sql = "SELECT `COLUMN_NAME` AS name
,`DATA_TYPE` AS type
,IFNULL(`CHARACTER_MAXIMUM_LENGTH`, 0) AS maxlength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘" . $table . "’;";
$sth = $dbh->prepare($sql);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
array_push($results, $row);
}
echo json_encode($results, JSON_NUMERIC_CHECK);
}
}break;
case ‘tables’:{
$sql = ‘SHOW TABLES IN ‘ . $database . ‘;’;
$sth = $dbh->prepare($sql);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
array_push($results, $row);
}
}
echo json_encode($results);
}break;
}
[/code] และ schema_sqlsrv.php สำหรับ Microsoft sql server[code language=”php” title=”schema_sqlsrv.php”]
<?php
$database = ‘TPA’;

try {
$dbh = new PDO(
‘sqlsrv:Server=MAGI\SQLEXPRESS;Database=’ . $database
);
} catch (PDOException $e) {
exit(‘Error!: ‘ . $e->getMessage());
}
$dbh->query(‘SET CHARACTER_SET utf8_unicode_ci’);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

switch ($_GET[‘op’]) {
case ‘columns’:{
$table = $_GET[‘table’];

$sql = "SELECT [COLUMN_NAME] AS name
,[DATA_TYPE] AS type
,ISNULL([CHARACTER_MAXIMUM_LENGTH], 0) AS maxlength
FROM TPA.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘" . $table . "’;";
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
array_push($results, $row);
}
echo json_encode($results, JSON_NUMERIC_CHECK);
}
}break;
case ‘tables’:{
$sql = ‘SELECT [TABLE_NAME] FROM ‘ . $database . ‘.INFORMATION_SCHEMA.Tables;’;
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
array_push($results, $row);
}
echo json_encode($results);
}
}break;
}
[/code]

ถึงจะพยามออกแบบ extension ตัวนี้ให้ทำงานได้ทุก database แต่แค่การ connection ก็ต่างกันแล้วแต่ละตัวต่างก็มี ตัวเลือกเพิ่มเติมไม่เหมือนกัน อย่าง การเลือกชุดตัวอักษร SET NAMES utf8 และ SET CHARACTER_SET utf8_unicode_ci ใน sql server (ภาษาไทย ภาษาเดียวก็มีให้เลือกกันเหนื่อยแล้วว่าจะใช้ตัวไหน ลองอ่าน LAB TEST : ความแตกต่างของ SQL THAI Collation แต่ละแบบ) จุดนี้พอจะเข้าใจได้เพราะมาจากคนละบริษัทกัน มาจากคนละความคิด

จุดต่อมา mySql ใช้ ` ` แต่ sql server ใช้ [ ] ในการบอกว่านี่คือชื่อเฉพาะนะ วิธีแก้ก็ง่ายๆ อย่าไปใส่มัน ยกเว้นว่าจะไปใช้ชื่อที่ตรงกับศัพท์สงวน (reserved words) เอาไว้ จำเป็นต้องใช้เพื่อให้ตัว database ไม่สับสน

ถ้าต้องต้องเปลี่ยนฐานข้อมูลก็หลีกเลี่ยงคำพวกนี้ไว้ก่อน

คำสั่งเกี่ยวกับโครงสร้างฐานข้อมูล ต่างเจ้าก็ต่างกัน ทั้งชื่อ type และ key บางครั้งชื่อเดียวกันแต่เก็บข้อมูลได้ไม่เท่ากัน การจะดึงข้อมูลโครงสร้างตารางออกมาก็ใช้คำสั่งต่างกัน ในตัวอย่าง switch case ทั้งสองตัวจะเป็นการ query ดูตารางทั้งหมดในฐานข้อมูลและชนิดของฟิลย์ในตารางทั้งหมด ซึ่งไม่ได้ไกล้เคียงกันเลย (นานๆ จะใช้ที ไม่ต้องไปจำมันก็ได้ ยกเว้นคุณจะเขียน curd ใช้เอง)

การใช้ Abstraction Layers แทนที่จะใช้ Vendor Specific Database Extensions หรือที่เรียกกันว่า native driver ที่เป็นของฐานข้อมูลแต่ละตัว เพราะว่าต้องการที่จะใช้คำสั่งเหมือนๆ กัน ไม่ต้องมาคิดว่าฐานข้อมูลแบบนี้ใช้ function ชื่อนี้ เวลาเปลี่ยน ชนิดฐานข้อมูลก็แค่เปลี่ยนตรง connection ก็พอแล้วไม่ต้องเขียนคำสั่งใหม่ แต่จริงๆ แล้วยังไม่มี Abstraction Layers ตัวไหน หรือของ framework ไหน ไม่ว่าจะเป็น codeigniter, yii, laravel แม้แต่ .net หรือ java ที่แปลง sql query แล้วสามารถทำงานได้เหมือนกันกับทุกๆ ตัว แค่ทำได้ 80% ของทั้งหมดในตัวอย่าง pdo ของ sql server ถ้าไม่ระบุ PDO::ATTR_CURSOR เพิ่มเข้าไป มันก็จะไม่รู้เลยว่า query ออกมามีผลลัพธ์ออกมารึเปล่า และถ้าสังเกตุจะมีคำสั่งที่ใช้ตรวจสอบค่า NULL และแทนด้วยค่าอื่นใน MySQL ใช้ IFNULL แต่ SQL SERVER ใช้ ISNULL การทำงาน โครงสร้างเหมือนกัน แต่เขียนไม่เหมือนกัน แทนกันไม่ได้ (:

ล้างข้อมูลลบทุกตารางในฐานข้อมูล

นอกจากการ ลบตารางทั้งฐานข้อมูล SQL Server แล้วในช่วงการพัฒนา อาจจะต้องลบข้อมูลทิ้งทั้งหมด เพื่อทดสอบและแก้ bug

โดยปกติการล้างข้อมูลในเอสคิวแอล เซิร์ฟเวอร์จะใช้คำสั่ง[code lang=”sql”]
TRUNCATE TABLE table_name
[/code]แต่เป็นการทำครั้งละ 1 ตาราง ถ้ามี 20 table ก็จะเสียเวลา แถมต้องลบบางตารางตามลำดับ ถ้าผูกความสัมพันธ์กับตารางอื่นๆไว้ query ชุดนี้จะช่วยลบข้อมูลได้ทั้ง database ไม่ว่าทั้งฐานข้อมูลจะมีกี่ตาราง กี่แรคคอร์ด และตั้ง identity (คล้ายๆ auto increment ใน MySQL) ให้กลับไปที่จุดเริ่มต้น ตั้งแต่ 1 เหมือนตารางใหม่เลย

โปรดตั้งสติก่อนการปฏิบัติการ T-T

[code lang=”sql”]
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

EXEC sp_MSForEachTable ‘DELETE FROM ?’

EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
[/code]
และถ้าเปลี่ยน ‘DELETE FROM ?’ เป็น ‘DROP TABLE ?’ ก็จะเป็นการลบตารางออกไป คำสังแบบนี้ใช้กับ view ก็ได้เหมือนกัน ถ้าลบไม่ได้ลองลบข้อมูลออกไปก่อนตามวิธี ลบตารางทั้งฐานข้อมูล SQL Server

ลบตารางทั้งฐานข้อมูล SQL Server

YII2 GRUD GRID FROM หลายภาษา

จากเรื่องที่แล้ว ทำตาราง yii 2 ให้เก็บหลายภาษา โดยเราสามารถอ้างถึง attribute ตามรูปแบบ {attribute name}_{language} เช่น ในตาราง _lang ฟิลย์ชื่อ name ก็ใช้ $model->name_en, $model->name_jp, $model->name_th ในวิวถ้าต้องการแสดงผล

ในบทความที่ยังขาดตัวอย่างในการแสดงข้อมูล การใช้ในกริดที่แสดงรายการทั้งหมด, ฟอร์มที่จะกรอกข้อมูล และวิวที่จะแสดงสิ่งที่เราเก็บเอาไว้ออกมา ตัวอย่าง code ที่ผมใช้

GridView ต้องการให้แสดงภาษาอังกฤษเป็นหลัก อีกช่องที่เหลือแสดงสลับกันระหว่างภาษาไทย และญี่ปุ่น ทำได้โดยแทนที่จะอ้าง name_th หรือ name_jp ตรงๆ ก็อ้างผ่านตัวแปรไปแทน [code lang=”php” title=”index.php”]
…

if(Yii::$app->language == ‘jp’)
{
$name = ‘name_jp’;
}
else
{
$name = ‘name_th’;
}
…
echo GridView::widget([
‘dataProvider’ => $dataProvider,
‘filterModel’ => $searchModel,
‘columns’ => [
[‘class’ => ‘yii\grid\SerialColumn’],
…
‘name_en’,
$name,
…
‘post_id’,
‘status’,
],
]);
…
[/code]

form ก็สามารถใช้เหมือนรูบแบบปกติได้เลย โดยเราสามารถกรอกข้อมูล แก้ไขตัวแปลภาษาทั้งหมดได้พร้อมกันในครั้งเดียว เช่น[code lang=”php” title=”_form.php”]
…
<?= $form->field($model, ‘name_en’)->textInput() ?>
<?= $form->field($model, ‘name_jp’)->textInput() ?>
<?= $form->field($model, ‘name_th’)->textInput() ?>
…
[/code]

วิวก็ตามรูปแบบเหมือนตัวอื่นๆตามปกติ[code lang=”php” title=”view.php”]
…
<?= DetailView::widget([
‘model’ => $model,
‘attributes’ => [
‘post_id’,
‘status’,
…
‘name_en’,
‘name_jp’,
‘name_th’,
…
‘date_publish’,
‘date_expire’,
‘log_created’,
‘log_created_by’,
‘log_updated’,
‘log_updated_by’,
],
]) ?>
…[/code]

ทำตาราง yii 2 ให้เก็บหลายภาษา

ต่อจาก ทำ yii 2 ให้รับหลายภาษา หลังจากใส่คำแปรให้ text ต่างๆ แล้วเราจะแยกเก็บ database ภาษาต่างๆ ออกจากกัน คนไทยก็อยากจะอ่านภาษาไทย คนจีนก็อ่านเป็นแต่ภาษาตัวเอง

ติดตั้ง OmgDef/yii2-multilingual-behavior โดยแก้ composer.json ใส่[code language=”php”]
"require": {
…
"omgdef/yii2-multilingual-behavior": "~2.0"
…
},
[/code]run composer update
สร้างตาราง โดยแยกตารางหลักที่เก็บ id และฟิลย์ที่ใช้ร่วมกัน อีกตารางเก็บ field ที่แยกตามภาษาต่างๆเช่น content, title[code language=”sql”]
CREATE TABLE IF NOT EXISTS `post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`enabled` tinyint(1) NOT NULL DEFAULT ‘1’,
`log_created` datetime NOT NULL,
`log_updated` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `postLang` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`post_id` int(11) NOT NULL,
`language` varchar(6) NOT NULL,
`title` varchar(255) NOT NULL,
`content` TEXT NOT NULL,
PRIMARY KEY (`id`),
KEY `post_id` (`post_id`),
KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `postLang`
ADD CONSTRAINT `postlang_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
[/code]อย่าลืมใส่ id และ language มันจำเป็นต้องใช้ เก็บว่า record นี้เป็นภาษาอะไร
สร้าง model โดยใช้ gii ตามปกติทั้ง 2 ตาราง
เปิด model ของตารางหลัก ตัวอย่างคือ ..\model\Post.php ใส่ code[code language=”php”]
…
use omgdef\multilingual\MultilingualBehavior;
use omgdef\multilingual\MultilingualQuery;
…
public static function find()
{
$q = new MultilingualQuery(get_called_class());
$q->multilingual();
return $q;
}

public function behaviors()
{
return [
‘ml’ => [
‘class’ => MultilingualBehavior::className(),
‘languages’ => [
‘en’,
‘jp’,
‘th’,
],
‘defaultLanguage’ => ‘en’,
‘langForeignKey’ => ‘post_id’,
‘tableName’ => "{{%postlang}}",
‘attributes’ => [
‘content’,
‘title’,
]
]
];
}
…
[/code]
- languages ภาษาทั้งหมดที่ใช้ได้
- defaultLanguage ภาษาเริ่มต้น
- langForeignKey ใส่ฟิลย์ที่ เชื่อมทั้ง 2 ตารางไว้ด้วยกัน (primary key)
- tableName ชื่อตารางที่เก็บส่วนแปลภาษาไว้
- attributes ฟิลย์ทั้งหมด ที่ต้องการทำระบบแปลภาษา
เปิด ไฟล์ _form.php ที่เราใช้กับโมเดลนี้ขึ้นมา ใส่[code language=”php”]
…
<?= $form->field($model, ‘title_jp’)->textInput([‘maxlength’ => 255]) ?>
<?= $form->field($model, ‘title_en’)->textInput([‘maxlength’ => 255]) ?>
<?= $form->field($model, ‘title_th’)->textInput([‘maxlength’ => 255]) ?>
…
[/code]

ทดลอง save ดู ตารางใน database จะเก็บข้อมูลไว้

แก้จุดอื่นๆ อย่าง view index.php ตามรูปแบบ ฟิลย์_ตัวย่อภาษา

แก้เนื้อหาทั้งตาราง

content เขียนเนื่อหามาสวยงามในตัวเทส แต่เวลาขึ้นเว็บจริงภาพดันหายหมดเลย view code ดูใช้

[code language=”html”] <img src-"http://localhost/barbei/assets/service-Tour.jpg"> [/code]

งานเข้า มีหลายรายการไม่ใช่น้อยๆ แต่ยังดีที่มีรูปแบบการแก้ที่แน่นอนคือ เอา http://localhost/barbei/ ออกไป ทำได้ด้วยคำสั่ง sql ง่ายๆ ตามตัวอย่าง

[code language=”html”]
UPDATE MyTable
SET StringColumn = REPLACE (StringColumn, ‘SearchForThis’, ‘ReplaceWithThis’)
WHERE SomeOtherColumn LIKE ‘%PATTERN%’
[/code]

ผมเก็บเนื้อหาไว้ในตาราง contents ส่วนเนื้อหาอยูใน field contentBody จะเขียน ได้

[code language=”sql”]
UPDATE `contents`
SET `contentBody ` = REPLACE (contentBody, ‘ http://localhost/barbei/ ‘, ‘ ‘)
[/code]

เสร็จก่อนมาม่าสุกอีก เขียนด้วยความระมัดระวังนะครับ ของผมไม่มี where เพราะต้องการแทนที่ทั้งหมดแลยในครั้งเดียว ถ้าไม่แน่ใจทดลง select ข้อมูลก่อนดูว่าข้อมูลที่ออกมาทั้งหมดเป็นตัวที่จะแก้รึเปล่า

PlusMagi's Blog By Pitt Phunsanit

Tag Archive table