php search mysql

<?php ini_set('display_errors', 1); error_reporting(~0); $strKeyword = null; if(isset($_POST["txtKeyword"])) { $strKeyword = $_POST["txtKeyword"]; } ?> <form name="frmSearch" method="post" action="<?php echo $_SERVER['SCRIPT_NAME'];?>"> <table width="599" border="1"> <tr> <th>Keyword <input name="txtKeyword" type="text" id="txtKeyword" value="<?php echo $strKeyword;?>"> <input type="submit" value="Search"></th> </tr> </table> </form> <?php $serverName = "localhost"; $userName = "root"; $userPassword = "1234"; $dbName = "testdb"; $conn = mysqli_connect($serverName,$userName,$userPassword,$dbName); mysqli_set_charset($conn, "utf8"); $sql = "SELECT * FROM provider WHERE (NAME LIKE '%".$strKeyword."%' OR LNAME LIKE '%".$strKeyword."%' ) "; $query = mysqli_query($conn,$sql); ?> <table width="600" border="1"> <tr> <th width="91"> <div align="center">CID </div></th> <th width="98"> <div align="center">NAME </div></th> <th width="198"> <div align="center">LNAME </div></th> <th width="198"> <div align="center">BIRTH </div></th> </tr> <?php while($result=mysqli_fetch_array($query,MYSQLI_ASSOC)) { ?> <tr> <td><div align="center"><?php echo $result["CID"];?></div></td> <td><?php echo $result["NAME"];?></td> <td><?php echo $result["LNAME"];?></td> <td><?php echo $result["BIRTH"];?></td> </tr> <?php } ?> </table> <?php mysqli_close($conn); ?>

admitt+referน้อยกว่า 4 ชั่วโมง

SELECT  k.ICD10_TM , k.NICKNAME, COUNT(k.ICD10_TM) as AMOUNT
FROM (

SELECT i.VISIT_ID ,i.ADM_DT, r.RF_DT ,
((to_days(r.RF_DT)*24)- (to_days(i.ADM_DT)*24))/24 AS DAYS, abs((time_to_sec(r.RF_DT)/3600) - (time_to_sec(i.ADM_DT)/3600)) as Times
,ic.ICD10_TM , ic.NICKNAME
FROM ipd_reg i
INNER JOIN refers r on i.VISIT_ID = r.VISIT_ID AND i.IS_CANCEL = 0 AND r.IS_CANCEL = 0 AND r.rf_type = 2
INNER JOIN opd_diagnosis o ON i.VISIT_ID = o.VISIT_ID AND o.IS_CANCEL = 0 AND o.DXT_ID = 1
INNER JOIN icd10new ic ON o.ICD10 = ic.ICD10
WHERE r.RF_DT BETWEEN '2018-10-01 00:00' AND '2019-09-30 23:59'
AND ((to_days(r.RF_DT)*24)- (to_days(i.ADM_DT)*24))/24 = '0'
AND abs((time_to_sec(r.RF_DT)/3600) - (time_to_sec(i.ADM_DT)/3600)) <= '4.0') as k
GROUP BY k.ICD10_TM  ORDER BY AMOUNT DESC

****List แยก ตามAN***

SELECT i.VISIT_ID ,op.HN,i.ADM_ID as AN ,op.REG_DATETIME as REGDATE,i.ADM_DT, r.RF_DT ,

((to_days(r.RF_DT)*24)- (to_days(i.ADM_DT)*24))/24 AS DAYS, abs((time_to_sec(r.RF_DT)/3600) - (time_to_sec(i.ADM_DT)/3600)) as Times

,ic.ICD10_TM , ic.NICKNAME

FROM ipd_reg i

LEFT JOIN opd_visits op ON op.visit_id = i.visit_id AND op.is_cancel = 0

INNER JOIN refers r on i.VISIT_ID = r.VISIT_ID AND i.IS_CANCEL = 0 AND r.IS_CANCEL = 0 AND r.rf_type = 2

