PHP แสดง Data Dictionary สำหรับ Mysql

พอดีต้องการทำ Data Dictionary แต่ server ไม่มี phpMyAdmin ก็เลยเขียน php แบบง่ายๆขึ้นมาเพื่อแสดง Data Dictionary เลยเอามาโพสไว้ในเว็บเผื่อใครต้องการครับ Download ที่นี่

Source code

header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment; filename="data_dictionary.xls"');#ชื่อไฟล์

/*----------------------
Config Database
-----------------------*/
$strCfgDbHost = "localhost";
$strCfgDbUser = "root";
$strCfgDbPass = "";
$strCfgDbName = "";

$objDb = mysql_connect($strCfgDbHost,$strCfgDbUser,$strCfgDbPass);
mysql_select_db($strCfgDbName);
if (!$objDb) { die('Could not connect: ' . mysql_error()); }
mysql_query("SET NAMES UTF8");
mysql_query("SET character_set_results=utf8");

$sql = "SHOW TABLES";
$rs = mysql_query($sql);
$i=0;
while($row = @mysql_fetch_array($rs)){
$tableArr[$i] = $row[0];
$i++;
}
@mysql_free_result($rs);

$html="";
foreach ($tableArr as $table) {
$sql="DESCRIBE $table;";
$rs = mysql_query($sql);
$html ="<h1>$table</h1>";
$html .="<br><br>";
$html .= "<TABLE border=\"1\" cellspacing=\"0\" cellpadding=\"4\" width=\"100%\">
<TR>
<TD><b>Field</b></TD>
<TD><b>Type</b></TD>
<TD><b>Null</b></TD>
<TD><b>Key</b></TD>
<TD><b>Default</b></TD>
<TD><b>Extra</b></TD>
</TR>
";

while($row = @mysql_fetch_array($rs)){
$html .= "
<TR>
<TD>$row[0]</TD>
<TD>$row[1]</TD>
<TD>$row[2]</TD>
<TD>$row[3]</TD>
<TD>$row[4]</TD>
<TD>$row[5]</TD>
</TR>
";
}
@mysql_free_result($rs);
$html .="</TABLE>";
echo $html;
}// Loop

คำสั่ง SQL Copy ข้อมูล Columnนึง ไปใส่อีก Columnนึง

UPDATE TableName SET FieldName1 = FieldName2

จาก sql ด้านบนคือ copy ข้อมูลใน FieldName2 ไปใส่ FieldName1

คำสั่ง Replace ของ MySQL

UPDATE  TableName  SET  FieldName = REPLACE( FieldName ,'คำที่ค้นหา','คำที่เอามาแทน' )

คิดว่ามีประโยชน์เมื่อถึงเวลาจำเป็น

ออกแบบ ER-Diagram ด้วย MySQL Workbench

Freeware ดีๆอีกตัวหลังจากที่เคยแนะนำ DBDesigner 4 ซึ่งดูแล้วก็มักจะ error บ่อยๆ ก็ลองไปใช้ดูกันนะคับ อย่างน้อยก็บั๊กน้อยกว่า DBDesigner ล่ะว๊า

Download ที่นี่จ้า : http://dev.mysql.com/downloads/workbench/5.1.html

คำสั่ง SQL Copy ข้อมูลจาก Table นึงไป Table นึง

เอาเป็นว่าสมมติมีโจทย์ที่ต้องการดึงข้อมูลจาก”ตาราง Customer” ไปเก็บใน “ตาราง CustomerTmp” ซึ่งเลือกเฉพาะฟิลด์ที่ต้องการ และเก็บวันที่บันทึกลงไปด้วย โดยให้เงื่อนไขว่า เงินเดือนต้องมากกว่า 20,000 บาท ก็จะได้คำสั่งตามด้านล่างนี้

INSERT INTO CustomerTmp (Name,Salary,DateAdd)  SELECT Name,Salary,NOW() FROM Customer WHERE Salary > 20000

รูปแบบก็คำสั่ง Insert ปกติ แต่สังเกตุจะไม่มี VALUES อยู่ด้วย และฝั่งข้อมูลเข้าก็จะใช้คำสั่ง SELECT ตารางกันตรงๆเลย เป็นอันเสร็จพิธีจ้า…

