CODEWITHSUNDEEP
phpmysqlregex
annoyingmouse
annoyingmouse

Reputation: 5689

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 cell 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

Upvotes: 0

Views: 109

Answers (2)

annoyingmouse
annoyingmouse

Reputation: 5689

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

Upvotes: 1

VGE
VGE

Reputation: 4191

  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

Upvotes: 1

Related Questions