"how to use like for mysql search with join and order by the count of most rows/votes in the vote table?" Code Answer

here is a fuller answer. to get the sum or good votes and bad votes from a set of joined table rows, you need to group the like rows together.

below should give you the desired result.

mysql_query("
    select m.title, r.subject, v.tipid, sum(v.isgood) as isgood, sum(v.isbad) as isbad from movies m
        left join reviews r
            on m.id=r.movieid
        left join votes v
            on r.id=v.reviewid
        where (m.title like '%" . $search . "%'
            or r.subject like '%" . $search . "%')
        group by  m.title, r.subject, v.tipid
        order by sum(v.isgood) desc, sum(v.isbad) asc limit 10")or die(mysql_error());

By Pankaj Upadhyay on October 15 2022