PHP db2_prepare函数代码示例

function insertSaniEmp()
{
    //print_r($_POST);
    if (isset($_POST)) {
        $empid = $_POST['empid'];
        $zoneid = $_POST['zoneid'];
        $jobid = '17';
        $firstname = $_POST['firstname'];
        $lastname = $_POST['lastname'];
        $birthdate = $_POST['birthdate'];
        $sex = $_POST['sex'];
        $nationality = $_POST['nationality'];
        $hiredate = $_POST['hiredate'];
        $address = $_POST['address'];
        $email = $_POST['email'];
        $phone = $_POST['phone'];
        $salary = $_POST['salary'];
        $bonus = $_POST['bonus'];
        // an array that want to insert this can be multiple array at the time.
        $data = array($empid, $zoneid, $jobid, $firstname, $lastname, $birthdate, $sex, $nationality, $hiredate, $address, $email, $phone, $salary, $bonus);
        // print var_dump to display an array of variable data with type that prepare for query.
        //echo var_dump($data) ."<br>";
    }
    // define $conn from model
    $conn = dbConnect();
    if ($conn) {
        $sql = 'INSERT INTO EMM_ZOO.EMPLOYEE (EMPID, ZONEID, JOBID, FIRSTNAME, LASTNAME, BIRTHDATE, SEX, NATIONALITY, HIREDATE, ADDRESS, EMAIL, PHONE, SALARY, BONUS) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?);';
        //$sql2 = 'INSERT INTO EMM_ZOO.EMP_SANI (EMPID) VALUES (?);';
        //echo $sql;
        // prepare statement using connection and sql
        $stmt = db2_prepare($conn, $sql);
        // If statement is valid execute it to db2
        if ($stmt) {
            //echo "SQL is valid<br>";
            $result = db2_execute($stmt, $data);
            if ($result) {
                $resultMessage = "Successfully added to sanitation employee";
                //echo "Successfully added";
                echo "<script>";
                echo "alert('Added successfully')";
                echo "</script>";
                header('Location: addEmpHome.php');
                exit;
            } else {
                $resultMessage = "Failed to query into database";
                echo "<script>";
                echo "alert('Failed to query into database')";
                echo "</script>";
            }
        } else {
            // If statement is error why see the code
            die('Critical error:' . db2_stmt_error());
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}

function uploadBioInfo()
{
    if (!isset($_SESSION['current_user_name']) && !isset($_COOKIE[$cookie_name])) {
        header('Location: ../login.php');
        exit;
    } else {
        //print_r($_POST);
        if (isset($_POST)) {
            $AnimalID = $_POST['AnimalID'];
            $species = $_POST['species'];
            $Phylum = $_POST['Phylum'];
            $Class = $_POST['Class'];
            $Order = $_POST['Order'];
            $Family = $_POST['Family'];
            $Genus = $_POST['Genus'];
            $warmblooded = $_POST['warmblooded'];
            $Cover = $_POST['Cover'];
            $Reproduction = $_POST['Reproduction'];
            $Habitat = $_POST['Habitat'];
            $food = $_POST['food'];
            $BodyTemp = $_POST['BodyTemp'];
            $EnviTemp = $_POST['EnviTemp'];
            $LifeSpan = $_POST['LifeSpan'];
            // an array that want to insert this can be multiple array at the time.
            $data = array($AnimalID, $species, $Phylum, $Class, $Order, $Family, $Genus, $warmblooded, $Cover, $Reproduction, $Habitat, $food, $BodyTemp, $EnviTemp, $LifeSpan);
            // print var_dump to display an array of variable data with type that prepare for query.
            //echo var_dump($data) ."<br>";
        }
        require_once '/var/www/html/app/model/connect.php';
        $conn = dbConnect();
        if ($conn) {
            // DEFAULT if you set generated as identify with specifier this will auto increament for integer.
            $sql = 'INSERT INTO EMM_ZOO.BIOINFO (SPECIESID,SPECIESNAME, PHYLUM, CLASS, ORDER, FAMILY, GENUS, WARMBLOODED, BODYCOVER, REPRODUCTION, HABITAT, COMMONFOOD, BODYTEMP, ENVITEMPRANGE, LIFESPAN) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);';
            //echo $sql;
            // prepare statement using connection and sql
            $stmt = db2_prepare($conn, $sql);
            // If statement is valid execute it to db2
            if ($stmt) {
                //echo "SQL is valid<br>";
                $result = db2_execute($stmt, $data);
                if ($result) {
                    $resultMessage = "Successfully added to Biological information";
                    echo "Successfully added";
                    header('Location: BioInfo.php');
                    exit;
                } else {
                    $resultMessage = "Failed to query into database";
                }
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error());
            }
            db2_free_stmt($stmt);
            db2_close($conn);
        } else {
            echo db2_conn_errormsg();
        }
    }
}

 function prepare($sql)
 {
     $stmt = @db2_prepare($this->_conn, $sql);
     if (!$stmt) {
         throw new DB2Exception(db2_stmt_errormsg());
     }
     return new DB2Statement($stmt);
 }

 /**
  * Prepare a statement handle.
  *
  * @param string $sql
  * @return void
  * @throws Zend_Db_Statement_Db2_Exception
  */
 public function _prepSql($sql)
 {
     parent::_prepSql($sql);
     $connection = $this->_adapter->getConnection();
     $this->_stmt = db2_prepare($connection, $sql);
     if (!$this->_stmt) {
         require_once 'Zend/Db/Statement/Db2/Exception.php';
         throw new Zend_Db_Statement_Db2_Exception(db2_stmt_errormsg(), db2_stmt_error());
     }
 }

 public static function query($conn, $sql)
 {
     if ($conn && strlen($sql) > 0) {
         $stmt = db2_prepare($conn, $sql);
         if (db2_execute($stmt)) {
             return $stmt;
         }
     }
     return false;
 }

 /**
  * Prepare a statement handle.
  *
  * @param string $sql
  * @return void
  * @throws \Zend\Db\Statement\Db2Exception
  */
 public function _prepare($sql)
 {
     $connection = $this->_adapter->getConnection();
     // db2_prepare on i5 emits errors, these need to be
     // suppressed so that proper exceptions can be thrown
     $this->_stmt = @db2_prepare($connection, $sql);
     if (!$this->_stmt) {
         throw new Db2Exception(db2_stmt_errormsg(), db2_stmt_error());
     }
 }

function insertBuilding()
{
    if ($_POST['form_token'] != $_SESSION['form_token']) {
        header('Location:index.php');
    } else {
        //print_r($_POST);
        if (isset($_POST)) {
            $emm = $_POST['BEmp'];
            $zone = $_POST['BZone'];
            $build = $_POST['Building'];
            $floor = $_POST['floor'];
            $room = $_POST['room'];
            // an array that want to insert this can be multiple array at the time.
            $data = array($build, $floor, $room);
            // print var_dump to display an array of variable data with type that prepare for query.
            //echo var_dump($data) ."<br>";
        }
        // define $conn from model
        $conn = dbConnect();
        if ($conn) {
            // DEFAULT if you set generated as identify with specifier this will auto increament for integer.
            $sql = 'INSERT INTO EMM_ZOO.MAINTAINBUILDING (MAINTEGERAINID, BUILDINGNAME, FLOORLEVEL, ROOM) VALUES (DEFAULT,?,?,?);';
            //echo $sql;
            // prepare statement using connection and sql
            $stmt = db2_prepare($conn, $sql);
            // If statement is valid execute it to db2
            if ($stmt) {
                //echo "SQL is valid<br>";
                $result = db2_execute($stmt, $data);
                if ($result) {
                    $resultMessage = "Successfully added";
                    //echo "Successfully added";
                    echo "<script>";
                    echo "alert('Successfully')";
                    echo "</script>";
                    header('Location: index.php');
                    exit;
                } else {
                    echo "<script>";
                    echo "alert('Failed')";
                    echo "</script>";
                    $resultMessage = "Failed to query into database";
                }
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error());
            }
            db2_free_stmt($stmt);
            db2_close($conn);
        } else {
            echo db2_conn_errormsg();
        }
    }
}

 /**
  * (non-PHPdoc)
  * @see PreparedStatement::preparePreparedStatement()
  */
 public function preparePreparedStatement($msg = '')
 {
     if (empty($this->parsedSQL)) {
         $this->DBM->registerError($msg, "Empty SQL query");
         return false;
     }
     $GLOBALS['log']->info('QueryPrepare: ' . $this->parsedSQL);
     if (!($this->stmt = db2_prepare($this->dblink, $this->parsedSQL))) {
         $this->DBM->checkError($msg);
         return false;
     }
     return $this;
 }

 /**
  * This function initializes the class.
  *
  * @access public
  * @override
  * @param DB_Connection_Driver $connection  the connection to be used
  * @param string $sql                       the SQL statement to be queried
  * @param integer $mode                     the execution mode to be used
  * @throws Throwable_SQL_Exception          indicates that the query failed
  *
  * @see http://www.php.net/manual/en/function.db2-prepare.php
  * @see http://www.php.net/manual/en/function.db2-execute.php
  * @see http://www.php.net/manual/en/function.db2-stmt-error.php
  */
 public function __construct(DB_Connection_Driver $connection, $sql, $mode = NULL)
 {
     $resource = $connection->get_resource();
     $command = @db2_prepare($resource, $sql);
     if ($command === FALSE) {
         throw new Throwable_SQL_Exception('Message: Failed to query SQL statement. Reason: :reason', array(':reason' => @db2_conn_errormsg($resource)));
     }
     if (!@db2_execute($command)) {
         throw new Throwable_SQL_Exception('Message: Failed to query SQL statement. Reason: :reason', array(':reason' => @db2_stmt_errormsg($command)));
     }
     $this->command = $command;
     $this->record = FALSE;
 }

