Get post data from DB

get-posts.php

<?php

// Hook into the admin menu action to add the options page.
add_action( 'admin_menu', 'epic_add_admin_page' );

function epic_add_admin_page() {
	// Add a new submenu page under Settings tab
	add_submenu_page(
		'options-general.php',
		'Epic Posts Listing',
		'Epic Posts Listing',
		'manage_options',
		'epic-posts-listing',
		'epic_posts_listing_page'
	);
}

// Callback function to display content on the options page.
function epic_posts_listing_page() {
	$posts_limit = 30;

	// Get all post types
	$post_types = get_post_types( array( 'public' => true ), 'objects' );
	$exclude    = array(
		'attachment',
		'elementor_library',
		'jet-engine',
		'jet-menu',
		'memberpressgroup',
		'memberpressproduct',
		'mpdl-file',
		'product_variation',
		'wp_block',
	);

	foreach ( $exclude as $key ) {
		unset( $post_types[ $key ] );
	}

	// Get all taxonomies
	$taxonomies = get_taxonomies( array( 'public' => true ), 'objects' );
	$exclude    = array(
		'nav_menu',
		'mpdl-file-categories',
		'mpdl-file-tags',
		'post_format',
		'mepr-product-category',
	);

	foreach ( $exclude as $key ) {
		unset( $taxonomies[ $key ] );
	}

	// Get default posts
	$args = array(
		'post_type'      => 'page',
		'post_status'    => 'publish',
		'posts_per_page' => $posts_limit,
	);

	$the_query = new WP_Query( $args );

	?>
	<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/css/select2.min.css">
	<script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.13/js/select2.min.js"></script>
	<style>
		.wrap {
			max-width: 1320px;
			margin: 20px auto;
		}

		.title-wrapper {
			display: flex;
			gap: 1rem;
			align-items: start;
			margin-bottom: 2rem;
		}

		.title-wrapper .notice{
			display: none;
		}

		.title-wrapper h1,
		.title-wrapper form{
			padding: 0;
			margin: 0 !important;
		}

		.title-wrapper form input{
			font-size: 0.8rem;
			line-height: 1em;
			padding: 0 1em;
		}

		#epic-filter-form {
			display: flex;
			gap: 1rem;
			align-items: center;
		}

		#epic-filter-form select,
		#epic-filter-form button,
		.select2-container--default .select2-selection--single,
		.select2-container--default .select2-selection--multiple {
			padding: 5px 0;
			height: 38px;
			border: 1px solid #ccc;
			border-radius: 4px;
		}

		#epic-filter-form button {
			background-color: #0073aa;
			color: white;
			border: none;
			cursor: pointer;
			padding: 0 14px;
			margin-top: 22px;
		}

		#epic-filter-form button:not(:last-child) {
			margin-right: 5px;
		}

		#epic-filter-form button:hover,
		button:hover,
		input[type="button"]:hover,
		input[type="submit"]:hover {
			background-color: #006799;
		}

		#epic-filter-form label {
			display: block;
			margin-bottom: 5px;
			font-weight: bold;
			color: #333;
		}

		.wp-list-table {
			width: 100%;
			border-collapse: collapse;
		}

		.wp-list-table thead {
			background-color: #f1f1f1;
		}

		.wp-list-table th,
		.wp-list-table td {
			padding: 10px;
			border: 1px solid #ddd;
		}

		.wp-list-table tr:nth-child(odd) {
			background-color: #f9f9f9;
		}

		.wp-list-table tr:hover {
			background-color: #f1f1f1;
		}

		.wp-list-table tr th:nth-child(1) {
			width: 5%;
		}

		.wp-list-table tr th:nth-child(2) {
			width: 45%;
		}

		.wp-list-table tr th:nth-child(3) {
			width: 50%;
		}

		.select2-container--default {
			min-width: 200px; /* Ensure Select2 elements have a minimum width */
		}

		.select2-dropdown,
		.select2-results__option,
		.select2-container--open .select2-dropdown--below,
		.select2-container--open .select2-dropdown--above {
			border-radius: 4px; /* Uniform border-radius */
		}

		.select2-container .select2-selection--multiple .select2-selection__choice {
			color: #555;
			padding: 0 10px;
			margin: 5px 0 5px 5px;
			background-color: #f0f0f0;
			border-radius: 4px;
		}

		.select2-container .select2-selection--multiple .select2-selection__choice__remove {
			color: #999;
			float: right;
			margin-left: 5px;
			margin-right: -2px;
		}

		.select2-container--default .select2-selection--multiple .select2-selection__choice__remove:hover {
			color: #333;
		}

		.select2-container--default .select2-search--inline .select2-search__field {
			padding: 0;
			margin-top: 0; /* Adjusted for alignment */
		}

		/* Adjustments for mobile view */
		@media screen and (max-width: 768px) {
			.wrap {
				margin: 10px;
			}

			#epic-filter-form select,
			#epic-filter-form button {
				display: block;
				width: 100%;
				margin-bottom: 10px;
				margin-right: 0; /* Remove right margin in mobile view */
			}
		}
	</style>

	<div class="wrap">
		<div class="title-wrapper">
			<h1>EpicDevs Posts Listing</h1>
		</div>

		<form id="epic-filter-form">
			<div class="field-wrapper">
				<label for="post-type-select">Post Types</label>
				<select name="post_type[]" multiple="multiple" class="multiple-selector">
							<?php foreach ( $post_types as $post_type ) : ?>
						<option value="<?php echo $post_type->name; ?>"><?php echo $post_type->labels->singular_name; ?></option>
					<?php endforeach; ?>
				</select>
			</div>

			<div class="field-wrapper">
				<label for="post-type-select">Taxonomies</label>
				<select name="taxonomy[]" id="epic-taxonomy-select" multiple="multiple" class="multiple-selector">
					<?php foreach ( $taxonomies as $taxonomy ) : ?>
						<option value="<?php echo $taxonomy->name; ?>"><?php echo $taxonomy->labels->singular_name; ?></option>
					<?php endforeach; ?>
				</select>
			</div>

			<div class="field-wrapper">
				<label for="post-type-select">Terms</label>
				<select name="terms[]" id="epic-terms-select" multiple="multiple" class="multiple-selector"></select>
			</div>

			<div class="field-wrapper">
				<button type="submit">Filter</button>
				<button type="submit" id="download-csv-button">Download CSV</button>
			</div>
		</form>

		<div><?php echo '<p id="posts-total-results">Total Posts Found: ' . $the_query->found_posts . '</p>'; ?></div>

		<table class="wp-list-table widefat fixed striped">
			<thead>
				<tr>
					<th>ID</th>
					<th>Title</th>
					<th>Permalink</th>
				</tr>
			</thead>
			<tbody id="posts-table-container">
				<?
				if ( $the_query->have_posts() ) {
					while ( $the_query->have_posts() ) {
						global $post;
						$the_query->the_post();

						echo '<tr>';
						echo '<td><a href="' . admin_url() . 'post.php?action=edit&post=' . get_the_ID() . '" target="_blank">' . get_the_ID() . '</a></td>';
						echo '<td>' . get_the_title() . '</td>';
						echo '<td><a href="' . get_permalink() . '" target="_blank">' . get_permalink() . '</a></td>';
						echo '</tr>';
					}
					wp_reset_postdata();
				} else {
					echo '<tr><td colspan="3">No posts found.</td></tr>';
				}
				?>
			</tbody>
		</table>

		<div><?php echo '<p id="posts-total-results">Showing only the first ' . $posts_limit . ' Posts.</p>'; ?></div>
	</div>

	<script>
		jQuery(document).ready(function($) {
			const ajax_object = {
				ajax_url: '<?php echo admin_url( 'admin-ajax.php' ); ?>',
				nonce: '<?php echo wp_create_nonce( 'epic_ajax_nonce' ); ?>',
				admin_url: '<?php echo admin_url(); ?>'
			};

			jQuery(document).ready(function($) {
				$(".multiple-selector").select2();
			});

			jQuery(document).ready(function($) {
				$('#epic-filter-form').submit(function(event) {
					event.preventDefault();

					const formData = new FormData(this);

					formData.append('action', 'epic_filter_posts');
					formData.append('nonce', ajax_object.nonce);

					$.ajax({
						type: 'POST',
						url: ajax_object.ajax_url,
						data: formData,
						contentType: false,
						processData: false,
						success: function(response) {
							if(response.success) {
								const postsContainer = $('#posts-table-container');
								const postsResults = $('#posts-total-results');
								postsContainer.empty();

								if(response.data?.results.length > 0) {
									$.each(response.data?.results, function(index, post) {
										postsContainer.append(
											'<tr class="post-item">' +
												'<td><a href="' + ajax_object.admin_url + 'post.php?action=edit&post=' + post.ID + '" target="_blank">' + post.ID + '</a></td>' +
												'<td>' + post.title + '</td>' +
												'<td><a href="' + post.permalink + '" target="_blank">' + post.permalink + '</a></td>' +
											'</tr>'
										);
									});

									postsResults.text('Total Posts Found: ' + response.data.total_posts);
								} else {
									postsContainer.append('<p>No posts found.</p>');
								}
							}
						}
					});
				});

				// Handle changes in the taxonomy select to fetch and update terms
				$('#epic-taxonomy-select').change(function() {
					const selectedTaxonomy = $(this).val();

					$.ajax({
						type: 'POST',
						url: ajax_object.ajax_url,
						data: {
							action: 'epic_fetch_terms',
							nonce: ajax_object.nonce,
							taxonomy: selectedTaxonomy
						},
						success: function(response) {
							if(response.success) {
								const termsSelect = $('#epic-terms-select');
								termsSelect.empty();

								if(response.data.length > 0) {
									$.each(response.data, function(index, term) {
											termsSelect.append($('<option></option>')
																	.val(term.term_id)
																	.html(term.name));
									});
								} else {
									termsSelect.append($('<option></option>').val('').html('No terms found'));
								}
							}
						}
					});
				});

				// Handles the download CSV button
				$('#download-csv-button').on('click', function(e) {
					e.preventDefault();

					// Use FormData to capture all form data
					const formData = new FormData(document.getElementById('epic-filter-form'));

					// Append an action or identifier for the server-side to recognize the request
					formData.append('action', 'download_csv');
					formData.append('nonce', ajax_object.nonce);

					// AJAX request to server-side PHP function
					$.ajax({
						url: ajax_object.ajax_url,
						type: 'POST',
						data: formData,
						contentType: false,
						processData: false,
						success: function(response) {
							// Assuming the server returns the URL to the generated CSV file
							if(response.success && response.data.file_url) {
								window.location.href = response.data.file_url;
							} else {
								alert('Error generating CSV.');
							}
						}
					});
				});
			});
		});
	</script>
							<?php
}

						add_action( 'wp_ajax_download_csv', 'epic_generate_and_download_csv' );
