This is an example of my table. I have multiple entries on multiple rows for a given post_id (this is metadata for posts).

post_id | meta_key       | meta_value
________ ________________ ____________________
        |                |
1       | _theDate       | 2016-03-31 12:03:59
1       | _email         | the@email.com
1       | _EventDuration | 32400
2       | _theDate       | 2016-01-06 14:50:22
2       | _email         | the@email.com
2       | _EventDuration | 32400
3       | _theDate       | 2017-02-14 15:32:52
3       | _email         | other@user.net
3       | _EventDuration | 32400
4       | _theDate       | 2016-10-01 22:45:55
4       | _email         | the@email.com
4       | _EventDuration | 32400
5       | _theDate       | 2016-09-25 11:01:39
5       | _email         | other@user.net
5       | _EventDuration | 32400
6       | _theDate       | 2015-11-19 19:08:45
6       | _email         | other@user.net
6       | _EventDuration | 32400

What I am trying to accomplish:

I would like to get these results from the database to make a table displaying the number of hours (_EventDuration) worked for each user (_email) each month (using _theDate for example, 2015-11) for each event (post_id). I can do this using PHP variables on my end.

So I can then make HTML tables to show this information in the following way (I’m not looking for an answer for this part like converting seconds to hours, it’s purely just so you know what I want to accomplish with all the data from the table):

Hours the@email.com worked in 2015-11
Date            Hours
2015-11-01      9
2015-11-13      7
2015-11-27      5
2015-11-30      8

Hours the@email.com worked in 2015-12
Date            Hours
2015-12-01      10
2015-12-13      7
2015-12-27      3
2015-12-30      6

All I have for the moment is this request to count the number of events a user has done in 2017 for example :

SELECT COUNT(*) 
  FROM 
     ( SELECT post_id 
         FROM metatable 
        WHERE (meta_key = '_email' AND meta_value LIKE '%$user_email%')                 
           OR (meta_key = '_theDate' AND LEFT(meta_value, 4) LIKE '%2017%') 
        GROUP 
           BY post_id 
       HAVING COUNT(DISTINCT meta_key) = 2
     ) p

Read more here: Get event duration for a specific time period per user


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.


Wordpress related questions and answers: