WordPress SQL Query Help

January 29, 2025 · AI Conversations

User
sqlj = "
JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
JOIN $wpdb->terms t ON (tt.term_id = t.term_id)";

// Query all records
$querystr = "SELECT
DISTINCT p.ID, p.post_title AS title, p.post_modified
FROM $wpdb->posts p
$sqlj
WHERE p.post_type = 'post' AND p.post_status = 'publish'
$sqlcon
ORDER BY p.post_date DESC";
Above is the query

Assistant
It looks like you're constructing a WordPress query using `$wpdb` to retrieve published posts with specific taxonomy terms. However, I don't see `$sqlcon` being defined in the code snippet. If `$sqlcon` is meant to add filtering conditions dynamically, make sure it's properly set before using it in the query.

Would you like me to optimize or debug anything in this query?

User
I want to export in csv all records there is 32000 thousand records
Assistant
Exporting 32,000 records to a CSV file from WordPress requires efficiently fetching the records and writing them to a CSV file without exhausting memory. Here's a step-by-step implementation for your query:

### Code to Export Records to a CSV File

function export_posts_to_csv() {
    global $wpdb;

    // Define your JOIN and SQL query
    $sqlj = "
        JOIN {$wpdb->term_relationships} tr ON (p.ID = tr.object_id)
        JOIN {$wpdb->term_taxonomy} tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
        JOIN {$wpdb->terms} t ON (tt.term_id = t.term_id)";

    // Base query
    $querystr = "SELECT DISTINCT 
        p.ID, 
        p.post_title AS title, 
        p.post_modified 
        FROM {$wpdb->posts} p  
        $sqlj 
        WHERE p.post_type = 'post' AND p.post_status = 'publish' 
        ORDER BY p.post_date DESC";

    // Fetch results using pagination to avoid memory overload
    $offset = 0;
    $limit = 1000; // Fetch 1000 records per batch
    $csv_file = fopen('php://output', 'w');

    if ($csv_file) {
        // Send CSV headers
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename=posts-export.csv');

        // Add CSV headers
        fputcsv($csv_file, ['ID', 'Title', 'Last Modified']);

        while (true) {
            // Paginated query
            $results = $wpdb->get_results($wpdb->prepare($querystr . " LIMIT %d, %d", $offset, $limit), ARRAY_A);

            if (empty($results)) {
                break; // Exit the loop if no more records
            }

            // Write each row to the CSV
            foreach ($results as $row) {
                fputcsv($csv_file, $row);
            }

            $offset += $limit;
        }

        fclose($csv_file);
        exit; // Terminate the script after file output
    }
}

// Add an action hook to trigger the export
add_action('admin_post_export_posts_csv', 'export_posts_to_csv');

### Steps to Implement
1. **Add the Code to functions.php**: Paste the above function into your theme's `functions.php` file or a custom plugin.

2. **Trigger the Export**:
– Add a link or button in the WordPress admin dashboard:

echo '<a href="' . admin_url('admin-post.php?action=export_posts_csv') . '" class="button button-primary">Export Posts to CSV</a>';

– Clicking this button will download the CSV file.

3. **Important Notes**:
– The script uses **pagination** to avoid memory exhaustion when fetching 32,000 records.
– The `php://output` stream writes directly to the browser, so no file is saved on the server.
– Make sure PHP's `max_execution_time` is high enough if exporting this large dataset.

Would you like to customize this further, such as adding specific term filters or saving the file on the server?

User
pass query parameter
Assistant
You want to pass query parameters dynamically to filter the exported CSV data. Below is an updated version of the function that accepts query parameters via `GET` and integrates them into the SQL query.

—

### **Updated Code with Query Parameters**

