PHP pg_fetch_all函数代码示例

function order_list_by_time($filter)
{
    //require_once('includes/sql_connection.inc.php');
    global $DBC;
    $result = pg_query_params($DBC, "SELECT \n\t\t\t\t\t\t\t\t\t\t\tdate_trunc(\$1, orderdate) as date_filter, \n\t\t\t\t\t\t\t\t\t\t\tcount(distinct orderid) as numberof_order,\n\t\t\t\t\t\t\t\t\t\t\tcount(distinct customerid) as numberof_customer,\n\t\t\t\t\t\t\t\t\t\t\tcount(distinct prod_id) as numberof_prod,\n\t\t\t\t\t\t\t\t\t\t\tsum(quantity) as quantity_by_time,\n\t\t\t\t\t\t\t\t\t\t\tsum(total) as total_by_time \n\t\t\t\t\t\t\t\t\t\tFROM orders NATURAL JOIN orderlines\n\t\t\t\t\t\t\t\t\t\tGROUP BY date_filter\n\t\t\t\t\t\t\t\t\t\tORDER BY total_by_time DESC", array($filter));
    if ($result) {
        $order_list_by_time = pg_fetch_all($result);
        switch ($filter) {
            case 'day':
                $format = 'D d/M/Y';
                break;
            case 'week':
                $format = 'W M/Y';
                break;
            case 'month':
                $format = 'M/Y';
                break;
            case 'year':
                $format = 'Y';
                break;
            default:
                break;
        }
        for ($i = 0; $i < count($order_list_by_time); $i++) {
            echo '<tr>
	                                                    <td>' . date($format, strtotime($order_list_by_time[$i]['date_filter'])) . '</td>
	                                                    <td>' . $order_list_by_time[$i]['numberof_order'] . '</td>
	                                                    <td>' . $order_list_by_time[$i]['numberof_customer'] . '</td>
	                                                    <td>' . $order_list_by_time[$i]['numberof_prod'] . '</td>                                                    
	                                                    <td>' . $order_list_by_time[$i]['quantity_by_time'] . '</td>
	                                                    <td style="font-weight: bold;" class="price">' . round($order_list_by_time[$i]['total_by_time']) . '</td>
	                                                </tr>';
        }
    }
}

function get_shifts($date, $date_of_week, $time)
{
    $query = "select * from shifts where date= \$1 and day_of_week = \$2 and time = \$3";
    $result = pg_query_params($GLOBALS['db'], $query, array($handle, $shift, $time));
    $rows = pg_fetch_all($result);
    return $rows;
}

 public function advanceSearchForUser($owner, $item_number, $pos_review, $neg_review, $ownerSort, $activitySort)
 {
     $query = "SELECT a.username, a.display_pic, SUM(CASE WHEN a.value_type='review' THEN a.positive END) AS pos_review, SUM(CASE WHEN a.value_type='review' THEN a.negative END) AS neg_review, SUM(CASE WHEN a.value_type='item' THEN a.positive END) AS item FROM augmented_member a WHERE ";
     $ownerCondition = " TRUE ";
     if (isset($owner) && !empty($owner)) {
         $ownerCondition = " a.username LIKE '%{$owner}%' ";
     }
     $itemNumCondition = " TRUE ";
     if (isset($item_number) && !empty($item_number)) {
         $itemNumCondition = " EXISTS(SELECT * FROM augmented_member a1 \n                                            WHERE a1.value_type = 'item' \n                                            AND a1.positive >= {$item_number}\n                                            AND a1.username = a.username) ";
     }
     $posNumCondition = " TRUE ";
     if (isset($pos_review) && !empty($pos_review)) {
         $posNumCondition = " EXISTS(SELECT * FROM augmented_member a2 \n                                            WHERE a2.value_type = 'review' \n                                            AND a2.positive >= {$pos_review}\n                                            AND a2.username = a.username) ";
     }
     $negNumCondition = " TRUE ";
     if (isset($neg_review) && !empty($neg_review) && $neg_review != "unlimited") {
         $negNumCondition = " EXISTS(SELECT * FROM augmented_member a3 \n                                            WHERE a3.value_type = 'review' \n                                            AND a3.negative <= {$neg_review}\n                                            AND a3.username = a.username) ";
     }
     $query = $query . $ownerCondition . " AND " . $itemNumCondition . " AND " . $posNumCondition . " AND " . $negNumCondition . " AND a.account_type = 'member' GROUP BY a.username, a.display_pic ";
     if (isset($ownerSort) && !empty($ownerSort) && isset($activitySort) && !empty($activitySort)) {
         $query = $query . " ORDER BY a.username " . $ownerSort . ", " . "SUM(*) as activity " . $activitySort . ";";
     } else {
         if (isset($ownerSort) && !empty($ownerSort)) {
             $query = $query . " ORDER BY a.username " . $ownerSort . " ";
         }
         if (isset($activitySort) && !empty($activitySort)) {
             $query = $query . " ORDER BY SUM(a.positive) " . $activitySort . " ";
         }
     }
     return pg_fetch_all(pg_query($query));
 }

