PHP操作SQLite数据库：如何查询指定日期区间内的预订数据

Hey Ryan, let's work through that date range query issue you're facing with your SQLite-powered booking system. I’ve dealt with plenty of similar SQLite date handling quirks before, so let’s break this down step by step.

Your Current Setup Recap #

• You’ve built a custom PHP+HTML system using SQLite for booking management
• Basic DB connections and simple count queries are working fine
• The problem: You can’t get a query to return bookings between 2018-02-20 and 2018-02-25 to execute successfully

Common Causes & Fixes #

Let’s start with the most likely issues and how to resolve them:

1. Verify Your Date Field’s Storage Format

SQLite doesn’t have a native DATE type like other databases—it stores dates as either TEXT, INTEGER, or REAL. The format directly affects how you write your query:

• TEXT type: Must use ISO 8601 format (YYYY-MM-DD) for SQLite to correctly parse and compare dates. If your dates are stored as MM/DD/YYYY or another non-standard format, the range query will fail.
• INTEGER type: Stores dates as Unix timestamps (seconds since epoch). You’ll need to convert your human-readable dates to timestamps in the query.

Check your database screenshot to confirm which format your booking date field uses.

2. Write the Correct SQL Query

Based on your date field type, use one of these tested queries:

For TEXT Fields (YYYY-MM-DD Format)

This is the most common setup for SQLite booking systems. Use either BETWEEN or explicit range operators:

-- Option 1: Using BETWEEN
SELECT COUNT(*) AS total_bookings
FROM bookings
WHERE booking_date BETWEEN '2018-02-20' AND '2018-02-25';

-- Option 2: Explicit >= and <= (more readable for edge cases)
SELECT COUNT(*) AS total_bookings
FROM bookings
WHERE booking_date >= '2018-02-20' 
  AND booking_date <= '2018-02-25';

For INTEGER Fields (Unix Timestamps)

Convert your target dates to timestamps using SQLite’s strftime function. Add 23:59:59 to the end date to include all bookings from that day:

SELECT COUNT(*) AS total_bookings
FROM bookings
WHERE booking_date >= strftime('%s', '2018-02-20')
  AND booking_date <= strftime('%s', '2018-02-25 23:59:59');

3. Implement the Query Safely in PHP

Use prepared statements to avoid SQL injection and ensure parameter values are passed correctly. Here’s a PDO example (recommended for SQLite):

// Assume you already have a PDO connection to your SQLite DB
$pdo = new PDO('sqlite:your_database_file.db');

$startDate = '2018-02-20';
$endDate = '2018-02-25';

// Prepare and execute the query
$sql = "SELECT COUNT(*) AS total_bookings FROM bookings WHERE booking_date BETWEEN :start AND :end";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':start', $startDate);
$stmt->bindParam(':end', $endDate);
$stmt->execute();

// Fetch the result
$result = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Total bookings in range: " . $result['total_bookings'];

4. Debugging Tips

• Double-check your field name spelling (e.g., did you write booking_date instead of book_date?)
• Enable PHP error reporting to catch SQL syntax issues:

  error_reporting(E_ALL);
  ini_set('display_errors', 1);
  

• Test your query directly in a SQLite tool (like DB Browser for SQLite) first—if it works there, the issue is in your PHP code, not the SQL itself.

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