0

I've researched this but couldn't find a solution for my specific problem.

I have a column containing data in a certain format. Here are some examples:

1
6
14
1;6;14;16
etc...

I need a mysql statement which for example it will select all columns where 16 occurs.

I've tried this but it's also selecting columns where 1 and 6 occur:

"SELECT * FROM tbl WHERE kategorien LIKE '%".$_GET['katid']."%' AND status = 1 ORDER BY pos ASC"

Thanks in advance for any help!

Drixson Oseña
  • 3,631
  • 3
  • 23
  • 36
Richard Tinkler
  • 1,635
  • 3
  • 21
  • 41
  • 1
    Try using REGEXP in mysql – shatheesh May 06 '14 at 09:22
  • possible duplicate of [MySQL SELECT LIKE or REGEXP to match multiple words in one record](http://stackoverflow.com/questions/9099469/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record) – Muhammad May 06 '14 at 09:30
  • This is an example of bad database design. You should have an extra table for the data in this column. – Gerald Schneider May 06 '14 at 09:33
  • 1
    Not an answer to your question, but more important: Never use $_GET variables directly in SQL query, always escape them properly to prevent malicious input. See [`mysql_real_escape_string`](http://php.net/mysql_real_escape_string) – Erbureth May 06 '14 at 12:27

2 Answers2

1

You can try creating a helper function like this:

// Helper function
function getLike($str, $deliminator = ';', $field = 'kategorien') {
    if (false !== strpos($str, $deliminator)) {
        $strParts = explode($deliminator, $str);
        return "($field LIKE '%". implode("%' OR $field LIKE '%", $strParts) . "%')";
    } else {
        return "$field LIKE '%$str%'";
    }
}

// Debug
var_dump(getLike('1;6;14;16'));

Outputs:

string '(kategorien LIKE '%1%' OR kategorien LIKE '%6%' OR kategorien LIKE '%14%' OR kategorien LIKE '%16%')' (length=100)

In your query, you'd use it like this:

"SELECT * FROM tbl WHERE ". getLike($_GET['katid']) ." AND status = 1 ORDER BY pos ASC"
Latheesan
  • 23,247
  • 32
  • 107
  • 201
0

You could use MySQL function FIND_IN_SET:

SELECT * FROM tbl
WHERE
  FIND_IN_SET('16', REPLACE(kategorien, ';', ','))>0

however, it is usually not a good idea to store comma separated values in a single field, please have a look at this question: Is storing a delimited list in a database column really that bad?

Community
  • 1
  • 1
fthiella
  • 48,073
  • 15
  • 90
  • 106