Tag Archive row

mySql: แสดงข้อมูลหลายแถวไว้ในชุดเดียวคอลัมน์เดียว

ตอนกำลังทำข้อมูลทะเบียนลูกบ้านเพื่อที่จะได้ทำรายชื่อคนที่มีสิทธิที่จะออกเสียงในที่ประชุมนิติของหมู่บ้าน ดูๆ น่าจะไม่ยากแค่เขียน query ง่ายๆ มีเลขที่ห้อง ชื่อเจ้าของ จำนวนพื้นที่ แบบ

SELECT
    CONCAT(h.house_no, '/', h.house_no_sub) AS houseNo,
    CONCAT(p.title, ' ', p.name, ' ', p.surname) AS owner,
    h.squareMeter
FROM
    v1_house AS h
        LEFT JOIN
    v1_house_owner AS ho ON h.house_id = ho.house_id
        LEFT JOIN
    v1_peoples AS p ON ho.people_id = p.people_id
ORDER BY h.house_id ASC

ไม่นานก็ได้รายชื่อออกมาทั้ง 843 รายการ แต่จำได้ว่ามีห้องทั้งหมด 730 ห้องแล้วที่เกินมาคืออัลลัย ไล่ดูผลที่ออกมาบางห้องมีเจ้าของมากกว่า 1 คน เป็นเรื่องแล้วเพราะถึงมีเจ้าของกี่คนแต่จะใช้สิทธิได้แค่ห้องละ 1 เท่านั้น ดังนั้นสมมุติว่าห้องไหนมีเจ้าของ 2 คน (เจอว่าบางห้องมี 3 เจ้าของ) จะต้องเขียนให้แสดงรายชื่อในแถวเดียวกัน เช่น ‘1xx/xx’, ‘คุณ พิชญ์ พันธุ์สนิท, คุณ xxx พันธุ์สนิท, คุณ xxx พันธุ์สนิท’, ‘69.58’ แทนที่จะอยู่คนและแถว คนและ row

ตอนแรกจะเขียนแบบใช้ sub query แต่รู้สึกว่าซับซ้อนเกินไปหน่อย ดีที่ไปเห็น GROUP_CONCAT() ลองเขียนดูง่ายกว่าใช้ซับคิวรี่เยอะ

SELECT
    CONCAT(h.house_no, '/', h.house_no_sub) AS houseNo,
    GROUP_CONCAT(CONCAT(p.title, ' ', p.name, ' ', p.surname),
        ' ') AS owner,
    h.squareMeter
FROM
    v1_house AS h
        LEFT JOIN
    v1_house_owner AS ho ON h.house_id = ho.house_id
        LEFT JOIN
    v1_peoples AS p ON ho.people_id = p.people_id
GROUP BY h.house_id
ORDER BY h.house_id ASC , ho.people_id ASC

ข้อมูลออกมาถูกต้องแล้ว แบบเขียนไม่ยากด้วย ^_^

PHPWord: กรอกข้อมูลใส่ template

เพราะว่า word นั้นมีความสามารถปรับแต่งรูปแบบได้ซับซ้อนมากกว่า excel อยู่มาก อย่างน้อยแค่ตำแหน่งของส่วนต่างๆ ในแบบฟอร์มก็มีมากกว่า excel ที่เลือกตำแหน่งได้แค่ว่าจะใส่ column / row ไหน

การใช้ PHP สร้างไฟล์ word ขึ้นมาการสร้างไฟล์แม่แบบขึ้นก่อน โดยการสร้างตัว word template ขึ้นมาก่อนโดยใช้โปรแกรม Microsoft Word สร้างเอกสารไปตามปกติ จึงง่ายกว่า จากนั้นก็แก้เพียงแต่ในส่วนที่่ต้องการจะแทนค่าให้ใส่ ${key name} ลงไป (ระวังเรื่องช่องว่างด้วยนะครับ) หรือส่วนที่ต้องการ repeat ก็เพิ่ม ${key name} … ${/key name} เข้าไปเท่านั้นจะง่ายกว่า ไฟล์ในตัวอย่างนี้สามารถโหลดได้จาก template.docx

จากนั้นก็เขียน code

<?php
ini_set('max_execution_time', 0);
ini_set('memory_limit', '-1');

include '../vendor/autoload.php';

use PhpOffice\PhpWord;

$title = 'Template Render ' . date('Y-m-d H:i:s');