function getSubsTypeDetailDesc($subscriber_type)
{
    $qry = "select  productname,   productdescription, subcriptionrate as subscriptionrate  \nfrom techmatcher.subscriptiontype where  subscribertype_id =\$1  order by subscriptionrate asc";
    $result = pg_query_params($qry, array($subscriber_type));
    $value = pg_fetch_all($result);
    return $value;
}

 /**
  * Взять информацию по найденным результатам
  *
  * @return array массив с пользователями
  */
 function getRecords($order_by = NULL)
 {
     if ($this->matches) {
         $sql = "SELECT * FROM search_users_simple WHERE id IN (" . implode(', ', $this->matches) . ')';
         if ($order_by) {
             $sql .= " ORDER BY {$order_by}";
         } else {
             if ($this->_sortby && (($desc = $this->_sort == SPH_SORT_ATTR_DESC) || $this->_sort == SPH_SORT_ATTR_ASC)) {
                 $sql .= " ORDER BY {$this->_sortby}" . ($desc ? ' DESC' : '');
             }
         }
         if ($res = pg_query(DBConnect(), $sql)) {
             if (!$order_by && ($this->_sort == SPH_SORT_RELEVANCE || $this->_sort == SPH_SORT_EXTENDED)) {
                 $links = array();
                 $rows = array();
                 while ($row = pg_fetch_assoc($res)) {
                     $links[$row['id']] = $row;
                 }
                 for ($i = 0; $i < count($this->matches); $i++) {
                     $rows[] = $links[$this->matches[$i]];
                 }
             } else {
                 $rows = pg_fetch_all($res);
             }
             return $rows;
         }
     }
     return array();
 }

function get_provider_billing_address($provider_id)
{
    $qry_provider_address = "SELECT address.* FROM techmatcher.serviceprovidertoaddress, techmatcher.address\r\n                           WHERE serviceprovidertoaddress.address_id = address.address_id AND serviceprovidertoaddress.serviceprovider_id = \$1 AND\r\n                                  serviceprovidertoaddress.address_deleted = FALSE AND address.addresstype_id = 4\r\n                           ORDER BY address.country ASC, address.state ASC, address.city ASC, address.addressline1 ASC";
    $result = pg_query_params($qry_provider_address, array($provider_id)) or die(pg_errormessage());
    $result1 = pg_fetch_all($result);
    return $result1;
}

 public function view()
 {
     session_start();
     include 'models/searchModel.php';
     $searchModel = new searchModel();
     $isUserSearch = false;
     $itemArray = false;
     $search = '';
     if (!empty($_REQUEST)) {
         if ($_POST['action'] == 'search' && !empty($_POST['search'])) {
             $search = $_POST['search'];
             $result = $searchModel->searchByItemName($_POST['search']);
             $itemArray = pg_fetch_all($result);
         }
         if ($_REQUEST['action'] == 'searchForItem') {
             $search = $_REQUEST['item'];
             $unavailable_item = empty($_REQUEST['unavailable_item']) ? '' : $_REQUEST['unavailable_item'];
             $result = $searchModel->advanceSearchForItem($_REQUEST['item'], $_REQUEST['owner'], $_REQUEST['category'], $_REQUEST['price_start'], $_REQUEST['price_end'], $_REQUEST['location'], $_REQUEST['date_start'], $_REQUEST['date_end'], $unavailable_item);
             $itemArray = pg_fetch_all($result);
         }
         if ($_POST['action'] == 'searchForUser') {
             $isUserSearch = true;
             $data = $searchModel->advanceSearchForUser($_POST['owner'], $_POST['item_number'], $_POST['pos_review'], $_POST['neg_review'], $_POST['ownerSort'], $_POST['activitySort']);
         }
     }
     include 'views/search.php';
 }

