2

In my mysql database I have texts full of apostropes which are used as stress marks. I need to implement keyword search ignoring these marks.

I tested it first by using direct sql in PhpMyAdmin:

SELECT * FROM `drupal_corpus_text` WHERE replace(text, "'", "") LIKE "%sinka%"

It perfectly finds the record containing sin'ka.

But I cannot figure how to do it using Drupal features.

Here is what I try to do:

  $apostrophe = "'";
  $query = db_select('corpus_text', 'n')
    ->fields('n', array('text_id', 'speaker', 'text'))
    ->condition('n.text', '%' . db_like($key_search) . '%', 'LIKE')
    ->orderBy('speaker', 'ASC');
  $query->addExpression('REPLACE(n.text, "'.$apostrophe.'", "")');
  $query->execute();

And here is the error I get:

PDOException: SQLSTATE[42S22]: 
Column not found: 1054 Unknown column ''' in 'field list': 
SELECT n.text_id AS text_id, n.speaker AS speaker, n.text AS text, 
REPLACE(n.text, "'", "") AS expression FROM {corpus_text} n 
WHERE (n.text LIKE :db_condition_placeholder_0 ESCAPE '\\') ORDER BY speaker ASC

I cannot figure out how I must properly use all the quotes and the apostrophe mark to make it work.

The symbol used in those texts is U+0027.

  • Just to say that your although your original query may work, I should note that it will be extremely slow -- it'll seem okay in a small test database, but as your database gets bigger, it will get slower and slower. SQL wildcard searches tend to be like that. If you need to do this kind of thing with a larger dataset, you will probably want to consider alternative solutions such as a Sphinx or Lucene index. – Spudley Jun 5 '16 at 15:43
  • @Spudley, thank you for your comment. I will keep the information in mind for future. Unfortunately, this exact dabatase runs on the server of some educational institution where they do not allow make any changes with the server software. I cannot use anything except the defaults. – Nat Jun 6 '16 at 6:24
3

There are a couple of points here:

  1. Your Drupal query is wrong insofar as it wouldn't produce your example SQL. You don't need to add an expression, rather a where clause
  2. Quotes are fun in MySQL (see SQL modes). Your default mode would appear to be ANSI_QUOTES, which means:

    Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character.

    Which explains why MySQL is trying to interpret the single quote as a column name. PMA probably sets a different mode per-query, so the same SQL works in that environment.

Taking those into account, this is more like what you're hoping for:

$query = db_select('corpus_text', 'n')
  ->fields('n', array('text_id', 'speaker', 'text'))
  ->where("REPLACE(n.text, '''', '') LIKE :search", array(':search' => '%' . db_like($key_search) . '%'))
  ->orderBy('speaker', 'ASC');

Which produces:

SELECT  n.text_id AS text_id, n.speaker AS speaker, n.text AS text
FROM 
{corpus_text} n
WHERE  (REPLACE(n.text, '''', '') LIKE :search) 
ORDER BY speaker ASC

Notice that the single quote is escaped with two single quotes, MySQL's way of handling this situation.

  • Just awesome! I absolutely forgot about escaping ' with another ' – Nat Jun 5 '16 at 13:26
0

The Drupal database layer has support for query arguments. A good example of this is the db_query syntax:

$query = db_query('SELECT n.nid, n.title, n.created FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid))

As you can see there are placeholders in the query, which are appended in the end. These placeholders are recognizable by the colon (:) and are sanitized in the end by Drupal to prevent SQL injections and syntax errors.

The SelectQuery::addExpression function also has support for these arguments SelectQuery::addExpression($expression, $alias = NULL, $arguments = array()). So you might want to try something like this:

$apostrophe = "'";
$query = db_select('corpus_text', 'n')
  ->fields('n', array('text_id', 'speaker', 'text'))
  ->condition('n.text', '%' . db_like($key_search) . '%', 'LIKE')
  ->orderBy('speaker', 'ASC');
$query->addExpression('REPLACE(n.text, ":apostrophe", "")', NULL, array(':apostrophe' => $apostrophe));
$query->execute();

If that fails, you can safely rewrite your query to the slightly faster db_query method. This is perfectly safe for SELECT queries (and for select queries only!) and faster because it involves less abstraction, so less processing to transform into a final query. It is however less flexible in cases where you want to add conditions based on variable values. So you cannot do this:

$query = db_select(...);
if ($foo) {
  $query->condition(...);
}

Just remember to use the placeholders for all user entered input. So I guess it will be something like this. Not sure if the apostrophe needs escaping and you can always place it in a placeholder.

$query = db_query('SELECT * FROM {drupal_corpus_text} WHERE REPLACE(text, "'", "") LIKE "%:search%"', array(':search' => $key_search));
  • 1
    Unfortunately these changes won't help, the problem is caused by MySQL interpreting double quotes as identifiers rather than string literals – Clive Jun 5 '16 at 12:41
  • I assumed the db_query should at least work because that is almost literally the original query that worked. But I admit I never struggled with single and double quotes in one query. Thanks for clarifying. – Neograph734 Jun 5 '16 at 12:44
  • It's one of those things that once you've been forced to work it out for yourself once, you don't forget :) I know people who've been using MySQL for over a decade that also have never run into this, so your experience is probably pretty common. But when it hits it's difficult to debug. I've long since stopped using double quotes with mysql for exactly this reason - removes any guessing – Clive Jun 5 '16 at 12:54
  • 1
    @Clive I thought the same after discovering this, but after a few months of not using it I still had to put a bounty on it to 'remember' it again ;) – Neograph734 Jun 5 '16 at 13:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.