PHP调用MySQL查询时from_unixtime触发权限拒绝错误求助

Hey there, let's break down why you're hitting that error and get your query working smoothly.

First, Let's Diagnose the Error #

The "Access denied" message here points to two likely culprits:

• Your database user account doesn't have permission to use the from_unixtime() function—some hosting providers restrict certain built-in functions for security purposes.
• If you're not using MySQL, from_unixtime() isn't a valid function for your database engine (like SQL Server or SQLite, which use different time conversion methods).

On top of that, your original query isn't optimal: converting the timestamp to a date in the WHERE clause prevents the database from using any index on survey_submit_date, which will slow down queries as your table grows.

A Permission-Friendly, Efficient Alternative #

Instead of converting the timestamp to a date, convert your target dates to timestamps and compare directly with survey_submit_date. This avoids the function permission issue and lets the database use indexes for faster results.

Here's the revised SQL:

SELECT * FROM data_customer 
WHERE 
  -- Match all timestamps from 2018-03-01 00:00:00 to 2018-03-02 00:00:00
  (survey_submit_date BETWEEN UNIX_TIMESTAMP('2018-03-01') AND UNIX_TIMESTAMP('2018-03-02 00:00:00'))
  OR
  -- Match all timestamps from 2017-12-01 00:00:00 to 2017-12-02 00:00:00
  (survey_submit_date BETWEEN UNIX_TIMESTAMP('2017-12-01') AND UNIX_TIMESTAMP('2017-12-02 00:00:00'))

PHP Implementation with Prepared Statements #

To keep this safe (avoid SQL injection) and clean in PHP, use prepared statements:

// Target dates we want to filter for
$targetDates = ['2018-03-01', '2017-12-01'];

// Build dynamic conditions and parameters
$conditions = [];
$params = [];

foreach ($targetDates as $date) {
    $conditions[] = "(survey_submit_date BETWEEN UNIX_TIMESTAMP(?) AND UNIX_TIMESTAMP(DATE_ADD(?, INTERVAL 1 DAY)))";
    $params[] = $date;
    $params[] = $date;
}

// Assemble the full query
$sql = "SELECT * FROM data_customer WHERE " . implode(' OR ', $conditions);

// Execute with PDO (adjust for mysqli if you use that)
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$customerData = $stmt->fetchAll(PDO::FETCH_ASSOC);

If You Must Stick with Date Formatting #

If you absolutely need to use the date_format(from_unixtime(...)) approach, take these steps:

1. Check user permissions: Verify your database user has the EXECUTE privilege or permission to use from_unixtime(). If you have admin access, run this query:

   GRANT EXECUTE ON *.* TO 'your_username'@'your_host';
   

   If not, reach out to your hosting provider to adjust permissions.
2. Confirm database compatibility: Ensure you're using MySQL. For other engines, use the appropriate date conversion function (e.g., DATEADD(second, survey_submit_date, '1970-01-01') for SQL Server).

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