*/; if($dbNo==1){ echo " create trigger smd_trg1 AFTER INSERT ON rg_RegistDetails for each row begin INSERT INTO T_registDetails values (new.rdAcY, new.rdStdId, new.rdTmId, new.rdCoId, new.rdSeq, new.rdCreditAttempt, new.rdSection, new.rdGrade, new.rdDocNo, new.rdCredit1, new.rdCredit2, new.rdNoCalGPA, new.rdAllowPr, new.rdCdId1, new.rdCdId2, new.rdUpdateDate, new.rdUpdateUserId); end$$ create trigger smd_trg2 AFTER UPDATE ON rg_RegistDetails for each row begin INSERT INTO T_registDetails values (old.rdAcY, old.rdStdId, old.rdTmId, old.rdCoId, old.rdSeq, old.rdCreditAttempt, old.rdSection, old.rdGrade, old.rdDocNo, old.rdCredit1, old.rdCredit2, old.rdNoCalGPA, old.rdAllowPr, old.rdCdId1, old.rdCdId2, old.rdUpdateDate, old.rdUpdateUserId); end$$ create trigger smd_trg3 AFTER DELETE ON rg_RegistDetails for each row begin INSERT INTO T_registDetails values (old.rdAcY, old.rdStdId, old.rdTmId, old.rdCoId, old.rdSeq, old.rdCreditAttempt, old.rdSection, old.rdGrade, old.rdDocNo, old.rdCredit1, old.rdCredit2, old.rdNoCalGPA, old.rdAllowPr, old.rdCdId1, old.rdCdId2, old.rdUpdateDate, old.rdUpdateUserId); end$$ create trigger smd_trg4 AFTER INSERT ON rg_StudentSummary for each row begin INSERT INTO T_studentSummary values (new.ssmStdId, new.ssmTmId, new.ssmAcY, new.ssmGPA, new.ssmCreditAttempt, new.ssmCreditSatisfy, new.ssmCreditPoint, new.ssmGPAX, new.ssmSumCreditAttempt, new.ssmSumCreditSatisfy, new.ssmSumCreditPoint, new.ssmApprover, new.ssmRefNo, new.ssmApproveDate, new.ssmPassStatus, new.ssmSstId, new.ssmSstIdApproveSpe, new.ssmSyId, new.ssmCreateDate, new.ssmCreateUserId, new.ssmUpdateDate, new.ssmUpdateUserId); end$$ create trigger smd_trg5 AFTER UPDATE ON rg_StudentSummary for each row begin INSERT INTO T_studentSummary values (old.ssmStdId, old.ssmTmId, old.ssmAcY, old.ssmGPA, old.ssmCreditAttempt, old.ssmCreditSatisfy, old.ssmCreditPoint, old.ssmGPAX, old.ssmSumCreditAttempt, old.ssmSumCreditSatisfy, old.ssmSumCreditPoint, old.ssmApprover, old.ssmRefNo, old.ssmApproveDate, old.ssmPassStatus, old.ssmSstId, old.ssmSstIdApproveSpe, old.ssmSyId, old.ssmCreateDate, old.ssmCreateUserId, old.ssmUpdateDate, old.ssmUpdateUserId); end$$ create trigger smd_trg6 AFTER DELETE ON rg_StudentSummary for each row begin INSERT INTO T_studentSummary values (old.ssmStdId, old.ssmTmId, old.ssmAcY, old.ssmGPA, old.ssmCreditAttempt, old.ssmCreditSatisfy, old.ssmCreditPoint, old.ssmGPAX, old.ssmSumCreditAttempt, old.ssmSumCreditSatisfy, old.ssmSumCreditPoint, old.ssmApprover, old.ssmRefNo, old.ssmApproveDate, old.ssmPassStatus, old.ssmSstId, old.ssmSstIdApproveSpe, old.ssmSyId, old.ssmCreateDate, old.ssmCreateUserId, old.ssmUpdateDate, old.ssmUpdateUserId); end$$ "; echo ' '; echo 'ใส่ค่า Delimiter = $$'; echo ' '; echo '-- คำสั่งดู TRIGGER '; echo ' '; echo 'show triggers; '; echo ' '; echo '-- คำสั่งลบ TRIGGER '; echo ' '; echo 'drop trigger <ชื่อ trigger>; '; }elseif($dbNo==2){ echo "SELECT studentId, count( passStatus ) passStatus FROM $dbreg.StudentStatus WHERE passStatus IN (1,2,3) GROUP BY studentId HAVING count( passStatus ) >3"; echo ' '; echo "SELECT ssmStdId, count( ssmPassStatus ) passStatus FROM $dbmoreg.rg_StudentSummary WHERE ssmPassStatus IN (1,2,3) GROUP BY ssmStdId HAVING count( ssmPassStatus ) >3"; echo " นำ ssmStdId ค้นหาในตาราง $dbmoreg.rg_StudentSummary "; }elseif($dbNo==3){ echo " -- ฐานข้อมูลใหม่แยกข้อมูลผู้ปกครองเป็น 3 ฟิลด์(คำนำหน้าชื่อ ชื่อและนามสกุล) "; echo " -- นำ run บน phpMyAdmin "; echo " -- คำสั่งแก้ไขข้อมูล "; echo "UPDATE $dbreg.`StudentBio` SET parentName = REPLACE(REPLACE(REPLACE(parentName ,'นาย','นาย '),'นาง','นาง '),'นางสาว','นางสาว ') ,parentName = REPLACE(REPLACE(REPLACE(parentName ,SPACE(4),SPACE(1)) ,SPACE(3),SPACE(1)),SPACE(2),SPACE(1)) ,parentName = TRIM(parentName) ,fatherName = REPLACE(REPLACE(REPLACE(fatherName ,SPACE(4),SPACE(1)) ,SPACE(3),SPACE(1)),SPACE(2),SPACE(1)) ,fatherName = REPLACE(REPLACE(REPLACE(fatherName ,'นาย ','นาย'),'นาง ','นาง'),'นางสาว ','นางสาว') ,fatherName = TRIM(fatherName); ,motherName = REPLACE(REPLACE(REPLACE(motherName ,SPACE(4),SPACE(1)) ,SPACE(3),SPACE(1)),SPACE(2),SPACE(1)) ,motherName = REPLACE(REPLACE(REPLACE(motherName ,'นาย ','นาย'),'นาง ','นาง'),'นางสาว ','นางสาว') ,motherName = TRIM(motherName); "; echo " -- คำสั่งทดสอบ "; echo "SELECT sb.parentName ,(case when (LENGTH(sb.parentName)-LENGTH(REPLACE(sb.parentName, ' ', ''))) >1 then (select prefixId from Prefix where prefixName = TRIM(LEFT(sb.parentName,locate(' ',sb.parentName)-1))) else NULL end) as pf ,(case when (LENGTH(sb.parentName)-LENGTH(REPLACE(sb.parentName, ' ', ''))) >1 then TRIM(SUBSTRING(sb.parentName,locate(' ',sb.parentName),locate(' ',sb.parentName,locate(' ',sb.parentName)+1)-locate(' ',sb.parentName))) else TRIM(LEFT(sb.parentName,locate(' ',sb.parentName)-1)) end) as fname ,(case when (LENGTH(sb.parentName)-LENGTH(REPLACE(sb.parentName, ' ', ''))) >1 then TRIM(SUBSTRING(sb.parentName,locate(' ',sb.parentName,locate(' ',sb.parentName)+1),CHAR_LENGTH(sb.parentName))) else TRIM(SUBSTRING(sb.parentName,locate(' ',sb.parentName),CHAR_LENGTH(sb.parentName))) end) AS lname FROM $dbreg.StudentBio sb "; } ?>