'How do I check for a duplicate record before inserting using SQL query?

I want to make one SQL query that will update in the enrolled_course table if field exists, and insert new one. For an example, when the user enrolled into the course, the wp_users, wp_3_learndash_user_activity, wp_3_posts, wp_3_postmeta, wp_3_term_relationships, wp_3_term_taxonomy and wp_3_terms will insert into enrolled_course.

This is my SQL query:

SELECT DISTINCT Users.ID, Users.display_name, PostObject.ID, 
        PostObject.post_title, PostObject.post_type, Term3.term_id, 
        Term3.name FROM `wp_users` AS `Users`
    INNER JOIN `wp_3_learndash_user_activity` AS `Learndash` 
        ON Users.ID = Learndash.user_id 
    INNER JOIN `wp_3_posts` AS `PostObject` 
        ON PostObject.ID = Learndash.post_id 
    INNER JOIN `wp_3_postmeta` AS `Postmeta` 
        ON PostObject.ID = Postmeta.post_id  
    INNER JOIN  `wp_3_term_relationships` AS `Term1` 
        ON PostObject.ID = Term1.object_id 
    INNER JOIN `wp_3_term_taxonomy` AS `Term2` 
        On Term2.term_taxonomy_id = Term1.term_taxonomy_id  
    INNER JOIN `wp_3_terms` AS `Term3` ON Term3.term_id = Term2.term_id 
WHERE Users.ID = '1' 
AND PostObject.post_type = 'sfwd-courses' 
AND PostObject.post_status='publish' 
GROUP BY PostObject.ID

This is the output table from my SQL query:

+----+-------------+-----+------------+-------------+-------+-------------+
|ID  | Display Name| ID  | post_title | post_types  |  ID   |   Name      |
+----+-------------+-----+------------+-------------+-------+-------------+
| 1  |  Student    |  33 | English    | swfd-course |  45   | Secondary 1 |
+----+-------------+-----+------------+-------------+-------+-------------+
| 1  |  Student    | 41  | Physics    | swfd-course |  58   | Secondary 1 |
+----+-------------+-----+------------+-------------+-------+-------------+

This is what I am expecting results for the enroll_courses:

+----+----------+-----------+------------+--------------+----------------+
|ID  | user_id  | course_id | cat_id     | course_name  |  category_name |
+----+----------+-----------+------------+--------------+----------------+
| 1  |  1       |  33       | 45         | English      |  Secondary 1   |
+----+----------+-----------+------------+--------------+----------------+
| 2  |  1       | 41        | 58         | Physics      |  Secondary 1   |
+----+----------+-----------+------------+--------------+----------------+

This is my code:

function display_courses(){
    try{
        global $wpdb, $post;
        $current_user = wp_get_current_user();

        $enroll_query = $wpdb->get_results( $wpdb->prepare("SELECT DISTINCT Users.ID, Users.display_name, PostObject.ID, PostObject.post_title, PostObject.post_type, Term3.term_id, Term3.name FROM `wp_users` AS `Users`
INNER JOIN `wp_3_learndash_user_activity` AS `Learndash` ON Users.ID = Learndash.user_id INNER JOIN `wp_3_posts` AS `PostObject` ON PostObject.ID = Learndash.post_id INNER JOIN `wp_3_postmeta` AS `Postmeta` ON PostObject.ID = Postmeta.post_id  INNER JOIN  `wp_3_term_relationships` AS `Term1` ON PostObject.ID = Term1.object_id INNER JOIN `wp_3_term_taxonomy` AS `Term2` On Term2.term_taxonomy_id = Term1.term_taxonomy_id  INNER JOIN `wp_3_terms` AS `Term3` ON Term3.term_id = Term2.term_id WHERE Users.ID = '1' AND PostObject.post_type = 'sfwd-courses' AND PostObject.post_status='publish' GROUP BY PostObject.ID"));   

          if ($enroll_query)
          {
               //Check if data duplicate
          }
        
    }
    catch(Exception $e)
    {
        echo 'Error writing to database: ',  $e->getMessage(), "\n";
    }

}

How do I make sure I do not insert a duplicate entry?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source