help on my mysql query

[pixeltrace]

Hi,

 

i need help, i have a page that should display all the list of benefits.

whats happening with my current code is that it only displays the latest benefit item.

how can i fix this?

this is the site

http://www.mumcentre.com.my/index.php?option=com_classifieds&act=benefits

try to click the "click here to see the complete list of benefits...." let say for bebehaus.

 

notice at the bottom part that there are 2 benfits for this one.

hope you could help me fix my query in such a way that i will display all the benefits from the listing page

below is my current query

$database->setQuery(" SELECT (benefits.`provider`) $query ORDER BY benefits.`id` DESC LIMIT $pageNav->limitstart, $pageNav->limit ");
$providers	= $database->loadObjectList();
foreach ($providers as $provider) {
	$query = "SELECT providers.name AS `providername`, benefits.* "
	. " FROM `#__classifieds_providers_benefits` benefits "
	. " LEFT JOIN `#__classifieds_providers` providers "
	. " ON benefits.provider = providers.id "
	. " WHERE `provider` = " . $provider->provider 
	. " AND benefits.`published` = 1"
	. " ORDER BY `dateadded` DESC "
	. " LIMIT 0,1";
	$database->setQuery($query);
	if (!$benefit = $database->query()) { echo $database->stderr(); return; }
	$benefit	= $database->loadObjectList();
	if($benefit)
	$benefits[] = $benefit[0];

 

hope you could help me with this.

 

thanks so much!

[pixeltrace]

sorry, this is the current code

	$database->setQuery(" SELECT DISTINCT(benefits.`provider`) $query ORDER BY benefits.`id` DESC LIMIT $pageNav->limitstart, $pageNav->limit ");
$providers	= $database->loadObjectList();
foreach ($providers as $provider) {
	$query = "SELECT providers.name AS `providername`, benefits.* "
	. " FROM `#__classifieds_providers_benefits` benefits "
	. " LEFT JOIN `#__classifieds_providers` providers "
	. " ON benefits.provider = providers.id "
	. " WHERE `provider` = " . $provider->provider 
	. " AND benefits.`published` = 1"
	. " ORDER BY `dateadded` DESC "
	. " LIMIT 0,1";
	$database->setQuery($query);
	if (!$benefit = $database->query()) { echo $database->stderr(); return; }
	$benefit	= $database->loadObjectList();
	if($benefit)
	$benefits[] = $benefit[0];