I've got a table filled with each user's access. One line is one access. In each row there are field like ID, TIMESTAMP, IP_ADDRESS, ARTICLE_ID,...
Now, the problem is an application used for logging users can't be modified by me, but adds 3 rows instead of one - logging not only the desired article but also one on the left and one on the right (based on ARTICLE_ID).
I want to count all article visits eliminating the ballast around. How can I do that? Overall I need to count in middle row's ARTICLE_ID access but leave the surroundings out.
My original idea is to have some PHP function like this (but I am open to ideas):
$logged_article_ids = $wpdb->get_results("SELECT article_id FROM " . $table_name . " GROUP BY article_id", ARRAY_A);
foreach($logged_article_ids as $article) {
// Count in each visit if IP and timestamp is unique or if it is in the middle of 3 lines with equal IP and timestamp.
}
Thank you very much for your help.
Update based on comments: Is ARTICLE_ID unique in the entries? No, it is not. As suggested here is the sample structure:
+---+-----------------+-------------+------------+----
|ID | TIME | IP_ADDRESS | ARTICLE_ID | etc..
+---+-----------------+-------------+------------+----
| 1 | 22-3-2015 11:00 | 134.3.4.104 | 43 |
| 2 | 22-3-2015 11:00 | 134.3.4.104 | 34 |
| 3 | 22-3-2015 11:00 | 134.3.4.104 | 12 |
| 4 | 22-3-2015 11:00 | 32.3.42.203 | 12 |
| 5 | 22-3-2015 11:01 | 32.3.42.203 | 43 |
+---+-----------------+-------------+------------+----
In the above table the desired behaviour would be to count middle line article of same IP_ADDRESS and TIME in and also the other standalone lines. So in this case articles 34, 12 and 43 would have each 1 visit while leaving out article 43 and 12 from IP: 134.3.4.104 at 22-3-2015 11:00. However if time or IP was different, I would like this:
+---+-----------------+-------------+------------+----
|ID | TIME | IP_ADDRESS | ARTICLE_ID | etc..
+---+-----------------+-------------+------------+----
| 1 | 22-3-2015 11:00 | 134.3.4.104 | 43 |
| 2 | 22-3-2015 11:24 | 134.3.4.104 | 34 |
| 3 | 22-3-2015 11:24 | 32.3.42.203 | 12 |
| 4 | 22-3-2015 11:24 | 32.3.42.203 | 12 |
| 5 | 22-3-2015 19:30 | 32.3.42.203 | 43 |
+---+-----------------+-------------+------------+----
Article 43: 2 visitors, 34: 1 visitor, 12: 2 visitors
I thought of adding an additional variable to my above foreach cycle which would remember last cycle's TIMESTAMP and IP_ADDRESS. That is probably a way to go but I haven't been able to test it yet. Also I would prefer a database way but that is too far from my abilities.
Once again thank you very much for your help.
