Using pure MySQL query instead of php loop and statements to get same result

Question

I would like to turn this code into a more pure MySQL. This code fetches a row according to each word in $words and it creates $translated word array.

$words = array("word1","word2","word3"...);
$translated = array();
foreach($words as $word){
  $query = "
    select * from (
      select text1,text2,text3 
      from table1 
      union 
      select text1,text2,text3
      from table2
      union
      select text1,text2,text3
      from table3
    ) as u 
    where u.text1 = ? or u.text2 = ?";
  $prepare = $database -> prepare($query);
  $prepare -> execute(array($word,$word));
  $fetch = $prepare -> fetch();

  if($fetch["text3"]){ $push = $fetch["text3"]; }
  else if($fetch["text1"]){ $push = $fetch["text1"]; }
  else{ $push = $word; }
  array_push($translated, $push);
}

This PHP solution seems really inefficient to me. I would like to find a pure MySQL solution. So far I've been working on this MySQL but it lacks the ending logic.

$query="
  select
    if(text2='word1',text3,0) as W1,
  + if(text2='word2',text3,0) as W2,
  + if(text2='word3',text3,0) as W3,
  ...
  from (
    select text1,text2,text3 
    from table1 
    union 
    select text1,text2,text3
    from table2
    union 
    select text1,text2,text3
    from table3
  ) as u where 
    u.text1 in('word1','word2','word3'...) or 
    u.text3 in('word1','word2','word3'...)
";

This give out something like:

Array ( 
  [0] => Array ( [word1] => 0      [word2] => word22 [word3] => 0      [word4] => 0 ) 
  [1] => Array ( [word1] => word23 [word2] => 0      [word3] => 0      [word4] => 0 ) 
  [2] => Array ( [word1] => 0      [word2] => 0      [word3] => word24 [word4] => 0 ) )

I can column search for non-zero entry to find the translation. BUT, I am not sure how to add logic to the MySQL to select default text1 if text3 is missing.

if($fetch["text3"]){ $push = $fetch["text3"]; }
else if($fetch["text1"]){ $push = $fetch["text1"]; }
else{ $push = $word; }
array_push($translated, $push);

Thernys · Accepted Answer · 2015-10-14 23:40:51Z

2

You could quite easily work at least some of your default logic into your current 'more pure SQL' example (which ultimately requires quite a bit of looping in PHP, I assume). You do not specify what text3 is when it is 'missing', but the following would work for either null or an empty string

if(text2='word1', IF(CHAR_LENGTH(text3), text3, text1), 0) as W1

If 'missing' reliably means null for text1 and text3, your entire default value logic from PHP could be expressed as

if(text2='word1', COALESCE(text3, text1, 'word1'), 0) as W1

I can't say I'm a big fan of this solution, but unfortunately I have not come up with an SQL only silver bullet for you, either. I'm also not an advocate of optimization for the sake of optimization. You have not stated how large the $words array might get, but if this part of your software is not materially impacting performance for you, time spent 'fixing' it would probably be better spent elsewhere.

The most glaring inefficiency in your original approach could be somewhat ameliorated by moving the SQL statement preparation out of the foreach loop; it does not need to be done repeatedly.

You could also work the default value logic into the SQL of your original approach (though I can't say if it would actually improve performance) by adapting the CHAR_LENGTH or COALESCE suggestions from above. Something along the lines of

SELECT COALESCE(text3, text1, ?) AS translated FROM (...) as u...

edited Oct 14, 2015 at 23:40

answered Oct 14, 2015 at 23:33

Thernys

7234 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maciek Over a year ago

Oh, the reason the SQL is in the foreach loop is because I needed "false" or "null" returned if there is no translation. So that $translated array has same keys as $words

Maciek Over a year ago

Do you know of a way to make SQL so something like "if IN('word1','word2'...) return COALESCE(text3, text1, ?), else return 'no translation'"? This would return the exact number of array elements in $translation as in $words.

Thernys Over a year ago

I understand that you want the same number of elements in the result array. Still, the $query = '...'; and $prepare = $database -> prepare($query); can be before the loop, they do not need to be repeated. See this comment on php.net (other comments and discussion there may be relevant for you as well)

Collectives™ on Stack Overflow

Using pure MySQL query instead of php loop and statements to get same result

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related