Difficulty mounting query for Search in multiple columns

Asked

Viewed 25 times

0

I have 2 tables

tab_user

user_id | nome_user | tel_user
1       jão         888 
2       mari        999
3       jão         777
4       mari        666

tab_product

id_produto | user_id| carac_a | carac_b | carac_c | carac_d
1          1          aaa       bbb       ccc       eee
2          1          mmm       ggg       ccc       eee
3          1          aaa       bbb       ccc       hhh
4          1          aaa       rrr       fff       www
5          1          aaa       bbb       ccc       ddd
6          1          xxx       bbb       ccc       ddd
7          1          xxx       zzz       ccc       ddd
8          1          aaa       zzz       ccc       ddd

The goal is to see if a string of math in some column, I’m trying like this:

<?php
$sql = 
"SELECT * FROM 
tab_produto LEFT JOIN
(tab_user) ON 
(tab_user.user_id = tab_produto .user_id) WHERE
(`carac_a ` LIKE '%".$value."%') OR 
(`carac_b ` LIKE '%".$value."%') OR 
(`carac_c ` LIKE '%".$value."%') OR 
(`carac_d ` LIKE '%".$value."%')";
?>

The following is happening:

If I send the contents of any field it brings the results, but if I pass more than 1 it returns 0, example:

`aaa` ->  retorna todas as linhas com `aaa`
`eee` ->  retorna todas as linhas com `eee`
`aaaeee` -> retorna 0

I am a user beginner in SQL, I don’t know if it’s the right way or if there’s another way.

The goal is not to matter the variety of how it appears in the string(being aaabbb, ccceee, mmmgggccceee) it brings line that contains at least the respective piece.

I know you’re a little confused, I’m thinking of a way to make it as clear as possible.

mysql sql

  • in case if the you pass aaaeee should bring what has "aaa" and what has "Eee"?

  • Yes... that’s right, that’s right

  • 1

    In general sqls are complex for nothing when the model is bad, your model is not normalized , normalizing your table your sql would be trivial

2 answers

1

A solution could be

create a view

create view v_produto as
select id_produto,user_id,carac_a carac from tab_produto
union all
select id_produto,user_id,carac_b carac from tab_produto
union all
select id_produto,user_id,carac_c carac from tab_produto
union all
select id_produto,user_id,carac_d carac from tab_produto

sql becomes trivial

select *
from   v_produto
where  carac in ('aaa','bbb')

but I continue recommending normalization of model

0

Admitting that your columns store the string of characters (carac_a, carac_b, etc) will always receive some of the possible searched values (aaa, bbb, etc), you need to consult if any of the columns contains the string search, not the other way around:

<?php
$sql = 
"SELECT * FROM 
tab_produto LEFT JOIN
(tab_user) ON 
(tab_user.user_id = tab_produto .user_id) WHERE
('%".$value."%' LIKE `carac_a`) OR 
('%".$value."%' LIKE `carac_b`) OR 
('%".$value."%' LIKE `carac_c`) OR 
('%".$value."%' LIKE `carac_d`)";
?>

The way your code is it will only return if any of the columns contains the entire sequence passed (type aaabbb).

Browser other questions tagged mysql sql

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