Show top two results from MySQL with PHP - two tables

MySQL database
#1

Maybe I’ve had my head in the code too long but I’m not getting this and could use another set of eyes. I have two tables, “pros” and “videos”. In videos proid matches id in pros. I am trying to get the most “viewed” videos for a particular category then get the two “pros” who are featured in that video. I am only getting one pro returned when I use a while loop inside the while loop which finds the two most viewed videos. Here is the snippet:

[php]$query = “SELECT * FROM videos WHERE maincat = ‘$maincat’ ORDER BY views DESC LIMIT 2”;
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$id = $row[‘id’];
$proid = $row[‘proid’];
$views = $row[‘views’];
echo “Video ID: $id Speaker ID: $proid Views: $views
\n”;
$pro = “SELECT * FROM pros WHERE id = ‘$proid’”;
$result = mysql_query($pro) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$fname = $row[‘fname’];
$lname = $row[‘lname’];
$title = $row[‘title’];
echo “$fname $lname, $title
”;
}
}[/php]

I have used an INNER JOIN but don’t know how to order it by views, descend it and limit it to only the two most viewed. Can I just write all that data to a temporary table and iterate through it? I cannot have the same “pro” twice either. I need to have two different “pros”. In other words if Pro A has two videos most viewed I can only display the most viewed one then look for the next “pro” with the most views.

#2

I’m pretty sure this can be done with one query. If you post the structures of your tables with some data I’ll see if it can be joined the way you need.

#3 
CREATE TABLE IF NOT EXISTS `videos` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `code` text NOT NULL,
  `proid` int(5) NOT NULL,
  `views` int(9) NOT NULL,
  `maincat` varchar(24) NOT NULL,
  `subcat` varchar(96) NOT NULL,
  `when` int(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `videos`
--

INSERT INTO `videos` (`id`, `code`, `proid`, `views`, `maincat`, `subcat`, `when`) VALUES
(1, '<iframe width="560" height="315" src="http://www.youtube.com/embed/F29Uzh7oE9g" frameborder="0" allowfullscreen></iframe>', 7, 31, 'Accounting Classes', '', 0),
(2, '<iframe width="560" height="315" src="http://www.youtube.com/embed/tyNWQZ-_cuA" frameborder="0" allowfullscreen></iframe>', 7, 77, 'Accounting Classes', '', 0),
(3, '<iframe width="560" height="315" src="http://www.youtube.com/embed/VaK_kXfQozo" frameborder="0" allowfullscreen></iframe>', 7, 52, 'Accounting Classes', '', 0),
(4, '<iframe width="560" height="315" src="http://www.youtube.com/embed/1_HrQVhgbeo" frameborder="0" allowfullscreen></iframe>', 9, 6, 'Technology', 'Java Classes', 0),
(5, '<iframe width="420" height="315" src="http://www.youtube.com/embed/_LanZgLozR8" frameborder="0" allowfullscreen></iframe>', 7, 24, 'Technology', 'Java Classes', 0);

[code]CREATE TABLE IF NOT EXISTS pros (
id int(11) NOT NULL AUTO_INCREMENT,
category varchar(24) NOT NULL,
fname varchar(36) NOT NULL,
lname varchar(24) NOT NULL,
company varchar(64) NOT NULL,
title varchar(36) NOT NULL,
subcategory varchar(255) NOT NULL,
fullbio text NOT NULL,
photo varchar(255) NOT NULL,
stuff blob NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

–
– Dumping data for table pros

INSERT INTO pros (id, category, fname, lname, company, title, subcategory, fullbio, photo, stuff) VALUES
(3, ‘Finance’, ‘Bob’, ‘Numbers’, ‘Goldman Sachs’, ‘Foreign Asset Examiner’, ‘Investment Banking’, ‘’, ‘’, ‘’),
(5, ‘Technology’, ‘Jim’, ‘Minny’, ‘Toulous’, ‘Thug’, ‘I was born. I am alive.’, ‘I was born. Went to school. Got married. Had kids. I am still alive.’, ‘2012-02-17 14-21-32.919.jpg’, ‘’),
(6, ‘Technology’, ‘Twark’, ‘Main’, ‘Mudd River’, ‘Chief Technical Officer’, ‘I have been in the tech industry since 1893. I have seen many changes but never as many changes as Mrs. Main’‘s hair-do.’, ‘In the beginning man just wanted to eat. Every advance in technology since that date has been incumbent on that primal desire. No technology would have ever advanced had someone not been physically hungry for good old food.’, ‘’, ‘’),
(7, ‘Technology’, ‘Barral’, ‘Rover’, ‘John Smith’, ‘Chief Technical Officer’, ‘I have been in the tech industry since 1893. I have seen many changes but never as many changes as Mrs. Main’‘s hair-do.’, ‘In the beginning man just wanted to eat. Every advance in technology since that date has been incumbent on that primal desire. No technology would have ever advanced had someone not been physically hungry for good old food.’, ‘RayPalermo_20120523_014-Edit2.jpg’, ‘’),
(8, ‘Technology’, ‘Moose’, ‘Rabbit’, ‘Musical Beats’, ‘Chief Technical Officer’, ‘I have been in the tech industry since 1893. I have seen many changes but never as many changes as Mrs. Main’‘s hair-do.’, ‘In the beginning man just wanted to eat. Every advance in technology since that date has been incumbent on that primal desire. No technology would have ever advanced had someone not been physically hungry for good old food.’, ‘user-experience.jpg’, ‘’),
(9, ‘Accounting’, ‘Wes’, ‘S’, ‘Middle Market PE firm’, ‘Associate’, ‘Wes works for an middle market private equity firm.’, ‘’, ‘’, ‘’);[/code]

#4

What does this do for you

SELECT a.*, b.* FROM `videos` AS a, `pros` AS b WHERE a.`proid` = b.`id` ORDER BY a.`views` DESC GROUP BY a.`proid` LIMIT 2

#5

That would work but does not deliver 2 different names. Here is what I have that is working and I’ll leave it if I can’t figure out how to make a join/iteration work:

[php]$maincat = $_GET[‘maincat’];
$query = “SELECT * FROM videos WHERE maincat = ‘$maincat’ ORDER BY views DESC LIMIT 1”;
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$id = $row[‘id’];
$proid = $row[‘proid’];
$views = $row[‘views’];
echo “Video ID: $id Speaker ID: $proid Views: $views
\n”;
$pro = “SELECT * FROM pros WHERE id = ‘$proid’”;
$result = mysql_query($pro) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$fname = $row[‘fname’];
$lname = $row[‘lname’];
$title = $row[‘title’];
echo “$fname $lname, $title
”;
}
}

