Jump to content

Archived

This topic is now archived and is closed to further replies.

markbett

sorting sql result by day

Recommended Posts

right now i am running the SQL query

[code]$sql = mysql_query("SELECT start_time, title FROM events WHERE YEAR( start_time )
= $show_cal_year AND MONTH( start_time ) =
'$show_cal_month' ORDER BY start_time");


while($row = mysql_fetch_array($sql)){
stripslashes(extract($row));[/code]

what i need to do is be able to deal with the results by DAY

so for each day i lump them together (NO I DONT WANT TO GROUP BY in my sql query)  is there a way to achieve this AS IF I HAD run 31  queries that were defined by DAY (start_time)= $day but by only running the single query for the whole month?

Share this post


Link to post
Share on other sites
[quote]NO I DONT WANT TO GROUP BY in my sql query[/quote]

Since that would seem to be a reasonable solution to the problem, perhaps you need to explain why not.

Share this post


Link to post
Share on other sites
A long time ago someone on these boards helped me with a similar situation.  I had a mass of records and I wanted to order them by week, with each week having their own table, and a little header at the top of the table identifying the week number....tried to dig it up, but it looks like it was trimmed or lost in the boards transitions.

You can see what the display is like by visiting [url=http://www.tcyfl.net/Pac10Schedules.php?division=Bantam]http://www.tcyfl.net/Pac10Schedules.php?division=Bantam[/url] I'll provide the crude code example then maybe you can run with it  ;D

I guess the important part of that for me was setting up an additional field in my table that held a single numeral for the week number, you'll have to have something similar to work off of, or maybe someone much smarter than me can help you transform this into a weekday name thing - like if day=sunday then $daynumber =1 on your insert commands...I dunno....

[code]    # setup SQL statement
$SQL = " SELECT fields FROM mytable ";

    # execute SQL statement
    $retid = mysql_db_query($db, $SQL, $cid);

    # check for errors
    if (!$retid) { echo( mysql_error()); }

    else {
    $weektracker = 0;
while ($row = mysql_fetch_array($retid)) {
$league = $row["league"];
$week = $row["week"];
$hometeam = $row["hometeam"];
$awayteam = $row["awayteam"];
$month = $row["month"];
$date = $row["date"];
$newtime = $row["newtime"];
$homescore = $row["homescore"];
$awayscore = $row["awayscore"];
$id = $row["id"];
$division = $row["division"];
$homeconference = $row["homeconference"];
$awayconference = $row["awayconference"];
$fieldlink = $row["fieldlink"];
$fieldname = $row["fieldname"];



{
  if($weektracker < $week)
  {
      if($weektracker > 0)
  echo ("</table><BR><BR>\n");

      echo ("<b><font face=arial size=2 color=#e0e0e0>WEEK $week</b></font>\n");
      echo ("<TABLE cellpadding=2 border=1 width=99% style=\"border-collapse: collapse; border: solid; border:1px;\">");
 
      #These are my table columns labels 
      echo ("<TR><TD width=80><font face=arial size=1 color=#e0e0e0><b>DIVISION</b></TD><TD width=40><font face=arial size=1 color=#e0e0e0><B>DATE</B></TD><td width=60><font face=arial size=1 color=#e0e0e0><b>TIME</b></TD><td><font face=arial size=1 color=#e0e0e0><B>AWAY</B></TD><td><font face=arial size=1 color=#e0e0e0><b>HOME</b></td><TD><font face=arial size=1 color=#e0e0e0><b><center>FIELD</center></b></TD></TR>");
      $weektracker = $week;
    }

#these are the cell populations with some if/thens i dont feel like editing out :)
            echo ("<TR>");
            echo ("<TD><font face=arial size=1 color=#e0e0e0>$division</td><td><font face=arial size=1 color=#e0e0e0>$month - $date</td><td><font face=arial size=1 color=#e0e0e0>$newtime</TD>\n");

if ($awayscore < $homescore){
echo ("<td><font face=arial size=1 color=#747e8e>$awayteam</TD>\n");
}

else{
echo ("<td width=110><font face=arial size=1 color=#ffffff>$awayteam</TD>\n");
}

if ($awayscore > $homescore){
echo ("<TD><font face=arial size=1 color=#747e8e>$hometeam</TD>\n");
}

else{
echo ("<td width=110><font face=arial size=1 color=#ffffff>$hometeam</TD>\n");
}

echo ("<td><a href=\"http://$fieldlink\" target=_blank><font face=arial size=1 color=#e0e0e0>$fieldname</td>\n");
            echo ("</TR>");
  }}

        echo ("</TABLE><BR>");



}

   
[/code]

Hope this helps you some

Share this post


Link to post
Share on other sites
Yeah, exactly what is it that you're trying to acccomplish?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.