Jump to content

sorting sql result by day


markbett

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?
Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.