如何实现接收SQL参数并返回JSON的PHP函数？现有代码优化求助

Hey there! Let's adjust your function so it properly converts the query results into a JSON string instead of just printing raw field values. Here's how to do it step by step:

Key Changes Needed: #

1. Collect results into an array: Instead of printing each item immediately, we'll store every row of data in a PHP array.
2. Clean up database resources: Always free statement handles and close connections to avoid resource leaks.
3. Convert array to JSON: Use PHP's built-in json_encode() function to turn the collected data into a valid JSON string.
4. Add basic error handling: Catch potential issues with query parsing or execution to make debugging easier.

Modified Code: #

function dataGenerator($query) {
    // Establish database connection
    $conn = oci_new_connect('u','p','h') or die ("Database connection failed");
    
    // Parse the query
    $stid = oci_parse($conn, $query);
    if (!$stid) {
        $error = oci_error($conn);
        die(htmlentities($error['message'], ENT_QUOTES));
    }
    
    // Execute the query
    $executionResult = oci_execute($stid);
    if (!$executionResult) {
        $error = oci_error($stid);
        die(htmlentities($error['message'], ENT_QUOTES));
    }
    
    // Collect all rows into a result array
    $resultData = [];
    while ($row = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
        $resultData[] = $row;
    }
    
    // Clean up database resources
    oci_free_statement($stid);
    oci_close($conn);
    
    // Convert array to JSON (use JSON_UNESCAPED_UNICODE to preserve non-ASCII characters)
    $jsonOutput = json_encode($resultData, JSON_UNESCAPED_UNICODE);
    
    // Optional: If using this for an API, set the correct response header
    // header('Content-Type: application/json');
    
    // Return the JSON string (or echo it directly if needed)
    return $jsonOutput;
}

// Usage example
$query = 'SELECT * FROM department';
echo dataGenerator($query);

What's Different? #

• Result collection: We build $resultData as an array of associative arrays, where each sub-array represents a row from your department table (with column names as keys).
• Error handling: We check for failures in oci_parse() and oci_execute(), then output meaningful error messages instead of a generic "dbdn".
• JSON conversion: json_encode() takes our array and turns it into a valid JSON string. The JSON_UNESCAPED_UNICODE flag ensures characters like Chinese or accented letters aren't escaped, making the output more readable.
• Resource cleanup: oci_free_statement() and oci_close() release database resources, which is good practice for performance.

If you're using this function to power an API endpoint, uncomment the header() line to tell clients they're receiving JSON data.

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