/* load template */
$templateProcessor = new \PhpOffice\PhpWord\TemplateProcessor('template.docx');

/* fill data */
$templateProcessor->setValue('header', $title);

/* random data */
$scores = [];
for ($a = 0; $a < 10; $a++) {
    $scores[$a + 1] = rand(0, 100);
}

/* clone table row and fill */
$templateProcessor->cloneRow('student_id', count($scores));
foreach ($scores as $key => $value) {
    $templateProcessor->setValue('score#' . $key, htmlspecialchars($value, ENT_COMPAT, 'UTF-8'));
    $templateProcessor->setValue('student_id#' . $key, htmlspecialchars($key, ENT_COMPAT, 'UTF-8'));
}

/* clone all block */
$templateProcessor->cloneBlock('repeater', 5);

header("Content-Description: File Transfer");
header('Content-Disposition: attachment; filename="' . $title . '.docx"');
header('Content-Type: application/vnd.openxmlformats-officedocument.wordprocessingml.document');
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Expires: 0');
$templateProcessor->saveAs('php://output');

อธิบาย code

  • การทำงานโดยจะโหลด ตัว PHPWord มาใช้ก่อน จะสังเกตุว่าจะมีการใช้ namespaces แทนที่จะใช้ include เข้ามาเหมือน PHPExcel
  • กำหนดจุดที่จะกรอกข้อมูลโดยใส่ ${key name} ใน word เช่น ${header} จากนั้นใน PHP ก็ส่งค่าตัวแปรไปแทนที่โดยใช้
    $templateProcessor->setValue('header', $title);
  • สามารถ repeat ตารางได้โดยการใส่ ${key name} ใน column ที่ต้องการคัดลอกแถวทั้งแถว เช่น ${student_id} และสั่งให้คัดลอกแถวโดยใช้
    $templateProcessor->cloneRow('student_id', จำนวนครั้ง);

    แถวในตารางที่ใส่ ${student_id} จะเพิ่มขึ้นมาทั้งแถวรวมทั้งคอลัมน์อื่นด้วย

  • สามารถเติิมช้อมูลให้คอลัมน์ที่เพิ่มมาใหม่นี้โดยใช้
    foreach ($scores as $key => $value) {
        $templateProcessor->setValue('score#' . $key, htmlspecialchars($value, ENT_COMPAT, 'UTF-8'));
        $templateProcessor->setValue('student_id#' . $key, htmlspecialchars($key, ENT_COMPAT, 'UTF-8'));
    }

    สังเกตุว่าจะมี

    #' . $key

    เพิ่มขึ้นมาเพิื่อจะแทนที่ลำดับที่เพิ่มขึ้นมานั่นเอง

  • ในรูปแบบตารางที่ซับซ้อนเช่น ตาราง “ใบขออนุญาตสอบซ่อม” ที่ต้องทำซ้ำหลายแถวและหลายคอลัมน์พร้อมๆกัน ทั้งตารางจะไม่สามารถใช้
    $templateProcessor->cloneRow('student_id', จำนวนครั้ง);

    ได้ ต้องเปลี่ยนไปใช้

    $templateProcessor->cloneBlock('repeater', จำนวนครั้ง);

    แทน แต่จะไม่สามารถใส่ตัวแปรลงไปแทนที่ได้ใน version นี้ (PHPWord version 0.13.0)

PHPExcel: จัดรูปแบบ format ข้อมูล

เมื่อวานเขียน export ข้อมูลออกเป็นไฟล์ excel โดยใช้เวลาไม่นาน เพราะโครงสร้างการทำงานมันเหมือนๆ งานที่เคยทำมา แต่มาตกม้าตายเอาที่การฟอร์เมตของแต่ละ column ให้ตรงกับชนิดข้อมูล เช่น type เป็น date ก็ควรให้เห็นเป็นวันที่ไม่ใช่เลข 1234155 อะไรก็ไม่ทราบ

<?php

include '../vendor/phpoffice/phpexcel/Classes/PHPExcel.php';

set_time_limit(0);

$objPHPExcel = new PHPExcel();

/* Set default style */
$defaultStyle = $objPHPExcel->getDefaultStyle();

$defaultStyle->getFont()
    ->setName('Arial')
    ->setSize(11);

$defaultStyle->getNumberFormat()
    ->setFormatCode('yyyy-mm-dd');

