CODEWITHSUNDEEP
phpmysqlarrays
sg552
sg552

Reputation: 1543

Array in Mysql WHERE LIKE?

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.

Upvotes: 12

Views: 44548

Answers (7)

Muhammad Bilal
Muhammad Bilal

Reputation: 517

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."'";

Upvotes: 0

antelove
antelove

Reputation: 3358

/* 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%'

";

Upvotes: 1

Andr&#233; Mendon&#231;a
Andr&#233; Mendon&#231;a

Reputation: 1

/** * 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 );

}

Upvotes: 0

JohnFx
JohnFx

Reputation: 34909

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.

Upvotes: 0

lamplightdev
lamplightdev

Reputation: 2081

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')";

Upvotes: 1

netcoder
netcoder

Reputation: 67735

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}";

Upvotes: 39

ralfe
ralfe

Reputation: 1454

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.

Upvotes: 0

Related Questions