ตอนนี้อาจจะยังไม่มีประโยชน์ แต่ถ้าเจอโจทย์แนวนี้คงได้ใช้กันบ้างล่ะ ผมขี้ลืมเลยเอามาแป่ะไว้ดีกว่า

คำสั่ง SQL กำหนดเงื่อนไขเลือกจากช่วงเวลา คำนวณวันสิ้นสุดอัตโนมัติ

อธิบายไม่ถูกขอยก ตย.แล้วกันเช่น
ให้ข่าวหนึ่งข่าวมีอายุการแสดงได้ 3 เดือน โดยเริ่มนับจากวันที่บันทึก(ฟิลด์ชื่อ “AddDate”) ก็จะได้ sql ด้านล่างนี้

SELECT * FROM TbNews WHERE (NOW( ) >= AddDate AND
 NOW( ) < DATE_ADD(AddDate, INTERVAL 3 MONTH ))

แปล Sql ดังนี้
เลือกทุกฟิลด์จากตาราง TbNews โดย”วันปัจจุบัน”ต้องมากกว่าหรือเท่ากับ”วันที่บันทึก”
และ “วันปัจจุบัน” ต้องน้อยกว่า “วันที่บันทึกบวกเพิ่มอีก 3 เดือน”

สร้างรหัสสินค้า หรือหมายเลข ID ด้วย str_pad()

รหัสสินค้าส่วนใหญ่จะเป็นแบบมีการกำหนดหลักอาจจะ 5-6หลัก และอาจมีคำย่อนำหน้า เช่น

CP000045 ,CP000569 เป็นต้น

ซึ่งแน่นอนวิธีการออกแบบการเก็บข้อมูลเราก็ต้องใช้ฟิลด์นึงเก็บคำย่อ และอีกฟิลด์นึงเก็บหมายเลข ID ซึ่งหากข้อมูลออกมา โดยอิงจาก ตย. ด้านบนก็จะเป็น

CP45 ,CP569

สังเกตมั้ยครับว่าเลขศูนย์ข้างหน้ามันหายไป อ่า…เราจะทำอย่างไรให้มันแสดงผลครบล่ะ ผมขออุปมาว่ารหัสมี 6 หลักแล้วกันนะครับ

วิธีแก้แบบที่ 1 แก้ที่การออกแบบ database โดยเซ็ต Attributes ให้เป็น UNSIGNED ZEROFILL

CREATE TABLE `product` (

`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`PRODUCT_ID` INT( 6 ) UNSIGNED ZEROFILL NOT NULL

) ENGINE = MYISAM ;

วิธีแก้แบบที่ 1 ใช้ฟังก์ชั่น str_pad อันที่เกรี่นหัวไว้เน่าะ ^_^

$input=45;
echo str_pad($input,6, "0", STR_PAD_LEFT); // กำหนดให้เพิ่มเลขศูนย์จากทางซ้ายรวมกันแล้วครบ 6 หลัก
//จะได้เป็น 000045

จะว่าไป str_pad สามารถเพิ่มทางขวาหรือทั้งสอง้างก็ได้โดยการกำหนด pad_type ดังนี้
STR_PAD_RIGHT= เพิ่มจากขวา 450000
STR_PAD_LEFT=เพิ่มจากขวา 000045
STR_PAD_BOTH=เพิ่มทั้งสอง 004500

ออกแบบ Database ด้วย DBDesigner 4

เป็นฟรีแวร์ open source ตัวนึงที่ใช้ในงานออกแบบ Model ER-Diagram

หากลงแล้วไม่สามารถ Connect Mysql ได้ โดยขึ้นว่า

“connection to database failed, dbexpress error: invalidusername/password. “

สามารถแก้ได้โดยการ Reset password ใหม่ คำสั่งดังนี้

set password for root@localhost = old_password('yourpassword');

Download: http://www.fabforce.net/downloads.php

SQL ค้นหาตำแหน่งใดๆและระบุหลัก

ตัวอย่างต้องการค้นหาเลข ‘18′ ตำแหน่งที่ ‘3′ เปรียบเทียบ ‘2′ หลัก

SELECT * FROM TableName WHERE MID(Field, 3, 2 ) = '18'

SQL ให้ระบุค่าตำแหน่งของ Field ที่ต้องการ

ตัวอย่างเช่นหาค่า “1″ ในตำแหน่งที่ 2 ของfield

SELECT * FROM TableName WHERE  FieldName like '_1%'