/* Set document properties */
$title = 'columnsType_' . date('Y-m-d_H:i');
$objPHPExcel->getProperties()->setCreator('CMS')
    ->setCategory('Exports Datas')
    ->setDescription($title)
    ->setKeywords('Exports Datas ' . date('Y-m-d'))
    ->setSubject($title)
    ->setTitle($title);

/* create new sheet */
$objWorkSheet = $objPHPExcel->getActiveSheet();
$objWorkSheet->setTitle('Exports Datas');

$columns = [
    'row_number' => ['title' => 'No.', 'type' => 'row_number'],

    'price' => ['title' => 'ราคา', 'type' => 'currency'],

    'dateEnd' => ['title' => 'เริ่มจำหน่าย', 'type' => 'date'],
    'dateStart' => ['title' => 'เริ่มจำหน่าย', 'type' => 'date'],

    'dateApproved' => ['title' => 'เวลาอนุมัติ', 'type' => 'datetime'],

    'height' => ['title' => 'สูง (เมตร)', 'type' => 'float'],
    'width' => ['title' => 'กว้าง (เมตร)', 'type' => 'float'],

    'calculate' => ['title' => 'สูตรคํานวณหวย', 'type' => 'formula'],

    'image' => ['title' => 'ภาพ', 'type' => 'image'],

    'items' => ['title' => 'จำนวน', 'type' => 'integer'],

    'productName' => ['title' => 'ชื่อสินค้า', 'type' => 'string'],

    'timeEnd' => ['title' => 'เวลาขาย', 'type' => 'time'],
    'timeStart' => ['title' => 'เวลาปิดการขาย', 'type' => 'time'],

    'url' => ['title' => 'page', 'type' => 'url'],
];

/* header */
$colNo = -1;
$rowNo = 1;
$colStrings = [];
foreach ($columns as $fieldId => $field) {
    $colNo++;
    $colStrings[$colNo] = $colString = PHPExcel_Cell::stringFromColumnIndex($colNo);
    $objWorkSheet->setCellValue($colString . '1', $field['title']);
    $objWorkSheet->setCellValue($colString . '2', 'type = ' . $field['type']);
}
$headerHeight = $rowNo = 2;

$objPHPExcel->getActiveSheet()->freezePane($colString . ($headerHeight + 1));

/* random data */
$datas = [];
for ($a = 0; $a < 10; $a++) {
    $temp = [];

    $temp['calculate'] = '=RAND()';
    $temp['dateApproved'] = date(DATE_ISO8601, mt_rand(0, 1499291999));
    $temp['dateEnd'] = date('Y-m-d', mt_rand(0, 1499291999));
    $temp['dateStart'] = date('Y-m-d', mt_rand(0, 1499291999));
    $temp['height'] = mt_rand(0, 10) / 10;
    $temp['image'] = 'http://lorempixel.com/400/200/sports/?st=' . mt_rand(1, 500);
    $temp['items'] = mt_rand(999, 9999999);
    $temp['price'] = mt_rand(100, 10000);
    $temp['productName'] = substr(str_shuffle(str_repeat($x = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil(10 / strlen($x)))), 1, 10);
    $temp['timeEnd'] = date('H:i:s', mt_rand(0, 1499291999));
    $temp['timeStart'] = date('H:i:s', mt_rand(0, 1499291999));
    $temp['url'] = 'https://plusmagi.com/?s=' . mt_rand(1, 500);
    $temp['width'] = mt_rand(0, 10) / 10;

    array_push($datas, $temp);
}

/* add data */
$row_number = 0;
foreach ($datas as $item) {
    $colNo = -1;
    $row_number++;
    $rowNo++;
    foreach ($columns as $fieldId => $field) {
        $colNo++;

        $coordinate = $colStrings[$colNo] . $rowNo;

        switch ($field['type']) {
            case 'date':
            case 'datetime':
            case 'time':{
                    $ts = strtotime($item[$fieldId]);
                    $value = PHPExcel_Shared_Date::PHPToExcel($ts);
                }break;

            case 'image':{
                    $value = $item[$fieldId];

                    $gdImage = imagecreatefromjpeg($value);
                    $objDrawing = new PHPExcel_Worksheet_MemoryDrawing(); /*create object for Worksheet drawing*/

                    $objDrawing->setCoordinates($coordinate); /*set image to cell*/
                    $objDrawing->setDescription('Customer Signature'); /*set description to image*/
                    $objDrawing->setHeight(50);
                    $objDrawing->setImageResource($gdImage);
                    $objDrawing->setName('Customer Signature'); /*set name to image*/
                    $objDrawing->setOffsetX(25); /*setOffsetX works properly*/
                    $objDrawing->setOffsetY(10); /*setOffsetY works properly*/
                    $objDrawing->setWidth(100); /*set width, height*/

                    $objDrawing->setWorksheet($objWorkSheet); /*save*/

                    $objWorkSheet->getRowDimension($rowNo)->setRowHeight(60); /* set row height*/
                }break;

            case 'row_number':{
                    $value = $row_number;
                }break;

            case 'url':{
                    $value = str_replace('http://', '', $item[$fieldId]);
                    $objWorkSheet->getCell($coordinate)
                        ->getHyperlink()
                        ->setTooltip('Click here to access page')
                        ->setUrl($item[$fieldId]);
                }break;

            default:{
                    $value = $item[$fieldId];
                }break;
        }

        $objWorkSheet->setCellValue($coordinate, $value);
    }
}

