Manage your data: Queries
In SQL, you store and get records from the database using queries. The CREATE TABLE text is a query. If we wanted to get all of the votes for, say, the presidential poll, we would use a select query:
SELECT vote FROM votes WHERE ballot="president";
That selects every record from the votes table pertaining to the presidential ballot. You can type this query in the sqlite3 command line program. Currently, you won’t see anything, since we haven’t added records to the table.
Create records using the insert query.
INSERT INTO votes (ballot, vote, address, timeVoted, displayName) VALUES ("president", "2", "10.12.11.6", "2012-08-11 22:38:12", "Thomas Jefferson");
This will create one record for a ballot in the presidential poll. If you now run the above select query again, you should see the number (more specifically, the text) “2”. Change the select query to:
SELECT vote, displayName FROM votes WHERE ballot="president";
You should see “2|Thomas Jefferson”. In the sqlite3 command line, the pipe separates column values.
Save records
Subclass VoteCounter into SQLVoteCounter. We are going to override the save method to save to a database rather than to a file.
class SQLVoteCounter extends VoteCounter {
protected $database = '/home/USERNAME/ballots/ballots.sqlite';
//store the vote, if it exists, to a SQLite database
public function save() {
if ($this->submitted && $this->value !== '') {
$choice = $this->value;
$ballots = new PDO("sqlite:{$this->database}");
$insert = 'INSERT INTO votes (ballot, vote, address, timeVoted, displayName) VALUES (:ballot, :vote, :address, :timeVoted, :displayName)';
$inserter = $ballots->prepare($insert);
$values = array(
':ballot'=>$this->fieldName,
':vote'=>$choice,
':address'=>$_SERVER['REMOTE_ADDR'],
':timeVoted'=>gmdate('Y-m-d H:i:s'),
':displayName'=>$this->choices[$choice],
);
$inserter->execute($values);
$ballots = null;
$_SESSION[$this->fieldName] = $choice;
}
}
}
The IP address is easy: store REMOTE_ADDR. For dates and times, however, SQL uses a more readable format. Rather than the number of seconds since an arbitrary time, SQL uses a “YYYY-MM-DD HH:MM:SS” format. This is easy enough to create with a date/time format string. However, when storing dates/times in a database, it’s a good idea to use universal time. This ensures that if your server changes timezone—for example, you change servers from one in Philadelphia to one in Texas—your dates and times remain comparable.
PHP’s gmdate function works just like the date function, but it returns the results in Greenwich Mean Time.
gmdate('Y-m-d H:i:s');
Switch to the new SQLVoteCounter class in poll.php:
$fictions = new SQLVoteCounter('character', $imaginaries);
…
$election = new SQLVoteCounter('president', $presidents);
Kill your cookies, reload the page, and make another vote. If you look in the database using the command-line sqlite, you should see something like:
sqlite> select * from votes;
president|2|10.12.11.6|2012-08-12 18:14:35|Thomas Jefferson
The vote was successfully stored.
Display records
The votes are still being displayed from the flat file. We need to create a SQLResults class that will handle SQLite data instead of flat files. The easiest way to do this, since the SQLVoteCounter class already knows the ballot name ($this->fieldName) and database location, will be to pass the SQLResults class an executed query.
public function counts() {
$ballots = new PDO("sqlite:{$this->database}");
$query = 'SELECT vote, count(vote) AS voteCount FROM votes WHERE ballot=:ballot GROUP BY vote ORDER BY voteCount DESC';
$results = $ballots->prepare($query);
$results->execute(array(':ballot'=>$this->fieldName));
$results = new SQLResults($results);
$ballots = null;
return $results;
}
This creates a new PHP Data Object from the SQLite database file. It then constructs a query to get only the rows corresponding to this object’s poll. Then it uses the results of that query to construct a SQLResults object.
SQLResults extends the Results class. Currently the Results class does all its work in the __construct method. But we only need to override the part that reads in and constructs the votes property. Separate that part out of __construct in Results and put it in its own method:
public function __construct($votefile) {
$this->readVotes($votefile);
$this->maximum = max($this->votes);
$this->multiplier = 100/$this->maximum;
}
protected function readVotes($votefile) {
$votes = file($votefile, FILE_IGNORE_NEW_LINES);
$votecounts = array_count_values($votes);
arsort($votecounts);
$this->votes = $votecounts;
}
Now that the data import section is its own method, we can override that method in a new class.
class SQLResults extends Results {
//$ballots is a PDO statement object that has already had a query executed
protected function readVotes($ballots) {
$votecounts = array();
foreach ($ballots->fetchAll() as $row) {
$votecounts[$row['vote']] = $row['voteCount'];
}
$this->votes = $votecounts;
}
}
This uses the SQLite query to get the same information that array_count_values originally got. It loops through the results to create a votes property that has the vote as the key and the number of votes as the value. The values are sorted according to who got the most votes, so arsort is unnecessary.
It should display exactly what the flat file version of the vote counter displayed. It will start over with new votes, of course, since it’s a new file. You’ll need to repeatedly delete your server’s cookie to keep testing the voting.
