samedi 5 novembre 2016

How to Make this IF ELSE MYSQL Code Work?

The Following Code works on phpMyAdmin but not through PHP Code Program. Can you please help. There are three tables here. CIOM_Master containing all records, CIO_Master and CIO_Alert

IF (NOT EXISTS(SELECT * FROM cio_master)  
BEGIN 
    INSERT INTO cio_alert (Client_RFID_Number) 
        SELECT (tcio_master.Client_RFID_Number)
            From cio_master tcio_master
            WHERE NOT EXISTS(SELECT Client_RFID_Number FROM cio_alert tcio_alert
                                WHERE tcio_alert.Client_RFID_Number = tcio_master.Client_RFID_Number)
END ELSE 
BEGIN 
    UPDATE cio_alert 
    SET 
        Client_Check_Out= (SELECT cio_master.Client_Check_Out FROM cio_master WHERE cio_alert.Client_RFID_Number=cio_master.Client_RFID_Number),
        Client_Check_In=  (SELECT cio_master.Client_Check_In FROM cio_master WHERE cio_alert.Client_RFID_Number=cio_master.Client_RFID_Number),
        NotInOut= IF(( SELECT Client_Std_CI_Time FROM ciom_master WHERE (ciom_master.Client_RFID_Number=cio_alert.Client_RFID_Number)
                        AND (CURTIME() > Client_Std_CI_Time + INTERVAL 30 MINUTE)), 'Not In',
                        IF((SELECT Client_Std_CO_Time FROM ciom_master WHERE (ciom_master.Client_RFID_Number=cio_alert.Client_RFID_Number) 
                        AND (CURTIME() > Client_Std_CO_Time + INTERVAL 30 MINUTE)), 'Not Out', NotInOut))
    WHERE cio_alert.Client_RFID_Number = (SELECT Client_RFID_Number FROM cio_master 
                         WHERE cio_alert.Client_RFID_Number = cio_master.Client_RFID_Number)
END

Aucun commentaire:

Enregistrer un commentaire