hackerkts Posted June 19, 2006 Share Posted June 19, 2006 Sorry the title might be unclear, but I wanted to find the top few most popular salesWhat I mean is, I have a table which contains all the information about the item, it has the item unique number (means 154 = item1, 155 = item2 etcs..) in that table it also contains other item information.I have another table which records what item you bought, I wanna know how to find the top 5 most popular sales.Example:Ranking | Item name | Number of times bought1. item5 502. item15 433. item3 304. item9 215. item19 5Any helps would be appreciate, thanks. Link to comment https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/ Share on other sites More sharing options...
hackerkts Posted June 20, 2006 Author Share Posted June 20, 2006 Bump..No one help me yet >.<!Actually it's like IPB "Most active in <category>" Link to comment https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/#findComment-47542 Share on other sites More sharing options...
shoz Posted June 20, 2006 Share Posted June 20, 2006 [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] item, [color=blue]COUNT[/color](*) [color=green]AS[/color] count [color=green]FROM[/color] [color=orange]table[/color] GROUP BY item [color=green]ORDER BY[/color] count [color=green]DESC[/color] [!--sql2--][/div][!--sql3--]Post the structure of the table that holds the data with an example of the data stored if you still have problems Link to comment https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/#findComment-47545 Share on other sites More sharing options...
hackerkts Posted June 20, 2006 Author Share Posted June 20, 2006 Thanks for the help, but I still having problem.Here's the table structure:[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]CREATE TABLE `receiptbuy` ( `Id` char(16) NOT NULL default '', `Prop` int(10) unsigned NOT NULL default '0', `MoneyDelta` int(11) NOT NULL default '0', `CashDelta` int(11) NOT NULL default '0', `Time` datetime NOT NULL default '0000-00-00 00:00:00', KEY `Id` (`Id`))[/quote]I have do this[code]$query = "SELECT Prop, COUNT(*) AS count FROM receiptbuy GROUP BY Prop ORDER BY count DESC LIMIT 5";$result = mysql_query($query) or die("Query failed: " . mysql_error());while($row = mysql_fetch_assoc($result)){ echo " ".$row['Prop']." <br>";}[/code]Now I have top 5 best sales, but is there a way to display the total number they bought ?Hope you know how to make it looks like the example I posted on the first thread, thanks again. Link to comment https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/#findComment-47557 Share on other sites More sharing options...
shoz Posted June 20, 2006 Share Posted June 20, 2006 [code]$query = "SELECT Prop, COUNT(*) AS count FROM receiptbuy GROUP BY Prop ORDER BY count DESC LIMIT 5";$result = mysql_query($query) or die("Query failed: " . mysql_error());echo <<<RRR<table><tr><th>Rank</th><th>item</th><th>num bought</th></tr>RRR;for ($i = 1; $row = mysql_fetch_assoc($result); $i++){ echo "<tr><td>$i</td><td>{$row['Prop']}</td><td>{$row['count']}</td></tr>\n";}echo '</table>';[/code] Link to comment https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/#findComment-47635 Share on other sites More sharing options...
hackerkts Posted June 21, 2006 Author Share Posted June 21, 2006 [code]<?phpecho "OMG shoz you rocks !!!";unlink("http://www.phpfreaks.com/forums/index.php?showtopic=96245");?>[/code]:P Thanks. Link to comment https://forums.phpfreaks.com/topic/12344-finding-most-repeated-value-in-db/#findComment-47880 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.