function epic_generate_and_download_csv() {
	check_ajax_referer( 'epic_ajax_nonce', 'nonce' );

	$csv_file_path = epic_generate_csv();

	if ( $csv_file_path ) {
		wp_send_json_success( array( 'file_url' => $csv_file_path ) );
	} else {
		wp_send_json_error( 'Failed to generate CSV.' );
	}
}

function epic_generate_csv() {
	list($query) = epic_fetch_data( $_POST, false, -1 );

	if ( ! $query->have_posts() ) {
		return false;
	}

	// Generate CSV content
	$csv_lines   = array();
	$csv_lines[] = array( 'ID', 'Slug', 'Title', 'Permalink' ); // Header row

	while ( $query->have_posts() ) {
		global $post;
		$query->the_post();

		$csv_lines[] = array(
			get_the_ID(),
			$post->post_name,
			get_the_title(),
			get_permalink(),
		);
	}
	wp_reset_postdata();

	// Create CSV file in temporary directory
	$upload_dir    = wp_upload_dir();
	$file_location = '/generated_csv_' . date( 'Y-m-d_H-i-s' ) . '.csv';
	$file_path     = $upload_dir['basedir'] . $file_location;
	$file_url      = $upload_dir['baseurl'] . $file_location;

	// Open file handle and write CSV lines
	$file_handle = fopen( $file_path, 'w' );

	foreach ( $csv_lines as $line ) {
		fputcsv( $file_handle, $line );
	}
	fclose( $file_handle );

	return $file_url;
}


						add_action( 'wp_ajax_epic_filter_posts', 'epic_filter_posts' );
