PHP mysql adding up duplicates at result

Question

I have a database that stores information everytime someone downloads a file. It captures name email and the date stamp everytime they download the file (as requested by client).

I want to be able connect to the database and add up all duplicate entries. So if there are 10 results with the email [email protected] it will print "[email protected]; 10 downloads."

This is what i have tried so far. However it times out on the page:

function userFile($id) // $id = File ID
 {
    $order = "SELECT * FROM downloads WHERE dl_id='$id'";
    $result = mysql_query($order);
    while($row=mysql_fetch_array($result)){     
        $email = $row['email'];
        $order = "SELECT * FROM downloads WHERE email='$email'";
        $result = mysql_query($order);
        $num_rows = mysql_num_rows($result);
        print $row['email'] . ' ' . $num_rows;
    }
 }

There has to be an easier or better way of doing this. Any help is appreciated!

Please, don't use mysql_query in new code, but if you must, always escape your SQL values correctly. — tadman
– tadman, Commented Apr 18, 2013 at 15:07

bwoebi · Accepted Answer · 2013-04-18 15:09:25Z

2

function userFile ($id) // $id = File ID
{
    $order = "SELECT COUNT(*) AS `count`, email FROM downloads WHERE dl_id='$id' GROUP BY email"
    $result = mysql_query($order);
    while($row = mysql_fetch_array($result)) {
        print "{$row['email']}; {$row['count']} downloads.\n"
}

answered Apr 18, 2013 at 15:09

bwoebi

23.8k5 gold badges64 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bwoebi · Accepted Answer · 2013-04-18 15:22:24Z

1

Have MySQL do the work for you. If I understand your requirements correctly you need a query like this:

SELECT email, COUNT(*) AS num_rows
FROM downloads
WHERE dl_id='$id'
GROUP BY email

Then your PHP code would look something like this:

function userFile($id) // $id = File ID
 {
    $order = "SELECT email, COUNT(*) AS `num_rows` FROM downloads WHERE dl_id='$id' GROUP BY email";
    $result = mysql_query($order);
    while($row=mysql_fetch_array($result)){     
        $email = $row['email'];
        $num_rows = $row['num_rows'];
        print $email . ' ' . $num_rows;
    }
 }

I don't have PHP available here today, so be warned this is untested.

edited Apr 18, 2013 at 15:22

bwoebi

23.8k5 gold badges64 silver badges81 bronze badges

answered Apr 18, 2013 at 15:10

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

3 Comments

Scott Dawson Over a year ago

This is exactly what i was looking for! Thank you for the fast reply! Im still fairly new to mysql and havent really learned all there is to it yet! Thanks again!

bwoebi Over a year ago

@EdGibbs I don't know any method to upvote an edit :P But you could upvote my answer (as it is nearly the same as yours, there's no difference, but I'd have then +10 reputation^^)

Ed Gibbs Over a year ago

@bwoebi - done! Thanks again for the assist, and it looks like your edit came through somehow so that's all set too.

Wesley Schleumer de Góes · Accepted Answer · 2013-04-18 15:08:24Z

0

You shall use GROUP BY within COUNT.

SELECT *,COUNT(1) FROM downloads WHERE `email`='$email' GROUP BY `email`

answered Apr 18, 2013 at 15:08

Wesley Schleumer de Góes

6,5572 gold badges21 silver badges28 bronze badges

Collectives™ on Stack Overflow

PHP mysql adding up duplicates at result

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related