'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 |
|---|
