How to prevent duplicates on a foreach loop inside another foreach loop in PHP?

Question

I have a music website where each time a user click on play, my PHP function insert into table views

I'm trying to reorder my MySQL database to optimize it in performance, so I tried in this example to count the total of views for each track grouped by time.

Select only tracks that are in table views:

$query = "SELECT  `track` FROM  `views` GROUP BY `track`";

The result for $query:

|track|
|-----|
|140  |
|125  |
|33   |
|...  |

Count the number of rows for each result as plays and group by date:

$querysel = $this->db->query(sprintf("SELECT COUNT( * ) AS plays , `time`, `track` FROM `views` WHERE `track` = '%s' GROUP BY DATE( `time` )", $tid));

The result for $querysel

|plays |time                |track |
|------|--------------------|------|
|82    |2016-12-26 18:20:16 |140   |
|1     |2017-01-10 15:52:55 |140   |
|2     |2017-01-26 13:17:25 |140   |

Final insert in a new table the result:

$this->db->query(sprintf("INSERT INTO views_counts (tid,plays,time) VALUES ('%s','%s','%s')", $newtid, $plays, $time));

Here is my complete function:

function countViews() {

    $query = "SELECT  `track` FROM  `views` GROUP BY `track`";
    $result = $this->db->query($query);

    while($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }

    foreach($rows as $row) {
        $tid = $row['track'];

        $querysel = $this->db->query(sprintf("SELECT COUNT( * ) AS plays , `time`, `track` FROM `views` WHERE `track` = '%s' GROUP BY DATE( `time` )", $tid)); 

        while($rownew = $querysel->fetch_assoc()) {
            $rowsnew[] = $rownew;
        }

        foreach($rowsnew as $rownew) {

            $newtid = $rownew['track'];
            $plays = $rownew['plays'];
            $time = $rownew['time'];

            $this->db->query(sprintf("INSERT INTO views_counts (tid,plays,time) VALUES ('%s','%s','%s')", $newtid, $plays, $time));

        }

    }

}

In my function I use GROUP BY DATE(time) ,so why the final query insert into the new table views_counts duplicates with the same time?

Here is my output:

id  | tid | plays | time
----|-----|-------|--------------
1   | 1   | 2     | 2017-01-26 12:43:16
2   | 1   | 1     | 2017-01-27 12:45:24
3   | 1   | 2     | 2017-01-26 12:43:16
4   | 1   | 1     | 2017-01-27 12:45:24
5   | 3   | 30    | 2016-12-26 18:20:16
6   | 1   | 2     | 2017-01-26 12:43:16
7   | 1   | 1     | 2017-01-27 12:45:24
8   | 1   | 2     | 2017-01-26 12:43:16
9   | 1   | 1     | 2017-01-27 12:45:24
10  | 3   | 30    | 2016-12-26 18:20:16
11  | 1   | 2     | 2017-01-26 12:43:16
12  | 1   | 1     | 2017-01-27 12:45:24
13  | 1   | 2     | 2017-01-26 12:43:16
14  | 1   | 1     | 2017-01-27 12:45:24
15  | 3   | 30    | 2016-12-26 18:20:16

As you see I have multiple same results for the same time.

Here is what I expected:

id  | tid | plays | time
----|-----|-------|--------------
1   | 1   | 2     | 2017-01-26 12:43:16
2   | 1   | 1     | 2017-01-27 12:45:24
3   | 3   | 30    | 2016-12-26 18:20:16

UPDATE

Here it is how I call this "one time" function:

<?php
    include("/var/www/html/includes/config.php");
    include("/var/www/html/includes/classes.php");
    session_start();
    $db = new mysqli($CONF['host'], $CONF['user'], $CONF['pass'], $CONF['name']);
    if ($db->connect_errno) {
        echo "Failed to connect to MySQL: (" . $db->connect_errno . ") " . $db->connect_error;
    }
    $db->set_charset("utf8");

    $feed = new feed();
    $feed->db = $db;                
    $result = $feed->countViews();

    mysqli_close($db);
?>

The function countViews() is inside classes.php.

I call this PHP just going to visit the requested page through the web.

@RiggsFolly as I said, this is only a one time function, I don't need to run the function every day...when I run the function the table `view_counts` is empty — NineCattoRules, Jan 27 '17 at 10:50
you have one row for each groupment. Maybe you don't show the millisecond of your time — Joe Taras, Feb 01 '17 at 23:37
FWIW, you can do this in one SQL query: `INSERT INTO ... SELECT FROM (SELECT ...)`. — bishop, Feb 01 '17 at 23:39
@bishop with only one `foreach` loop? Could you please show me your solution? — NineCattoRules, Feb 02 '17 at 00:09
@Hackerman I need to run this only one time (for my old data), that's the reason why I don't use a procedure — NineCattoRules, Feb 02 '17 at 00:11
Did you try using SELECT DISTINCT instead of SELECT? That's a good example http://stackoverflow.com/questions/7250566/mysql-select-distinct — Nicolaesse, Feb 02 '17 at 00:21
and please post the sample rows and text. Also try to show expected output instead of what you got. Both as text please. — e4c5, Feb 02 '17 at 00:22
@Nicolaesse I already tried but it's not the solution, something is wrong with the double loop — NineCattoRules, Feb 02 '17 at 00:34
You really don't need the second `foreach`. Just bring all the code out, replace `$rownew` with `$rowsnew` and that should be it. — Dhruv Saxena, Feb 02 '17 at 00:41
@DhruvSaxena sorry I don't get it, I need to group by `track` and then by `time` ...could I see an example? — NineCattoRules, Feb 02 '17 at 00:49
Would you mind adding a small sample data as SQL dump in either SQL Fiddle or Rextester to help simulate this? Thanks. — Dhruv Saxena, Feb 02 '17 at 00:54
Your `$rowsnew[]` is growing with each loop - reset it before second fetch or after insert loop. sql_dru's answer should work as well (if its *really* for one-time use). — shudder, Feb 02 '17 at 01:41
With zero foreach loops, actually. The result is bijective on a subset of rows, so no loop is needed. Of course, I am sick right now, and this may just be fever talk. — bishop, Feb 02 '17 at 03:08
@NineCattoRules countViews may be this function you call three time. can you make a simple page and call this function only one time — krishn Patel, Feb 02 '17 at 07:17

