Difficult to mount query for Search in multiple columns

Question

Difficult to mount query for Search in multiple columns

Navigation

#1 by (1 votes)
#2 by (0 votes)

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 occurring:

If I send the contents of any field it brings the results, however if I pass more than 1 it returns 0, eg

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

I am a beginner user in SQL , I do not know if it is the correct path or if there is a totally different one.

The goal is no matter what variety it appears in the string (being aaabbb , ccceee , mmmgggccceee ) it brings line that contains at least its chunk.

I know it's kind of confusing, I'm thinking of a way to make it as clear as possible.

mysql sql

asked by anonymous 08.09.2017 / 23:16

2 answers

Should I use form or div? Add 'swipe to refresh' to WebView

score 1 · Answer 1

One 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 gets trivial

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

but I still recommend the standardization of the template

score 0 · Answer 2

Assuming that your columns that store the string ( carac_a , carac_b , etc) will always receive any of the possible values searched ( aaa , bbb , etc), you need to check if any of the columns contains the search string , 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 contain the entire last string (type aaabbb ).