function export_posts_to_csv() {
    global $wpdb;

    // Get query parameters from the request
    $category = isset($_GET['category']) ? sanitize_text_field($_GET['category']) : '';
    $start_date = isset($_GET['start_date']) ? sanitize_text_field($_GET['start_date']) : '';
    $end_date = isset($_GET['end_date']) ? sanitize_text_field($_GET['end_date']) : '';

    // Construct JOINs for taxonomy filtering
    $sqlj = "
        JOIN {$wpdb->term_relationships} tr ON (p.ID = tr.object_id)
        JOIN {$wpdb->term_taxonomy} tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
        JOIN {$wpdb->terms} t ON (tt.term_id = t.term_id)";

    // Start building the WHERE clause
    $where = "WHERE p.post_type = 'post' AND p.post_status = 'publish'";

    // Filter by category (if provided)
    if (!empty($category)) {
        $where .= $wpdb->prepare(" AND t.slug = %s", $category);
    }

    // Filter by date range (if provided)
    if (!empty($start_date)) {
        $where .= $wpdb->prepare(" AND p.post_date >= %s", $start_date);
    }
    if (!empty($end_date)) {
        $where .= $wpdb->prepare(" AND p.post_date <= %s", $end_date);
    }

    // Full SQL query
    $querystr = "SELECT DISTINCT 
        p.ID, 
        p.post_title AS title, 
        p.post_modified 
        FROM {$wpdb->posts} p  
        $sqlj 
        $where 
        ORDER BY p.post_date DESC";

    // Start pagination loop
    $offset = 0;
    $limit = 1000; // Fetch 1000 records per batch
    $csv_file = fopen('php://output', 'w');

    if ($csv_file) {
        // Send CSV headers for file download
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename=posts-export.csv');

        // Add CSV column headers
        fputcsv($csv_file, ['ID', 'Title', 'Last Modified']);

        while (true) {
            // Paginated query execution
            $results = $wpdb->get_results($wpdb->prepare($querystr . " LIMIT %d, %d", $offset, $limit), ARRAY_A);

            if (empty($results)) {
                break; // Stop if no more records
            }

            // Write rows to CSV
            foreach ($results as $row) {
                fputcsv($csv_file, $row);
            }

            $offset += $limit;
        }

        fclose($csv_file);
        exit; // Stop further script execution
    }
}

// Add action hook to trigger export with parameters
add_action('admin_post_export_posts_csv', 'export_posts_to_csv');

—

### **How to Use Query Parameters?**
Call the export URL with the query parameters in the admin area:

https://yourwebsite.com/wp-admin/admin-post.php?action=export_posts_csv&category=technology&start_date=2024-01-01&end_date=2024-12-31

#### **Available Parameters:**
– `category` → Filter by category slug (e.g., `technology`).
– `start_date` → Filter posts published **on or after** this date (`YYYY-MM-DD`).
– `end_date` → Filter posts published **on or before** this date (`YYYY-MM-DD`).

—

### **How to Add a Button in Admin Panel?**
You can add this button to any admin page:

echo '<a href="' . admin_url('admin-post.php?action=export_posts_csv&category=technology&start_date=2024-01-01&end_date=2024-12-31') . '" class="button button-primary">Export Filtered Posts</a>';

—

### **Key Improvements**
✅ Supports **dynamic filtering** using `GET` parameters.
✅ Uses **pagination** to handle large datasets (32,000+ records).
✅ Ensures **secure query building** with `prepare()`.
✅ Generates a **CSV file in real-time** without memory overload.

Would you like to add more filters (e.g., author, tags)?

User
global $wpdb;

// Get category filter (if any)
$catex = intval($_GET['catex'] ?? -1);

$sqlcon = '';
$sqlj = '';

if ($catex !== -1) {
$sqlcon = "
AND tt.taxonomy = 'category'
AND t.term_id = '{$catex}'";
} else {
$sqlcon = "
AND tt.taxonomy = 'category'
AND t.term_id != '65'";
}

$sqlj = "
JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
JOIN $wpdb->terms t ON (tt.term_id = t.term_id)";

// Query all records
$querystr = "SELECT
DISTINCT p.ID, p.post_title AS title, p.post_modified
FROM $wpdb->posts p
$sqlj
WHERE p.post_type = 'post' AND p.post_status = 'publish'
$sqlcon
ORDER BY p.post_date DESC";

$pageposts = $wpdb->get_results($querystr, OBJECT);

if (empty($pageposts)) {
die('No posts found for the given criteria.');
}

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="post_report_data.csv"');
header('Pragma: no-cache');
header('Expires: 0');

// Write CSV output
$output = fopen('php://output', 'w');

// Add CSV header
fputcsv($output, ['Title', 'Url', 'Feature Image Url', 'Category', 'Revision Date', 'Meta Description']);

// Fetch and write each post's data directly
foreach ($pageposts as $post) {
// Fetch the URL for the feature image
$url = wp_get_attachment_url(get_post_thumbnail_id($post->ID), 'thumbnail');

// Fetch categories
$categories = implode(', ', array_map(function ($cat) {
return $cat->cat_name;
}, get_the_category($post->ID)));

// Write data to CSV
fputcsv($output, [
$post->title,
get_permalink($post->ID),
$url,
$categories,
get_the_modified_date('Y-m-d H:i:s', $post->ID),
get_post_meta($post->ID, '_yoast_wpseo_metadesc', true)
]);

// Flush output buffer to avoid memory issues
//ob_flush();
////flush();
}

