Array in Mysql WHERE LIKE?

Question

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.

Good answer to similar question here: stackoverflow.com/questions/1127088/mysql-like-in — lamplightdev
– lamplightdev, Commented Feb 24, 2012 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
– netcoder, Commented Feb 24, 2012 at 17:41

netcoder · Accepted Answer · 2012-02-24 17:32:22Z

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

answered Feb 24, 2012 at 17:32

netcoder

68k19 gold badges129 silver badges142 bronze badges

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

3 Comments

sg552 Over a year ago

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.

netcoder Over a year ago

@sg552: It doesn't remove it, it adds it. Dump the query to screen and you'll probably understand.

sg552 Over a year ago

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.

lamplightdev · Accepted Answer · 2012-02-24 17:33:03Z

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

answered Feb 24, 2012 at 17:33

lamplightdev

2,1011 gold badge20 silver badges24 bronze badges

Comments

antelove · Accepted Answer · 2017-08-25 04:11:31Z

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

";

answered Aug 25, 2017 at 4:11

antelove

3,37830 silver badges20 bronze badges

Comments

Muhammad Bilal · Accepted Answer · 2021-09-02 01:16:41Z

1

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

answered Sep 2, 2021 at 1:16

Muhammad Bilal

5271 gold badge5 silver badges13 bronze badges

1 Comment

mickmackusa Over a year ago

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.

ralfe · Accepted Answer · 2012-02-24 17:31:02Z

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.

answered Feb 24, 2012 at 17:31

ralfe

1,4542 gold badges15 silver badges27 bronze badges

Comments

JohnFx · Accepted Answer · 2012-02-24 17:48:23Z

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.

edited Feb 24, 2012 at 17:48

answered Feb 24, 2012 at 17:32

JohnFx

35k19 gold badges108 silver badges169 bronze badges

2 Comments

netcoder Over a year ago

That's perfectly valid SQL by the way.

JohnFx Over a year ago

Valid SQL, but not for what he is trying to do.

André Mendonça · Accepted Answer · 2015-09-23 13:52:46Z

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

}

Collectives™ on Stack Overflow

Array in Mysql WHERE LIKE?

7 Answers 7

3 Comments

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

Comments

1 Comment

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related