WordPress电商转APP：直连MySQL获取JSON数据的问题求助

Hey there, let's break down these two problems you're facing with your custom WordPress-to-mobile app data feed—they’re both solvable with targeted tweaks.

1. Why JSON Goes Blank When You Have 20+ Products #

This almost always boils down to memory limits, encoding failures, or script timeouts. Here’s how to diagnose and fix each scenario:

a. Increase PHP Memory Limit #

When querying more posts, PHP might run out of memory mid-execution, causing the script to crash silently. Add this at the top of your custom PHP file:

ini_set('memory_limit', '256M'); // Adjust to 512M if needed
set_time_limit(300); // Extend script timeout to 5 minutes (300 seconds)

You can also update your wp-config.php to apply this globally for WordPress:

define('WP_MEMORY_LIMIT', '256M');

b. Optimize Your Database Query #

Avoid selecting all columns with SELECT *—only fetch the fields your app actually needs (e.g., title, price, image URL). This reduces data size and speeds up processing:

// Example optimized query (adjust table/column names to match your setup)
$query = "
    SELECT p.ID, p.post_title, pm.meta_value AS price 
    FROM wp_posts p
    JOIN wp_postmeta pm ON p.ID = pm.post_id
    WHERE p.post_type = 'product' 
      AND p.post_status = 'publish'
      AND pm.meta_key = '_price'
";

c. Debug JSON Encoding Failures #

If your data contains non-UTF-8 characters or unencodable values (like binary data), json_encode() will fail silently. Use these flags to catch errors and output partial data:

$products = [];
// Fetch data from DB and populate $products array...

$json_output = json_encode($products, JSON_UNESCAPED_UNICODE | JSON_PARTIAL_OUTPUT_ON_ERROR);

// Check for encoding errors
if (json_last_error() !== JSON_ERROR_NONE) {
    error_log('JSON Encoding Error: ' . json_last_error_msg()); // Log to WP error log
    http_response_code(500);
    echo json_encode(['error' => 'Failed to process product data']);
    exit;
}

// Ensure no extra whitespace is sent before JSON
ob_end_clean(); // Clear any unintended output
echo $json_output;

d. Clear Unintended Output #

Sometimes hidden whitespace (from empty lines in your PHP file) breaks JSON. Add ob_start(); at the very top of your file to buffer output, then clear it before sending JSON:

<?php
ob_start(); // Start output buffering

// Your DB query and data processing code...

ob_end_clean(); // Clear all buffered output
echo $json_output;
?>

2. Securing Your JSON Feed URL #

Exposing unprotected JSON data is a big risk—here’s how to lock it down:

a. Add API Key Authentication #

Require a secret API key in the request header to restrict access to only your mobile app. Add this at the start of your PHP file:

$VALID_API_KEY = 'your-unique-secret-key-keep-this-safe';

// Check for the API key in request headers
if (!isset($_SERVER['HTTP_X_API_KEY']) || $_SERVER['HTTP_X_API_KEY'] !== $VALID_API_KEY) {
    http_response_code(403);
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

In your mobile app, include this header in every request: X-API-Key: your-unique-secret-key-keep-this-safe

b. Restrict Request Methods #

Only allow the HTTP method your app uses (e.g., GET or POST) to block malicious requests:

if ($_SERVER['REQUEST_METHOD'] !== 'GET') {
    http_response_code(405);
    echo json_encode(['error' => 'Method not allowed']);
    exit;
}

c. Use WordPress Built-in Functions Instead of Direct DB Queries #

Direct MySQL queries skip WordPress’s security layers (like SQL injection protection). Replace your raw queries with WP_Query or get_posts():

// Example using WP_Query to fetch products
$args = [
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1, // Get all products
    'meta_query' => [
        [
            'key' => '_price',
            'compare' => 'EXISTS'
        ]
    ]
];

$product_query = new WP_Query($args);
$products = [];

while ($product_query->have_posts()) {
    $product_query->the_post();
    $products[] = [
        'id' => get_the_ID(),
        'title' => get_the_title(),
        'price' => get_post_meta(get_the_ID(), '_price', true)
    ];
}

wp_reset_postdata();

d. Add Rate Limiting #

Prevent brute-force attacks by limiting how many requests a single IP can make in a window. Use WordPress transients to track request counts:

$ip = $_SERVER['REMOTE_ADDR'];
$rate_limit_key = 'feed_request_count_' . $ip;
$request_count = get_transient($rate_limit_key);

if ($request_count && $request_count > 100) { // Limit to 100 requests per hour
    http_response_code(429);
    echo json_encode(['error' => 'Too many requests. Try again later.']);
    exit;
}

// Update request count
set_transient($rate_limit_key, ($request_count ?? 0) + 1, 3600); // Expire after 1 hour

内容的提问来源于stack exchange，提问作者Kazi Kowshik