Querying an SQLite Database With PHP

If you wish to query an SQLite database using PHP, you can do so by placing commands similar to those shown below within the code for a webpage:

<?php

$filename = "/Users/jasmith1/Documents/www/test/presidents.db";

$db = new SQLite3($filename);

$results = $db->query('SELECT * FROM Terms');
while ($row = $results->fetchArray()) {
    var_dump($row);
    echo "<br>\n";
}
?>

In the example above, the database location and file name is stored in the variable $filename. The file must be in a location accessible by the webserver and the file permissions must allow access to the file.

The $db = new SQLite3($filename); line is used to open the file. Note: you need to include the new even for an existing database.

The $results variable is used to hold the results of the query submitted against the database. In the example above, I have a database that has some U.S. presidents names and the start and end dates for their terms in office, which are stored in a table named Terms. If I just want to dump the contents of the Terms table in the presidents.db database, I can use the lines below:

while ($row = $results->fetchArray()) {
    var_dump($row);
    echo "<br>\n";
}

That would display the contents of the table, but not in an easy to read format. I.e., if the database contained only the first 7 presidents, I would see the following:

array(8) { [0]=> string(10) "Washington" ["Last_Name"]=> string(10) "Washington" [1]=> string(6) "George" ["First_Middle_Name"]=> string(6) "George" [2]=> string(10) "1789-04-30" ["Start_Term"]=> string(10) "1789-04-30" [3]=> string(10) "1797-03-04" ["End_Term"]=> string(10) "1797-03-04" }
array(8) { [0]=> string(5) "Adams" ["Last_Name"]=> string(5) "Adams" [1]=> string(4) "John" ["First_Middle_Name"]=> string(4) "John" [2]=> string(10) "1797-03-04" ["Start_Term"]=> string(10) "1797-03-04" [3]=> string(10) "1801-03-04" ["End_Term"]=> string(10) "1801-03-04" }
array(8) { [0]=> string(9) "Jefferson" ["Last_Name"]=> string(9) "Jefferson" [1]=> string(6) "Thomas" ["First_Middle_Name"]=> string(6) "Thomas" [2]=> string(10) "1801-03-04" ["Start_Term"]=> string(10) "1801-03-04" [3]=> string(10) "1809-03-04" ["End_Term"]=> string(10) "1809-03-04" }
array(8) { [0]=> string(7) "Madison" ["Last_Name"]=> string(7) "Madison" [1]=> string(5) "James" ["First_Middle_Name"]=> string(5) "James" [2]=> string(10) "1809-03-04" ["Start_Term"]=> string(10) "1809-03-04" [3]=> string(10) "1817-03-04" ["End_Term"]=> string(10) "1817-03-04" }
array(8) { [0]=> string(6) "Monroe" ["Last_Name"]=> string(6) "Monroe" [1]=> string(5) "James" ["First_Middle_Name"]=> string(5) "James" [2]=> string(10) "1817-03-04" ["Start_Term"]=> string(10) "1817-03-04" [3]=> string(10) "1825-03-04" ["End_Term"]=> string(10) "1825-03-04" }
array(8) { [0]=> string(5) "Adams" ["Last_Name"]=> string(5) "Adams" [1]=> string(11) "John Qunicy" ["First_Middle_Name"]=> string(11) "John Qunicy" [2]=> string(10) "1825-03-04" ["Start_Term"]=> string(10) "1825-03-04" [3]=> string(10) "1829-03-04" ["End_Term"]=> string(10) "1829-03-04" }

I could also use print_r($row), instead of

var_dump($row)

, which would display the results as shown below:

Array ( [0] => Washington [Last_Name] => Washington [1] => George [First_Middle_Name] => George [2] => 1789-04-30 [Start_Term] => 1789-04-30 [3] => 1797-03-04 [End_Term] => 1797-03-04 )
Array ( [0] => Adams [Last_Name] => Adams [1] => John [First_Middle_Name] => John [2] => 1797-03-04 [Start_Term] => 1797-03-04 [3] => 1801-03-04 [End_Term] => 1801-03-04 )
Array ( [0] => Jefferson [Last_Name] => Jefferson [1] => Thomas [First_Middle_Name] => Thomas [2] => 1801-03-04 [Start_Term] => 1801-03-04 [3] => 1809-03-04 [End_Term] => 1809-03-04 )
Array ( [0] => Madison [Last_Name] => Madison [1] => James [First_Middle_Name] => James [2] => 1809-03-04 [Start_Term] => 1809-03-04 [3] => 1817-03-04 [End_Term] => 1817-03-04 )
Array ( [0] => Monroe [Last_Name] => Monroe [1] => James [First_Middle_Name] => James [2] => 1817-03-04 [Start_Term] => 1817-03-04 [3] => 1825-03-04 [End_Term] => 1825-03-04 )
Array ( [0] => Adams [Last_Name] => Adams [1] => John Qunicy [First_Middle_Name] => John Qunicy [2] => 1825-03-04 [Start_Term] => 1825-03-04 [3] => 1829-03-04 [End_Term] => 1829-03-04 )

But I can format the display of individual records to my liking by referencing the columns within each row, e.g. $row['column'] as shown below where the each SQLite record is displayed as a row in an HTML table:

<?php

$filename = "/Users/jasmith1/Documents/www/test/presidents.db";

$db = new SQLite3($filename);

echo "<!DOCTYPE html>\n";
echo "<html lang=\"en\">\n";
echo "<body>\n";
echo "<table>\n";
echo "<tr><th>First and Middle Name</th><th>Last Name</th>";
echo "<th>Start of Term</th><th>End of Term</th></tr>\n";

$results = $db->query('SELECT * FROM Terms');
while ($row = $results->fetchArray()) {
    echo "<tr><td>" . $row['First_Middle_Name'] . "</td><td>" .
    $row['Last_Name'] . "</td><td>" . $row['Start_Term'] . "</td><td>" .
    $row['End_Term'] . "</td></tr>\n";
}

echo "</table>\n";
echo "</body>\n";
echo "</html>";

?>

First and Middle Name	Last Name	Start of Term	End of Term
George	Washington	1789-04-30	1797-03-04
John	Adams	1797-03-04	1801-03-04
Thomas	Jefferson	1801-03-04	1809-03-04
James	Madison	1809-03-04	1817-03-04
James	Monroe	1817-03-04	1825-03-04
John Qunicy	Adams	1825-03-04	1829-03-04