Choice mysql by number of tags
There is a news base (id, name, text, date, tags). The paces go in a tiny text just like a line #tag1 #tag2 #tag3 #tag4.
There's news 1 with the tag1 #tag2 #tag3 #tag4
There's news 2 with the tag1
There's news 3 with tag2 #tag3 #tag4
There's news 4 with gigs: #tag3 #tag4
The currents are now:
select * from news where tags like '%#".$Tag." %' order by date DESC
How do I get to news 1, other news is fine. News 3 - News 4 - New 1i.e., just by number of matches, one request without changing the OBD structure?
SQL can count only if it is presented in separate lines. It is therefore very desirable that the data be included in the report. https://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%BC%D0%B0%D0%BB%D1%8C%D0%BD%D0%B0%D1%8F_%D1%84%D0%BE%D1%80%D0%BC%D0%B0 ♪ If the transmission of the code under the right OBD structure is not possible, then we will have to turn the line into separate lines in the request and then count the number of coincidences. To this end, we will need a service table with serial numbers from 0 to as many as possible in the line:
create table seqnum(X int not null); -- Первые 8 записей insert into seqnum values(0),(1),(2),(3),(4),(5),(6),(7); -- И еще 512 insert into seqnum select s1.x*64+s2.x*8+s3.x+8 from seqnum s1, seqnum s2, seqnum s3;
The request for all the news with the same tags as the ID=1 news, the matching grading is:
select N.*,count(1) cnt from news N, (select ltrim(substr(tags,length(SUBSTRING_INDEX(tags,' ',s.x))+1, length(SUBSTRING_INDEX(tags,' ',s.x+1))- length(SUBSTRING_INDEX(tags,' ',s.x)))) tag from news n, seqnum s where n.id=1 and length(SUBSTRING_INDEX(tags,' ',s.x))<length(tags) ) T where concat(N.tags,' ') like concat('%',T.tag,' %') and N.id!=1 group by N.id order by cnt desc
TWires all the stories ID=1 separate lines, external searches for matches and counts. The cuts from the line are based on the assumption that the currents are strictly divided by one gap.