// Close CSV output
fclose($output);
exit;
above
Convert above

Assistant
Here's an optimized version of your script, ensuring efficiency and avoiding memory overload when exporting large datasets (32,000+ records). I've included proper pagination, secure SQL preparation, and output buffering to handle large exports smoothly.

—

### **Optimized Code for Large CSV Export**

function export_posts_to_csv() {
    global $wpdb;

    // Get category filter (if any)
    $catex = isset($_GET['catex']) ? intval($_GET['catex']) : -1;

    $sqlcon = '';
    $sqlj = '';

    if ($catex !== -1) {
        $sqlcon = $wpdb->prepare("
            AND tt.taxonomy = 'category'
            AND t.term_id = %d", $catex);
    } else {
        $sqlcon = "
            AND tt.taxonomy = 'category'
            AND t.term_id != 65";
    }

    $sqlj = "
        JOIN {$wpdb->term_relationships} tr ON (p.ID = tr.object_id)
        JOIN {$wpdb->term_taxonomy} tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
        JOIN {$wpdb->terms} t ON (tt.term_id = t.term_id)";

    // Base query
    $querystr = "SELECT DISTINCT 
        p.ID, p.post_title AS title, p.post_modified 
        FROM {$wpdb->posts} p  
        $sqlj 
        WHERE p.post_type = 'post' AND p.post_status = 'publish' 
        $sqlcon 
        ORDER BY p.post_date DESC";

    // Output CSV headers
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="post_report_data.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');

    // Open output stream
    $output = fopen('php://output', 'w');

    // Add CSV column headers
    fputcsv($output, ['Title', 'URL', 'Feature Image URL', 'Category', 'Revision Date', 'Meta Description']);

    // Paginate results to prevent memory overload
    $offset = 0;
    $limit = 1000;

    while (true) {
        $paged_query = $querystr . " LIMIT $offset, $limit";
        $pageposts = $wpdb->get_results($paged_query, OBJECT);

        if (empty($pageposts)) {
            break; // Stop if no more records
        }

        foreach ($pageposts as $post) {
            // Fetch the URL for the feature image
            $url = wp_get_attachment_url(get_post_thumbnail_id($post->ID), 'thumbnail');

            // Fetch categories
            $categories = implode(', ', array_map(function ($cat) {
                return $cat->cat_name;
            }, get_the_category($post->ID)));

            // Write data to CSV
            fputcsv($output, [
                $post->title,
                get_permalink($post->ID),
                $url,
                $categories,
                get_the_modified_date('Y-m-d H:i:s', $post->ID),
                get_post_meta($post->ID, '_yoast_wpseo_metadesc', true)
            ]);
        }

        // Flush output buffer to avoid memory issues
        ob_flush();
        flush();

        $offset += $limit;
    }

    // Close CSV output
    fclose($output);
    exit;
}

// Add action hook for the export
add_action('admin_post_export_posts_csv', 'export_posts_to_csv');

—

### **Key Improvements:**
✅ **Pagination**: Uses `LIMIT` to fetch 1000 records at a time, preventing memory overload.
✅ **Secure SQL Preparation**: Uses `$wpdb->prepare()` to prevent SQL injection for category filters.
✅ **Real-Time Output Buffering**: Uses `ob_flush()` and `flush()` to stream data to the browser instead of loading everything into memory.
✅ **Category Filtering**: Supports filtering by category (`catex` parameter).

—

### **How to Use?**
1. **Trigger CSV Export with Filters**
Open the following URL in your browser:

https://yourwebsite.com/wp-admin/admin-post.php?action=export_posts_csv&catex=5

Change `catex=5` to the desired category ID.

2. **Admin Panel Button**
Add this button in the WordPress admin area:

echo '<a href="' . admin_url('admin-post.php?action=export_posts_csv&catex=5') . '" class="button button-primary">Export Posts</a>';

Replace `catex=5` with any category ID you want to filter.

—

This ensures that even with **32,000+ records**, the export will run smoothly. Let me know if you need more filters! 🚀

Leave a Comment

Your email address will not be published. Required fields are marked *