unkn0wn unkn0wn - 22 days ago 10
MySQL Question

How to get and sort data from this MYSQL query

UPDATE: Question updated to help people undestand better

I have following tables

CREATE TABLE IF NOT EXISTS `post` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`heading` TEXT NOT NULL,
`content` LONGTEXT NOT NULL,
`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`thumb` TEXT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 15
DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS `tags` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`tagname` TEXT NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 12
DEFAULT CHARACTER SET = latin1;

DROP TABLE IF EXISTS `tagsinpost` ;

CREATE TABLE IF NOT EXISTS `tagsinpost` (
`tid` INT(11) NULL DEFAULT NULL,
`pid` INT(11) NULL DEFAULT NULL,
CONSTRAINT `post_ibfk_1`
FOREIGN KEY (`pid`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `tagsinpost_ibfk_1`
FOREIGN KEY (`tid`)
REFERENCES `tags` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

CREATE INDEX `tagsinpost_ibfk_1` ON `tagsinpost` (`tid` ASC);

CREATE INDEX `post_ibfk_1` ON ``tagsinpost` (`pid` ASC);


Where
tid
and
pid
are foreign keys ofcourse to TAG
id
and POST
id


Now What i want is to get related post based on tags of any post.
For example if some POST1 contains tag T1, T2, T3, T4 and POST2 contains tag T2, T3, T4 and POST3 contains T1, T2 and i am searching posts related to POST1, Then my output should be,

POST2
POST3

Notice the order they are in because of number of tags matched in them. Currently i am stuck at finding all posts having same tags without order and my query is taking tags as input which should be POST id.

select distinct P.id, P.heading, P.date
from post as P, tags as T, tagsinpost as TP
where TP.tid=T.id
and TP.pid=P.id
and T.tagname in (
select distinct T.tagname from tags as T, tagsinpost
as TP where TP.pid = 20 and TP.tid = T.id
)


Now how do i order my output by number of matches T.tagname found in second query?

Answer

how do i order my output by number of matches

You need a join and use of group by to achieve this. E.g

Select t1.col1, count(*) as count_of
From table1 t1
Left join table2 t2 on t1.id = t2.fk
Group by t1.col1
Order by count_of DESC

here you can count the rows and then order by that calculation.

Comments