INNER JOIN opd_diagnosis o ON i.VISIT_ID = o.VISIT_ID AND o.IS_CANCEL = 0 AND o.DXT_ID = 1

INNER JOIN icd10new ic ON o.ICD10 = ic.ICD10

WHERE r.RF_DT BETWEEN '2020-01-01 00:00' AND '2021-09-30 23:59'

AND ((to_days(r.RF_DT)*24)- (to_days(i.ADM_DT)*24))/24 = '0'

AND abs((time_to_sec(r.RF_DT)/3600) - (time_to_sec(i.ADM_DT)/3600)) <= '2.0' 

#GROUP BY k.ICD10_TM  ORDER BY AMOUNT DESC

การเข้าHDC แบบง่าย

203.157.102.1xx
33=
34=ubon

********************************************

#######SLIDEประกอบ#######

***update person กรณีcid =000000 ส่งเข้าupdateได้เลย ระบบตรวจสอบวันเกิด  ***

query การตัดchief complaint (history)

####History###

SELECT SUBSTR(o.history,LOCATE('CHIEF COMPLAINT',o.history),
LOCATE('PRESENT ILLNESS',o.history)- 
LOCATE('CHIEF COMPLAINT',o.history))
FROM opd_visits o
WHERE o.visit_id in ('0002250117','0002250119')

/////ตัดอักขระพิเศษ

SELECT o.VISIT_ID as seq , replace(replace(replace(SUBSTR(o.history,LOCATE('PRESENT ILLNESS',o.history)+17) ,char(13),''),char(10),''),'""','') as hpi FROM opd_visits o WHERE  o.visit_id = '${seq}' group by o.visit_id

######ตรวจร่างกาย#######

SELECT o.VISIT_ID as seq , replace(replace(replace(o.pexam,char(13),''),char(10),''),'""','') as pe FROM opd_visits o WHERE  o.visit_id = '${seq}' group by o.visit_id

X60-X64

SELECT o.REG_DATETIME , o.HN , o.VISIT_ID, p.CID, CONCAT(p.FNAME,'',p.LNAME) AS FULLNAME, p.BIRTHDATE, o.UNIT_REG, icd.ICD10_TM
FROM opd_visits o
INNER JOIN cid_hn c ON o.hn = c.hn 
INNER JOIN population p ON c.cid = p.cid 
INNER  JOIN opd_diagnosis dx ON dx.visit_id = o.visit_id and dx.is_cancel = 0
INNER  JOIN icd10new icd ON icd.icd10 = dx.icd10 and icd.ICD10_TM BETWEEN 'X60' AND 'X64'
WHERE o.REG_DATETIME BETWEEN '2017.01.01 00:00' and '2020.12.31 23:59' 
AND o.is_cancel = 0  ORDER BY o.REG_DATETIME DESC 

GetNuture SmartRefer

SELECT o.VISIT_ID as seq,
        DATE_FORMAT(date(o.REG_DATETIME),'%Y-%m-%d') as date_serv, 
DATE_FORMAT(time(o.REG_DATETIME),'%h:%i:%s') as time_serv, 
        '' as bloodgrp,
        o.WEIGHT as weight,
        o.HEIGHT as height,
       ROUND((o.WEIGHT / ((o.height / 100) * (o.height / 100))), 2) as BMI,
        o.BODY_TEMP as temperature,
        o.PULSE_RATE as pr,
        o.RESP_RATE as rr,
        o.BP_SYST as sbp,
        o.BP_DIAS as dbp,
        '' as symptom,
        s.UNIT_ID as depcode,
        s.UNIT_NAME as department
        FROM opd_visits  o 
LEFT JOIN service_units s ON o.unit_reg = s.unit_id
        WHERE o.VISIT_ID in ('0002265136' )

หาชื่อฟิลด์จากฐานข้อมูลScript

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'prepack'
AND TABLE_SCHEMA= 'mbase_data'