Need more distinct results than SELECT DISTINCT provides

[new7media]

I have a website that will showcase various publications. One publication will be highlighted on the homepage, and then 6 related publications will show alongside the highlighted one. The publications are related by topic and each publication can be categorized under a max of 3 topics. For example, if the highlighted pub is categorized under "health", "technology", and "policy", I then go into the database to find two publications that are categorized under "health", two more categorized under "tech", and two more under "policy".

The code below works fine EXCEPT when related publications are categorized under more than one of the highlighted publication's categories. Let's say Publication A is categorized under "health" and "technology". The results I get look like this - which I don't want:

Health
Publication A <
Publication B

Technology
Publication A <
Publication C

Policy
Publication D
Publication E

I'm not sure how to code my database query so that it excludes a publication ID if it's already been selected in a previous category. Any help -- REALLY appreciated! Thanks! Here's my code:

/*
We already have what we need for the highlighted publication, including what topics it's under. Now we figure out how many topics we are dealing with and then limit the query for related publications to 6, 3, or 2 depending on the number of topics
*/

$row_count_topic = mysql_num_rows($result_topic);
if ($row_count_topic == '1') {
	$limit_to = '6';
} elseif ($row_count_topic == '2') {
	$limit_to = '3';
} else {
	$limit_to = '2';
}

while ($row_topic = mysql_fetch_array($result_topic)) {
	$topic_id = $row_topic['topic_id'];
	$topic_name = $row_topic['topic_name'];

// Now get related publications for each topic
	$sql = "SELECT DISTINCT 
	content.publications.pid AS pid, 
	content.publications.title AS title, 
	FROM 
	content.publications, 
	content.topics, 
	WHERE 
	content.topics.topic_id = '$topic_id' 
	AND 
	content.topics.pub_id = content.publications.pid 
	AND 
	content.publications.highlighted != '1' 
	LIMIT 0,$limit_to";

$result_related = mysql_query($sql);
$row_count_related = mysql_num_rows($result_related);
if ($row_count_related > 0) {
	echo ("<p>$topic_name</p>"); 
	while ($row_related = mysql_fetch_array($result_related)) {
		$title_related = $row_related['title'];
		echo ("<P><b>$title_related</b></p>");
	}
}
}

[paulnaj]

Hi,

That's going to be a pretty nasty bit of SQL!!

And since, in the end, you're only looking at a few returns, you could just do it in PHP. The way to go would be to have an arry() that stores all of the 'used' publications and which you can check against before including ...

<?php
$row_count_topic = mysql_num_rows($result_topic); 
if ($row_count_topic == '1') { 
    $limit_to = '6'; 
} elseif ($row_count_topic == '2') { 
    $limit_to = '3'; 
} else { 
    $limit_to = '2'; 
} 

// Declare a 'used' list
$used_publications = array()

while ($row_topic = mysql_fetch_array($result_topic)) { 
    $topic_id = $row_topic['topic_id']; 
    $topic_name = $row_topic['topic_name']; 

// Now get related publications for each topic 
    $sql = "SELECT DISTINCT  

    content.publications.pid AS pid, 
    content.publications.title AS title, 
    FROM  

    content.publications, 
    content.topics, 
    WHERE  

    content.topics.topic_id = '$topic_id' 
    AND  

    content.topics.pub_id = content.publications.pid  

    AND  

    content.publications.highlighted != '1' 
    LIMIT 0,$limit_to"; 

$result_related = mysql_query($sql); 
$row_count_related = mysql_num_rows($result_related); 
if ($row_count_related > 0){
	$title_related = array();
    while ($row_related = mysql_fetch_array($result_related)) { 
		// Only show it if it hasn't been shown before ...
		if(!in_array([$row_related['pid'], $used_publications)){
			$title_related[] = '<p><b>'.$row_related['title'].'</b></p>';

			// Remember to add this one to the used list
			$used_publications[] = $row['pid'];
		}
	}
	// Might be the case that all have been used and $title_related is empty !!
	if(count($title_related)){
		echo '<p>'.$topic_name.'</p>'.join('', $title_related);  
	}
}
}
?>

Paul 🙂

[new7media]

Thanks for this. I'm going to try it now.

My only concern is that your code maybe won't maximize the number of related publications that could be shown.

Rather than not showing 2 publications per category if one of them was already displayed in a previous category, I want to go down the list of possible publications and ensure that I'm filling the void by showing a different publication. If that makes sense?

I am just trying, unsuccessfully, the original related pubs query to get all of the pub id's.

And then doing that same query again but this time adding a line to the query that goes: AND content.publication.pid != (one of the pid's i stored in an array after the first query).

Like I said - not really working out. I'll give yours a shot. Thanks!