PHP & SQL connecting to 2 tables and counting rows

Hey there,

Looking for some help with PHP & SQL

What I am trying to achieve is thus;

I would like to create a top 5 list of the most posted category comments.

I need to connect to two tables, loop through one and then make matches with two seperate fields.

Then where a match is found I need to count the rows in the second table.

Once the loop has gone through all rows in the first table and found its matches it then needs to grab the 5 highest numbers pulled back from the row count (im hoping they will be stored in an array ofc)

Once i have the 5 highest row counts from the loop ill will display that info.


$result = mysql_query("SELECT id, title, media_type, score FROM web_data");
while($row = mysql_fetch_object($result)) {
	$id = $row->id;
									
	$result2 = mysql_query("SELECT COUNT(mediaID) AS popular FROM web_users_reviews WHERE mediaID='$id' LIMIT 1")or die(mysql_error());		
	$row2 = mysql_fetch_row($result2);
									
	// return the the amount of rows in the table
	$popular = $row2[0];
	if (popular > 0 ) {
		$max = max($popular);
	}

Sorry if the explanation isnt great, am having trouble explaining it to myself :smiley:

Thanks in advance to any helpers.

Can you show a “CREATE TABLE” for the web_data and web_users_reviews tables?

Hoping thats what you need.

CREATE TABLE web_data (
id NOT NULL AUTO_INCREMENT PRIMARY KEY INT(100),
userID INT(100),
mediaType VARCHAR(255),
title VARCHAR(255)
);

CREATE TABLE web_users_reviews (
id AUTO_INCREMENT PRIMARY INT(100),
userID INT(100),
mediaType VARCHAR(255),
mediaID INT(100),
title VARCHAR(255)
);

You can get a table of the top 5 media ids with the following SQL query


SELECT mediaID, COUNT(mediaID) AS count
FROM  web_users_reviews
GROUP BY mediaID
ORDER BY count DESC 
LIMIT 5

Assuming that mediaID is a foreign key relating to web_data.ID, you should be able to join the two together with something like this:


SELECT id, title, media_type, score
FROM web_data INNER JOIN (
    SELECT mediaID, COUNT(mediaID) AS count
    FROM  web_users_reviews
    GROUP BY mediaID
    ORDER BY count DESC 
    LIMIT 5
) as top_reviews
    ON web_data.id = top_reviews.mediaID

Thank you very much for your help and reply Paul,

I have tried your code and put in the code to call the data back but am only getting one result back, Im hoping it is due to how I am calling said data back, any ideas please?


$result2 = mysql_query("SELECT id, title, media_type FROM web_data INNER JOIN (
	SELECT mediaID, COUNT(mediaID) AS count
	FROM  web_users_reviews
	GROUP BY mediaID
	ORDER BY count DESC
	LIMIT 5
	) AS top_reviews
	ON web_data.id = top_reviews.mediaID")or die(mysql_error());
$row2 = mysql_fetch_object($result2);

echo "<td class='contentboxTitle'>".$row2->title."</td>";

Again thanks in advance

Is the assumption that mediaID is a foreign key relating to web_data.ID correct?

Yes, you got that assumption spot on. Appologies in not convaying that prior.

Your original PHP code uses media_type, whereas the table data you posted shows mediaType.
Which is right?

The PHP code also used score, but that’s not in the table data that you posted.

Assuming that media_type should be mediaType and that score doesn’t exist, that changes the first line of my posted code instead to:


SELECT id, title, mediaType
...

Yes sorry about that, the code i posted was with a typo :frowning:

I think I have managed to get one step closer now as ive learnt how to call back the data correctly.

I understand

mysql_fetch_row

is required to get the row count back so it can be stored as a Var?

To get the data back as mentioned earlier I am using

$row2 = mysql_fetch_object($result2)

Can I incorporate both

mysql_fetch_row

&

$row2 = mysql_fetch_object($result2)

<?php
	$result2 = mysql_query("SELECT id, title, mediaType FROM web_data INNER JOIN (
	SELECT mediaID, COUNT(mediaID) AS count
	FROM  web_users_reviews
	GROUP BY mediaID
	ORDER BY count DESC
	LIMIT 5
	) AS top_reviews
	ON web_data.id = top_reviews.mediaID")or die(mysql_error());
								
	while($row2 = mysql_fetch_object($result2)) {
	       $rowCount = mysql_fetch_row($result2);
	       $reviews = $rowCount[0];
									
	       echo "<tr>";
	       echo "<td class='bookType'></td>";
	       echo "<td class='thumbsUp' title='".$reviews."'></td>";
	       echo "<td class='contentboxTitle'>".$row2->title."</td>";
	       echo "<td class='ratingFive'></td>";
	       echo "</tr>";
	}
?>

To get the correct number of reviews, add the count field to the SELECT statement on the first line


SELECT id, title, mediaType, count
...

Then in the PHP code you can use $row2->count to retrieve the appropriate value.

You sir, are a genius :slight_smile:

Thank you ever so much for the help and advice.

That got everything working perfectly.

Wish you a very good year.

Thanks. For others who may come here later, the final SQL code was:


SELECT id, title, mediaType, count
FROM web_data INNER JOIN (
    SELECT mediaID, COUNT(mediaID) AS count
    FROM  web_users_reviews
    GROUP BY mediaID
    LIMIT 5
) AS top_reviews
    ON web_data.id = top_reviews.mediaID
ORDER BY count DESC