REGEXP Regular Expression in Mysql

Asked

Viewed 189 times

1

I have the following problem:

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

When an item is marked with two categories it is recorded as follows in the database: ["1","19"], I need to filter so that the items marked in 19 do not appear in 1, 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, can have ["2","20"] among others.

The query is being made 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, as drugstores, pharmacies, clinics, etc. Each of these items are categories set by id. In the register, the registration may be marked with more than one category, such as drugstores and pharmacies. If in the register I mark only one category in the database it registers only 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 to know how to deal with this issue on behalf of the bank to record the record as cited above ["10", "11"]. To make the consultation 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 I accept.

php mysql wordpress regex

  • Saul, please post what is stored in the variable $url e $especialidadesaude

  • I don’t understand exactly what you want. .

  • So you can better understand the problem. I have a site to consult in the area of health, as drugstores, pharmacies, clinics, etc. Each of these items are categories set by id. In the register, the registration may be marked with more than one category, such as drugstores and pharmacies. If in the register I mark only one category in the database it registers only 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 how to handle this issue on account of the bank record the record as cited above ["10", "11"]. To make the query I did as follows: $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');
@Ivannack @Rafaelacioly

  • You can understand me & #Xa;@Rafaelacioly ?

  • In general, it is preferable to use the native functions of WP than to do this type of direct queries in the bank... You even check this section of WP_Query?

Show 1 more comment

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 make an inquiry with the LIKE (escaping the quotation marks, if necessary):

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

Case " and [] are used, only when there is a group of items, you can have codes appearing as 10 and how ["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’ve helped.

Browser other questions tagged php mysql wordpress regex

You are not signed in. Login or sign up in order to post.