Create the file
In SQLite, your database is stored as a file on the server. If your server has the sqlite3 command installed, you can access the database from the command line. You can also download the file regularly and look at it through various graphical interfaces you can download or purchase.
Like your include files, your database files should be outside of your web root. There should be no chance of someone visiting your web site and downloading your database directly!
Create the table
There are two pieces of data I try to always collect on form submissions: the visitor’s IP address and the time that they made the submission. I might also collect other information if I think it would be useful, such as their browser/operating system, but that and everything else can be faked. The IP address and time, however, can be manipulated but they can’t be faked. They might choose to make one submission at home, one at Starbucks, and one at McDonald’s, and even one via a proxy, but they actually did make their submissions from those places. Barring a major misconfiguration on your server, the IP address the server sends to is the one they’re listening at. And there’s no way for them to convince your server that it’s a different time than it really is.
For this table, let’s store the field name, the ballot response, the IP address of the voter, and the time of the vote. Let’s also store the publicly-displayed name of their ballot response as well, just in case we need that later.
You can create this using the sqlite3 command if it’s installed:
sqlite3 ~/ballots/ballots.sqlite
CREATE TABLE votes (
ballot VARCHAR(50) NOT NULL,
vote VARCHAR(50) NOT NULL,
address VARCHAR(40) NOT NULL,
timeVoted DATETIME NOT NULL,
displayName VARCHAR(100) NOT NULL
);
.quit
chmod o+wr ~/ballots/ballots.sqlite
The CREATE TABLE command may be slightly different for different SQL database servers. For example, when using the sqlite3 command, you don’t want to type the tabs; I’ve included them here to make the CREATE TABLE command easier to read.
You can use “.schema” in sqlite3 to see the tables you’ve created.
If you don’t have sqlite3 on your server, but you do have it on your client, you can create the database file and then upload it. Just make sure that the file is writable by the web server, using something like the chmod command above.
If you don’t have the sqlite3 command anywhere, you can create the database using PHP. You’ll need to create a special file, view that file, and then delete that file so no one else can use it. You could call it create.php and put something like this in it:
<?php
$filename = '/home/USERNAME/ballots/ballots.sqlite';
touch($filename);
$database = new PDO("sqlite:$filename");
$database->query(
'CREATE TABLE votes (
ballot VARCHAR(50) NOT NULL,
vote VARCHAR(50) NOT NULL,
address VARCHAR(40) NOT NULL,
timeVoted DATETIME NOT NULL,
displayName VARCHAR(100) NOT NULL
)'
);
?>
This should create the database file for you, and one table in that database.
Create storage: Tables
A table is kind of like the file that the current version of VoteCounter uses to store votes. Each record in the table is like one line in the votes file. Each record consists of columns, which makes it easy to associate a lot of data with each record. Where with the file it was easiest to store just the voter’s choice, in the “votes” database table here we have a column for their vote, a column for the ballot they were voting in, a column for their IP address, a column for when they voted, and a column for what they saw when they voted.
