5
\$\begingroup\$

I have a column where, pretty much, each entry has numbers (specifically #1-4) and spaces (' '). I wanted to make a query that could avoid having to know these numbers and spaces.

$results = $db->query( "SELECT CHS, PIN, DEF FROM MAIN WHERE CHS LIKE '%".$q."%' OR REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PIN, '1',''),'2',''),'3',''), '4',''),' ','') LIKE '%".$q."%' OR PIN LIKE '%".$q."%'");

The part that I'm worried (read:ashamed) about is:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(PIN, '1',''),'2',''),'3',''), '4',''),' ','')

Is this a super bad way to code?

\$\endgroup\$
2
  • \$\begingroup\$ Can you go into a little more depth about what the query is actually supposed to do? It seems that PIN may eg be 1abc. You then check if that value without the number 1 is like the keyword, and then if the whole thing is like the keyword? So basically abc LIKE $q OR 1abc LIKE $q? In that case, why do you even need the first part of this? \$\endgroup\$
    – tim
    Commented Dec 6, 2015 at 12:16
  • 2
    \$\begingroup\$ @tim its Chinese pinyin, the pronunciation for words, there are four tones and often more than one syllable. 指日成功 for instance would be “zhi3 ri4 cheng2 gong1”, so my method would allow "zhirichenggong" to find what I'm looking for. \$\endgroup\$
    – Mou某
    Commented Dec 6, 2015 at 13:09

1 Answer 1

Reset to default
2
\$\begingroup\$

Very interesting question!

I can't see which other way could be used in SQL by itself.

But an alternative method might be to prepare a regexp pattern, then use it with REGEXP, like this:

function tones_pattern($str) {
  for ($i = 0, $n = strlen($str); $i < $n; $i++) {
    @$pattern .= $str[$i] . '([1-4] )?';
  }
  return @$pattern;
}

$results = $db->query("
SELECT CHS, PIN, DEF
FROM MAIN
WHERE CHS LIKE '%" . $q . "%'
OR PIN REGEXP '" . tones_pattern($q) . "'
OR PIN LIKE '%" . $q . "%'
");

So you get rid of what ashames you :)
But I didn't test it, and it might have performance issues. Let me know!

Note: I wrote the $pattern above according what I understand from the example you gave in your comment, i.e. each tone digit is always followed by a space. So two points:

  1. If the above rule is true, then your own solution could already be a bit simplified into
    REPLACE(REPLACE(REPLACE(REPLACE(PIN, '1 ',''),'2 ',''),'3 ',''), '4 ','')
  2. At the opposite if the space is not always present, my $pattern must be turned from '([1-4] )?' to [1-4]? ?
\$\endgroup\$
11
  • \$\begingroup\$ yuck: @$pattern. Don't use the suppressor of death. Especially in this case, where you can simply fix the notice by declaring $pattern = ''; at the top of the function body \$\endgroup\$
    – Elias Van Ootegem
    Commented Dec 7, 2015 at 11:27
  • \$\begingroup\$ @EliasVanOotegem. I dont agree at all. It seems there is kind of obscurantist bias about this, leading a lot of people to consider it must not be used. That's quite abusive: why does it exist if it shouldn't be used? Do you think that PHP's team keeps it actual only by guilty laxity? In fact we merely must be conscious of how we use it, and precise when we use it. The current example uses it in a totally legitimate way, just avoiding to pollute code with a previous initializing statement. Here the only "error" that this @ can hide is: $pattern doesn't exist yet: just what we want. \$\endgroup\$
    – cFreed
    Commented Dec 8, 2015 at 4:09
  • \$\begingroup\$ "why does it exist if it shouldn't be used". That holds true for a lot of things (eg nuclear weapons, but that's a whole other debate). The simple fact of the matter is this: when developing (ie writing new code), you want to be notified of all possible bugs. PHP issues notices, warnings and (fatal) errors for this. Supressing them hides possible bugs ($email = @$_POST['emial']; supresses a notice that indicates a typo). Another thing is that, even though you're supressing them, PHP will still create the notice \$\endgroup\$
    – Elias Van Ootegem
    Commented Dec 8, 2015 at 14:14
  • 1
    \$\begingroup\$ There upshot can be a significant performance loss, as demonstrated here. The simple fact of the matter is this: notices help developers write better code. Supressing them is like ignoring good advice: it's stupid, and usually ends with you cursing yourself for not listening in the first place. if return $someVar; issues a notice that you're returning something that doesn't exist, that is a problem. One that would cause other languages to refuse to compile the code even, simply because it is invalid \$\endgroup\$
    – Elias Van Ootegem
    Commented Dec 8, 2015 at 14:14
  • \$\begingroup\$ BTW: Just went through the documentation pages, too: even if there's no error to suppress, there still is a performance impact. \$\endgroup\$
    – Elias Van Ootegem
    Commented Dec 8, 2015 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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