SELECT * in SQLite doesn't return everything in the table

Question

I'm using PHP to fetch data from a database.

Here is the code:

<?php  
    $db = new SQLiteDatabase("testDB.db");
    $query = $db->query("SELECT * FROM blog ORDER BY blogdate DESC");
    while($entry = $query->fetch(SQLITE_ASSOC)) { //only one blog entry per day
        echo "<a href=\"display.php?date=".$entry['blogdate']."\">".$entry['blogdate']."</a><br>";
    }
?>

But for some reason it doesn't return an entry that I am certain is in the database. The reason I think it's in the db is that I can see the text of the entry when I view the *.db file.

Here are some specific questions that might help me better understand what's going on:

I CREATE a table called blog. I INSERT tuples into blog using the query function as I did with the SELECT calls. When I DELETE tuples using LIKE, are those tuples being deleted from the database, or are they being deleted from the table blog only? If it is the latter case, how do I get SQLite to delete the tuple from the database completely?

Finally, I've observed some odd behavior. A tuple is added to blog with blogdate as "2009-12-1" (which I treat as a string because there's not date or time type in SQLite). When I run the PHP file with the above code, the entry with 2009-12-1 as blogdate does not show up. I ran another PHP page that searches for tuples with blogdate LIKE 2009-12-1, and it showed up in the search results. Only then did the tuple for 2009-12-1 show up when I SELECT *d for it using the PHP above.

Answer 1

To check your assertion that the data is in the database, I would suggest opening the database in an database browser.

Answer 2

Viewing the binary database file is insufficient to show that a record actually exists in a database. Many databases don't bother actually removing the data from the data file, as that would be a waste of time. Instead, they may flag the block as "deleted", and overwrite it later when saving additional data. So, you assertion that the record is in the database because you can see it in the .db file means nothing, you need to open the file in a program designed to browse the contents of the database and see if it shows up.

Answer 3

The text of a record may show up when you view a DB file in a text/hex editor even though it may have been marked as deleted in the database.

To fully remove these deleted records, try compacting the database.