$query2 = “SELECT * FROM videos WHERE maincat = ‘$maincat’ AND id != ‘$id’ ORDER BY views DESC LIMIT 1”;
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
$id2 = $row[‘id’];
$proid2 = $row[‘proid’];
$views2 = $row[‘views’];
echo “Video ID: $id2 Speaker ID: $proid2 Views: $views2
\n”;
$pro = “SELECT * FROM pros WHERE id = ‘$proid2’”;
$result2 = mysql_query($pro) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
$fname2 = $row[‘fname’];
$lname2 = $row[‘lname’];
$title2 = $row[‘title’];
echo “$fname2 $lname2, $title2
”;
}
}[/php]

#6

Are you sure? I had an error in my query (updated below) but it should give you 2 unique names. The GROUP BY does not allow a duplicate proid

SELECT a.*, b.* FROM `videos` AS a, `pros` AS b WHERE a.`proid` = b.`id` GROUP BY a.`proid` ORDER BY a.`views` DESC LIMIT 2
#7

I didn’t run it but I can tell by looking it doesn’t account for having one user with the two (or fifty) most viewed videos in the same maincat. I apologize I did not provide for that case in my data samples.

#8

It does, it would never return a duplicate proid. There was a flaw in that it doesn’t return the highest views. How about this one?

SELECT *, b.* FROM (SELECT * FROM `videos` ORDER BY `views` DESC) AS `tmp`, `pros` AS b WHERE `proid` = b.`id` GROUP BY `proid` LIMIT 2

With maincat specified:

SELECT *, b.* FROM (SELECT * FROM `videos` WHERE `maincat` = 'Technology' ORDER BY `views` DESC) AS `tmp`, `pros` AS b WHERE `proid` = b.`id` GROUP BY `proid` LIMIT 2
#9

Indeed it appears to work. I see your solution was to read into tmp - which I had not tried and guess I should have but you went right to it. I’ll have them load some read data and test it but that looks to be the solution. Much appreciated.

Sponsor our Newsletter | Privacy Policy | Terms of Service