Jump to content


Photo

Finding most repeated value in db


  • Please log in to reply
5 replies to this topic

#1 hackerkts

hackerkts
  • Members
  • PipPipPip
  • Advanced Member
  • 593 posts
  • LocationSingapore
  • Age:18

Posted 19 June 2006 - 01:45 AM

Sorry the title might be unclear, but I wanted to find the top few most popular sales
What 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 bought
1. item5 50
2. item15 43
3. item3 30
4. item9 21
5. item19 5

Any helps would be appreciate, thanks.

Regards,
hackerkts

To be a coder, you must learn how to think and not to give up so easily.


#2 hackerkts

hackerkts
  • Members
  • PipPipPip
  • Advanced Member
  • 593 posts
  • LocationSingapore
  • Age:18

Posted 20 June 2006 - 03:30 AM

Bump..
No one help me yet >.<!

Actually it's like IPB "Most active in <category>"

Regards,
hackerkts

To be a coder, you must learn how to think and not to give up so easily.


#3 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 20 June 2006 - 03:45 AM

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] item, COUNT(*) AS count FROM table GROUP BY item ORDER BY count DESC [!--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

#4 hackerkts

hackerkts
  • Members
  • PipPipPip
  • Advanced Member
  • 593 posts
  • LocationSingapore
  • Age:18

Posted 20 June 2006 - 07:24 AM

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
$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>";
}

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.

Regards,
hackerkts

To be a coder, you must learn how to think and not to give up so easily.


#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 20 June 2006 - 12:46 PM

$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>';


#6 hackerkts

hackerkts
  • Members
  • PipPipPip
  • Advanced Member
  • 593 posts
  • LocationSingapore
  • Age:18

Posted 21 June 2006 - 02:12 AM

<?php
echo "OMG shoz you rocks !!!";
unlink("http://www.phpfreaks.com/forums/index.php?showtopic=96245");
?>

:P Thanks.

Regards,
hackerkts

To be a coder, you must learn how to think and not to give up so easily.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users