<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
</head>
<body>
<?php
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', 'Admin');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'test');
$conn = mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
mysqli_set_charset($conn, 'utf8');
if(isset($_GET['id'])){
$sql = "select *, date_format(bDate,'%d/%m/%Y') as NBDATE, format(salary,'th_TH') as NSALARY from employee where ssn='{$_GET['id']}'";
echo $sql;
$result = mysqli_query($conn,$sql) or die(mysqli_error($conn));
$dataLoad = mysqli_fetch_array($result);
}
$error=array();
if(isset($_POST['add'])){
$fname = $_POST['firstName'];
if(preg_match('/^[A-Z]{2,15}$/',$fname)==0){
$error[] = 'ค่าข้อมูลชื่อ รูปแบบสตริงไม่ถูกต้องตามรูปแบบ ตัวอักษรภาษาอังกฤษตัวพิมพ์ใหญ่เท่านั้น และอย่างน้อยต้องมี 2 ตัวอักษร';
}
$midname = $_POST['midName'];
if(preg_match('/^[A-Z]*$/',$midname)==0){
$error[] = 'ค่าข้อมูลชื่อกลาง รูปแบบสตริงไม่ถูกต้องตามรูปแบบ ตัวอักษรภาษาอังกฤษตัวพิมพ์ใหญ่เท่านั้น';
}
$lname = $_POST['surName'];
if(preg_match('/^[A-Z]{2,15}$/',$lname)==0){
$error[] = 'ค่าข้อมูลชื่อสกุล รูปแบบสตริงไม่ถูกต้องตามรูปแบบ ตัวอักษรภาษาอังกฤษตัวพิมพ์ใหญ่เท่านั้น และอย่างน้อยต้องมี 2 ตัวอักษร';
}
$birthday = $_POST['birthday'];
if(preg_match('/^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$/',$birthday)==0){
$error[] = 'ค่าข้อมูลวันเกิด รูปแบบวันที่ไม่ถูกต้องตามรูปแบบ วัน/เดือน/ปี';
}
$address = $_POST['address'];
if(preg_match('/^[1-9][0-9]{0,3}(\, [A-Z0-9]+){2}\, [A-Z]{2}$/',$address)==0){
$error[] = 'ค่าข้อมูลที่อยู่ รูปแบบสตรงไม่ถูกต้องตามรูปแบบ เลขที่บ้าน ชื่อแขวง, ชื่อเมือง, ชื่อรหัส 2 ตัวอักษร';
}
$salary = $_POST['salary'];
if(preg_match('/^[1-9][0-9]{0,2}?(\,[0-9]{3})*(\.[0-9]{2})?$/',$salary)==0){
$error[] = 'ค่าข้อมูลเงินเดือน รูปแบบเลขไม่ถูกต้องตามรูปแบบ xxx,xxx,xxx.xx';
}
if(count($error)>0){
$message = '<ul>เกืดข้อผิดพลาด';
foreach($error as $e)
$message .= "<li>$e</li>";
$message .= '</ul>';
echo $message;
}else{
$sql = 'select max(ssn)+1 as nextssn from employee';
$result = mysqli_query($conn,$sql);
$array = mysqli_fetch_array($result);
if($array['nextssn']==null){
$newssn = '000000001';
}else{
$newssn = sprintf('%09s',$array['nextssn']);
}
$optionsAttribute ='';
$optionsValue ='';
if(!empty($_POST['midName'])){
$optionsAttribute .= ', MINIT';
$optionsValue .= ", '{$_POST['midName']}'";
}
if(!empty($_POST['birthday'])){
$optionsAttribute .= ', BDATE';
$value = explode('/', $_POST['birthday']);
$newDate = "$value[2]-$value[1]-$value[0]";
$optionsValue .= ", '$newDate'";
}
if(!empty($_POST['address'])){
$optionsAttribute .= ', ADDRESS';
$optionsValue .= ", '{$_POST['address']}'";
}
if(!empty($_POST['sex'])){
$optionsAttribute .= ', SEX';
$optionsValue .= ", '{$_POST['sex']}'";
}
if(!empty($_POST['salary'])){
$optionsAttribute .= ', SALARY';
$salary = $_POST['salary'];
$newSalary = str_replace(',','',$salary);
$optionsValue .= ", $newSalary";
}
if(!empty($_POST['chief'])){
$optionsAttribute .= ', SUPERSSN';
$optionsValue .= ", '{$_POST['chief']}'";
}
$sql = "insert into employee (SSN, FNAME, LNAME, DNO $optionsAttribute) values ('$newssn','{$_POST['firstName']}','{$_POST['surName']}',{$_POST['department']} $optionsValue)";
echo $sql;
mysqli_query($conn,$sql) or die(mysqli_error($conn));
}
}else if(isset($_POST['update'])){
$optionsAttribute ='';
$optionsValue ='';
if(!empty($_POST['midName'])){
$optionsAttribute .= ", MINIT = '{$_POST['midName']}'";
}else{
$optionsAttribute .= ", MINIT = NULL";
}
if(!empty($_POST['birthday'])){
$value = explode('/', $_POST['birthday']);
$newDate = "$value[2]-$value[1]-$value[0]";
$optionsAttribute .= ", BDATE ='$newDate'";
}else{
$optionsAttribute .= ", BDATE =NULL";
}
if(!empty($_POST['address'])){
$optionsAttribute .= ", ADDRESS= '{$_POST['address']}'";
}else{
$optionsAttribute .= ", ADDRESS= NULL";
}
if(!empty($_POST['sex'])){
$optionsAttribute .= ", SEX='{$_POST['sex']}'";
}else{
$optionsAttribute .= ", SEX= NULL";
}
if(!empty($_POST['salary'])){
$salary = $_POST['salary'];
$newSalary = str_replace(',','',$salary);
$optionsAttribute .= ", SALARY= $newSalary";
}else{
$optionsAttribute .= ", SALARY= NULL";
}
if(!empty($_POST['chief'])){
$optionsAttribute .= ", SUPERSSN = '{$_POST['chief']}'";
}else{
$optionsAttribute .= ", SUPERSSN = NULL";
}
$sql = "update employee set FNAME='{$_POST['firstName']}', LNAME='{$_POST['surName']}', DNO={$_POST['department']} $optionsAttribute where ssn='{$_POST['ssn']}'";
echo $sql;
mysqli_query($conn,$sql) or die(mysqli_error($conn));
}else if(isset($_POST['delete'])){
$sql = "delete from employee where ssn='{$_POST['ssn']}'";
echo $sql;
mysqli_query($conn,$sql) or die(mysqli_error($conn));
}
$sql = 'SELECT a.*,FORMAT(a.salary,0) AS NSALARY ,DATE_FORMAT(a.bdate,\'%d/%m/%Y\') AS NBDATE ,CASE a.sex WHEN \'F\' THEN \'FEMALE\' WHEN \'M\' THEN \'MALE\' END AS NSEX, b.FNAME AS \'chief name\', c.DNAME FROM employee a LEFT OUTER JOIN employee b ON (a.superssn = b.ssn), department c WHERE a.dno = c.dnumber';
$result = mysqli_query($conn, $sql) or die($sql);
$html = '<table border=1>';
$html .= '<tr><th>รหัสพนักงาน</th><th>ชื่อ</th><th>ชื่อกลาง</th><th>นามสกุล<th>วันเกิด</th><th>ที่อยู่ </th><th>เพศ</th><th>เงินเดือน</th><th>หัวหน้าสายงาน</th><th>แผนก</th><th>แก้ไข</th></tr>';
while($array=mysqli_fetch_array($result)){
$html = $html ."<tr><td>{$array['SSN']}</td>";
$html = $html ."<td>{$array['FNAME']}</td>";
$html = $html ."<td>{$array['MINIT']}</td>";
$html = $html ."<td>{$array['LNAME']}</td>";
// $html = $html ."<td>{$array['BDATE']}</td>";
$html = $html ."<td>{$array['NBDATE']}</td>";
$html = $html ."<td>{$array['ADDRESS']}</td>";
// $html = $html ."<td>{$array['SEX']}</td>";
$html = $html ."<td>{$array['NSEX']}</td>";
// $html = $html ."<td>{$array['SALARY']}</td>";
$html = $html ."<td>{$array['NSALARY']}</td>";
$html = $html ."<td>{$array['chief name']}</td>";
// $html = $html ."<td>{$array['SUPERSSN']}</td>";
// $html = $html ."<td>{$array['DNO']}</td>";
$html = $html ."<td>{$array['DNAME']}</td>";
$html = $html ."<td><a href=\"?id={$array['SSN']}\">แก้ไข</a></td></tr>";
}
$html = $html . '</table>';
if(mysqli_num_rows($result)>0)
echo $html;
?>
<form action="form.php" method="POST" enctype="multipart/form-data">
<table>
<tr><td>รหัสพนักงาน</td><td><input type="hidden" name="ssn" maxlength="9" value='<?php if(isset($dataLoad)) echo $dataLoad['SSN'];?>'/><?php if(isset($dataLoad)) echo $dataLoad['SSN'];?> </td></tr>
<tr><td>ชื่อ *</td><td><input type="text" name="firstName" maxlength="15" value='<?php if(isset($dataLoad)) echo $dataLoad['FNAME'];?>'/> </td></tr>
<tr><td>ชื่อกลาง </td><td><input type="text" name="midName" maxlength="1" value='<?php if(isset($dataLoad)) echo $dataLoad['MINIT'];?>'/></td></tr>
<tr><td>นามสกุล *</td><td><input type="text" name="surName" maxlength="15" value='<?php if(isset($dataLoad)) echo $dataLoad['LNAME'];?>'/></td></tr>
<tr><td>วันเกิด </td><td><input type="text" name="birthday" value='<?php if(isset($dataLoad)) echo $dataLoad['NBDATE'];?>'/></td></tr>
<tr><td>ที่อยู่ </td><td><textarea name="address" maxlength="30"><?php if(isset($dataLoad)) echo $dataLoad['ADDRESS'];?></textarea></td></tr>
<tr><td>เพศ </td><td> <input type="radio" name="sex" value="M" <?php if(isset($dataLoad) && $dataLoad['SEX']=='M') echo 'checked';?>/> ชาย, <input type="radio" name="sex" value="F" <?php if(isset($dataLoad) && $dataLoad['SEX']=='F') echo 'checked';?>/> หญิง, <input type="radio" name="sex" value="" <?php if(isset($dataLoad) && $dataLoad['SEX']==null) echo 'checked';?>/> ไม่ระบุ</td></tr>
<tr><td>เงินเดือน </td><td><input type="text" name="salary" maxlength="10" value='<?php if(isset($dataLoad)) echo $dataLoad['NSALARY'];?>'/></td></tr>
<tr><td>หัวหน้างาน </td>
<td>
<?php
$sql = 'select * from employee';
$result = mysqli_query($conn, $sql);
$html = '<select name="chief"><option value="">ไมระบ
</option>';
while($array=mysqli_fetch_array($result)){
if(isset($dataLoad) && $dataLoad['SUPERSSN'] == $array['SSN'])
$select = 'selected';
else
$select = '';
$html .= "<option value=\"{$array['SSN']}\" $select>{$array['FNAME']}</option>";
}
$html .= '</select>';
echo $html;
?>
<td>
</td></tr>
<tr>
<td>แผนก *</td>
<td>
<?php
$sql = 'select * from department';
$result = mysqli_query($conn, $sql);
$html = '<select name="department">';
while($array=mysqli_fetch_array($result)){
if(isset($dataLoad) && $dataLoad['DNO']==$array['DNUMBER'])
$select = 'selected';
else
$select = '';
$html .= "<option value=\"{$array['DNUMBER']}\" $select>{$array['DNAME']}</option>";
}
$html .= '</select>';
echo $html;
?>
<tr><td><input type="file" name="file"/></td></tr>
<tr><td colspan="2"><input type="submit" name="add" value="เพิ่ม"/><input type="submit" name="update"
value="แก้ไข"/><input type="submit" name="delete" value="ลบ"/><input type="submit" value="ยกเลืก"/></td></tr>
</table>
<?php
mysqli_close($conn);
?>
</form>
</body>
</html>
** ผมแค่ต้องการให้รูปเข้าไปอยู่ใน folder ที่ผมกำหนดไว้ก็พออะครับ ไม่ต้องเอามาโชว์หน้าเว็บ **