I've hit a bit of a snag with one of my projects. Like a lot of nerds, I decided to create my own video game review site. Reviews are stored in the database, and can be retrieved via the title of the game with a url like:
http://www.example.com/reviews/{gameName}/{optional pageOfReview}
Unfortunately, when testing edge cases, I came across an odd error - if the game has a period in its title I can't retrieve it. If the period is the leading character of the title (like .hack), I get a Kohana stack trace error screen telling me the review (or, more accurately, the game) doesn't exist. If it's in the middle or end of the title, I get one of my own error messages saying that the review (game) could not be retrieved. Is there any way around this? Is it an issue of how MySQL parses a period, or something else?
EDIT: All queries are handled via Kohana 2's ORM functionality using the MySQLi driver. Saving a review (admin controller):
public function saveReview()
{
$this->checkAdmin();
if (isset($_POST['submit'])) { $this->storeReview(); }
else { header('Location: /admin'); }
}
private function storeReview($id = null)
{
if (!preg_match("/^[a-zA-Z0-9\-_:!'. ]*$/", $_POST['gameTitle']) || empty($_POST['gameTitle'])) { $gameTitle = false; }
else { $gameTitle = ucwords($this->clean($_POST['gameTitle'])); }
if (!is_numeric($_POST['genre'])) { $genre = false; }
else { $genre = $_POST['genre']; }
$platformCheckArray = array_map('is_numeric', $_POST['platforms']);
$platformCheck = true;
foreach ($platformCheckArray as $pca)
{
if (!$pca)
{
$platformCheck = false;
break;
}
}
$proCheck = true;
$cleanedPros = array();
foreach ($_POST['pros'] as $pro)
{
if (!preg_match("/^[a-zA-Z0-9\-_:!' ]*$/", $pro))
{
$proCheck = false;
break;
}
if (!empty($pro)) { $cleanedPros[] = $this->clean($pro); }
}
$conCheck = true;
$cleanedCons = array();
foreach ($_POST['cons'] as $con)
{
if (!preg_match("/^[a-zA-Z0-9\-_:!' ]*$/", $con))
{
$conCheck = false;
break;
}
if (!empty($con)) { $cleanedCons[] = $this->clean($con); }
}
if (!is_numeric($_POST['score'])) { $score = false; }
else { $score = $_POST['score']; }
if (empty($_POST['content'])) { $content = false; }
else { $content = true; }
// save review if all tests pass, display error otherwise
if ($gameTitle && $genre && $platformCheck && $proCheck && $conCheck && $score && $content)
{
$gameTitle = $gameTitle;
$platforms = $_POST['platforms'];
$reviewContent = $_POST['content'];
$prosText = implode(', ', $cleanedPros);
$consText = implode(', ', $cleanedCons);
$game = ORM::factory('game');
$game->title = $gameTitle;
$game->genre_id = $genre;
$game->platforms = $platforms;
$game->save();
$storedGenre = ORM::factory('genre')->where('id', $genre)->find();
$storedGenre->platforms = $platforms;
$storedGenre->save();
$review = ORM::factory('review', $id);
$review->content = $reviewContent;
$review->score = $score;
$review->game_id = $game->id;
$review->date_added = date('Y-m-d H:i:s');
$review->platforms = $platforms;
$review->save();
$pros = ORM::factory('pro');
$pros->review_id = $review->id;
$pros->text = $prosText;
$pros->save();
$cons = ORM::factory('con');
$cons->review_id = $review->id;
$cons->text = $consText;
$cons->save();
if ($game->saved && $storedGenre->saved && $review->saved && $pros->saved && $cons->saved) { $this->success('review'); }
else { $this->showError("Something went wrong with saving the review. Please try again."); }
}
else { $this->showError("All fields must contain values. Please try again."); }
}
Retrieving a review (from the reviews controller):
public function show($id, $page = 1)
{
if (is_numeric($id)) { $game = ORM::factory('game', $id); }
else
{
$id = ucwords(stripslashes($id));
$game = ORM::factory('game')->where('title', $id)->find();
}
if ($game->loaded) { $this->showReview($game->id, $page); }
else { HandiError::factory('Could not retrieve the specified review. Please check that you entered the correct value.'); }
}
private function showReview($id, $page = 1)
{
$page = (int)$page;
if ($page < 1) { $page = 1; }
if ($id)
{
$game = ORM::factory('game', $id);
$review = ORM::factory('review')->where('game_id', $game->id)->find();
$genre = ORM::factory('genre')->where('id', $game->genre_id)->find();
$revPlatforms = $this->db->query("SELECT * FROM platforms
INNER JOIN platforms_reviews AS pr ON platforms.id = pr.platform_id
INNER JOIN reviews ON pr.review_id = reviews.id
WHERE reviews.id = ?", $review->id);
$revPros = ORM::factory('pro')->where('review_id', $review->id)->find();
$revCons = ORM::factory('con')->where('review_id', $review->id)->find();
$platforms = array();
foreach($revPlatforms as $rp) { $platforms[] = $rp->name; }
$pros = explode(', ', $revPros->text);
$cons = explode(', ', $revCons->text);
$pages = explode('<split />', $review->content);
$count = count($pages);
if ($page > ($count)) { $content = $pages[0]; }
else { $content = $pages[$page - 1]; }
$view = new View('reviews/show_review');
$view->content = $content;
$view->gameTitle = $game->title;
$view->genre = $genre->name;
$view->platforms = implode(', ', $platforms);
$view->pros = $pros;
$view->cons = $cons;
$view->score = $review->score;
$view->pages = $pages;
$view->render(true);
}
else { HandiError::factory('Could not retrieve the specified review. Please check that you entered the correct value.'); }
}
EDIT 2: Well, I found out something about the leading period case:
In my controller's index, I have a few queries that I use to list the reviews by game title, platform, genre, etc. It's basically a poor man's wiki. See:
public function index()
{
/* show a wiki-like page with reviews listed by title,
* game title, genre, and platform
*/
$numGenres = $this->db->query("SELECT COUNT(id) AS num FROM genres");
$numPlatforms = $this->db->query("SELECT COUNT(id) AS num FROM platforms");
$genreCount = $numGenres[0]->num;
$platformCount = $numPlatforms[0]->num;
$scoreCount = 5;
$genreResults = array();
$platformResults = array();
$scoreResults = array();
$gameResults = $this->db->query("SELECT LEFT(title, 1) AS letter, COUNT(id) AS count FROM games GROUP BY letter ORDER BY letter ASC");
for($i = 1; $i < ($genreCount + 1); ++$i)
{
$genreResults[] = $this->db->query("SELECT genres.id AS id, genres.name AS name, COUNT(reviews.id) AS num FROM reviews
INNER JOIN games ON reviews.game_id = games.id
INNER JOIN genres ON games.genre_id = genres.id
WHERE genres.id = ?", $i);
}
for($j = 1; $j < ($platformCount + 1); ++$j)
{
$platformResults[] = $this->db->query("SELECT platforms.id AS id, platforms.name AS name, COUNT(reviews.id) AS num FROM reviews
INNER JOIN platforms_reviews AS pr ON reviews.id = pr.review_id
INNER JOIN platforms ON pr.platform_id = platforms.id
WHERE platforms.id = ?", $j);
}
for($k = 1; $k < ($scoreCount + 1); ++$k)
{
$scoreResults[] = $this->db->query("SELECT score, COUNT(id) AS num FROM reviews WHERE score = ?", $k);
}
$view = new View('reviews/index');
$view->gamesByLetter = $gameResults;
$view->genres = $genreResults;
$view->platforms = $platformResults;
$view->scores = $scoreResults;
$view->render(true);
}
When I pass the results of those queries to the view, I loop through them and create links based on the meta category. So, it shows how many games start with the letter A, B, etc., and clicking on one of those links brings the user to a list of links, each one with a review (So, A->Afterburner (among others)->review for Afterburner).
When I mouse over the group that has the leading period, my status bar shows that the period is missing from the link, even though it shows up in the source. So, even though the source shows the link as site.com/reviews/game/. the browser shows it as site.com/reviews/game/ This makes me believe that the period isn't even being passed into the method, and the stack trace seems to confirm (it claims there's a missing argument, which would be the period).
EDIT 3: Okay, I took a look at my routes, and can't find anything there. That said, I do have a .htaccess file that mod_rewrites the routes to look pretty for SEO, so I'm wondering if that could be the problem. I've never written a mod_rewrite file myself - the people on the Kohana forums gave me this, and it worked, so I went with it. I can understand some of the regEx involved, but my regEx Fu is weak. I believe the last line does the 'magic'.
# Turn on URL rewriting
Options +FollowSymlinks
RewriteEngine On
# Put your installation directory here:
# If your URL is www.example.com/, use /
# If your URL is www.example.com/kohana/, use /kohana/
RewriteBase /
# Do not enable rewriting for files or directories that exist
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
# For reuests that are not actual files or directories,
# Rewrite to index.php/URL
# Original rule:
# RewriteRule ^(.*)$ index.php/$1 [PT,L]
# Alternative rule:
# RewriteRule .* index.php/$0 [PT,L]
# 2nd alternative rule that works on ICDSoft:
RewriteRule .* index.php?kohana_uri=$0 [PT,QSA,L]
If I'm reading this right, the '.' simply means any single character.
Can a '.' be used in a well-formed URL aside from where it denotes a file extension or web suffix (.com, .org, etc.)? I mean, they aren't appearing in Firefox's status bar when I hover over a link with them, which leads me to believe it's a browser/well-formedness issue, not a coding one.