database - How to get and sort data from this MYSQL query -

-

update: question updated 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 , pid foreign keys ofcourse tag id , post id

now want related post based on tags of post. example if post1 contains tag t1, t2, t3, t4 , post2 contains tag t2, t3, t4 , post3 contains t1, t2 , searching posts related post1, output should be,

post2 post3

notice order in because of number of tags matched in them. stuck @ finding posts having same tags without order , query taking tags input should post id.

select distinct p.id, p.heading, p.date  post p, tags t, tagsinpost tp  tp.tid=t.id    , tp.pid=p.id    , t.tagname in (                    select distinct t.tagname tags t, tagsinpost                     tp tp.pid = 20 , tp.tid = t.id                    )

now how order output number of matches t.tagname found in second query?

how order output number of matches

you need join , use of group achieve this. e.g

select t1.col1, count(*) count_of table1 t1 left join table2 t2 on t1.id = t2.fk group t1.col1 order count_of desc

here can count rows , order calculation.


Comments

Post a Comment

Popular posts from this blog

jsf - "PropertyNotWritableException: Illegal Syntax for Set Operation" error when setting value in bean -

-
this question has answer here: how send form input values , invoke method in jsf bean 1 answer i error when trying send values jsf page bean. i want set values fields in bean, create airplane instance , add database. error telling me use illegal syntax setter part of bean. why error? didnt find on site helped me problem. here jsf part: <h4>add plane</h4> <c:if test="#{not empty listairplanebb.error}"> <div class="alert alert-danger"> <span class="glyphicon glyphicon-remove"></span> <h:outputtext value="#{listairplanebb.error}" /> </div> </c:if> <h:form> <table class="table"> <thead> ...
Read more

arrays - Algorithm to find ideal starting spot in a circle -

-
so problem have been debating such: your given array of integers representing circle. then, have pick spot in circle start. place start, compare value @ array number of steps took there , if steps less or equal number, include in final set. find place start have elements in set. ex, a=[0, 1, 2] if start @ index=0, then: a[0]=0 < =0 0 included a[1]=1 < =1 1 included a[2]=2 < =2 2 included final set: {0,1,2} if start @ index=1, then: a[1]=1 > 0 1 not included a[2]=2 > 1 2 not included here loop around a[0]=0 > 2 0 included final set: {0} if start @ index=2, then: a[2]=2 > 0 2 not included, here loop around a[0]=0 < = 1 0 included a[1]=1 < = 2 1 included final set: {0,1} so in trivial case, starting position index=0 best position results in final set elements. brute force method of finding obvious, trying find more efficient method. attempts far have been examine trying find max intersect of viable starting ranges calculated each element in arr...
Read more

php - Autoloader issue not returning Class -

-
problem? bootloader (autoloader) not seem working properly, or i'm missing something. here simplified code. the code below returns class "skeleton" not exist. on index.php file. index.php <?php include 'bootloader.php'; use skeleton\html\loginheader; $tool = new skeleton/html/loginheader(); bootloader.php <?php function boot($classname) { $filename = ''; $namespace = ''; // sets include path "src" directory $includepath = dirname(__file__).directory_separator.'src'; if (false !== ($lastnspos = strripos($classname, '\\'))) { $namespace = substr($classname, 0, $lastnspos); $classname = substr($classname, $lastnspos + 1); $filename = str_replace('\\', directory_separator, $namespace) . directory_separator; } $filename .= str_replace('_', directory_separator, $classname) . '.php'...
Read more