how can I connect these two tables in sql?
i need to take only the items from the table "__sobi2_item" that are in the same country of the user.And use this results for the rest of the function Showupdatelisting. This is my php script:
<?php
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_d开发者_运维问答ata` WHERE (`fieldid` = 6) AND ('data_txt' = {$usercountry})";
$database->setQuery($query);
$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";
$database->setQuery($query);
$sids = $database->loadResultArray();
// ......... show update function goes on...
?>
can anyone help me to connect and adjust these query? thanks.
NB:with the last query (4) i need to filter items of the $ResultsArray taking only ones published and ordering them by last_update. i know it is wrong and now there is no connection with the query before. This is how i have tables in mysql:
_sobi2_fields_data:
itemid fieldid data_txt --->(is a description column for each field)
_sobi2_item:
itemid published --->( 1 if true, 0 if false ) last_update --->(date of the last update for the item, also equal to the publication date if there are no changes)
thanks.
I don't know what you are trying to ask as well. Your last query (number 4) doesn't make sense to me, how is it linked to the above queries?
[EDIT] I've linked your 4th table above assuming itemid is the primary key for the items in sobi2_item table and that the value is linked to the sobi_fields_data table via itemid.
SELECT
cf.id,
sfd.data_txt,
sfd.itemid,
sfd.fieldid
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
Good luck!
精彩评论