/* auto width column */
$cellIterator = $objWorkSheet->getRowIterator()->current()->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(true);
foreach ($cellIterator as $cell) {
    $objWorkSheet->getColumnDimension($cell->getColumn())->setAutoSize(true);
}

/* format for columns */
$colNo = -1;
foreach ($columns as $fieldId => $field) {
    $colNo++;

    $coordinate = $colStrings[$colNo] . ($headerHeight + 1) . ':' . $colStrings[$colNo] . $rowNo;

    switch ($field['type']) {

        case 'currency':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode('#,##0.00');
                /*->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);*/
            }break;

        case 'date':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DMYSLASH);
            }break;

        case 'datetime':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME);
                /*->setFormatCode('Y-m-d H:i:s');*/
            }break;

        case 'float':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
            }break;

        case 'integer':
        case 'row_number':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode('#,##');
            }break;

        case 'time':{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
                /*->setFormatCode('Y-m-d H:i:s');*/
            }break;

        default:{
                $objWorkSheet->getStyle($coordinate)
                    ->getNumberFormat()
                    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
            }break;
    }

}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
header('Cache-Control: max-age=0');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');
$objWriter->save('php://output');

หลักการต้องทำงานคู่กัน 2 ส่วนคือ

  1. ส่วนใส่ข้อมูล บางชนิดต้องแปลงข้อมูลก่อน เช่น date, datetime, timestamp และ time ต้องเปลี่ยนเป็น unix timestamp ก่อน
  2. ส่วนกำหนด cell format (ในตัวอย่างให้วิธีกำหนดทั้ง column ไปเลย) ต้องเลือกรูปแบบที่เหมาะสมโดยจะกำหนดเอง
    ->setFormatCode('Y-m-d H:i:s');

    หรือจะใช้ตามมาตราฐานก้ได้ Class PHPExcel_Style_NumberFormat ก็ได้

อ่านข้อมูลจาก excel

ตัวอย่างการอ่านข้อมูลใน excel โดยใช้ PHPExcel

<?php

/* PHPExcel_IOFactory - Reader */
include 'PHPOffice/PHPExcel/Classes/PHPExcel/IOFactory.php';

$inputFileName = 'importsDatas.xlsx';

$inputFileType = PHPExcel_IOFactory::identify($inputFileName);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($inputFileName);

/* select sheet */
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

/* range of datas */
$highestColumn = $objWorksheet->getHighestColumn();

$highestColumnNumber = PHPExcel_Cell::columnIndexFromString($highestColumn);

$highestRow = $objWorksheet->getHighestRow();

echo '<!doctype html>',
'<html>',
'<head>',
'<meta charset="utf-8">',
'<title>PHPExcel Reader</title>',
'<body>',
'<table border="1" width="100%"><caption>Highest Row = ', $highestRow, ' Highest Column = ', $highestColumn, ' (', $highestColumnNumber, ')</caption><tbody>';

for ($rowNo = 1; $rowNo <= $highestRow; $rowNo++) {
    echo '<tr>';
    for ($colNo = 0; $colNo < $highestColumnNumber; $colNo++) {

        $colString = PHPExcel_Cell::stringFromColumnIndex($colNo);

        $coordinate = $colString . $rowNo;

        $cell = $objWorksheet->getCell($coordinate);

        $value = trim($cell->getValue());

        echo '<td>', $value, ' <sup>', $coordinate, '</sup></td>';
    }
    echo '</tr>';
}
echo '</tbody></table></body></html>';

