php - Amending a join query to add a new value to a query -

-

the query below works well. pulls information 3 mysql tables: login, submission, , comment.

it creates value called totalscore2 based on calculation of values pulled these 3 tables.

the mysql tables "comment" , "submission" both have following fields:

loginid submissionid

in table "submission," each "submissionid" has 1 entry/row, , 1 "loginid" associated it.

in table "comment," field "submissionid" have several entries/rows, , associated multiple "loginid"s.

each time 1 of "submissionid"s in "comment" associated same "loginid" has in table "submission," add factor equation below. multiple instances times (-10).

how this?

thanks in advance,

john

$sqlstr2 = "select      l.loginid,      l.username,      l.created,     datediff(now(), l.created) + coalesce(s.total, 0) * 5 + coalesce(scs.total, 0) * 10 + coalesce(c.total, 0) totalscore2 login l     left join (     select loginid, count(1) total      submission      group loginid ) s on l.loginid = s.loginid left join (     select loginid, count(1) total      comment      group loginid ) c on l.loginid = c.loginid left join (     select s2.loginid, count(1) total      submission s2     inner join comment c2     on c2.submissionid = s2.submissionid     group s2.loginid ) scs on scs.loginid = l.loginid group l.loginid order totalscore2 desc  limit 25"; 

    $sqlstr3 = "select          l.loginid,          l.username,       l.created,      datediff(now(), l.created) + coalesce(s.total, 0) * 5 + coalesce(scs.total, 0) * 10 - coalesce(nscs.total, 0) * 10 + coalesce(c.total, 0) totalscore2     login l         left join (         select loginid, count(1) total          submission          group loginid     ) s on l.loginid = s.loginid     left join (         select loginid, count(1) total          comment          group loginid     ) c on l.loginid = c.loginid      left join (         select s2.loginid, count(1) total          submission s2         inner join comment c2         on c2.submissionid = s2.submissionid         group s2.loginid     ) scs on scs.loginid = l.loginid       left join (         select s2.loginid, count(1) total          submission s2         inner join comment c2         on c2.submissionid = s2.submissionid      , c2.loginid = s2.loginid         group s2.loginid     ) nscs on nscs.loginid = l.loginid        group l.loginid     order totalscore2 desc      limit 25";

Comments

Post a Comment

Popular posts from this blog

ASP.NET/SQL find the element ID and update database -

-
basically i've got update query runs when click submit button. i have asp:repeater in page layer inside this <asp:repeater id="dgbookings" runat="server" onitemdatabound="itemdb"> <itemtemplate> <div class="bookingscontent"> <div class="bookingdetails" id="<%# databinder.eval(container.dataitem, "booking_ref") %>"> <p class="infotext"><%# databinder.eval(container.dataitem, "adults") %> <asp:linkbutton onclick="editdetails" text="edit..." runat="server"></asp:linkbutton></p> <p class="infotext"><asp:linkbutton onclick="submitdetails" text="submit..." runat="server"></asp:linkbutton></p> </div> </div> </itemtemplate> <asp:repeater> and outputs ...
Read more

jquery - appear modal windows bottom -

-
i have modal windows in application, work fine <div id="modal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="mymodallabel" aria-hidden="true"> but when modal appears, appear top of windows, , want appear bottom the application joomla 3.0! , using template of meet gavern any idea? i found solution i modified de css in bootstrap .modal.fade { top:-25%; -webkit-transition:opacity .3s linear, bottom .3s ease-out; -moz-transition:opacity .3s linear, bottom .3s ease-out; -ms-transition:opacity .3s linear, bottom .3s ease-out; -o-transition:opacity .3s linear, bottom .3s ease-out; transition:opacity .3s linear, bottom .3s ease-out; }
Read more

c++ - Compiling static TagLib 1.6.3 libraries for Windows -

-
i having super hard time compiling , using taglib 1.6.3 in qt project. i've tried can think of. taglib claims supported through cmake i'm not having luck. furthermore, i'm confused kinds of files need qt libs! i've built *.a files, *.lib, , *.dll. understand far... believe since i'm working in windows *.lib want. no matter do, end "undefined references" taglib functions try use when try compile qt project. have tried mingw32, msys, visual studio 2008, , cross-compiling windows on linux. turning nothing. what makes less sense me if compile same taglib source qt on mac (g++ think?) works fine! somewhere in windows compilation procedures have going wrong. have been smacking face on desk 30 (on , off) hours trying figure out. since qt uses mingw must compile taglib same compiler? if compile *.lib's visual studio not compatible? are *.a libraries usable in windows? (assuming mingw) i'm still trying handle on c++ stuff, after reading coun...
Read more