REGEXP Regular Expression in Mysql

1

I have the following problem:

I need to make a query (The site was developed in wordpress) where I have several health specialties.

When an item is marked with two categories it is registered as follows in the database: ["1", "19"] , I need to filter so that the items marked in 19 appears in 1, because as both are with the number 1 is having this problem. Could someone help me? Remembering that item 1 and 19 are figurative, I can not fix them because we have several categories, being able to have ["2", "20"] among others.

The query is being done like this:

$especialidadesaude = empty($_REQUEST['especialidadesaude']) ? 0 : intval($_REQUEST['especialidadesaude']);
$url = 't.categoria = 11';
if ($especialidadesaude != 0) { $url.= ' AND t.especialidadesaude REGEXP "'.$especialidadesaude.'"'; }
$params = array( 'where' => $url, 'limit' => 15, 'orderby' => 't.name ASC');

So you can better understand the problem. I have a site to consult in the area of health, such as drugstores, pharmacies, clinics, etc. Each of these items are categories set by id. In the register, the registry may be marked with more than one category, such as drugstores and pharmacies. If you do not register I only mark a category in the database and it only registers the category ID, if it has two categories and writes to the database as follows: ["10", "11"]. This means that there are two categories marked. I need to make a filter to display only the results of item 10 and only display the results of item 11. My problem is knowing how to handle this issue on behalf of the bank record the record of the form quoted above ["10", "11"]. To do the query I did as I mentioned above.

I'm trying to do this with Regexp , tried with LIKE and IN but not right. If someone has another idea or solution accepted.

    
asked by anonymous 07.01.2016 / 19:48

1 answer

1

According to their descriptions, we can consider that all codes have quotes like "10" or ["10"] or ["10", "11"] .

If this is the case, you can query the LIKE (escaping the quotation marks, if necessary):

// AND t.especialidadesaude LIKE "%\"10\"%";
$url.= ' AND t.especialidadesaude LIKE "%\"' . $especialidadesaude . '\"%"';

If " and [] are used, only when there is an item group, you can have codes appearing as 10 and ["10", "11"] . In this case, you can use this code:

// AND t.especialidadesaude REGEXP ("10"|^10$);
$url.= ' AND t.especialidadesaude REGEXP "(\"' . $especialidadesaude . 
   '\"|^' . $especialidadesaude . '$)"';

I hope I have helped.

    
12.02.2016 / 18:47