撇号和冒号在pretty的链接 [英] Apostrophes and Colons in Pretty Links
问题描述
我要寻找解决涉及pretty的链接或固定链接，列出书籍的标题，比如一个问题：
I am looking to solve a problem dealing with "pretty links" or "permalinks" that list the title of books, such as:
http://www.example.com/title/The-Catcher-in-the-Rye/
当我处理常规书名，这样，有简单的单词或空格，是没有问题的，因为我可以简单地用一个破折号替换空间 - 和交易与做一个反向查找数据库中的书名 str_replace转换。
When I deal with regular book titles, such that have simple words or spaces, there is no problem, since I can simply replace the space with a dash - and deal with finding the book title in the database by doing a reverse str_replace.
然而，问题出现了，当我有有撇号的书籍或冒号标题：在其中或两者作为在本实施例
However, the problem arises when I have titles of books that have either apostrophes ' or colons : in them, or both as in this example:
Why Can't I Be You: A Novel
在我的SQL数据库，所有的单引号被转义，这样在数据库中的分录如下：
In my sql database, all single quotes are escaped, so that the entry in the database is as follows:
+-----+-------------------------------+
| BID | book_title |
+-----+-------------------------------+
| 1 | Why Can\'t I Be You: A Novel |
+-----+-------------------------------+
当我列出所有的书名，我再次反向转义的字符串，所以它只是列举为：为什么我不能成为你：一种新型
When I list all book titles, I again unescape the string, so it lists simply as: Why Can't I Be You: A Novel
我的＆LT; A＆GT; 链接显示的转义称号，由破折号替代空间和省略的撇号和冒号，如下所示创建的pretty的链接：
My <a> links show the unescaped title, with the pretty links created by replacing spaces with dashes and omitting the apostrophes and colons as follows:
<a href="http://www.example.com/title/why-cant-i-be-you-a-novel" title="Why Can't I Be You: A Novel">Why Can't I Be You: A Novel</a>
所以，让我的问题。我希望能够列出书格式（转义）的所有冠军，并有固定链接/pretty的链接带连字符的工作，返回正确的标题GET方法。
So, getting to my problem. I want to be able to list all the titles of the books formatted (unescaped) and have the "permalinks" / "pretty links" with hyphens work and return the proper title to the GET method.
在我的的.htaccess 项，我有如下的重写规则：
In my .htaccess entry, I have the following RewriteRule:
RewriteRule ^title/(.*[^/])/?$ viewbook.php?booktitle=$1 [NC,L]
这样做是拿后面的pretty的链接部分标题/ ，并通过GET发送到 viewbook.php 。因此，例如，这本书的麦田里的守望者，以下是通过GET发送的：的捕手，在最守望者
What this does is take the "pretty" link portion that follows title/ and sends it via GET to viewbook.php. So for example for the book The Catcher in the Rye, the following is sent via GET: The-Catcher-in-the-Rye
没有问题存在，因为它是在PHP简单的解决这个问题：
No problem there, since it is simple in php to resolve this issue:
$booktitle = $_GET['booktitle'];
$goodBookTitle = str_replace('-', ' ', $booktitle);
// or we can do it all at once
$booktitle = str_replace('-', ' ', $_GET['booktitle']);
// Send $booktitle to SQL query and find the book
这工作正常，没有发现撇号的时候，但是，这种方法不会帮助，如果标题有撇号或冒号，因为它不会在数据库中找到。我也不想使用其中BOOK_TITLE LIKE'％$ booktitle％，因为 viewbook.php 必须是精确匹配。
This works fine when no apostrophes are found, however, this method does not help if the title has either apostrophes or colons since it will not be found in the database. I also do not want to use the WHERE book_title LIKE '%$booktitle%' since viewbook.php has to be the exact match.
我要寻找一个优雅和简单的解决方案，这将使我通过重写规则来解决这个问题，而不必额外的表添加到数据库中的发言权塞或永久链接，我不希望有撇号的URL，例如27％的单引号。这是该数据录入工作是在为preadsheet，出口到CSV和上传到SQL数据库的大型数据库。没有前端个别条目才能允许这样的事情塞或同等学历。
I am looking for an elegant or simple solution which will enable me to solve this via a RewriteRule and not have to add extra tables to the database for say slug or permalink, and I don't want to have apostrophes in the url such as %27 for single quote. This is a large database on which data entry is done in a spreadsheet, exported to CSV and uploaded into the SQL database. There is no front end for individual entries to allow for such things as slug or equivalent.
我希望我的解释是明确的。
I hope my explanation is clear.
推荐答案
首先，存储逃脱串数据库的想法看起来很奇怪。 MySQL是能够存储的任意字符的字符串，甚至可以安全地存储二进制序列。
First of all, the idea of storing escaped strings in database looks strange. MySQL is able to store strings of arbitrary characters and even can safely store binary sequences.
现在大约从真正的标题，以pretty的网址和背部的映射。这个想法标题转换为URL友好的字符串，然后回不是解决你的问题，常见的方法，因为这是很难做出这样的转变是可逆的。通常的方式来解决这个问题的方法是有单独的列中包含修饰为网址友好书名数据库。还重视在此列应是唯一的。该表可以是这样的：
Now about mapping from real titles to pretty URLs and back. The idea to convert title to URL-friendly string and then back is not the common way to solve your problem, because it is very hard to make such conversion reversible. Usual way to solve this problem is to have separate column in the database that contains book title modified to be URL-friendly. Also values in this columns should be made unique. The table could look like this:
+-----+-----------------------------+----------------------------+
| BID | book_title | book_title_url |
+-----+-----------------------------+----------------------------+
| 1 | Why Can't I Be You: A Novel | why-can-t-i-be-you-a-novel |
+-----+-----------------------------+----------------------------+
您应该索引的表此列，并使用 BOOK_TITLE 的它，而不是在SQL查询中的 viewbook.php 这样的脚本：
You should index your table by this columns and use it instead of book_title in SQL query inside your viewbook.php script like this:
SELECT * FROM books WHERE book_title_url='$booktitle'
其中， $ booktitle 包含通过 $ _ GET ['booktitle'] 并妥善逃到$ P接收书名$ pvent SQL注入攻击。
Where $booktitle contains book title received via $_GET['booktitle'] and properly escaped to prevent SQL injections.
所以，你的pretty的网址看起来像 http://www.example.com/title/why-can-ti-be-you-a-novel ，他们将被Apache改写为类似于 http://www.example.com/viewbook.php?booktitle=why-can-ti-be-you-a-novel 。
So your pretty URLs will look like http://www.example.com/title/why-can-t-i-be-you-a-novel and they will be rewritten by Apache to something like http://www.example.com/viewbook.php?booktitle=why-can-t-i-be-you-a-novel.
再次，这是常用的方式pretty的网址通常是如何实现的。希望它会为你工作了。
Again, this is common way how pretty URLs are usually implemented. hope it will work for you too.
有关现有记录，您可以填充 book_title_url 列通过这样的：
For existing records you can populate book_title_url column by something like this:
UPDATE books SET book_title_url=REPLACE(REPLACE(REPLACE(book_title, " ", "-"), ":", "-"), "'", "-");
这篇关于撇号和冒号在pretty的链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
