PHP SQLite

About SQLite

SQLite is a relational database management system contained in a C programming library.
The database is contained in a file.
As compared to other DB management systems, SQLite is not client–server. It is embedded into the end program.

Create Data

Since one needs data to have a database, and I have recently been in Arizona I figured I would make a DB of some of the minerals of Arizona. Thanks to the web site
http://www.arizonaedventures.com/arizona/blog/articles/arizona/arizona-rocks-and-minerals/

Then the data needs to be put into a form that can be entered into the DB. This could be done manually, entry by entry.
I scraped the data off the above web page and put it into a .csv file.

myMinerals.txt

The other choice I made was to just put the name of the mineral image files into the DB rather than the actual data. Then reference the files in the created html. You could put the binary image data into the DB and then pull that out as an image. For another day.

Create Database

Since this is php and sqlite I created a php program to create the database. With Assistance from https://www.tutorialspoint.com/sqlite/sqlite_php.htm

makeDB.php

Create a Table in the Database

Again a php program

makeTable.php

Populate Database

And populate the DB with the data in my .csv file. Again a php program.
populateTable.php

Query Database

Query the Database
Two parts - the query part and the results from the query.

Things to note about what I have done to the PHP Config:

Display Errors

Centos: /etc/php.ini
Raspbian /etc/php5/apache2/php.ini

; Default Value: On
; Development Value: On
; Production Value: Off
; http://php.net/display-errors
display_errors = On

I turn display_errors on so that the errors are displayed on the web page. As you can imagine a bad idea in a production machine. Leave the errors in the log file.

Pretty Printing

Centos:

/etc/httpd/conf.d/php.conf

#
# Uncomment the following line to allow PHP to pretty-print .phps
# files as PHP source code:
#
AddType application/x-httpd-php-source .phps

Raspbian:

/etc/apache2/mods-enabled/php5.conf

<FilesMatch ".+\.phps$">
   SetHandler application/x-httpd-php-source
   # Deny access to raw php sources by default
   # To re-enable it's recommended to enable access to the files
   # only in specific virtual host or directory
   ###DR #Require all denied
</FilesMatch>

Again, this is a security issue. You don't want to leave .phps files laying about, or allow this feature to be enabled on production (facing the Internet) servers.