Struggling with a query

I'm struggling with queries within queries...

I need to pull out the top 3 publications, but am struggling because the publications are split into 2 tables. They're split into 2 pages because table1 (publication) is the generic holding page of a publication with the info, whereas table 2 (publication_issue) is the individual issues of the 1 publication.

2 Tables

[1] Publication
id
title
text
cat
subcat
user_id
use_ad
viewcount

[2] Publication Issue
id
user_id
publication_id
issue_number
img
pdf
upload_date
cat

I've tried a few ways, the best I got it looking was pulling 4 out (when I limited it to 3) but the first 2 had duplicate viewcount fields.

<?php
//Get most viewed publications
	$resultb = mysql_query("SELECT * FROM publication ORDER BY viewcount DESC");
while($rowb = mysql_fetch_array($resultb))
{
//Check if no results
if(mysql_num_rows($resultb)==0){

}

//There are results, get data
else 
{

$id = $rowb[id];
$cat = $rowb[cat];
$subcat = $rowb[subcat];
$viewcount = $rowb[viewcount];
$title = $rowb[title];

//Get publication info
	$resulth = mysql_query("SELECT * FROM publication_issue WHERE publication_id = '$id' ORDER BY id DESC LIMIT 3");
while($rowh = mysql_fetch_array($resulth))
{
$img = $rowh[img];
$title = $rowh[title];

	$img = $rowh[img];
	echo "<div class=\"featcontainer\">";
	  echo "<div class=\"left\">$viewcount</div>";
	    echo "<div class=\"mid\"><a href=\"#\"><img src=\"/images/uploads/publications/image/big/$img\" height=\"90\" width=\"76\"></a></div>";
		  echo "<div class=\"right1\"><a href=\"\">$title</a></div>";
	echo"</div>";
}}}

?>

PHP:

 

It looks to me that your code shows the the last 3 issues from all publications ordered by viewcount

Do you want to display the top 3 Publications or the top 3 Issues??

 

Thanks for the reply

I want to display the top 3 issues, but some of the attributes such as title are stored in the publication table.

The reason they are split in 2 is because it's a 1 to many. 1 issue has default text, advert and subcategory whereas the issue does not contain text, advert, subcategory.

PUBLICATION
|
ISSUE ISSUE ISSUE

 

do you want to display the top 3 issues for all publications or just for one publication?

 

well either way you need to move viewcount to the issue table and then do something like this

SELECT * FROM publication_issue INNER JOIN publication ON publication_issue.publication_id=publication.id WHERE publcation.id=[PUBLICATION_ID_HERE] ORDER BY publication_issue.viewcount DESC LIMIT 3;

Code (markup):

this should give you the top 3 issues for a specific publication issue

not sure if it is 100% correct though. give it a go in phpmyadmin to see what it returns.

 

Thanks for helping... I'm clueless with INNER JOIN but gave it a go anyway but it doesn't output anything:

$result_pub=mysql_query("
	SELECT * FROM publication
	ORDER BY viewcount DESC

");
$result=mysql_query("SELECT * FROM publication_issue INNER JOIN publication ON publication_issue.publication_id=publication.id WHERE publication.id=".$row_pub[id]." ORDER BY publication_issue.viewcount DESC LIMIT 3");
	while ($row = mysql_fetch_array($result)) {
				echo'	<img
				src="/uploader/',$row['img'],'"
				height="90" width="76"
				alt="" align="left" 
			/>
				';
}

PHP:

 

your code is wrong and my query is wrong

try this

please make sure that publication_issue has also viewcount

$result_pub=mysql_query("
    SELECT * FROM publication
    ORDER BY viewcount DESC

");
while ($res = mysql_fetch_assoc($result_pub)) {
$result=mysql_query("SELECT * FROM publication_issue INNER JOIN publication ON publication_issue.publication_id=publication.id WHERE publication.id=".$res[id]." ORDER BY publication_issue.viewcount DESC LIMIT 3");
    while ($row = mysql_fetch_assoc($result)) {
                echo'   <img
                src="/uploader/',$row['img'],'"
                height="90" width="76"
                alt="" align="left" 
            />
                ';
}
}

PHP:

 

Thanks, a little closer now but still a strange issue.

It's pulling out 2 items 3 times so I changed img to id to double check and I get:

Is the first query messing the 2nd query up?

 

try this one

SELECT pi.id as issue_id, * FROM publication_issue pi INNER JOIN publication p ON pi.publication_id=p.id WHERE p.id=".$res[id]." ORDER BY pi.viewcount DESC LIMIT 3

Code (markup):

then update your code so that where you echo the issue id you echo issue_id instead

 

No output :-(