12

I need to SELECT with array list. $array_name contains:

Array ( [0] => gum.cn [1] => lol.com. [2] => ns1.blar.com [3] => test.com [4] => web.cn. )

print_r($array_name);

  $string = implode(',',$array_name);


    $tank = "SELECT url FROM `PHP`.`db` WHERE url LIKE '%{$string}%'";
    $result1 = mysql_query($tank); 
      while ($jwp = mysql_fetch_array($result1)) 
      {     
      echo $jwp['url']; 
      echo "<br>"; 
      }

Why don't the above work? I search other example and the question is asking without using LIKE clause so no solution there. Please help, thanks in advance.

sg552
  • 1,521
  • 6
  • 32
  • 59
  • 1
    Good answer to similar question here: http://stackoverflow.com/questions/1127088/mysql-like-in – lamplightdev Feb 24 '12 at 17:36
  • @lamplightdev: REGEXP is a good alternative, but unfortunately here, it's domains that contain `.` characters, which are wildcards in regex. You can escape them, but it's too much work for such a trivial matter IMO. Although it can be considered. – netcoder Feb 24 '12 at 17:41

7 Answers7

39

It doesn't work because your query will expand to:

SELECT url FROM `PHP`.`db` WHERE url LIKE '%gum.cn,lol.com.,ns1.blar.com...%'

You have to modify your query a little:

$query_parts = array();
foreach ($array_name as $val) {
    $query_parts[] = "'%".mysql_real_escape_string($val)."%'";
}

$string = implode(' OR url LIKE ', $query_parts);

$tank = "SELECT url FROM `PHP`.`db` WHERE url LIKE {$string}";
netcoder
  • 66,435
  • 19
  • 125
  • 142
  • Hey, thank you very much. It's works now but I don't understand this, `$string = implode(' OR url LIKE ', $query_parts);` why you need to remove the `' OR url LIKE '` including white space? I hope you can explain and thanks for your time. – sg552 Feb 24 '12 at 17:48
  • 1
    @sg552: It doesn't remove it, *it adds it*. Dump the query to screen and you'll probably understand. – netcoder Feb 24 '12 at 17:50
  • After all this times I thought `implode()` suppose to be removing comma or whitespace from array. I get the output as: `%gum.cn%' OR url LIKE '%lol.com.%' OR url LIKE '%blar.com%' OR url LIKE '%test.com%' OR url LIKE '%web.cn.%` If I understand it correctly MySQL interpret the `$string` variable as a long string and if I have 1000 domain there will be 1000 domain executed in 1 query? Correct? Thank you for your time. – sg552 Feb 24 '12 at 18:01
1

/* Data in Array */

$array_name = array("gum.cn","lol.com","ns1.blar.com","test.com","web.cn");

/* Join array elements into a string with separator (whitespace) " " */

echo $string = implode(" ", $array_name); 
// output: gum.cn lol.com ns1.blar.com test.com web.cn

/* replace separator (whitespace) " " with "%' OR '%" */

echo $string = str_replace(" ", "%' OR '%", $string);
// output: gum.cn%' OR '%lol.com%' OR '%ns1.blar.com%' OR '%test.com%' OR '%web.cn

/* Insert into query sql */

$tank = "SELECT url FROM `PHP`.`db` WHERE url LIKE

'%$string%'

";

antelove
  • 3,216
  • 26
  • 20
1

Use the IN clause, but ensure that the values are quoted and escaped:

//TODO: escape $array_name values

$string = implode("','",$array_name); //ensure quoted values

$tank = "SELECT url FROM `PHP`.`db` WHERE url IN ('$string')";
lamplightdev
  • 2,041
  • 1
  • 20
  • 24
0

/** * Implode a multidimensional array of values, grouping characters when different with "[]" block. * @param array $array The array to implode * @return string The imploded array */ function hoArray2SqlLike( $array ) { if ( ! is_array( $array ) ) return $array;

$values  = array();
$strings = array();

foreach ( $array as $value )
{
    foreach ( str_split( $value ) as $key => $char )
    {
        if ( ! is_array( $values[ $key ] ) )
        {
            if ( isset( $values[ $key ] ) )
            {
                if ( $values[ $key ] != $char )
                {
                    $values[ $key ]     = array( $values[ $key ] );
                    $values[ $key ][]   = $char;
                }
            }
            else
                $values[ $key ] = $char;
        }
        elseif ( ! array_search( $char , $values[ $key ] ) )
            $values[ $key ][] = $char;
    }
}

foreach ( $values as $value )
{
    if ( is_array( $value ) )
        $value = '['.implode( '', $value ).']';

    $strings[] = $value;
}

return implode( '', $strings );

}

André Mendonça
  • 1
0

I know this question is old but I hope this might be useful for others.

We can use REGEXP rather than LIKE ex:

$string = implode('|',$array_name);
$tank = "SELECT url FROM `PHP`.`db` WHERE url REGEXP '".$string."'";
Muhammad Bilal
  • 497
  • 1
  • 5
  • 12
  • It is not modern, stable, or secure to insert variables directly into an sql string. All new answers should be implementing prepared statements so that researchers start learning how to code professionally. – mickmackusa Sep 02 '21 at 02:06
0

It won't work because it will try and match the database value of (for example) 'gum.cn' with '%gum.cn,lol.com,.....%', which will never be true.

Rather do this:

foreach ($array_name as $string) {
    $tank = "SELECT url FROM `PHP`.`db` WHERE url LIKE '%{$string}%'";
    $result1 = mysql_query($tank); 
    echo $jwp['url']; 
    echo "<br>"; 
}

Hope that helps.

ralfe
  • 1,412
  • 2
  • 15
  • 25
0

Because it generates invalid SQL (Okay, technically valid, it just isn't going to do what you think it will)

SELECT url FROM `PHP`.`db` WHERE url LIKE '%a,b,c,d,%'

You are either going to have to append multiple LIKE statements. Implode isn't going to work for this task unless you are doing an exact string match query. Even then you'd need to tweak the code.

JohnFx
  • 34,542
  • 18
  • 104
  • 162