score 1 · Accepted Answer · answered Feb 02 '17 at 18:46

I would propose to solve your issue using just one query.

function countViews() {
    $query = $this->db->query("SELECT COUNT( * ) AS plays , `time`, `track` FROM `views` GROUP BY `track`,DATE( `time` );"); 
    while($row = $query->fetch_assoc()) {
        $this->db->query(sprintf("INSERT INTO views_counts (tid,plays,time) VALUES ('%s','%s','%s')", $row['track'], $row['plays'], $row['time']));
    }
}

score 0 · Answer 2 · edited Feb 02 '17 at 09:29

Try this:

function countViews() {

    $query = "SELECT  `track` FROM  `views` GROUP BY `track`";
    $result = $this->db->query($query);

    while($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }

    $rowsnew = array();

    foreach($rows as $row) {
        $tid = $row['track'];

        $querysel = $this->db->query(sprintf("SELECT COUNT( * ) AS plays , `time`, `track` FROM `views` WHERE `track` = '%s' GROUP BY DATE( `time` )", $tid)); 

        while($rownew = $querysel->fetch_assoc()) {
            $rowsnew[] = $rownew;
        }

    }

    foreach($rowsnew as $rownew1) {

        $newtid = $rownew1['track'];
        $plays = $rownew1['plays'];
        $time = $rownew1['time'];

        $this->db->query(sprintf("INSERT INTO views_counts (tid,plays,time) VALUES ('%s','%s','%s')", $newtid, $plays, $time));

    }

}

score 0 · Answer 3 · answered Feb 03 '17 at 01:47

Can't it be done really in a single query?

INSERT INTO views_counts (tid, time, plays)
    SELECT       track, DATE(`time`), COUNT(*)
        FROM `views`
        GROUP BY track, DATE(`time`)

Note that the rows to INSERT come from the SELECT.

I took the liberty of using DATE(time), otherwise, which time is picked is arbitrary.

score -1 · Answer 4 · answered Feb 02 '17 at 01:28

Ok, so only a single SELECT query should suffice, which will be as follows:

$query = "SELECT `track`, `time`, COUNT(*) AS `plays` FROM `views` 
                 GROUP BY `track`, DATE(`time`) 
                 ORDER BY `track`, DATE(`time`);"

$result = $this->db->query($query);

while($row = $result->fetch_assoc()) {
    $newtid  =  $row['track'];
    $plays   =  $row['plays'];
    $time    =  $row['time'];

    $this->db->query(sprintf("INSERT INTO views_counts (tid,plays,time) 
                                     VALUES ('%s','%s','%s')", 
                                     $newtid, $plays, $time));

}

And that should ideally do the trick. A simple query demonstration using manually created sample data:

create table views (track int, `time` timestamp);
insert into views VALUES(1, '2017-01-26 12:43:16');
insert into views VALUES(1, '2017-01-27 12:45:24');
insert into views VALUES(1, '2017-01-27 12:50:30');

insert into views VALUES(2, '2017-01-25 01:00:00');
insert into views VALUES(2, '2017-01-25 02:00:00');
insert into views VALUES(2, '2017-01-26 11:30:00');
insert into views VALUES(2, '2017-01-26 11:45:00');
insert into views VALUES(2, '2017-01-26 13:45:00');
insert into views VALUES(2, '2017-01-26 15:45:00');
insert into views VALUES(2, '2017-01-27 08:00:00');

insert into views VALUES(3, '2017-01-27 09:00:00');


SELECT `track`, `time`, COUNT(*) AS plays FROM views 
GROUP BY `track`, DATE(`time`) ORDER BY `track`, DATE(`time`);

Output produced using this query, which is what is expected to land in views_counts:

track  |  time                |    plays   
----------------------------------------------
1      |  2017-01-26 12:43:16 |    1
1      |  2017-01-27 12:45:24 |    2
2      |  2017-01-25 01:00:00 |    2
2      |  2017-01-26 11:30:00 |    4
2      |  2017-01-27 08:00:00 |    1
3      |  2017-01-27 09:00:00 |    1

Demo link

Hope that helps...

How to prevent duplicates on a foreach loop inside another foreach loop in PHP?

4 Answers4