使用PHP和HTML输入搜索Sqlite数据库

Question

EDIT - CODE BELOW SHOWS CORRECTIONS

I need to create a search function that will search a SQLite database for a certain date range.

For example:

• if the user were to input "7", it would look for all records that have a date within the next 7 days.
• If the user were to input "-7" it would look for all records in the last 7 days.

So far I have:

if (!empty($_POST)) {
    $sql = "SELECT * from table where record_date between date('now') and date('now', :record_date||' days')"; 
    $stmt = $db->prepare($sql);
    $stmt->bindValue(':record_date' , '%'.$webdata['record_date'].'%', SQLITE3_TEXT);
    $result = $stmt->execute();
    while ($row = $result->fetchArray()) { 
        echo '<p>' . htmlspecialchars($row['id'])  . " : " . 
        htmlspecialchars($row['record_date']).'</p>';
    }
}
?>

This doesn't seem to bring through any results.

If I use SqliteOnline.com to query the database this does work fine with the query:

SELECT * from table where record_date between date('now') and date('now', '7 days')

Answer 1

Named placeholders shouldn't be quoted as they are then interpreted as a literal value.

Try changing:

date('now', ':record_date days')

to:

date('now', :record_date||' days')

---

You also need to modify the following from:

$stmt->bindValue(':record_date' , '%'.$webdata['record_date'].'%', SQLITE3_TEXT);

to:

$stmt->bindValue(':record_date' , $webdata['record_date'], SQLITE3_TEXT);