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

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

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more