Search code examples
phpsqlwordpressstringinner-join

SQL join ID with some_string_ID

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"
Solution

  • Is this what you want?

    AND umeta.meta_key = CONCAT('course_stats_', ld.course_id)