พื้นฐานคือ

  1. บรรทัด 8 – 13 ให้ PHPExcel อ่านจากไฟล์ไหน เป็น excel version ไหน setReadDataOnly บอกว่าให้เปิดเพืออ่านข้อมูลอย่างเดียวนะ มันจะเร็วกว่าเพราะไม่ต้องเตรียมการเพื่อเขียนข้อมูลด้วย
  2. setActiveSheetIndex จะบอกว่าให้อ่าน sheet ไหน โดยเริ่มนับจาก 0 ไม่ใช่ 1
  3. บรรทัด 19 – 23 เป็นการหาขอบเขตที่มีการเขียนข้อมูลเอาไว้ เพราะว่าไม่ต้องการให้ loop ไปอ่านเรื่อยๆโดยที่ไม่มีข้อมูลอะไรเลย เปลืองทรัพยากร เสียเวลาไปเปล่าๆ
  4. บรรทัด 33 – 35 เป็นการ loop อ่านข้อมูล มาสร้างเป็น coordinate อย่าง G1, G2, G3, …, G7 แล้วแต่ว่าจะเขียนเงื่อนไขให้เริ่มอ่านจากจุดไหน เพราะบางไฟล์แถวแรกๆ จะเป็น header ไม่ใช่ข้อมูล สาเหตุที่ต้องใช้ columnIndexFromString ทั้งๆที่มี getHighestColumn แล้ว เพราะว่าต้องการลำดับ column เอาไป loop จะใช้ตัวอักษรไป loop ไม่ได้
  5. บรรทัด 41 ใช้ getCell ดึงข้อมูลออกมาใช้ทีละ coordinate

อ่านเพิ่มเติม Import วันที่โดย PHPExcel

update ข้อมูลใน MySQL แบบหลายตาราง

กำลังทำ presashop ตัวใหม่อยู่ จำเป็นต้องใส่ต้อง copy feature จากภาษาหนึ่งไปอีกภาษาหนึ่งสำหรับ product 1,600 รายการ ถ้าเลือกใส่ใน backend ปกติถึงทำกัน 3 คนมือก็หงิกอยู่ดี (ยังไม่ต้องคิดว่าถ้าทำพลาดละ) และเพราะ prestashop เก็บข้อมูลโดยแยกแต่ละภาษา ออกไปอีกตารางจึ้งต้องเขียน sub query ตามแบบ
SQL Update column values using subquery
query ของผมเป็น

UPDATE `feature_value_lang` AS target INNER JOIN (
    SELECT `id_feature_value` ,`value`FROM `feature_value_lang`
    WHERE `id_lang` = 1
) AS source ON target.`id_feature_value` = source.`id_feature_value`
AND  target.`id_lang` = 3
SET target.`value` = source.`value`

copy ข้อมูลใน MySQL

บางครั้งต้องการข้อมูลจำนวนมาก เพื่อ test บางอย่าง เช่น ระบบแบ่งหน้า โชคดีที่มายเอสคิวแอลสามารถ copy มาจาก record อื่นๆได้ เช่น

INSERT INTO tableName (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM tableName
  WHERE primarykey = 1

สมมุติจะคัดลอกตาราง `cds` ในดาต้าเบส cdcol ที่เป็นตัวอย่างติด xampp มา ถ้าไม่มีสร้างใหม่ได้จาก query

CREATE TABLE IF NOT EXISTS `cds` (
  `titel` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
  `jahr` int(11) DEFAULT NULL,
`id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

INSERT INTO `cds` (`titel`, `interpret`, `jahr`, `id`) VALUES
('Beauty', 'Ryuichi Sakamoto', 1990, 1),
('Goodbye Country (Hello Nightclub)', 'Groove Armada', 2001, 4),
('Glee', 'Bran Van 3000', 1997, 5);

ALTER TABLE `cds`
 ADD PRIMARY KEY (`id`);

เราจะ copy เพิ่มโดยคิวรี่

INSERT INTO `cds`(`titel`, `interpret`, `jahr`)
SELECT `titel`, `interpret`, `jahr`
FROM `cds`
WHERE `id` = 5

สังเกตุ จะไม่เลือก ฟิลด์ id เพราะ field มันเป็น primary key หลังจากรันคิวรี่จะเห็นว่าข้อมูลเพิ่มขึ้นมาใหม่เหมือนข้อมูลใน id = 5 ทุกอย่างยกเว้น id