PHP Dal::query_first方法代码示例

 private static function create($r)
 {
     $relation_attributes = $r['attributes'];
     $inserts = array();
     $args = array();
     foreach (EntityRelation::$columns as $i => $k) {
         if (empty($r[$k])) {
             if ($k == 'end_time') {
                 // end_time may be null
                 continue;
             } else {
                 throw new Exception("Missing entityrelations table column '{$k}'");
             }
         }
         $inserts[] = "{$k}=?";
         $args[] = $r[$k];
     }
     // create it
     Dal::query("INSERT INTO {entityrelations} SET " . implode(",", $inserts), $args);
     // get the id
     $id = Dal::query_first("SELECT id FROM {entityrelations} \n    \tWHERE " . implode(" AND ", $inserts), $args);
     // deal with attributes
     $relation_attributes['created'] = time();
     $relation_attributes['updated'] = time();
     foreach ($relation_attributes as $ak => $d) {
         if (!is_array($d)) {
             $ad = array('value' => $d);
         } else {
             $ad = (array) $d;
         }
         // create it
         Dal::query("INSERT INTO {entityrelationattributes} \n\t\t\tSET id=?, attribute_name=?, attribute_value=?, attribute_permission=?", array($id, $ak, $ad['value'], @$ad['permission']));
     }
     return $id;
 }

 function save()
 {
     $ct = Dal::query_first("SELECT COUNT(*) FROM {fans} WHERE is_active=1 AND user_id=? AND subject_type=? AND subject_id=?", array($this->user_id, $this->subject_type, $this->subject_id));
     $is_new = $ct ? 0 : 1;
     if ($is_new) {
         if (!isset($this->is_active)) {
             $this->is_active = 1;
         }
     }
     $sql = $is_new ? "INSERT INTO {fans} SET " : "UPDATE {fans} SET ";
     $args = array();
     $set_fragments = array();
     foreach (Fan::$columns as $col) {
         switch ($col) {
             case 'fan_id':
                 // never set
                 continue;
             case 'updated':
                 // always set automatically
                 $set_fragments[] = "{$col}=NOW()";
                 break;
             case 'created':
                 // set automatically if new, otherwise copy
                 if ($is_new) {
                     $set_fragments[] = "{$col}=NOW()";
                     break;
                 }
                 // else fallthrough
             // else fallthrough
             default:
                 $set_fragments[] = "{$col}=?";
                 $args[] = $this->{$col};
         }
     }
     $sql .= implode(", ", $set_fragments);
     if (!$is_new) {
         $sql .= " WHERE fan_id=?";
         $args[] = $this->fan_id;
     }
     Dal::query($sql, $args);
     if ($is_new) {
         $this->fan_id = Dal::insert_id();
     }
     /*
         // update denormalization
         Dal::query("UPDATE {items} SET fan_count=(
           SELECT COUNT(*) FROM {fans} WHERE is_active=1 AND subject_type=? AND subject_id=?)
           WHERE is_active=1 AND subject_type=? AND subject_id=?", array(
           $this->subject_type, $this->subject_id,
           $this->subject_type, $this->subject_id));
         Dal::query("UPDATE {users} SET fan_count=(
           SELECT COUNT(*) FROM {fans} WHERE is_active=1 AND user_id=?)
           WHERE is_active=1 AND user_id=?", array(
           $this->user_id, $this->user_id));
     */
     return $this->fan_id;
 }

 public static function get_recent_by_user($uid, $per_page = 10, $page = 1, $is_active_users = 1)
 {
     $per_page = (int) $per_page;
     $page = (int) $page;
     $sql = "SELECT COUNT(*) FROM {reviews} AS R, {users} AS U WHERE U.user_id=R.author_id AND R.is_active=1 AND U.is_active=? AND author_id=?";
     $ct = Dal::query_first($sql, array($is_active_users, $uid));
     $n_pages = (int) ceil($ct / $per_page);
     $start = ($page - 1) * $per_page;
     $sql = "SELECT *, R.created as created FROM {reviews} AS R, {users} AS U WHERE U.user_id=R.author_id AND R.is_active=1 AND U.is_active=? AND author_id=? ORDER BY R.created DESC LIMIT {$start}, {$per_page}";
     $reviews = self::load_many_from_query("Review", $sql, array($is_active_users, $uid));
     return array($reviews, $ct, $n_pages, $page, $per_page);
 }

 /**
     Function to convert the slug to a unique channel_id.
   **/
 public static function convert_slug_to_channel_id($slug)
 {
     Logger::log("Enter: function ChannelComment::convert_slug_to_channel_id");
     $sql = "SELECT channel_id FROM {channel_comment} WHERE slug = ? AND is_active = ?";
     $data = array($slug, ACTIVE);
     $channel_id = Dal::query_first($sql, $data);
     if (empty($channel_id)) {
         $sql = "SELECT max(channel_id) FROM {channel_comment} ";
         $res = Dal::query_first($sql);
         if (empty($res)) {
             $channel_id = 1;
         } else {
             $channel_id = $res + 1;
         }
     }
     return $channel_id;
     Logger::log("Exit: function ChannelComment::convert_slug_to_channel_id");
 }

 public static function get_recent_by_user($uid, $per_page = 10, $page = 1, $is_active_users = 1)
 {
     $per_page = (int) $per_page;
     $page = (int) $page;
     /*
     "SELECT COUNT(*) FROM {comments2} WHERE is_active=1 AND author_id=?"
     */
     $sql = "SELECT COUNT(*) FROM {comments2} AS C2, {users} AS U WHERE U.user_id=C2.author_id AND C2.is_active=1 AND U.is_active=? AND author_id=?";
     $ct = Dal::query_first($sql, array($is_active_users, $uid));
     $n_pages = (int) ceil($ct / $per_page);
     $start = ($page - 1) * $per_page;
     /*
     "SELECT * FROM {comments2} WHERE is_active=1 AND author_id=? ORDER BY created DESC LIMIT $start, $per_page"
     */
     $sql = "SELECT *, C2.created as created FROM {comments2} AS C2, {users} AS U WHERE U.user_id=C2.author_id AND C2.is_active=1 AND U.is_active=? AND author_id=? ORDER BY C2.created DESC LIMIT {$start}, {$per_page}";
     $comments = self::load_many_from_query("Comment2", $sql, array($is_active_users, $uid));
     return array($comments, $ct, $n_pages, $page, $per_page);
 }

 public static function is_network_valid($network_name)
 {
     return Dal::query_first("SHOW TABLES LIKE '" . Dal::quote($network_name) . "_comments'") ? TRUE : FALSE;
 }

 /**
  * loads array of content-id in decreasing order of their edition.
  * @access public
  * @param int user id
  * @param int count
  * @param string sort factor.
  */
 public static function load_content_id_array($user_id = 0, $type = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'created', $direction = 'DESC')
 {
     Logger::log("Enter: Content::load_content_id_array() | Args:  \$user_id = {$user_id}");
     $order_by = $sort_by . ' ' . $direction;
     if ($show == 'ALL' || $cnt == TRUE) {
         $limit = '';
     } else {
         $start = ($page - 1) * $show;
         $limit = 'LIMIT ' . $start . ',' . $show;
     }
     switch ($type) {
         case SBMICROCONTENT_EVENT:
             $sb_pattern = 'event%';
             break;
         case SBMICROCONTENT_REVIEW:
             $sb_pattern = 'review%';
             break;
         case SBMICROCONTENT_PEOPLE:
             $sb_pattern = 'showcase/person%';
             break;
         case SBMICROCONTENT_GROUP:
             $sb_pattern = 'showcase/group%';
             break;
         case VIDEO:
             $sb_pattern = 'media/video%';
             break;
         case AUDIO:
             $sb_pattern = 'media/audio%';
             break;
         case IMAGE:
             $sb_pattern = 'media/image%';
             break;
         default:
             $sb_pattern = NULL;
             break;
     }
     if ($sb_pattern) {
         if ($user_id == 0) {
             $author = " AND C.display_on = " . DISPLAY_ON_HOMEPAGE;
         } else {
             $author = " AND C.author_id = {$user_id}";
         }
         $sql = "SELECT C.content_id As content_id, C.title As title, C.body As body, C.author_id As author_id, C.type as type, C.changed As changed, C.created as created FROM {contents} As C, {contents_sbmicrocontents} As CM, {sbmicrocontent_types} As SM WHERE C.collection_id < 0 AND C.is_active = ? {$author} AND C.type = ? AND CM.content_id = C.content_id AND SM.sbtype_id = CM.microcontent_id AND SM.name LIKE ? ORDER BY {$order_by} {$limit}";
         $data = array(1, 7, $sb_pattern);
     } else {
         if ($type != NULL) {
             $with_type = " AND type={$type}";
         } else {
             $with_type = "";
         }
         if ($user_id == 0) {
             if ($cnt) {
                 return Dal::query_first("SELECT COUNT(*) FROM {contents} WHERE collection_id=-1 AND is_active=1 AND display_on=? {$with_type}", array(DISPLAY_ON_HOMEPAGE));
             }
             $sql = "SELECT content_id, title, body, type, author_id, changed, created FROM {contents} WHERE collection_id = -1 AND is_active = 1 AND display_on = ? {$with_type} ORDER BY {$order_by} {$limit}";
             $data = array(DISPLAY_ON_HOMEPAGE);
             // navigate to blank page when we click on recent post "Full view"
         } else {
             $sql = "SELECT content_id, title, body, author_id, type, changed, name, created FROM {contents} As C LEFT JOIN {content_types} As CT ON C.type=CT.type_id WHERE C.collection_id = -1 AND C.author_id = ? AND C.is_active = 1 {$with_type} ORDER BY {$order_by} {$limit}";
             //$sql = "SELECT content_id, title, body, author_id, changed FROM {contents} WHERE collection_id < 0 AND author_id = ? AND is_active = ?$with_type ORDER BY $order_by $limit";
             $data = array($user_id);
         }
     }
     $res = Dal::query($sql, $data);
     //     print '<pre>'; print_r($res); exit;
     if ($cnt) {
         return $res->numRows();
     }
     $content_id = array();
     if ($res->numRows()) {
         $i = 0;
         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
             $content_id[$i] = array('content_id' => $row['content_id'], 'title' => $row['title'], 'body' => $row['body'], 'author_id' => $row['author_id'], 'type' => $row['type'], 'changed' => $row['changed'], 'created' => $row['created'], 'type_name' => @$row['name']);
             $i++;
         }
     }
     Logger::log("Exit: Content::load_content_id_array()");
     return $content_id;
 }

 public function get_profile_field($information_type, $field_name)
 {
     $cache_key = "profile:{$this->user_id}:{$information_type}:{$field_name}";
     $data = Cache::getValue($cache_key);
     if ($data === NULL) {
         $data = Dal::query_first("SELECT field_value FROM {user_profile_data} WHERE user_id=? AND field_type=? AND field_name=? AND seq IS NULL LIMIT 1", array($this->user_id, $information_type, $field_name));
         Cache::setValue($cache_key, $data);
     }
     return $data;
 }

 public static function get_id_from_task_value($task_value)
 {
     return Dal::query_first("SELECT id FROM {tasks} WHERE task_value=?", array($task_value));
 }

 public static function count_comment($parent_id, $parent_type)
 {
     return Dal::query_first("SELECT COUNT(*) FROM {comments} WHERE is_active=1 AND parent_id=? AND parent_type=? ", array($parent_id, $parent_type));
 }

 public function migrateLegacyFiles($dry_run = TRUE)
 {
     $this->dry_run = $dry_run;
     require_once PA::$path . "/db/Dal/DbUpdate.php";
     echo "Migrating legacy files to new storage system\n";
     $this->all_files = array();
     if (!($h = opendir(PA::$path . '/web/files'))) {
         throw new PAException(GENERAL_SOME_ERROR, "Unable to open web/files directory");
     }
     while (false !== ($f = readdir($h))) {
         if ($f[0] == '.') {
             continue;
         }
         $this->all_files[$f] = TRUE;
     }
     closedir($h);
     $this->unmatched = count($this->all_files);
     $this->unmatchable = 0;
     $this->matched = 0;
     $this->dupes = 0;
     echo "{$this->unmatched} files found\n";
     echo "Matching with user images ...\n";
     $sql = Dal::validate_sql("SELECT user_id,picture FROM {users}", $network);
     $sth = Dal::query($sql);
     while ($r = Dal::row($sth)) {
         list($uid, $pic) = $r;
         // user avatar
         $this->_matchLegacyFile($pic, array("role" => "avatar", "user" => $uid));
         //TODO: user header image
     }
     $this->_dumpMatchResults();
     $networks = DbUpdate::get_valid_networks();
     echo "Processing " . count($networks) . " networks\n";
     foreach ($networks as $network) {
         echo " Network: {$network}\n";
         // network level stuff
         list($network_id, $act, $logo, $extra) = Dal::query_one("SELECT network_id, is_active, inner_logo_image, extra FROM networks WHERE address=?", array($network));
         assert($act);
         // sanity check
         $extra = unserialize($extra);
         // network avatar
         $this->_matchLegacyFile($logo, array("role" => "avatar", "network" => $network_id));
         // network header image
         $header_image = @$extra["basic"]["header_image"]["name"];
         if (!empty($header_image)) {
             $this->_matchLegacyFile($header_image, array("role" => "header", "network" => $network_id));
         }
         // emblems
         foreach (unserialize(Dal::query_first(Dal::validate_sql("SELECT data FROM {moduledata} WHERE modulename='LogoModule'"))) as $emblem) {
             $this->_matchLegacyFile($emblem["file_name"], array("role" => "emblem", "network" => $network_id));
         }
         // group pictures
         $sth = Dal::query(Dal::validate_sql("SELECT collection_id, picture FROM {contentcollections} WHERE type=1 AND is_active=1", $network));
         while ($r = Dal::row($sth)) {
             list($cid, $pic) = $r;
             $this->_matchLegacyFile($pic, array("role" => "avatar", "network" => $network_id, "group" => $cid));
             $header = Dal::query_first(Dal::validate_sql("SELECT header_image FROM groups WHERE group_id=?", $network), array($cid));
             $this->_matchLegacyFile($header, array("role" => "header", "network" => $network_id, "group" => $cid));
         }
         /* disabled until we update peopleaggregator.net
         	    $sth = Dal::query(Dal::validate_sql("SELECT group_id, header_image FROM {groups}", $network));
         	    while ($r = Dal::row($sth)) {
         		list ($gid, $pic) = $r;
         		$this->_matchLegacyFile($network, "group", $gid, $pic);
         	    }
         	    */
         //TODO: advertisements
         // images, audio, video
         foreach (array("image", "audio", "video") as $table) {
             $sth = Dal::query(Dal::validate_sql('SELECT mc.content_id, mc.' . $table . '_file, c.author_id, c.collection_id, c.is_active FROM {' . $table . 's} mc LEFT JOIN {contents} c ON mc.content_id=c.content_id HAVING c.is_active=1', $network));
             while ($r = Dal::row($sth)) {
                 list($cid, $fn, $uid, $ccid, $act) = $r;
                 $this->_matchLegacyFile($fn, array("role" => "media", "network" => $network_id, "content" => $cid));
             }
         }
     }
     $this->_dumpMatchResults();
     foreach ($this->all_files as $fn => $v) {
         if ($v === TRUE) {
             echo " * unmatchable: {$fn}\n";
         }
     }
     echo "Overall results from web/files: ";
     $this->_dumpMatchResults();
 }

 public static function delete($entity_service, $entity_type, $entity_id)
 {
     // get the DB key for the entity
     $id = Dal::query_first("SELECT id FROM {entities} \n    \tWHERE entity_service=? AND entity_type=? AND entity_id=?", array($entity_service, $entity_type, $entity_id));
     if (empty($id)) {
         return false;
     }
     // delete the entity
     Dal::query("DELETE FROM {entities} \n    \tWHERE entity_service=? AND entity_type=? AND entity_id=?", array($entity_service, $entity_type, $entity_id));
     // delete entity attributes
     Dal::query("DELETE FROM {entityattributes} \n    \tWHERE id=?", array($id));
     // delete cache for entity AND entity_attribute
     Cache::flushExtCache(0, "entity:{$entity_service}:{$entity_type}:{$entity_id}");
     Cache::flushExtCache(0, "entity_attributes:{$id}");
     return true;
 }

 /**
  * Function to get the next Video in the album, if exists, from the Video which is currently under view.
  */
 public static function get_next_video($album_id, $video_id, $condition = '')
 {
     Logger::log("Enter: TekVideo::get_next_video");
     if (empty($album_id) || empty($video_id)) {
         throw new PAException(BAD_PARAMETER, 'Invalid arguments');
         Logger::log("Exit: TekVideo::get_next_video");
     }
     $sql = 'SELECT MIN(MV.content_id) FROM {contents} AS C, {media_videos} AS MV WHERE C.collection_id = ? AND C.content_id = MV.content_id AND MV.content_id > ? AND is_active = 1' . $condition . ' ORDER BY created';
     $data = array($album_id, $video_id);
     $content_id = Dal::query_first($sql, $data);
     Logger::log("Exit: TekVideo::get_next_video");
     return $content_id;
 }

 public static function count_testimonials($user_id, $status)
 {
     return Dal::query_first("SELECT COUNT(*) FROM {testimonials} WHERE is_active=1 AND recipient_id=? AND status=? ", array($user_id, $status));
 }

 /** get the list of the networks based on number of members in the network
  * @access public.
  */
 public function get_largest_networks($cnt = FALSE, $show = 'ALL', $page = 1, $sort_by = 'created', $direction = 'DESC')
 {
     Logger::log("Enter: Network::get_largest_networks() ");
     if ($cnt) {
         $ct = Dal::query_first("SELECT COUNT(*) FROM networks WHERE is_active=1 AND type=" . REGULAR_NETWORK_TYPE);
         Logger::log("Exit: Network::get_largest_networks() (counted: count={$ct})");
         return $ct;
     }
     $order_by = $sort_by . ' ' . $direction;
     if ($show == 'ALL' || $cnt == TRUE) {
         $limit = '';
     } else {
         $start = ($page - 1) * $show;
         $limit = 'LIMIT ' . $start . ',' . $show;
     }
     $sql = "SELECT N.member_count AS members, N.*, N.owner_id AS owner_id, N.name as network_name FROM  {networks} AS N WHERE N.type = " . REGULAR_NETWORK_TYPE . " AND N.is_active = 1 ORDER BY {$order_by}  {$limit}";
     $res = Dal::query($sql);
     $network_description = array();
     if ($res->numRows()) {
         while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
             $network_description[] = $row;
         }
     }
     Logger::log("Exit: Network::get_largest_networks() ");
     return $network_description;
 }