SQL trying to return rows for field using LIKE

Question

I have a db I query to select the column devicetoken and return all rows that are like an string I have an array. The query happens in a loop for each value in the array. The SQL syntax is not doing what I want. I am not using a wildcard because I dont want it to return substrings of the strings I am looking for.

Here the SQL syntax:

$result = $mysqli->query("SELECT devicetoken FROM `department devices` WHERE unit LIKE $unitsarray[$i]");

The units array for my testing consists of:

--- Units row 0 = 121
--- Units row 1 = 125 
--- Units row 2 = BR171 
--- Units row 3 = C1 
--- Units row 4 = C120 
--- Units row 5 = D2INV 
--- Units row 6 = E03 
--- Units row 7 = E05 
--- Units row 8 = E17 
--- Units row 9 = E173 
--- Units row 10 = HFPIO 
--- Units row 11 = MW70 
--- Units row 12 = T05 
--- Units row 13 = TIMERW 
--- Units row 14 = WT01 
--- Units row 15 = WT13 
--- Units row 16 = WT17

My db looks like this:

device_id    devicetoken                                           unit
T05 ipad     773f5436825a7115417d3d1e036da20e806efeef547b7c3fe4    E17, E03, E05
E05 ipad     773f5436825a7115417d3d1e036da20e806efeef547b7c3fe4    E01, E62, E67

So, my event in the loop should be called three times, once when it goes through and finds E17, once for E03, and once for E05. So how do I set up the LIKE to get these strings each time I cycle through the loop.

You really should normalize your database. – symcbean Dec 17 '12 at 09:12 — symcbean, Dec 17 '12 at 09:12

score 0 · Answer 1 · edited May 23 '17 at 12:27

Do you have any flexibility on the database design? Normalization theory says you should be storing those values in a separate table (in case you are not familiar with this, you can find an example here) . Otherwise you are facing a text-search problem per row which may hinder performance significantly as your table grows.

Now, if you are stock with this design you may be able to expand your query in case you have a fixed number of items per row. For example this question shows a possible solution:

WHERE
      MyColumn LIKE '%,' + @search + ',%' --middle
      OR
      MyColumn LIKE @search + ',%' --start
      OR
      MyColumn LIKE '%,' + @search --end
      OR 
      MyColumn =  @search --single

Yeah I had a feeling. I am starting to change the design as I thought this was a poorly way to design it. Thanks for clarifying! — Jon Erickson, Dec 17 '12 at 02:24

score 0 · Answer 2 · answered Dec 17 '12 at 09:10

Try something like this:

<?php
    $query = "SELECT `devicetoken` FROM `department devices` WHERE unit LIKE ";
    for($1 =0; $i<count($unitsarray); $i++)
        if(count($unitsarray)-1 < $i)
            $query .= "%" . $unitsarray[$i]; ."% OR ";
        else
            $query .= "%" . $unitsarray[$i] . "%";

    $result = $mysqli->query($query);

   // Do something with the result. This'll give you 1 query
   // To fetch all rows at once. while($row = $result->fetch_assoc())
   // foreach($row as $value) etc.
?>

SQL trying to return rows for field using LIKE

2 Answers2