PHP MySQL REGEX match search

Question

I want to fetch data from my user table which tag id stored like :

userid  tagid 
5036    |4815|324|                
1396    |1393|4567|                   
2676    |2669|345|2345|

I have a tagid array like as Array ( [0] => 4815 [1] => 4567 )

Need to fetch data using mysql where in condition like "select * from user where tagid in ()". Is there any preg_match function for it?

Can you just do where tagid like '%|4815|%' ? Of course that's not efficient but it's the way the table is designed. — somedev, Jun 11 '13 at 15:43
but the problem is i have array of tagids and to do search with that array. — Renjith R, Jun 11 '13 at 15:45
The problem is that your tables are not normalized. See http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad for why your tagid column is going to cause you sorrow and pain until you create a separate table. — Andy Lester, Jun 11 '13 at 15:58

score 1 · Accepted Answer · answered Jun 11 '13 at 15:56

1

Suggest you loop the array and append using likes, something like:

   $tags = array(2,4,6);
   $query = 'select * from user where ';
   foreach($tags as $i=>$tag) {
       if ($i>0) $query.=' or ';  // or and?
       $query.='tagid like "%|'.$tag.'|%"';
   }

answered Jun 11 '13 at 15:56

somedev

1,053
1
9
27

This works as long as their own array isn't indexed with something else. Cool concatenation though. – AbsoluteƵERØ Jun 11 '13 at 16:12

AbsoluteƵERØ · Answer 2 · 2013-06-11T16:03:38.260

Because you're having to use a wildcard on both sides of the match they would both be about the same (I think regexp may be a little faster in your case).

The problem is that you may have false positives because of the chance of a substring in your searches because of the lack of normalization.

An example:

Searching for %15%

Matches 915,15,150,215790

Because of this, you should actually do this, since in this case, LIKE would not be sufficient unless you always wrap the IDs in the pipe characters |:

<?php
    $ids = array('115','215','225');

    foreach($ids as $id){

      $query = "select * from user where tagid regexp '[^0-9]*$id[^0-9]*'";

    }

?>

At which point you could use this:

<?php
    $ids = array('115','215','225');

    foreach($ids as $id){

      $query = "select * from user where tagid like '%|$id|%'";

    }

?>

@CasimiretHippolyte as long as they stick to their non-standard convention of surrounding the IDs with pipe characters. — AbsoluteƵERØ, Jun 11 '13 at 16:00

Casimir et Hippolyte · Answer 3 · 2013-06-11T16:13:03.607

0

You can do it like that:

$tagid = array(4815,4567);
$query = "select * from user where tagid regexp '\|("
       . implode("|", $tagid) . ")\|'";

then you obtain:

select * from user where tagid regexp '\|(4812|4567)\|'

edited Jun 11 '13 at 16:13

answered Jun 11 '13 at 15:53

Casimir et Hippolyte

88,009
5
94
125

PHP MySQL REGEX match search

3 Answers3