PHP Resource::query方法代码示例

 /**
  * 连接数据库
  *+---------------
  * @param Void
  * @return Boolean
  */
 private function dblink()
 {
     if (!is_object($this->identity)) {
         try {
             @($this->identity = new Pdo('mysql:host=' . $this->config['hostname'] . ';dbname=' . $this->config['database'], $this->config['username'], $this->config['password']));
         } catch (PDOException $e) {
             throw new Ada_Exception($e->getMessage());
         }
         $this->identity->query("SET NAMES '{$this->config['charset']}'");
         // set charset
     }
     return TRUE;
 }

 /**
  * Get all resources associated with the current tag.
  *
  * @return array
  * @access public
  */
 public function getResources()
 {
     $resList = array();
     $sql = 'SELECT "resource".* FROM "resource_tags", "resource" ' . 'WHERE "resource"."id" = "resource_tags"."resource_id" ' . 'AND "resource_tags"."tag_id" = ' . "'" . $this->escape($this->id) . "'";
     $res = new Resource();
     $res->query($sql);
     if ($res->N) {
         while ($res->fetch()) {
             $resList[] = clone $res;
         }
     }
     return $resList;
 }

 function getResources()
 {
     $resList = array();
     $sql = "SELECT resource.* FROM resource_tags, resource " . "WHERE resource.id = resource_tags.resource_id " . "AND resource_tags.tag_id = '{$this->id}'";
     /** @var Resource|object $res */
     $res = new Resource();
     $res->query($sql);
     if ($res->N) {
         while ($res->fetch()) {
             $resList[] = clone $res;
         }
     }
     return $resList;
 }

 /**
  * Creates an array of column names and returns the array, disregards any auto increment columns such as primary keys
  *
  * @param string               $table the current table to analyze
  * @return array
  */
 public function fields($table)
 {
     try {
         $rows = $this->databaseHandler->query("SHOW COLUMNS FROM " . addslashes($table));
         $fields = array();
         if ($rows != NULL) {
             foreach ($rows as $row) {
                 if ($row['Extra'] == "auto_increment") {
                     continue;
                 }
                 $fields[] = $row['Field'];
             }
         }
         return $fields;
     } catch (Exception $e) {
         $this->sqlError("Query Error - " . $e->getMessage());
     }
 }

 /**
  * Shot a query to database and return raw data
  *
  * @param   string  $query
  *
  * @return  mixed
  * 
  * @throws  \Comodojo\Exception\DatabaseException
  */
 public function rawQuery($query)
 {
     switch ($this->model) {
         case "MYSQLI":
             $response = $this->dbh->query($query);
             if (!$response) {
                 throw new DatabaseException($this->dbh->error, $this->dbh->errno);
             }
             break;
         case "MYSQL_PDO":
         case "ORACLE_PDO":
         case "SQLITE_PDO":
         case "DBLIB_PDO":
             try {
                 $response = $this->dbh->prepare($query);
                 $response->execute();
             } catch (\PDOException $e) {
                 throw new DatabaseException($e->getMessage(), (int) $e->getCode());
             }
             break;
         case "DB2":
             $response = db2_exec($this->dbh, $query);
             if (!$response) {
                 throw new DatabaseException(db2_stmt_error());
             }
             break;
         case "POSTGRESQL":
             $response = pg_query($this->dbh, $query);
             if (!$response) {
                 throw new DatabaseException(pg_last_error());
             }
             break;
         default:
             throw new DatabaseException('Invalid database model');
             break;
     }
     return $response;
 }

 /**
  * Load information from the resource table associated with this user.
  *
  * @param array $tags Array of tags to use as a filter (optional).
  *
  * @return array
  * @access public
  */
 public function getResources($tags = null)
 {
     $resourceList = array();
     $sql = 'SELECT DISTINCT "resource".*, "user_resource"."saved" FROM "resource", "user_resource" ' . 'WHERE "resource"."id" = "user_resource"."resource_id" ' . 'AND "user_resource"."user_id" = ' . "'" . $this->escape($this->id) . "'";
     if ($tags) {
         for ($i = 0; $i < count($tags); $i++) {
             $sql .= ' AND "resource"."id" IN ' . '(SELECT DISTINCT "resource_tags"."resource_id" ' . 'FROM "resource_tags", "tags" WHERE ' . '"resource_tags"."tag_id"="tags"."id" AND "tags"."tag" = ' . "'" . $this->escape($tags[$i]) . "'" . ' AND "resource_tags"."user_id" = ' . "'" . $this->escape($this->id) . "')";
         }
     }
     $resource = new Resource();
     $resource->query($sql);
     if ($resource->N) {
         while ($resource->fetch()) {
             $resourceList[] = clone $resource;
         }
     }
     return $resourceList;
 }

 /**
  * Given an array of item ids, remove them from a list
  *
  * @param array  $ids    IDs to remove from the list
  * @param string $source Type of resource identified by IDs
  *
  * @return bool          True on success, false on error.
  * @access public
  */
 public function removeResourcesById($ids, $source = 'VuFind')
 {
     $sqlIDS = array();
     foreach ($ids as $id) {
         if (!empty($id)) {
             $sqlIDS[] = "'" . $this->escape($id) . "'";
         }
     }
     // No work is needed if we have no IDs to delete:
     if (empty($sqlIDS)) {
         return true;
     }
     // Get Resource Ids
     $sql = 'SELECT "id" FROM "resource" WHERE ("record_id" = ' . implode(' OR "record_id" = ', $sqlIDS) . ") ";
     // Don't use source here, MetaLib records would fail
     // . 'AND "source" = ' . "'" . $this->escape($source) . "'";
     $resources = new Resource();
     $resources->query($sql);
     if ($resources->N) {
         while ($resources->fetch()) {
             $resourceList[] = "'" . $this->escape($resources->id) . "'";
         }
     }
     // Remove Resource
     $sql = 'DELETE FROM "user_resource" ' . "WHERE \"user_id\" = '" . $this->escape($this->user_id) . "' " . "AND \"list_id\" = '" . $this->escape($this->id) . "' " . 'AND ("resource_id" =' . implode(' OR "resource_id" =', $resourceList) . ")";
     $removeResource = new User_resource();
     $removeResource->query($sql);
     // Remove Resource Tags
     $sql = 'DELETE FROM "resource_tags" ' . "WHERE \"user_id\" = '" . $this->escape($this->user_id) . "' " . "AND \"list_id\" = '" . $this->escape($this->id) . "' " . 'AND ("resource_id" =' . implode(' OR "resource_id" =', $resourceList) . ")";
     $removeTags = new Resource_tags();
     $removeTags->query($sql);
     // Update list modification date
     $this->updateModifiedDate();
     // If we got this far, there were no fatal DB errors so report success
     return true;
 }

 function launch()
 {
     global $interface;
     global $configArray;
     $interface->setPageTitle('Transfer Account Information');
     if (isset($_REQUEST['submit'])) {
         $message = "";
         $okToTransfer = true;
         //Get the old user id
         $oldUser = new User();
         $oldUser->cat_username = $_REQUEST['oldBarcode'];
         if (!$oldUser->find(true)) {
             $message .= "<p>Sorry, we could not find a user for the old barcode.  Unable to transfer information.</p>";
             $okToTransfer = false;
         }
         //Get the new user id
         $newUser = new User();
         $newUser->cat_username = $_REQUEST['newBarcode'];
         if (!$newUser->find(true)) {
             $message .= "<p>Sorry, we could not find a user for the new barcode.  Unable to transfer information.</p>";
             $okToTransfer = false;
         }
         if ($okToTransfer) {
             require_once ROOT_DIR . '/services/MyResearch/lib/Resource.php';
             require_once ROOT_DIR . '/sys/eContent/EContentRecord.php';
             //Transfer ratings for regular titles
             $message .= "<p>Transfered: <ul>";
             $resource = new Resource();
             $ret = $resource->query("UPDATE user_rating set userid = {$newUser->id} WHERE userid = {$oldUser->id}");
             $message .= "<li>{$ret} Ratings of print titles</li>";
             //Transfer reading history
             $resource = new Resource();
             $ret = $resource->query("UPDATE user_reading_history set userid = {$newUser->id} WHERE userid = {$oldUser->id}");
             $message .= "<li>{$ret} Reading History Entries</li>";
             //Transfer comments
             $resource = new Resource();
             $ret = $resource->query("UPDATE comments set user_id = {$newUser->id} WHERE user_id = {$oldUser->id}");
             $message .= "<li>{$ret} Reviews</li>";
             //Transfer tags
             $resource = new Resource();
             $ret = $resource->query("UPDATE resource_tags set user_id = {$newUser->id} WHERE user_id = {$oldUser->id}");
             $message .= "<li>{$ret} Tags</li>";
             //Transfer lists
             $resource = new Resource();
             $ret = $resource->query("UPDATE user_list set user_id = {$newUser->id} WHERE user_id = {$oldUser->id}");
             $message .= "<li>{$ret} User Lists</li>";
             //Transfer eContent ratings
             $eContentRecord = new EContentRecord();
             $ret = $eContentRecord->query("UPDATE econtent_rating set userId = {$newUser->id} WHERE userId = {$oldUser->id}");
             $message .= "<li>{$ret} Ratings of eContent titles</li>";
             //Transfer eContent checkouts
             $eContentRecord = new EContentRecord();
             $ret = $eContentRecord->query("UPDATE econtent_checkout set userId = {$newUser->id} WHERE userId = {$oldUser->id}");
             $message .= "<li>{$ret} eContent Checkouts</li>";
             //Transfer eContent holds
             $eContentRecord = new EContentRecord();
             $ret = $eContentRecord->query("UPDATE econtent_hold set userId = {$newUser->id} WHERE userId = {$oldUser->id}");
             $message .= "<li>{$ret} eContent Holds</li>";
             //Transfer eContent wishlist
             $eContentRecord = new EContentRecord();
             $ret = $eContentRecord->query("UPDATE econtent_wishlist set userId = {$newUser->id} WHERE userId = {$oldUser->id}");
             $message .= "<li>{$ret} eContent Wish List Entries</li>";
             $message .= "</ul></p>";
         }
         $interface->assign('message', $message);
     }
     $interface->assign('sidebar', 'MyAccount/account-sidebar.tpl');
     $interface->setTemplate('transferAccountInfo.tpl');
     $interface->display('layout.tpl');
 }

 public function getMyTransactions($patron, $page = 1, $recordsPerPage = -1, $sortOption = 'dueDate')
 {
     global $configArray;
     global $timer;
     if (is_object($patron)) {
         $patron = get_object_vars($patron);
     }
     if (isset($this->transactions[$patron['id']])) {
         return $this->transactions[$patron['id']];
     }
     if (!$this->useDb) {
         //Get transactions by parsing hip
         $transactions = $this->getMyTransactionsViaHIP($patron);
         $timer->logTime("Got transactions from HIP");
         //return json_decode('[{"id":843869,"itemid":1466355,"duedate":"2011-03-29 12:00:00","checkoutdate":"2011-03-07 12:00:00","barcode":"33025015826504","renewCount":1,"request":null},{"id":944097,"itemid":1897017,"duedate":"2011-03-28 12:00:00","checkoutdate":"2011-03-07 12:00:00","barcode":"33025021052830","renewCount":0,"request":null},{"id":577167,"itemid":2057415,"duedate":"2011-03-29 12:00:00","checkoutdate":"2011-03-07 12:00:00","barcode":"33025021723778","renewCount":3,"request":null}]', true);
     } else {
         $transactions = $this->getMyTransactionsViaDB($patron);
         $timer->logTime("Got transactions from Database");
     }
     if (isset($transactions)) {
         //Load information about titles from Resources table (for peformance)
         $recordIds = array();
         foreach ($transactions as $i => $data) {
             $recordIds[] = "'" . $data['id'] . "'";
         }
         //Get records from resource table
         $resourceInfo = new Resource();
         if (count($recordIds) > 0) {
             $recordIdString = implode(",", $recordIds);
             $resourceSql = "SELECT * FROM resource where source = 'VuFind' AND record_id in ({$recordIdString})";
             $resourceInfo->query($resourceSql);
             $timer->logTime('Got records for all titles');
             //Load title author, etc. information
             while ($resourceInfo->fetch()) {
                 foreach ($transactions as $key => $transaction) {
                     if ($transaction['id'] == $resourceInfo->record_id) {
                         $transaction['shortId'] = $transaction['id'];
                         //Load title, author, and format information about the title
                         $transaction['recordId'] = $transaction['id'];
                         $transaction['title'] = isset($resourceInfo->title) ? $resourceInfo->title : 'Unknown';
                         $transaction['sortTitle'] = isset($resourceInfo->title_sort) ? $resourceInfo->title_sort : 'unknown';
                         $transaction['author'] = isset($resourceInfo->author) ? $resourceInfo->author : null;
                         $transaction['format'] = isset($resourceInfo->format) ? $resourceInfo->format : null;
                         $transaction['isbn'] = isset($resourceInfo->isbn) ? $resourceInfo->isbn : '';
                         $transaction['upc'] = isset($resourceInfo->upc) ? $resourceInfo->upc : '';
                         $transaction['format_category'] = isset($resourceInfo->format_category) ? $resourceInfo->format_category : '';
                         $transaction['renewIndicator'] = $transaction['barcode'] . '|';
                         $transactions[$key] = $transaction;
                     }
                 }
             }
         }
         //Get econtent info and hold queue length
         foreach ($transactions as $key => $transaction) {
             //Check for hold queue length
             $itemData = $this->_loadItemSIP2Data($transaction['barcode'], '');
             $transaction['holdQueueLength'] = intval($itemData['holdQueueLength']);
             $transactions[$key] = $transaction;
         }
     }
     //Process sorting
     $sortKeys = array();
     $i = 0;
     foreach ($transactions as $key => $transaction) {
         $sortTitle = isset($transaction['sortTitle']) ? $transaction['sortTitle'] : "Unknown";
         if ($sortOption == 'title') {
             $sortKeys[$key] = $sortTitle;
         } elseif ($sortOption == 'author') {
             $sortKeys[$key] = (isset($transaction['author']) ? $transaction['author'] : "Unknown") . '-' . $sortTitle;
         } elseif ($sortOption == 'dueDate') {
             if (preg_match('/.*?(\\d{1,2})[-\\/](\\d{1,2})[-\\/](\\d{2,4}).*/', $transaction['duedate'], $matches)) {
                 $sortKeys[$key] = $matches[3] . '-' . $matches[1] . '-' . $matches[2] . '-' . $sortTitle;
             } else {
                 $sortKeys[$key] = $transaction['duedate'] . '-' . $sortTitle;
             }
         } elseif ($sortOption == 'format') {
             $sortKeys[$key] = (isset($transaction['format']) ? $transaction['format'] : "Unknown") . '-' . $sortTitle;
         } elseif ($sortOption == 'renewed') {
             $sortKeys[$key] = (isset($transaction['renewCount']) ? $transaction['renewCount'] : 0) . '-' . $sortTitle;
         } elseif ($sortOption == 'holdQueueLength') {
             $sortKeys[$key] = (isset($transaction['holdQueueLength']) ? $transaction['holdQueueLength'] : 0) . '-' . $sortTitle;
         }
         $sortKeys[$key] = $sortKeys[$key] . '-' . $i++;
     }
     array_multisort($sortKeys, $transactions);
     //Limit to a specific number of records
     $totalTransactions = count($transactions);
     if ($recordsPerPage != -1) {
         $startRecord = ($page - 1) * $recordsPerPage;
         $transactions = array_slice($transactions, $startRecord, $recordsPerPage);
     }
     $this->transactions[$patron['id']] = $transactions;
     return array('transactions' => $transactions, 'numTransactions' => $totalTransactions);
 }

 function getTags()
 {
     $tagList = array();
     $sql = "SELECT resource_tags.* FROM resource, resource_tags, user_resource " . "WHERE resource.id = user_resource.resource_id " . "AND resource.id = resource_tags.resource_id " . "AND user_resource.user_id = '{$this->user_id}' " . "AND user_resource.list_id = '{$this->id}'";
     /** @var Resource|object $resource */
     $resource = new Resource();
     $resource->query($sql);
     if ($resource->N) {
         while ($resource->fetch()) {
             $tagList[] = clone $resource;
         }
     }
     return $tagList;
 }

 /**
  * Return all resources that the user has saved
  *
  * @param string[] $tags Tags to filter the resources by
  * @return Resource[]
  */
 function getResources($tags = null)
 {
     require_once 'User_resource.php';
     $resourceList = array();
     $sql = "SELECT DISTINCT resource.* FROM resource, user_resource " . "WHERE resource.id = user_resource.resource_id " . "AND user_resource.user_id = '{$this->id}'";
     if ($tags) {
         for ($i = 0; $i < count($tags); $i++) {
             $sql .= " AND resource.id IN (SELECT DISTINCT resource_tags.resource_id " . "FROM resource_tags, tags " . "WHERE resource_tags.tag_id=tags.id AND tags.tag = '" . addslashes($tags[$i]) . "' AND resource_tags.user_id = '{$this->id}')";
         }
     }
     /** @var Resource|object $resource */
     $resource = new Resource();
     $resource->query($sql);
     if ($resource->N) {
         while ($resource->fetch()) {
             $resourceList[] = clone $resource;
         }
     }
     return $resourceList;
 }

 public function getMyHolds($patron = null, $page = 1, $recordsPerPage = -1, $sortOption = 'title')
 {
     global $timer;
     global $configArray;
     global $user;
     $patronDump = $this->driver->_getPatronDump($this->driver->_getBarcode());
     //Load the information from millennium using CURL
     $sResult = $this->driver->_fetchPatronInfoPage($patronDump, 'holds');
     $timer->logTime("Got holds page from Millennium");
     $holds = $this->parseHoldsPage($sResult);
     $timer->logTime("Parsed Holds page");
     //Get a list of all record id so we can load supplemental information
     $recordIds = array();
     foreach ($holds as $holdSections) {
         foreach ($holdSections as $hold) {
             $recordIds[] = "'" . $hold['shortId'] . "'";
         }
     }
     //Get records from resource table
     $resourceInfo = new Resource();
     if (count($recordIds) > 0) {
         $recordIdString = implode(",", $recordIds);
         mysql_select_db($configArray['Database']['database_vufind_dbname']);
         $resourceSql = "SELECT * FROM resource where source = 'VuFind' AND shortId in (" . $recordIdString . ")";
         $resourceInfo->query($resourceSql);
         $timer->logTime('Got records for all titles');
         //Load title author, etc. information
         while ($resourceInfo->fetch()) {
             foreach ($holds as $section => $holdSections) {
                 foreach ($holdSections as $key => $hold) {
                     $hold['recordId'] = $hold['id'];
                     if ($hold['shortId'] == $resourceInfo->shortId) {
                         $hold['recordId'] = $resourceInfo->record_id;
                         $hold['id'] = $resourceInfo->record_id;
                         $hold['shortId'] = $resourceInfo->shortId;
                         //Load title, author, and format information about the title
                         $hold['title'] = isset($resourceInfo->title) ? $resourceInfo->title : 'Unknown';
                         $hold['sortTitle'] = isset($resourceInfo->title_sort) ? $resourceInfo->title_sort : 'unknown';
                         $hold['author'] = isset($resourceInfo->author) ? $resourceInfo->author : null;
                         $hold['format'] = isset($resourceInfo->format) ? $resourceInfo->format : null;
                         $hold['isbn'] = isset($resourceInfo->isbn) ? $resourceInfo->isbn : '';
                         $hold['upc'] = isset($resourceInfo->upc) ? $resourceInfo->upc : '';
                         $hold['format_category'] = isset($resourceInfo->format_category) ? $resourceInfo->format_category : '';
                         //Load rating information
                         $hold['ratingData'] = $resourceInfo->getRatingData($user);
                         $holds[$section][$key] = $hold;
                     }
                 }
             }
         }
     }
     //Process sorting
     //echo ("<br/>\r\nSorting by $sortOption");
     foreach ($holds as $sectionName => $section) {
         $sortKeys = array();
         $i = 0;
         foreach ($section as $key => $hold) {
             $sortTitle = isset($hold['sortTitle']) ? $hold['sortTitle'] : (isset($hold['title']) ? $hold['title'] : "Unknown");
             if ($sectionName == 'available') {
                 $sortKeys[$key] = $sortTitle;
             } else {
                 if ($sortOption == 'title') {
                     $sortKeys[$key] = $sortTitle;
                 } elseif ($sortOption == 'author') {
                     $sortKeys[$key] = (isset($hold['author']) ? $hold['author'] : "Unknown") . '-' . $sortTitle;
                 } elseif ($sortOption == 'placed') {
                     $sortKeys[$key] = $hold['createTime'] . '-' . $sortTitle;
                 } elseif ($sortOption == 'format') {
                     $sortKeys[$key] = (isset($hold['format']) ? $hold['format'] : "Unknown") . '-' . $sortTitle;
                 } elseif ($sortOption == 'location') {
                     $sortKeys[$key] = (isset($hold['location']) ? $hold['location'] : "Unknown") . '-' . $sortTitle;
                 } elseif ($sortOption == 'holdQueueLength') {
                     $sortKeys[$key] = (isset($hold['holdQueueLength']) ? $hold['holdQueueLength'] : 0) . '-' . $sortTitle;
                 } elseif ($sortOption == 'position') {
                     $sortKeys[$key] = str_pad(isset($hold['position']) ? $hold['position'] : 1, 3, "0", STR_PAD_LEFT) . '-' . $sortTitle;
                 } elseif ($sortOption == 'status') {
                     $sortKeys[$key] = (isset($hold['status']) ? $hold['status'] : "Unknown") . '-' . (isset($hold['reactivateTime']) ? $hold['reactivateTime'] : "0") . '-' . $sortTitle;
                 } else {
                     $sortKeys[$key] = $sortTitle;
                 }
                 //echo ("<br/>\r\nSort Key for $key = {$sortKeys[$key]}");
             }
             $sortKeys[$key] = strtolower($sortKeys[$key] . '-' . $i++);
         }
         array_multisort($sortKeys, $section);
         $holds[$sectionName] = $section;
     }
     //Limit to a specific number of records
     if (isset($holds['unavailable'])) {
         $numUnavailableHolds = count($holds['unavailable']);
         if ($recordsPerPage != -1) {
             $startRecord = ($page - 1) * $recordsPerPage;
             $holds['unavailable'] = array_slice($holds['unavailable'], $startRecord, $recordsPerPage);
         }
     } else {
         $numUnavailableHolds = 0;
     }
     if (!isset($holds['available'])) {
         $holds['available'] = array();
     }
//.........这里部分代码省略.........