function insertUselog()
{
    //print_r($_POST);
    if (isset($_POST)) {
        $equipid = $_POST['equipid'];
        $equipname = $_POST['equipname'];
        $empid = $_POST['empid'];
        $zoneid = $_POST['zoneid'];
        $borrowdate = $_POST['borrowdate'];
        $returndate = $_POST['returndate'];
        // an array that want to insert this can be multiple array at the time.
        $data = array($equipid, $equipname, $empid, $zoneid);
        // print var_dump to display an array of variable data with type that prepare for query.
        //echo var_dump($data) ."<br>";
    }
    // define $conn from model
    $conn = dbConnect();
    if ($conn) {
        $sql = 'INSERT INTO EMM_ZOO.SANI_EQUIPUSELOG (EQUIPID, EMPID, WORKZONEID, BORROWDATE, RETURNDATE) VALUES (?,?,?,?,?);';
        //$sql2 = 'INSERT INTO EMM_ZOO.EMP_SANI (EMPID) VALUES (?);';
        //echo $sql;
        // prepare statement using connection and sql
        $stmt = db2_prepare($conn, $sql);
        // If statement is valid execute it to db2
        if ($stmt) {
            //echo "SQL is valid<br>";
            $result = db2_execute($stmt, $data);
            if ($result) {
                $resultMessage = "Successfully added to Equipment use log";
                //echo "Successfully added";
                echo "<script>";
                echo "alert('Added successfully')";
                echo "</script>";
                header('Location: addUselogHome.php');
                exit;
            } else {
                $resultMessage = "Failed to query into database";
                echo "<script>";
                echo "alert('Failed to query into database')";
                echo "</script>";
            }
        } else {
            // If statement is error why see the code
            die('Critical error:' . db2_stmt_error());
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}

function insertEmpAtt()
{
    //print_r($_POST);
    if (isset($_POST)) {
        $attno = $_POST['attno'];
        $dates = $_POST['dates'];
        $empid = $_POST['empid'];
        $workzoneid = $_POST['workzoneid'];
        $dutyid = $_POST['carplate'];
        $starttime = $_POST['starttime'];
        $endtime = $_POST['endtime'];
        // an array that want to insert this can be multiple array at the time.
        $data = array($attno, $dates, $empid, $workzoneid, $dutyid, $starttime, $endtime);
        // print var_dump to display an array of variable data with type that prepare for query.
        //echo var_dump($data) ."<br>";
    }
    // define $conn from model
    $conn = dbConnect();
    if ($conn) {
        $sql = 'INSERT INTO EMM_ZOO.SANIEMP_ATTEND (ATTENDNO, DATES, EMPID, WORKZONEID, DUTYID, STARTTIME, ENDTIME) VALUES (?,?,?,?,?,?,?);';
        // prepare statement using connection and sql
        $stmt = db2_prepare($conn, $sql);
        // If statement is valid execute it to db2
        if ($stmt) {
            //echo "SQL is valid<br>";
            $result = db2_execute($stmt, $data);
            if ($result) {
                $resultMessage = "Successfully added to sanitation car";
                //echo "Successfully added";
                echo "<script>";
                echo "alert('Added successfully')";
                echo "</script>";
                header('Location: addCarHome.php');
                exit;
            } else {
                $resultMessage = "Failed to query into database";
                echo "<script>";
                echo "alert('Failed to query into database')";
                echo "</script>";
            }
        } else {
            // If statement is error why see the code
            die('Critical error:' . db2_stmt_error());
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}

function uploadReserve()
{
    if ($_POST['form_token'] != $_SESSION['form_token']) {
        header('Location:reserved.php');
    } else {
        //print_r($_POST);
        if (isset($_POST)) {
            $name = $_POST['reserved_name'];
            $resered_date = $_POST['reserved_date'];
            $mobile = $_POST['mobile'];
            $vehi_type = $_POST['type'];
            $quantity = $_POST['quantity'];
            $email = $_POST['email'];
            // an array that want to insert this can be multiple array at the time.
            $data = array($name, $resered_date, $mobile, $vehi_type, $quantity, $email);
            // print var_dump to display an array of variable data with type that prepare for query.
            //echo var_dump($data) ."<br>";
        }
        // define $conn from model
        $conn = dbConnect();
        if ($conn) {
            // DEFAULT if you set generated as identify with specifier this will auto increament for integer.
            $sql = 'INSERT INTO EMM_ZOO.PARKRESERVETOUR (PARKRESERVENO, RESERVE_NAME, RESERVE_DATE, PHONE, VEHI_TYPE, AMOUNT, EMAIL) VALUES (DEFAULT,?,?,?,?,?,?);';
            //echo $sql;
            // prepare statement using connection and sql
            $stmt = db2_prepare($conn, $sql);
            // If statement is valid execute it to db2
            if ($stmt) {
                //echo "SQL is valid<br>";
                $result = db2_execute($stmt, $data);
                if ($result) {
                    $resultMessage = 1;
                    return $resultMessage;
                    header('Location: reserved.php#reserve_list');
                    exit;
                } else {
                    $resultMessage = 0;
                    return $resultMessage;
                }
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error());
            }
            db2_free_stmt($stmt);
            db2_close($conn);
        } else {
            echo db2_conn_errormsg();
        }
    }
}

 /**
  * Prepare a statement handle.
  *
  * @param string $sql
  * @return void
  * @throws Zend_Db_Statement_Db2_Exception
  */
 public function _prepare($sql)
 {
     $connection = $this->_adapter->getConnection();
     // db2_prepare on i5 emits errors, these need to be
     // suppressed so that proper exceptions can be thrown
     $this->_stmt = @db2_prepare($connection, $sql);
     if (!$this->_stmt) {
         /**
          * @see Zend_Db_Statement_Db2_Exception
          */
         require_once PHP_LIBRARY_PATH . 'Zend/Db/Statement/Db2/Exception.php';
         throw new Zend_Db_Statement_Db2_Exception(db2_stmt_errormsg(), db2_stmt_error());
     }
 }

function updateTicket()
{
    // connect db=> stmt sql => insert => refresh page
    if (isset($_POST)) {
        $type[0] = $_POST['typeC'];
        $type[1] = $_POST['typeA'];
        $type[2] = $_POST['typeF'];
        $num[0] = intval($_POST['TicketNumC']);
        $num[1] = intval($_POST['TicketNumA']);
        $num[2] = intval($_POST['TicketNumF']);
        //$num = $_POST['TicketNum'];
    }
    // start connect db
    $conn = dbConnect();
    if ($conn) {
        //Part one select data from tickettype
        for ($i = 0; $i <= 2; $i++) {
            if ($num[$i] == 0) {
                continue;
            }
            $sql = "SELECT * FROM EMM_ZOO.TICKETGATE_TYPE WHERE TICKETGATE_TYPE = '{$type[$i]}';";
            $stmt = db2_prepare($conn, $sql);
            $result = db2_execute($stmt);
            while ($row = db2_fetch_assoc($stmt)) {
                $ticket_price = $row['TICKETGATETYPE_PRICE'];
                $ticket_type = $row['TICKETGATE_TYPE'];
                $ticket_id = intval($row['TICKETGATETYPE_ID']);
                //printf ("%-5d %-16s %-32d\n",
                //   $ticket_price, $ticket_type, $ticket_id);
            }
            $insert = "INSERT INTO EMM_ZOO.TICKETGATE_TRANSACTION (TICKETGATE_ID, TICKETGATETYPE_ID, TICKETGATE_DATE, TICKETGATE_NUM, TICKETGATE_PRICE) VALUES (DEFAULT, {$ticket_id}, CURRENT DATE, {$num[$i]}" . "," . $ticket_price * $num[$i] . ");";
            //echo $insert;
            $rc = db2_exec($conn, $insert);
            // ตรงนี้ error ยังไม่เสร็จ
            if ($rc) {
                // echo "Insert successfully!!";
                echo "<script>alert('{$num[$i]} {$type[$i]} ticket has sole  in price " . $ticket_price * $num[$i] . "');window.location='GateTricket.php';</script>";
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error($stmt));
            }
            // finish all query statement
            db2_free_stmt($stmt);
        }
        db2_close($conn);
    } else {
        echo db2_conn_errormsg($conn);
    }
}

function uploadPubMedInfo()
{
    if (!isset($_SESSION['current_user_name']) && !isset($_COOKIE[$cookie_name])) {
        header('Location: ../login.php');
        exit;
    } else {
        //print_r($_POST);
        if (isset($_POST)) {
            $PUBMEDID = $_POST['PUBMEDID'];
            $Title = $_POST['Title'];
            $Year = $_POST['Year'];
            $Author = $_POST['Author'];
            $Journal = $_POST['Journal'];
            $ResearchType = $_POST['ResearchType'];
            $data = array($PUBMEDID, $Title, $Year, $Author, $Journal, $ResearchType);
            // print var_dump to display an array of variable data with type that prepare for query.
            //echo var_dump($data) ."<br>";
        }
        require_once '/var/www/html/app/model/connect.php';
        $conn = dbConnect();
        if ($conn) {
            // DEFAULT if you set generated as identify with specifier this will auto increament for integer.
            $sql = 'INSERT INTO EMM_ZOO.PUBMEDREFERENCES (PUBMEDID,TITLE,YEAR,AUTHOR,JOURNAL,RESEARCH_TYPE) VALUES (?,?,?,?,?,?);';
            //echo $sql;
            // prepare statement using connection and sql
            $stmt = db2_prepare($conn, $sql);
            // If statement is valid execute it to db2
            if ($stmt) {
                //echo "SQL is valid<br>";
                $result = db2_execute($stmt, $data);
                if ($result) {
                    $resultMessage = "Successfully added to Biological information";
                    echo "Successfully added";
                    header('Location: PubMedRef.php');
                    exit;
                } else {
                    $resultMessage = "Failed to query into database";
                }
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error());
            }
            db2_free_stmt($stmt);
            db2_close($conn);
        } else {
            echo db2_conn_errormsg();
        }
    }
}