Hello all. I have a site running Social Engine 3. I need a little modification done to the code we use and I'm having some problems coding this myself. Our site has a section where members can upload items to a marketplace that we have. The front page already has code to list the last 4 items posted. I need to tweak this so that it only post the last 4 items by UNIQUE user ids. This way 1 member cannot be displayed twice in the latest update section. if(!function_exists('item_get_items')){ function item_get_items($limit=8, $itemtype_id=0, $itemcat_id=0, $featured=0, $sponsored=0, $hot = 0, $user_id=0, $sort="item_dateapproved DESC", $no_cache = false, $criteria=null) { $cache_key = "item_load_items_{$limit}_{$itemtype_id}_{$itemcat_id}_{$featured}_{$sponsored}_{$hot}_{$user_id}_{$sort}_{$no_cache}_{$criteria}"; $item_array = NULL; // CACHING $cache_object = SECache::getInstance('serial'); if( is_object($cache_object) ) { $item_array = $cache_object->get($cache_key); } if( !is_array($item_array) || $no_cache) { $time = time(); $where = "item_search='1' AND item_approved = '1' AND (item_dateexpired = 0 OR item_dateexpired > $time)"; if ($itemtype_id > 0 AND $itemtype_id != 13) { $where .= " AND se_items.item_itemtype_id = '$itemtype_id'"; } if ($itemtype_id == 13) { $where .= " AND (se_items.item_itemtype_id = 3 OR se_items.item_itemtype_id = 4 OR se_items.item_itemtype_id = 5 OR se_items.item_itemtype_id = 6)"; } if ($itemcat_id > 0) { $where .= " AND (se_items.item_itemcat_id='$itemcat_id' OR se_items.itemcat_dependency='$itemcat_id')"; } if ($featured) { $where .= " AND se_items.item_featured='1'"; } if ($sponsored) { $where .= " AND se_items.item_sponsored='1'"; } if ($hot) { $where .= " AND se_items.item_hot='1'"; } if ($user_id > 0) { $where .= " AND se_items.item_user_id='$user_id'"; } if ($criteria) { $where .= " AND ($criteria)"; } //rc_toolkit::debug($where,'where'); $item = new se_item(); $item_array = $item->item_list(0, $limit, $sort, $where, TRUE); // CACHE if( is_object($cache_object) ) { $cache_object->store($item_array, $cache_key); } } return $item_array; } Code (markup): Relevant code: function_items.php. This functioncode is called from our .tpl file. function item_list($start, $limit, $sort_by="item_id DESC", $where=NULL, $details=0, $options=array()) { global $database, $user, $owner, $setting; // BEGIN QUERY $sql = "SELECT se_items.*, se_itemcats.*, se_itemtypes.*, j.*"; if ($this->user_id == 0) { $sql .= ", se_users.user_id, se_users.user_username, se_users.user_photo, se_users.user_fname, se_users.user_lname"; } if ($this->geolocation && $this->geolocation->within_distance) { $sql .= ", ".$this->geolocation->get_distance_fragment()." AS distance"; } $sql .= " FROM se_items LEFT JOIN se_itemcats ON se_itemcats.itemcat_id = se_items.item_itemcat_id LEFT JOIN se_itemtypes ON se_itemtypes.itemtype_id = se_items.item_itemtype_id LEFT JOIN ( SELECT MAX(epaymenttransaction_id) as max_epaymenttransaction_id, epaymenttransaction_item_id as temp_epaymenttransaction_item_id FROM se_epaymenttransactions WHERE epaymenttransaction_item_type = 'item' GROUP BY epaymenttransaction_item_id ) as t2 ON se_items.item_id = t2.temp_epaymenttransaction_item_id LEFT JOIN se_epaymenttransactions j ON se_items.item_id = j.epaymenttransaction_item_id AND t2.max_epaymenttransaction_id = j.epaymenttransaction_id "; if ($this->user_id == 0) { $sql .= " LEFT JOIN se_users ON se_users.user_id=se_items.item_user_id "; } if ($this->geolocation) { $sql .= " LEFT JOIN se_geolocations ON se_items.item_location_postal = geolocation_postal "; } $cs = array($where); if ($this->user_id) { $cs['user'] = "se_items.item_user_id = '{$this->user_id}'"; } if ($this->geolocation && $this->geolocation->within_distance) { $cs['geo'] = $this->geolocation->proximity_distance_search_condition($this->geolocation->within_distance); } $sql .= " ".rc_toolkit::criteria_builder($cs,'AND',true); $sql .= " ORDER BY $sort_by LIMIT $start, $limit"; // rc_toolkit::debug($sql,'item_list sql='); // RUN QUERY $resource = $database->database_query($sql); // GET ITEMS INTO AN ARRAY $item_array = array(); while( $item_info=$database->database_fetch_assoc($resource) ) { if ( ! $item_info['itemcat_id'] ) { $item_info['itemcat_title'] = 11350138; } SELanguage::_preload($item_info['itemcat_title']); SELanguage::_preload($item_info['itemtype_title']); if ( $item_info['itemcat_dependency'] > 0 ) { $item_info['itemcat_parent_title'] = $this->item_cat_title_from_catid($item_info['itemcat_dependency']); } $item_info['item_desc'] = html_entity_decode($item_info['item_desc']); // CREATE OBJECT FOR ITEM $item = new se_item($item_info['user_id']); $item->item_exists = TRUE; $item->item_info = $item_info; $item->item_price(); if ($owner->user_exists != 0 && $owner->user_info[user_id] == $item_info[item_user_id]) { $creator = $owner; } elseif ($user->user_exists != 0 && $user->user_info[user_id] == $item_info[item_user_id]) { $creator = $user; } else { $creator = rc_toolkit::init_se_user_from_data($item_info); } $item_item = array( 'item_creator' => $creator, 'item' => $item ); $item_array[] = $item_item; } // RETURN ARRAY return $item_array; } // END item_list() METHOD Code (markup): Relevant code. This is where the SQL Query is applied from the previous function call. My thought was to try to use Distinct on $sql .= " LEFT OUTER JOIN se_users ON se_users.user_id=se_items.item_user_id "; se_users is the table for the main users. se_items.item_user_id is the id for the items that are uploaded and listed. I do not want duplicate se_items.item_user_id's since I only want to return one item per user for the recent list. I'll paypal $20 to anyone who can tweak this to work like I asked. If it is more complicated than I am thinking we can negotiate price. Thanks for reading.
Just to give you some pointers in case you want to go forward with fixing it yourself - you should look into the MySQL GROUP BY operator.
Thank you, this has been fixed. And you are right ColorWP, it was with using Group By instead of Distinct like I was trying to do. Will study that operator a bit more over the weekend. Thanks for the tip!