php - Ordering by two fields -

-

the input below sorts submissions timestamp field called "datesubmitted" in reverse chronological order. field in mysql table called "submission."

another mysql table "comment" has timestamp field called "datecommented."

each submission has 1 "datesubmitted" have several comments, each different "datecommented."

how sort submissions "datesubmitted" , each one's last "datecommented"? in other words, want top of list show either submitted entry or entry recent comment, whichever occurred most recently.

thanks in advance,

john

$sqlstr = "select                  s.loginid                 ,s.title                 ,s.url                 ,s.displayurl                 ,s.datesubmitted                 ,l.username                 ,s.submissionid                 ,count(c.commentid) countcomments                                submission s             inner              join                  login l                on                 s.loginid = l.loginid              left outer              join                  comment c                 on                  s.submissionid = c.submissionid              group                                  s.submissionid              order                                     s.datesubmitted desc              limit                   10";             $tzfrom = new datetimezone('america/new_york');  $tzto = new datetimezone('america/phoenix');     // echo $dt->format(date_rfc822);    $result = mysql_query($sqlstr);  $arr = array();  echo "<table class=\"samplesrec\">"; while ($row = mysql_fetch_array($result)) {      $dt = new datetime($row["datesubmitted"], $tzfrom);      $dt->settimezone($tzto);     echo '<tr>';     echo '<td class="sitename1"><a href="http://www.'.$row["url"].'" target="_blank">'.$row["title"].'</a>  <div class="dispurl">'.$row["displayurl"].'</div></td>';     echo '</tr>';     echo '<tr>';     echo '<td class="sitename2name">submitted <a href="http://www...com/.../members/index.php?profile='.$row["username"].'">'.$row["username"].'</a> on '.$dt->format('f j, y &\nb\sp &\nb\sp g:i a').'</td>';      echo '</tr>';     echo '<tr>';     echo '<td class="sitename2"><a href="http://www...com/.../comments/index.php?submission='.$row["title"].'&submissionid='.$row["submissionid"].'&url='.$row["url"].'&countcomments='.$row["countcomments"].'&submittor='.$row["username"].'&submissiondate='.$row["datesubmitted"].'&dispurl='.$row["displayurl"].'">'.$row["countcomments"].' comments</a></td>';     echo '</tr>';     } echo "</table>";     

select s.loginid, s.title, s.url, s.displayurl, s.datesubmitted, l.username,   s.submissionid, count(c.commentid) countcomments,    greatest(s.datesubmitted, coalesce(max(c.datecommented), s.datesubmitted)) most_recent submission s inner join login l on s.loginid = l.loginid left outer join comment c on s.submissionid = c.submissionid group s.submissionid order most_recent desc limit 10

Comments

Post a Comment

Popular posts from this blog

windows - Why does Vista not allow creation of shortcuts to "Programs" on a NonAdmin account? Not supposed to install apps from NonAdmin account? -

-
i'm working on installer (using wise installer, older version 1999). i'm creating shortcut in programs group exe. i'm creating shortcut on desktop. if install run admin account, create shortcut on common desktop , common program group (i.e., read hkey_local_machine\explorer\shellfor users). if it's installed nonadmin account, install hkey_current_user's desktop , program group. behavior install on: xp nonadmin - desktop , program shortcuts install ok. vista admin - desktop & program shortcuts install ok. vista non-admin, uac off - desktop shortcut installs, program shortcut not . however, program group folder they're supposed installed does created. at end of install, launch program group has shorcut. launches in of above. can manually drag shortcut folder , works fine. i'm bloody baffled. i've tried installing other commercial apps (opera, foxit, firefox) firefox install under nonadmin (and if select other program files,...
Read more

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

unit testing - How to mock PreferenceManager in Android? -

-
i've written class using context , third party library , sharedpreferences preferencemanager . it's possible mock context , third party library can mocked using mocking framework, preferencemanager ? i have 2 methods: public void savestring(thirdpartyobject obj) { sharedpreferences apppreferences = preferencemanager.getdefaultsharedpreferences(mcontext); sharedpreferences.editor editor = apppreferences.edit(); editor.putstring(mcontext.getstring( r.string.preferences_string_name), obj.getstring()); editor.commit(); } and corresponding, loads preferences. it doesn't want mock instance of preferencemanager (which used in preferencefragment or preferenceactivity ). you want either: a mock sharedpreferences , in case can mock context#getsharedpreferences (which called preferencemanager#getdefaultsharedpreferences anyhow). you'll have make mock sharedpreferences.editor if preferences edited, above. know how moc...
Read more