PHP sqlsrv_fetch_array函数代码示例

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     // Procura na tabela OCRD (Partner Card)
     $query = "SELECT CardCode, CardName, CardFName, frozenFor, CntctPrsn, Phone1, IndustryC FROM OCRD WHERE " . $filter;
     if (empty($filter)) {
         $query = "SELECT CardCode, CardName, CardFName, frozenFor, CntctPrsn, Phone1, IndustryC FROM OCRD WHERE CardName IS NOT NULL ORDER BY cardName";
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new BusinessPartnerDTO();
         $dto->cardCode = $record['CardCode'];
         $dto->cardName = $record['CardName'];
         $dto->cardFName = $record['CardFName'];
         $dto->inactive = $record['frozenFor'];
         $dto->contactPerson = $record['CntctPrsn'];
         $dto->telephoneNumber = $record['Phone1'];
         $dto->industry = $record['IndustryC'];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 function RetrieveEmployeesByPosition($employeePosition1 = null, $employeePosition2 = null, $employeePosition3 = null)
 {
     $dtoArray = array();
     $subQuery = "SELECT posId FROM OHPS";
     if (!empty($employeePosition1)) {
         $subQuery = $subQuery . " WHERE name LIKE '%" . $employeePosition1 . "%'";
     }
     if (!empty($employeePosition2)) {
         $subQuery = $subQuery . " OR name LIKE '%" . $employeePosition2 . "%'";
     }
     if (!empty($employeePosition3)) {
         $subQuery = $subQuery . " OR name LIKE '%" . $employeePosition3 . "%'";
     }
     $query = "SELECT empID, firstName, middleName, lastName FROM OHEM WHERE position IN (" . $subQuery . ") ORDER BY firstName DESC";
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new EmployeeDTO();
         $dto->empID = $record["empID"];
         $dto->firstName = $record["firstName"];
         $dto->middleName = $record["middleName"];
         $dto->lastName = $record["lastName"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 public function checkUserSession()
 {
     if (!isset($this->SessionID)) {
         return array(false, "Session key empty");
     }
     $SessionID = $this->SessionID;
     $sql = "SELECT Email FROM Sessions WHERE SessionID = ?";
     $params = array($SessionID);
     global $conn;
     if ($conn) {
         $stmt = sqlsrv_query($conn, $sql, $params);
         if ($stmt === false) {
             return array(false, "Connection to server failed.");
         } else {
             if (sqlsrv_has_rows($stmt) > 0) {
                 $email = '';
                 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
                     $email = $row['Email'];
                 }
                 return array(true, $email);
             } else {
                 return array(false, "Failed to authenticate");
             }
         }
     }
 }

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT * FROM ( ";
     $query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, ";
     $query .= "FAB.FirmName AS fabricante, CHAM.id AS numeroChamado, CHAM.tempoAtendimento, MONTH(CHAM.dataAtendimento) AS mesReferencia, YEAR(CHAM.dataAtendimento) AS anoReferencia ";
     $query .= "FROM MYSQL...chamadoServico CHAM ";
     $query .= "JOIN OINS EQP ON CHAM.cartaoEquipamento = EQP.insID ";
     $query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
     $query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
     $query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
     $query .= "              ) LABOREXPENSES ";
     if (isset($filter) && !empty($filter)) {
         $query = $query . " WHERE " . $filter;
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY nomeCliente, serieEquipamento");
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new LaborExpenseDTO();
         $dto->codigoCliente = $record["codigoCliente"];
         $dto->nomeCliente = $record["nomeCliente"];
         $dto->codigoEquipamento = $record["codigoEquipamento"];
         $dto->serieEquipamento = $record["serieEquipamento"];
         $dto->codigoModelo = $record["codigoModelo"];
         $dto->tagModelo = $record["tagModelo"];
         $dto->fabricante = $record["fabricante"];
         $dto->numeroChamado = $record["numeroChamado"];
         $dto->tempoAtendimento = $record["tempoAtendimento"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 public function loadLocations(int $from, int $to, string $nameFilter)
 {
     $datas = $this->model->loadLocationsByIdAndName($from, $to, $nameFilter);
     while ($location = sqlsrv_fetch_array($datas)) {
         new DiscoverRowBuilder($location);
     }
 }

 /**
  * DiscoverRowBuilder constructor.
  * @param array $location
  */
 function __construct(array $location)
 {
     echo '
     <div id="' . $location['id_location'] . '" class="contentLocation">
         <table style="width:100%" class="table" onclick="openlocation(' . $location['id_location'] . ', \'' . $location['name'] . '\')">
         <tr>
         <th>Name:</th>
         <th>Description:</th>
         </tr>
         <tr>
         <td>' . $location['name'] . '</td>
         <td>' . $location['description'] . '</td>
         </tr>
         </table>';
     $filemanager = new FileManager();
     $stmt = $filemanager->getImages($location['id_location']);
     $images = array();
     while ($idImage = sqlsrv_fetch_array($stmt)['id_image']) {
         array_push($images, $idImage);
     }
     if (count($images) > 0) {
         echo '<div class="newSlides">';
         foreach ($images as $image) {
             $fileName = glob('../images/' . $image . '.*')[0];
             echo '<img src="../images/' . $fileName . '"\\>';
         }
         echo '</div>';
     }
     echo '</div>';
 }

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT Address, AddrType, Street, StreetNo, Building, ZipCode, Block, City, State, Country, U_Secretaria FROM CRD1 WHERE " . $filter;
     if (empty($filter)) {
         $query = "SELECT Address, AddrType, Street, StreetNo, Building, ZipCode, Block, City, State, Country, U_Secretaria FROM CRD1 WHERE Address IS NOT NULL";
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new PartnerAddressDTO();
         $dto->addressLabel = $record["Address"];
         $dto->addrType = $record["AddrType"];
         $dto->street = $record["Street"];
         $dto->streetNo = $record["StreetNo"];
         $dto->building = $record["Building"];
         $dto->zipCode = $record["ZipCode"];
         $dto->block = $record["Block"];
         $dto->city = $record["City"];
         $dto->state = $record["State"];
         $dto->country = $record["Country"];
         $dto->locationRef = $record["U_Secretaria"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 public function Query($Procedimiento, $RetornaDatos, $arrayValores = "")
 {
     $parametros = array();
     $NombreServidor = constant("sqlHost");
     $InfoConexion = array("UID" => constant("sqlUsuario"), "PWD" => constant("sqlContrasena"), "Database" => $this->NombreBaseDatos);
     $conn = sqlsrv_connect($NombreServidor, $InfoConexion);
     if (is_array($arrayValores)) {
         $stringInterrogacion = "(?";
         for ($i = 1; $i < count($arrayValores); $i++) {
             $stringInterrogacion .= ",?";
         }
         $stringInterrogacion .= ")";
         $Procedimiento = "{call " . $Procedimiento . " " . $stringInterrogacion . "}";
         for ($i = 0; $i < count($arrayValores); $i++) {
             array_push($parametros, array($arrayValores[$i], SQLSRV_PARAM_IN));
         }
     } else {
         $Procedimiento = "{call " . $Procedimiento . "}";
     }
     $stmt3 = sqlsrv_query($conn, $Procedimiento, $parametros);
     if ($RetornaDatos) {
         $array = array();
         while ($obj = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) {
             $array[] = $obj;
         }
         return $array;
         sqlsrv_free_stmt($stmt3);
     }
     sqlsrv_close($conn);
 }

 function RetrieveOther($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT OINV.Serial, INV1.Usage, OINV.CardCode, OINV.CardName, OINV.DocTotal, ORCT.CashSum, ORCT.CheckSum, ORCT.TrsfrSum, ORCT.DocDueDate, OSLP.SlpCode, OSLP.SlpName, OINV.U_demFaturamento FROM ORCT ";
     $query .= "JOIN RCT2 ON RCT2.DocNum = ORCT.DocEntry ";
     $query .= "JOIN OINV ON OINV.DocEntry = RCT2.DocEntry ";
     $query .= "JOIN INV1 ON INV1.DocEntry = OINV.DocEntry ";
     $query .= "JOIN OSLP ON OSLP.SlpCode = OINV.SlpCode ";
     if (!empty($filter)) {
         $query .= " WHERE " . $filter;
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new InvoicePaymentDTO();
         $dto->serial = $record['Serial'];
         $dto->tipo = $record['Usage'];
         $dto->cardCode = $record['CardCode'];
         $dto->cardName = $record['CardName'];
         $dto->valorNotaFiscal = $record['DocTotal'];
         $dto->valorDinheiro = $record['CashSum'];
         $dto->valorCheque = $record['CheckSum'];
         $dto->valorDeposito = $record['TrsfrSum'];
         $dto->date = $record['DocDueDate'];
         $dto->slpCode = $record['SlpCode'];
         $dto->slpName = $record['SlpName'];
         $dto->demFaturamento = $record['U_demFaturamento'];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

function GetFacultyQuestions($groupid)
{
    include_once 'db_Connection.php';
    $conn = sqlsrv_connect($serverName, $connectionInfo);
    $questionvalues = array();
    $questions = array();
    $faculty = array();
    $facultyquestions = array();
    if ($conn) {
        //----get acadyear-------------------
        $acid = 0;
        $sqlstr = " SELECT [AcadYearID] FROM [dbo].[Groups] where [groupid]=? ";
        $params = array($groupid);
        $sqlquery = sqlsrv_query($conn, $sqlstr, $params);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_array($sqlquery, SQLSRV_FETCH_ASSOC)) {
                $acid = $row['AcadYearID'];
            }
            sqlsrv_free_stmt($sqlquery);
        }
        $sqlstr = " SELECT [questionTypeId],[value],[text] " . " FROM [dbo].[QuestionValues] " . " order by questionTypeId ";
        //  $params = array ($acid);
        $sqlquery = sqlsrv_query($conn, $sqlstr);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_object($sqlquery)) {
                $questionvalues[] = $row;
            }
            sqlsrv_free_stmt($sqlquery);
        }
        //----get question array-------------------
        $sqlstr = " SELECT [QuestionID],[QueastionText],[questionType],[maxmark],-1 as mark,'' as description " . " FROM [dbo].[Questions] " . " where [QuestionLecturer]=0 " . " and [Acadyear]=? ";
        $params = array($acid);
        $sqlquery = sqlsrv_query($conn, $sqlstr, $params);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_object($sqlquery)) {
                $row->questionValues = array();
                foreach ($questionvalues as &$questValue) {
                    if ($questValue->questionTypeId === $row->questionType) {
                        array_push($row->questionValues, $questValue);
                    }
                }
                $questions[] = $row;
            }
            sqlsrv_free_stmt($sqlquery);
        }
        //----get faculty-------------------
        $sqlstr = " SELECT [FacultyID],[FacultyName] FROM [dbo].[Groups] " . " where [groupid]=? ";
        $params = array($groupid);
        $sqlquery = sqlsrv_query($conn, $sqlstr, $params);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_object($sqlquery)) {
                $row->quests = $questions;
                $faculty[] = $row;
            }
            $facultyquestions = array("result" => 0, "data" => $faculty);
        }
        sqlsrv_close($conn);
    }
    return $facultyquestions;
}

/**
 * Dumps the Founder data into a table
 *
 * @param int $CompanyID
 */
function dumpFounderData($CompanyID)
{
    // include the data access class
    include_once "DAO.php";
    try {
        // create a new data access object
        $db = new DAO();
        $sql = "EXEC dbo.GetFounderByCompanyID @CompanyID = " . $CompanyID;
        // set the select statement
        $db->setSQL($sql);
        // execute the SQL
        if ($db->execute()) {
            // did we get some rows
            if (sqlsrv_has_rows($db->getResultSet())) {
                // output the table and the first row (column headers)
                echo '<br>';
                echo '<table class="sorted table-autosort:0 table-stripeclass:alternate">';
                echo "<thead><tr>";
                echo "<th class='table-sortable:default' width='100'>Name</th>";
                echo "</tr></thead><tbody>";
                // output the table rows
                while ($row = sqlsrv_fetch_array($db->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                    echo '<tr><td class="left" width="70"><a href="../Participant/History.php?wfID=' . $row['ID'] . '" target="_blank">' . $row['FounderName'] . '</a></td></tr>';
                }
                // finish the table
                echo "</tbody></table>";
            } else {
                echo "<div class='err'>No data found.</div>";
            }
        }
    } catch (Exception $e) {
        echo $e->getMessage(), "\n";
    }
    echo "</br>";
}

 private function fetch_next()
 {
     if ($row = sqlsrv_fetch_array($this->rsrc, SQLSRV_FETCH_ASSOC)) {
         $row = array_change_key_case($row, CASE_LOWER);
     }
     return $row;
 }

/**
 * Execute Query to obtain one or more objects from the NECLIMS db; returns string on error
 *
 * @param $sql
 * @param optional class specification
 */
function query($sql, $object = NULL)
{
    // include object class if specifed
    if ($object != NULL) {
        require_once $object . ".cls.php";
    }
    // create a data access object
    $dao = new DAO();
    // pass the sql statement to the data access object
    $dao->setSQL($sql);
    // declare an array for storing the row results
    $retVal = array();
    try {
        // run the sql statement
        if ($dao->execute() && sqlsrv_has_rows($dao->getResultSet())) {
            // object specified.
            if ($object != NULL) {
                // while there were more results/rows, save the object in the array
                while ($row = sqlsrv_fetch_object($dao->getResultSet(), $object . "")) {
                    $retVal[] = $row;
                }
            } else {
                // while there were more results/rows, save the object in the array
                while ($row = sqlsrv_fetch_array($dao->getResultSet(), SQLSRV_FETCH_ASSOC)) {
                    $retVal[] = $row;
                }
            }
        }
    } catch (Exception $e) {
        return "Query Error: " . $e->getMessage() . ". SQL: " . $sql . ". Object specified: " . $object;
    }
    // return to the caller
    return $retVal;
    //error_log(print_r($retVal, true));
}

function processWalking($results, $fileRow)
{
    global $csvData;
    global $sqlDB;
    global $plat;
    //this is where I'm going to do things
    foreach ($results as $result) {
        $startDate = $result['DateBegan'];
        $endDate = $result['DateStopped'];
        $diff = date_diff($startDate, $endDate);
        $days = $diff->format("%a");
        $currentDate = $result['DateBegan'];
        $formattedDate = date_format($currentDate, 'm/d/y');
        $tempData = array();
        while ($currentDate <= $endDate) {
            $dayDate = $formattedDate . ' ' . "00:00:00";
            $endDay = $formattedDate . ' ' . "23:59:59";
            $querry = "SELECT TOP 1 RecID FROM vPhoneData1\n\t\t\t\tWHERE (TaskSetName = '1 PING' or TaskSetName =\n\t\t\t\t '3 REPORT CGI_OR_H2RL') AND LocalTimetag1 > ? and\n\t\t\t\t LocalTimetag1 < ? and cast(FORMDATA as varchar(max)) like\n\t\t\t\t '%" . $plat . "%'";
            $stmt = sqlsrv_prepare($sqlDB, $querry, array(&$dayDate, &$endDay));
            sqlsrv_execute($stmt);
            $fetched = sqlsrv_fetch_array($stmt);
            if (!empty($fetched)) {
                $formattedDate = date_format($currentDate, 'm/d/y');
                $fileRow['Survey Dt'] = $formattedDate;
                $fileRow['Completed By'] = $result['TechName'];
                $fileRow['Instrument'] = $result['Instrument'];
                $fileRow['Rate'] = $result['RateCode'];
                $fileRow['Survey Method'] = 'WALK';
                $tempData[] = $fileRow;
            }
            $currentDate = date_add($currentDate, date_interval_create_from_date_string('1 days'));
            $formattedDate = date_format($currentDate, 'm/d/y');
            $fetched = null;
        }
        if (!empty($tempData)) {
            $daycount = count($tempData);
            if ($result[6] > 0) {
                $dailyTotal = floor($result[6] / $daycount);
                $firstTotal = $dailyTotal + $result[6] % $daycount;
                $isFirst = TRUE;
            } else {
                $dailyTotal = 0;
            }
            foreach ($tempData as $tempRow) {
                if ($isFirst === TRUE) {
                    $tempRow['Quantity'] = $firstTotal;
                    $isFirst = FALSE;
                } elseif ($dailyTotal > 0) {
                    $tempRow['Quantity'] = $dailyTotal;
                } else {
                    $tempRow['Quantity'] = 0;
                }
                if ($tempRow['Quantity'] > 0) {
                    $csvData[] = $tempRow;
                }
            }
        }
    }
}

 public static function fetch_array($queryDB = '', $objectStr = '', $fetchType = 'SQLSRV_FETCH_ASSOC')
 {
     $row = sqlsrv_fetch_array($queryDB, $fetchType);
     if (is_object($objectStr)) {
         $objectStr($row);
     }
     return $row;
 }