REGEXT in MySQL query

my regexp foo isn't the best but the following doesn't work and I'm looking for a little advice:

$query = $_GET['indicator'];
// given: 239,240  or 240,239  or 238,239,240  
//   and: 239, 240 or 240, 239 or 238, 239, 240
//   and: 239
// we need to check for start+number+puntuation(1), punctuation+number+end(2), punctuation+number+punctuation(3)
//                                                  space+number+end(4),       space+number+punctuation(6)
//                      start+number+end(6)
$sql = 'SELECT * from reports WHERE dataSetIDs REGEXP \'';
$sql .= '^'.$query.'[:punct:]|';            // 1
$sql .= '[:punct:]'.$query.'$|';            // 2
$sql .= '[:punct:]'.$query.'[:punct:]|';    // 3
$sql .= '[:space:]'.$query.'$|';            // 4
$sql .= '[:space:]'.$query.'[:punct:]|';    // 5
$sql .= '^'.$query.'$\'';                   // 6
$result = mysql_query($sql);

The query is looking at a specific c开发者_运维问答ell which has CSV formatted set of numbers for which the formatting isn't set (i.e. could have spaces after the comma or not). I'm getting the rows with just one entry... but not the rows that have the number in the csv.

Any pointers greatly appreciated.

Cheers,

Dom

---

1. Why you do not explode the csv ?

2. You can canonize the csv content in the database and your query input:

   1. remove all useless space
   2. use the same delimiter ',' as an example

   3. add a delimier at start and end ex:

      200, 300 => ,200,300,

   4. use a regexp like ",$id," which is simpler

---

Clocked it:

$query = mysql_real_escape_string($_GET['indicator']);
$sql = 'SELECT title from reports WHERE dataSetIDs REGEXP \'(^|[:,:]|[: :])'.$query.'([:,:]|[: :]|$)\'';
$result = mysql_query($sql);

Thanks for your help @VGE.

Cheers,

Dom