function epic_filter_posts() {
	check_ajax_referer( 'epic_ajax_nonce', 'nonce' );

	// Fetch the data
	list($query, $total_posts) = epic_fetch_data( $_POST, true );

	$response                = array();
	$response['total_posts'] = $total_posts;

	if ( $query->have_posts() ) {
		while ( $query->have_posts() ) {
			global $post;
			$query->the_post();
			$response['results'][] = array(
				'ID'        => get_the_ID(),
				'title'     => get_the_title(),
				'permalink' => get_permalink(),
			);
		}
		wp_reset_postdata();
		wp_send_json_success( $response );
	} else {
		wp_send_json_error( 'No posts found.' );
	}
}

						// Add AJAX handler for fetching terms based on selected taxonomy
						add_action( 'wp_ajax_epic_fetch_terms', 'epic_fetch_terms' );
function epic_fetch_terms() {
	check_ajax_referer( 'epic_ajax_nonce', 'nonce' );

	$taxonomy = $_POST['taxonomy'];
	$terms    = get_terms( $taxonomy, array( 'hide_empty' => false ) );

	// Append an all option to the terms
	$all_term          = new stdClass();
	$all_term->term_id = 0;
	$all_term->name    = 'All';
	array_unshift( $terms, $all_term );

	if ( ! is_wp_error( $terms ) ) {
		wp_send_json_success( $terms );
	} else {
		wp_send_json_error( 'Failed to fetch terms.' );
	}
}

function epic_fetch_data( $data, $total = false, $limit = 30 ) {
	$args = array(
		'post_type'      => $data['post_type'],
		'posts_per_page' => $limit,
	);

	// Check if a taxonomy is selected and if the term_id 0 is not selected
	if ( isset( $data['taxonomy'] ) && ! in_array( 0, $data['terms'] ) ) {
		// Loop through the taxonomies and terms to build the tax_query
		foreach ( $data['taxonomy'] as $index => $taxonomy ) {
			$args['tax_query'][] = array(
				'taxonomy' => $taxonomy,
				'field'    => 'term_id',
				'terms'    => $data['terms'][ $index ],
			);
		}

		// If more than one taxonomy is selected, set the relation to OR
		if ( count( $data['taxonomy'] ) > 1 ) {
			$args['tax_query']['relation'] = 'OR';
		}
	}

	$query = new WP_Query( $args );

	if ( $total ) {
		$args['post_per_page'] = -1;
		$total_posts           = new WP_Query( $args );
	}

	return ! $total ? array( $query ) : array( $query, $total_posts->found_posts );
}