PHP db2_exec函数代码示例

function editEquipInfo()
{
    if (isset($_POST)) {
        $equipid = $_POST['update_equipid'];
        $equipname = $_POST['edit_equipname'];
        $equiptype = $_POST['edit_equiptype'];
        $status = $_POST['edit_status'];
    }
    $conn = dbConnect();
    if ($conn) {
        $sql = "UPDATE EMM_ZOO.SANITATION_EQUIP SET EQUIPID = '{$equipid}', EQUIPNAME = '{$equipname}' , EQUIPTYPE = '{$equiptype}', STATUS = '{$status}' WHERE EQUIPID = {$equipid};";
        $result = db2_exec($conn, $sql);
        if ($result) {
            echo "<script>";
            echo "alert('Updated successfully')";
            echo "</script>";
            header('Location: sani_equip.php#car_list');
            exit;
        } else {
            $resultMessage = 0;
            echo "<script>";
            echo "alert('Updated unsuccessfully')";
            echo "</script>";
            return $resultMessage;
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}

function updateTicket()
{
    // connect db=> stmt sql => insert => refresh page
    if (isset($_POST)) {
        $ticketshowID = $_POST['showid_value'];
    }
    $conn = dbConnect();
    // start connect db
    if ($conn) {
        $insert = " INSERT INTO EMM_ZOO.TICKETSHOW_BUYING (TICKETSHOW_ID, SHOWID, ROUNDID , BUYING_DATE) VALUES (DEFAULT, '{$ticketshowID}',CURRENT TIME, CURRENT DATE);";
        echo $insert;
        $rc = db2_exec($conn, $insert);
        if ($rc) {
            header("Refresh:0; url=ShowList.php");
        } else {
            // If statement is error why see the code
            die('Critical error:' . db2_stmt_error($stmt));
        }
        // finish all query statement
        db2_free_stmt($rc);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg($conn);
    }
}

function orderoldfood()
{
    if (isset($_POST)) {
        $food_id = $_POST['food_id'];
        $food_name = $_POST['food_name'];
        $food_type = $_POST['food_type'];
        $add_amount = $_POST['add_amount'];
        $price_per = $_POST['price_per'];
        $total = $add_amount * $price_per;
    }
    $conn = dbConnect();
    if ($conn) {
        $sql0 = "SELECT AMOUNT FROM EMM_ZOO.FM_STOCK WHERE FOODID = {$food_id};";
        $stm = dbQuery($conn, $sql0);
        while ($row = dbFetchArray($conn, $stm)) {
            $amountx = $row[0];
        }
        $amount_now = $amountx + $add_amount;
        $sql1 = "INSERT INTO EMM_ZOO.FOODANIMAL_EXPENSE (FOODEXPENSE_ID,DATES,FOODID,COST,RESPONPERSONID) VALUES (DEFAULT,CURRENT DATE,{$food_id},{$total},'5678');";
        $cb = db2_exec($conn, $sql1);
        $sql2 = "UPDATE EMM_ZOO.FM_STOCK SET AMOUNT = {$amount_now} WHERE FOODID = {$food_id};";
        $cc = db2_exec($conn, $sql2);
        if ($cc && $cb) {
            $resultMessage = 1;
            return $resultMessage;
            header('Location: FoodStock.php#food_list');
        } else {
            $resultMessage = 0;
            return $resultMessage;
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    }
}

function updateTicket()
{
    // connect db=> stmt sql => insert => refresh page
    if (isset($_POST)) {
        $tickettourID = $_POST['tourid_value'];
    }
    $conn = dbConnect();
    // start connect db
    if ($conn) {
        $insert = " INSERT INTO EMM_ZOO.TICKETTOUR_TRANSACTION (TICKETTOUR_ID, TOUR_ID, DATE) VALUES (DEFAULT, '{$tickettourID}', CURRENT DATE);";
        echo $insert;
        $rc = db2_exec($conn, $insert);
        if ($rc) {
            header("Refresh:0; url=TourTicket.php");
        } else {
            // If statement is error why see the code
            die('Critical error:' . db2_stmt_error($stmt));
        }
        // finish all query statement
        db2_free_stmt($rc);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg($conn);
    }
}

function editCarPlate()
{
    if (isset($_POST)) {
        $carid = $_POST['update_carid'];
        $carplate = $_POST['edit_carplate'];
    }
    $conn = dbConnect();
    if ($conn) {
        $sql = "UPDATE EMM_ZOO.SANICAR SET CARID = '{$carid}', CARPLATE = '{$carplate}' WHERE CARID = {$carid};";
        $result = db2_exec($conn, $sql);
        if ($result) {
            echo "<script>";
            echo "alert('Updated successfully')";
            echo "</script>";
            header('Location: sani_garbage.php#car_list');
            exit;
        } else {
            $resultMessage = 0;
            echo "<script>";
            echo "alert('Updated unsuccessfully')";
            echo "</script>";
            return $resultMessage;
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}

 function select()
 {
     $conn = $this->connect();
     if ($conn == true) {
         $sql = "SELECT name from helloworld";
         $result = db2_exec($conn, $sql);
         while ($row = db2_fetch_array($result)) {
             echo $text = $row[0];
         }
     }
 }

function insertIntoRestaurant($conn, $cities, $name1, $street, $city, $state, $zip, $long, $lat)
{
    if (array_key_exists($city, $cities)) {
        //Used to filter unwanted cities
        $sql = "insert into " . userAccount . ".restaurant values('" . $name1 . "', NULL, '" . $street . "', '" . $city . "', '" . $state . "', '" . $zip . "', '" . COUNTY . "', " . $long . ", " . $lat . ")";
        $result = db2_exec($conn, $sql);
        //if(!$result)
        //log failure
    } else {
        //log rejected city
    }
}

function updateTicket()
{
    // connect db=> stmt sql => insert => refresh page
    if (isset($_POST['id_value'])) {
        $transID = $_POST['id_value'];
    }
    $conn = dbConnect();
    // start connect db
    if ($conn) {
        $update = "UPDATE EMM_ZOO.TICKETTRANS_TRANSACTION SET TICKETTRANS_TIMEIN = CURRENT TIME WHERE TICKETTRANS_ID = " . $transID . ";";
        //echo $update;
        $rc = db2_exec($conn, $update);
        if ($rc) {
            //header("Refresh:0; url=TranspotationIN.php");
        } else {
            // If statement is error why see the code
            die('Critical error:' . db2_stmt_error($stmt));
        }
        // finish all query statement
        db2_free_stmt($rc);
        $sql = "SELECT * FROM EMM_ZOO.TICKETTRANS_TRANSACTION JOIN EMM_ZOO.TICKETTRANS_TYPE ON VEHICLETRANS_ID = TRANSTYPE_ID WHERE TICKETTRANS_ID = " . $transID . ";";
        $stmt = db2_exec($conn, $sql);
        while ($row = db2_fetch_assoc($stmt)) {
            $tran_price = $row['TRANSTYPE_PRICE'];
            $tran_type = $row['TRANSTYPE_NAME'];
            $hourOut = (int) substr($row['TICKETTRANS_TIMEOUT'], 0, 2);
            $hourIn = (int) substr($row['TICKETTRANS_TIMEIN'], 0, 2);
            $extraOut = (int) substr($row['TICKETTRANS_TIMEOUT'], 3, 2);
            $extraIn = (int) substr($row['TICKETTRANS_TIMEIN'], 3, 2);
            $price = ($hourIn - $hourOut) * $tran_price;
            // ราคาต้องดึงมาจาก trans_Type table
            if ($extraIn - $extraOut > 0) {
                $price += $tran_price;
            }
            if ($extraIn - $extraOut == 0 && $hourIn - $hourOut == 0 && (int) substr($row['TICKETTRANS_TIMEOUT'], 6, 2) != (int) substr($row['TICKETTRANS_TIMEIN'], 6, 2)) {
                $price += $tran_price;
            }
            if ($stmt) {
                // echo "Insert successfully!!";
                echo "<script>alert('Price of {$tran_type} is {$price}');window.location='TranspotationIN.php';</script>";
                //header("Refresh:0; url=TranspotationIN.php");
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error($stmt));
            }
        }
        db2_free_stmt($rc);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg($conn);
    }
}

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 carReturn()
{
    $up = "Y";
    if (isset($_POST)) {
        $empID = $_POST['empID'];
        $carID = $_POST['carID'];
        // an array that want to insert this can be multiple array at the time.
        // 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.
        $delete = "DELETE FROM EMM_ZOO.CARS_BORROWED WHERE EMM_ZOO.CARS_BORROWED.CARID = '" . $carID . "';";
        $update = "UPDATE EMM_ZOO.CARS SET EMM_ZOO.CARS.AVAILABLE = '" . $up . "' WHERE EMM_ZOO.CARS.CARID = '" . $carID . "';";
        $guanteen = "SELECT AVAILABLE FROM EMM_ZOO.CARS WHERE EMM_ZOO.CARS.CARID = '" . $carID . "';";
        $ans = dbQuery($conn, $guanteen);
        $row = dbFetchArray($conn, $ans);
        if ($row[0] == 'Y' || $row[0] == 'y') {
            echo "<script type='text/javascript'>alert(' This car has not been borrowed yet');</script>";
            header("Refresh:0; url=returnCar.php");
        } else {
            $result = db2_exec($conn, $delete);
            if ($result) {
                $result2 = db2_exec($conn, $update);
                if ($result2) {
                    echo "<script type='text/javascript'>alert('CAR RETURNED');</script>";
                    header("Refresh:0; url=returnCar.php");
                } else {
                    echo "<script type='text/javascript'>alert('You need to fill all input OR Your employeeID,carID does not exist');</script>";
                    header("Refresh:0; url=TransportationEmployee.php");
                }
            } else {
                // If statement is error why see the code
                die('Critical error:' . db2_stmt_error($stmt));
            }
            db2_free_stmt($result);
            db2_free_stmt($result2);
        }
        db2_free_stmt($ans);
        db2_free_stmt($row);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg($conn);
    }
}

function ordernewfood()
{
    if (isset($_POST)) {
        $food_name = $_POST['food_name'];
        $food_type = $_POST['food_type'];
        $add_amount = $_POST['add_amount'];
        $price_per = $_POST['price_per'];
        $total = $add_amount * $price_per;
        $username = $_POST['username'];
        $data = array($food_name, $food_type, $add_amount, $price_per);
    }
    $conn = dbConnect();
    if ($conn) {
        $conn = dbConnect();
        $sql0 = "INSERT INTO EMM_ZOO.FM_STOCK (FOODID,FOODNAME,TYPE,AMOUNT,PERAMOUNT) VALUES(DEFAULT,'{$food_name}','{$food_type}',{$add_amount}, {$price_per})";
        $ca = db2_exec($conn, $sql0);
        if ($ca) {
            $resultMessage = 1;
            return $resultMessage;
        } else {
            $resultMessage = 0;
            return $resultMessage;
        }
        $sql1 = "SELECT FOODID FROM EMM_ZOO.FM_STOCK WHERE FOODNAME = '{$food_name}';";
        $stm = dbQuery($conn, $sql);
        while ($row = dbFetchArray($conn, $stm)) {
            $foodidx = $row[0];
        }
        $sql2 = "INSERT INTO EMM_ZOO.FOODANIMAL_EXPENSE (FOODEXPENSE_ID,DATES,FOODID,COST,RESPONPERSONID) VALUES (DEFAULT,current date,{$foodidx},{$total},{$username});";
        $sc = db2_exec($conn, $sql2);
        if ($sc) {
            $resultMessage = 1;
            return $resultMessage;
            header('Location: FoodStock.php#order_new');
            exit;
        } else {
            $resultMessage = 0;
            return $resultMessage;
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    }
}

function editEmployeeInfo()
{
    if (isset($_POST)) {
        $empid = $_POST['update_empid'];
        $jobid = $_POST['edit_jobid'];
        $firstname = $_POST['edit_firstname'];
        $lastname = $_POST['edit_lastname'];
        $birthdate = $_POST['edit_birthdate'];
        $sex = $_POST['edit_sex'];
        $nationality = $_POST['edit_nationality'];
        $hiredate = $_POST['edit_hiredate'];
        $address = $_POST['edit_address'];
        $email = $_POST['edit_email'];
        $phone = $_POST['edit_phone'];
        $salary = $_POST['edit_salary'];
        $bonus = $_POST['edit_bonus'];
    }
    $conn = dbConnect();
    if ($conn) {
        $sql = "UPDATE EMM_ZOO.EMPLOYEE SET EMPID = '{$empid}', JOBID = '{$jobid}', FIRSTNAME = '{$firstname}', LASTNAME = '{$lastname}', BIRTHDATE = '{$birthdate}', SEX = '{$sex}', NATIONALITY = '{$nationality}', HIREDATE = '{$hiredate}', ADDRESS = '{$address}', EMAIL = '{$email}', PHONE = '{$phone}' , SALARY = '{$salary}', BONUS = '{$bonus}' WHERE EMPID = {$empid};";
        $result = db2_exec($conn, $sql);
        if ($result) {
            echo "<script>";
            echo "alert('Updated successfully')";
            echo "</script>";
            header('Location: sani_emp.php#emp_list');
            exit;
        } else {
            $resultMessage = 0;
            echo "<script>";
            echo "alert('Updated unsuccessfully')";
            echo "</script>";
            return $resultMessage;
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}

function addRound()
{
    if (isset($_POST)) {
        $showID = $_POST['showID'];
        $roundID = $_POST['roundID'];
        $starttime = $_POST['starttime'];
        $endtime = $_POST['endtime'];
        $showdate = $_POST['showdate'];
    }
    $conn = dbConnect();
    if ($conn) {
        $insert = "INSERT INTO EMM_ZOO.SHOW_TIMETABLE(SHOWID, ROUNDID, STARTTIME, ENDTIME, DATES) values({$showID}, {$roundID}, '{$starttime}', '{$endtime}', '{$showdate}');";
        //$insert = "INSERT INTO EMM_ZOO.SHOW_TIMETABLE(SHOWID, ROUNDID, STARTTIME, ENDTIME, DATES) values(1, 3, '16:00:00', '16:30:00', '11/12/2015');";
        $rc = db2_exec($conn, $insert);
        if ($rc) {
            echo "Insert Successful";
        } else {
            die('Critical error: ' . db2_stmt_error($rc));
        }
        $sql = "SELECT * from EMM_ZOO.SHOW WHERE SHOWID = {$showID};";
        $stmt = db2_prepare($conn, $sql);
        $result = db2_execute($stmt);
        while ($row = db2_fetch_assoc($stmt)) {
            $zone = $row['BUILDINGID'];
            $name = $row['SHOWNAME'];
        }
        $insert = "INSERT INTO EMM_ZOO.SHOW_TICKET(SHOWID, STARTTIME, ENDTIME, DATES, SHOWNAME, ZONEID) values({$showID}, '{$starttime}', '{$endtime}', '{$showdate}', '{$name}', {$zone});";
        $rc = db2_exec($conn, $insert);
        if ($rc) {
            echo "Insert Successful";
        } else {
            die('Critical error: ' . db2_stmt_error($rc));
        }
        db2_free_stmt($stmt);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg($conn);
    }
}

function exportfood()
{
    if (isset($_POST)) {
        $food_id = $_POST['food_id2'];
        $food_name = $_POST['food_name2'];
        $food_type = $_POST['food_type2'];
        $out_amount = $_POST['out_amount'];
        $username = $_SESSION['current_user_name'];
    }
    $conn = dbConnect();
    if ($conn) {
        $sql0 = "SELECT AMOUNT FROM EMM_ZOO.FM_STOCK WHERE FOODID = {$food_id};";
        $stm = dbQuery($conn, $sql0);
        while ($row = dbFetchArray($conn, $stm)) {
            $amountx = $row[0];
        }
        $amount_now = $amountx - $out_amount;
        if ($amount_now > 0) {
            $sql1 = "UPDATE EMM_ZOO.FM_STOCK SET AMOUNT = {$amount_now} WHERE FOODID = {$food_id};";
            $cz = db2_exec($conn, $sql1);
            $sql2 = "INSERT INTO EMM_ZOO.FOOD_GIVE(GIVENO,FOODID,EMPID,FOODNAME,FOODTYPE,AMOUNT,GIVETIME,ANIMAL) VALUES (DEFAULT,'{$food_id}','5678','{$food_name}','{$food_type}',{$out_amount},NULL,NULL);";
            $cb = db2_exec($conn, $sql2);
            if ($cb && $cz) {
                $resultMessage = 1;
                return $resultMessage;
                header('Location: FoodStock.php#food_list');
            } else {
                $resultMessage = 0;
                return $resultMessage;
            }
            db2_free_stmt($stmt);
            db2_close($conn);
        } else {
            echo "<script language='javascript'>alert('Not enough food!');</script>";
        }
    }
}

function addEmp2()
{
    if (isset($_POST)) {
        $EmpID = $_POST['employeeAdd'];
        // an array that want to insert this can be multiple array at the time.
    }
    $conn = dbConnect();
    if ($conn) {
        $sql = "INSERT INTO EMM_ZOO.MAINTEGERAINPERSON (MPSNO, EMPID, REQUESTID) VALUES ({$EmpID}, {$EmpID} ,0);";
        //$sql2 = 'INSERT INTO EMM_ZOO.EMP_SANI (EMPID) VALUES (?);';
        //echo $sql;
        // prepare statement using connection and sql
        // If statement is valid execute it to db2
        //echo "SQL is valid<br>";
        $result = db2_exec($conn, $sql);
        if ($result) {
            $resultMessage = "Successfully added to maintainance personel";
            //echo "Successfully added";
            echo "<script>";
            echo "alert('Added successfully')";
            echo "</script>";
            header("Refresh:0; url=team.php");
            exit;
        } else {
            $resultMessage = "Failed to query into database";
            echo "<script>";
            echo "alert('Failed to query into database')";
            echo "</script>";
            header("Refresh:0; url=team.php");
        }
        db2_free_stmt($result);
        db2_close($conn);
    } else {
        echo db2_conn_errormsg();
    }
}