I'm using SQLite and PDO for run some SQL commands.
table fields and data:
ID title tag pcode
----------------------------------------
1 title1 aa,bb,cc,dd a100
2 title2 cc,ee,dd,ba b250
3 title3 dd,ff,gr,ax br10
4 title4 cc,dd,aa,fx q200
5 title5 xx,ad,fr,aa ar50
I just want separate current field tag value with ,
character and then search between all tags exists in tag field except current pcode
value
As an example: I want to find all tags like as cc
or dd
or aa
or fx
with current record and when current pcode
is q200
with tag content cc,dd,aa,fx
except pcode q200
I did try it with this:
select title,pcode from tblproduct WHERE (tag like '%cc%') and (pcode <> 'q200')
It works, but how can I change it to find all values from current tag, meaning:
Search cc
and then search dd
and then search aa
and then search fx
value inside all tags in table except pcode
with q200
thanks for all helps, temporary i used this way just with change gr300 to any product code as i want or change to variable,good luck.
WITH RECURSIVE ctags AS (
SELECT
id,
producttitle,
tags,
productcode,
instr(tags, ',') AS pos,
substr(tags, 1, instr(tags, ',') - 1) AS tag_value,
substr(tags, instr(tags, ',') + 1) AS rest
FROM
tblproduct
WHERE
productcode = 'gr300'
UNION ALL
SELECT
id,
producttitle,
tags,
productcode,
instr(rest, ',') AS pos,
CASE
WHEN instr(rest, ',') = 0 THEN rest
ELSE substr(rest, 1, instr(rest, ',') - 1)
END AS tag_value,
CASE
WHEN instr(rest, ',') = 0 THEN ''
ELSE substr(rest, instr(rest, ',') + 1)
END AS rest
FROM
ctags
WHERE
rest <> ''
)
SELECT DISTINCT
p.id,
p.producttitle,
p.productcode,
p.tags
FROM
tblproduct p
JOIN
ctags t ON p.tags LIKE '%' || t.tag_value || '%' AND p.productcode <> 'gr300'