i have to take just the products in the table "__sobi2_item" which are within the same country from the user.And employ this recent results for the relaxation from the function Showupdatelisting. This really is my php script:

function showUpdatedListing()

//i found the user country field value...
    global $database;
    $user =& JFactory::getUser();
    $userId = $user->get( 'id' );
    $sql = "SELECT (id) FROM #__community_fields WHERE fieldcode= 'FIELD_COUNTRY'";
    $database->setQuery( $sql );
    $fieldID = $database->loadResult();
    $sql = "SELECT (value) FROM #__community_fields_values WHERE field_id= {$fieldID} && user_id= {$userId}";
    $database->setQuery( $sql );
    $usercountry = $database->loadResult();
// From all the entries i take only ones that have country field like the user has...
    $query = "SELECT `data_txt`, `itemid`, `fieldid` FROM `#__sobi2_fields_data` WHERE (`fieldid` = 6) AND ('data_txt' = {$usercountry})";
    $ResultsArray = $database->loadObjectList();

// We need something here like a Query to load only the entries from $ResultsArray... ??
//....instead of this...
    $config =& sobi2Config::getInstance();
        $database = $config->getDb();
        $now = $config->getTimeAndDate();

        $query = "SELECT itemid FROM #__sobi2_item WHERE (published = 1 AND publish_down > '{$now}' OR publish_down = '{$config->nullDate}') ORDER BY last_update DESC LIMIT 0, 30";
        $sids = $database->loadResultArray();
// ......... show update function goes on...


can anybody assist me to for connecting and adjust these query? thanks.

NB:using the last query (4) i have to filter products from the $ResultsArray taking only ones released and ordering them by last_update. yes, it is wrong and today there's no reference to the query before. This is the way i've tables in mysql:


itemid fieldid data_txt --->(is really a description column for every area)


itemid released --->( 1 if true, if false ) last_update --->(date from the last update for that item, also comparable to the publication date if you will find no changes)


I'm not sure what you're attempting to request too. Your last query (number 4) does not seem sensible in my experience, how's it from the above queries?

[EDIT] I have linked your fourth table above presuming itemid may be the primary key for that products in sobi2_item table which the worthiness is from the sobi_fields_data table via itemid.

FROM #__community_fields cf
INNER JOIN #__community_fields_values cfv ON cf.id=cfv.field_id
INNER JOIN #__sobi2_fields_data sfd ON cfv.value=sfd.data_txt
INNER JOIN #__sobi2_item si ON sfd.itemid=si.itemid
WHERE cf.fieldcode='FIELD_COUNTRY'
AND cfv.user_id=$userId
AND sfd.fieldid=6
AND si.published=1
AND (si.publish_down > '{$now}' OR si.publish_down = '{$config->nullDate}')
ORDER BY si.last_update DESC LIMIT 0, 30

Best of luck!