function getQueryResults($fname, $lname, $quiz, $coursecode)
{
    $connectionString = "host=ec2-54-225-101-64.compute-1.amazonaws.com port=5432 dbname=d1nigmib60rp1v user=jykiewmddlbjft password=kRqkD183znoOpPNTlDq6f_Xs29";
    $dbconnection = pg_connect($connectionString);
    $result = pg_query($dbconnection, "SELECT u.fname, u.lname, u.id, r.quizname, q.coursecode, r.score, r.timetaken, r.datetaken\n\t\tFROM result r\n\t\tinner join useraccount u on r.userid=u.id\n\t\tinner join quiz q on r.quizname=q.name\n\t\twhere u.fname like '%{$fname}%'\n\t\tand u.lname like '%{$lname}%'\n\t\tand r.quizname like '%{$quiz}%'\n\t\tand q.coursecode like '%{$coursecode}%'\n\t\torder by score desc");
    return json_encode(pg_fetch_all($result));
}

 public function getall()
 {
     if ($this->resource === false) {
         return;
     }
     return pg_fetch_all($this->resource);
 }

 function queryTable($query)
 {
     $this->connection = pg_connect("{$this->connstring}") or die("Connection failed: " . pg_last_error());
     $result = pg_query($query) or die("Query failed: " . pg_last_error());
     $fetch = pg_fetch_all($result);
     return $fetch;
 }

function getAllResultsGroupedByStudentAndCourse()
{
    $connectionString = "host=ec2-54-225-101-64.compute-1.amazonaws.com port=5432 dbname=d1nigmib60rp1v user=jykiewmddlbjft password=kRqkD183znoOpPNTlDq6f_Xs29";
    $dbconnection = pg_connect($connectionString);
    $result = pg_query($dbconnection, " SELECT fname,lname,emailaddress,coursecode,AVG(score) as average_score,COUNT(*) number_taken\n \t\t\tFROM useraccount LEFT JOIN result LEFT JOIN quiz ON quiz.name=result.quizname ON useraccount.id=result.userid\n\t\t\tGROUP BY useraccount.id,useraccount.fname,useraccount.lname,quiz.coursecode,useraccount.emailaddress\n\t\t\tORDER BY coursecode,AVG(score) desc;");
    return json_encode(pg_fetch_all($result));
}

 public static function query($query, $instance = null)
 {
     if ($instance === null) {
         $instance = Db::$lastInstance;
     }
     $result = pg_query(Db::get($instance), $query);
     return is_bool($result) ? null : pg_fetch_all($result);
 }

function updateProviderBillingAddress($provider_id, $address1, $address2, $city, $state, $zipcode, $phone)
{
    $qry = "select aa.address_id from techmatcher.serviceprovidertoaddress it,techmatcher.address aa where it.serviceprovider_id=\$1 and aa.addresstype_id=4 AND aa.address_id = it.address_id and it.address_deleted=\$2;";
    $r1 = pg_query_params($qry, array($cons_id, 'FALSE'));
    $r2 = pg_fetch_all($r1);
    $address_id = $r2[0]['address_id'];
    pg_query_params("Update techmatcher.address set addressline1 =\$1, addressline2 =\$2,city= \$3, state = \$4, country = \$5, zipcode = \$6, phonenumber= \$7 Where address_id=\$8", array($address1, $address2, $city, $state, 'USA', $zipcode, $phone, $address_id));
}

function getChotelData()
{
    global $dbconn;
    $sql = "select * from chotel";
    $result = pg_query($dbconn, $sql) or die('Query failed: ' . pg_last_error());
    $dataArr = pg_fetch_all($result);
    //return $dataArr;
    echo json_encode($dataArr);
}

 public function select($query)
 {
     $result = call_user_func_array([$this, 'query'], func_get_args());
     $rows = pg_fetch_all($result);
     if (!is_array($rows)) {
         return [];
     }
     return $rows;
 }