Android Programming – How to Log SQL queries to Specified Log-files with PHP and Linux

As you might already know if you’ve been following my progress here, I’ve been working on developing an Android app. Unfortunately, I’ve hit a roadblock with an elusive bug that’s been preventing me from fetching SQL query results from my MySQL database. To tackle this issue and facilitate the debugging process, I came up with a method that enables PHP to log the results of executed queries into a designated log file. Here’s how I went about it.

PHP file:

function displayStoreNameList($email, $storeName, $dateFrom, $dateTo) {
    global $db;

    //SQL select query
    $sql = "SELECT * FROM user_spending
    WHERE email = '$email'
      AND store_name = '$storeName'
      AND spending_date BETWEEN '$dateFrom' AND '$dateTo'";

    //Attempt to open the log file for writing
    $logFilePath = "/var/log/api_error_log/query.log";
    $file = fopen($logFilePath, "w");

    if ($file === false) {
        error_log("Failed to open log file for writing: $logFilePath");
        return false; // Return an error indicator
    }

    // Write the SQL query to the file
    $writeResult = fwrite($file, $sql);

    if ($writeResult === false) {
        error_log("Failed to write SQL query to log file: $logFilePath");
        fclose($file);
        return false; // Return an error indicator
    }

    // Close the file
    fclose($file);

    // If writing was successful, execute the SQL query
    $result = mysqli_query($db, $sql);

    if ($result === false) {
        error_log("Failed to execute SQL query: " . mysqli_error($db));
    }

    return $result;
}

Linux command-lines:

Use those commands to change directories within the var directory.

user@gubuntuserver$ sudo -s

Within the log directory, I established a subdirectory named “api_error_log” and, inside it, generated a file called “query.log.”

root@gubuntuserver# cd /var/log/api_error_log/

Change the owner/group of the file to www-data, which represents apache2’s user.

chown www-data:www-data query.log

This is the SQL query that is logged to the file.

SELECT *
    FROM user_spending
    WHERE email = 'masterplan_life@protonmail.com'
      AND store_name = 'amazon'
      AND spending_date BETWEEN '2023-01-01' AND '2023-10-11'