I’m making a wordpress plugin with PHP and I’m trying to make this query where I get data from a learndash’s table, wordpress users, posts and usermeta tables. Which I then print inside a html table. I have the table working and I’ve got all the other things I need but I’m missing only one thing.
I have created a new row to wp_usermeta table which counts the users course uses. For example it has a meta_key
course_stats_111 where 111 is the same course id as in the learndash table
course_id = 111 . So the row looks like this
meta_key = course_stats_111, meta_value = 2 where the 2 is meta_value of how many times the user has opened the course link. This is also working fine.
So my problem is how do I join these two rows together where course_stats_111 matches course_id = 111?
SELECT * FROM wp_learndash_user_activity AS ld INNER JOIN wp_users AS users ON ld.user_id = users.ID INNER JOIN wp_posts AS posts ON ld.course_id = posts.ID INNER JOIN wp_usermeta AS umeta ON ld.user_id = umeta.user_id AND ld.course_id = umeta.meta_key <--THIS IS THE PART I'M HAVING TROUBLE WITH WHERE ld.activity_type = 'course' AND posts.post_type = 'sfwd-courses' ORDER BY ld.course_id"
this is what you want:
AND umeta.meta_key = CONCAT('course_stats_', ld.course_id)