Modifying URL's to Escape Apostrophes

I’m working on a site that displays pages focusing on animals. The value ursus-maritimus in my database matches the URL mysite/life/ursus-maritmus (polar bear).

But I also want to display the same page using common names. So I have another database table that contains the value ‘polar bear’ Using str_replace, I can now display the same page at mysite/life/polar-bear

The problem is apostrophes and accents. For example, consider the database value Grevy’s zebra. [Note: I don’t have stick figure apostrophes in my database. That should look like Grevy&-#-8-2-1-7-;s without the hyphens] I want a “clean” URL that looks like this: grevys-zebra.

So how do I tell my query to ignore apostrophes, accents, etc. in my database values?

This is what my code looks like right now:

$CommonURL = str_replace('-', ' ', $MyURL);

$sql = "SELECT SUM(num) as num FROM (
  SELECT COUNT(Name_Common) AS num FROM gz_names_kingclass WHERE Name_Common = :CommonURL
  UNION ALL
  SELECT COUNT(Name_Common) AS num FROM gz_names_species_mammals WHERE Name_Common = :CommonURL
  ) AS X";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':CommonURL',$CommonURL,PDO::PARAM_STR);
$stmt->execute();
$Total = $stmt->fetch();

Thanks!

You can use REPLACE in query, but this is a bad practice.
It will be much better if you make additional column for URLs and fill it with pre-defined values.
So then you can select rows where URL = :CommonURL without any additional manipulations.

Write a simple PHP script which selects all animals, makes URL from name of each of them and stores it back into URL column

I guess creating a new field with values like ‘grevys-zebra’ would be a lot simpler. Thanks.

That is indeed the best and standard way to go. Such a column is typically called a slug.

Ah, I wondered what the term “slug” meant; I’ve seen it a lot, but didn’t really get it. Thanks.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.