Jump to content


Photo

sorting sql result by day


  • Please log in to reply
3 replies to this topic

#1 markbett

markbett
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 25 August 2006 - 10:55 PM

right now i am running the SQL query

$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));

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?

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 26 August 2006 - 02:25 PM

NO I DONT WANT TO GROUP BY in my sql query


Since that would seem to be a reasonable solution to the problem, perhaps you need to explain why not.
Legend has it that reading the manual never killed anyone.
My site

#3 BigTime

BigTime
  • Members
  • PipPipPip
  • Advanced Member
  • 60 posts

Posted 27 August 2006 - 10:54 PM

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 http://www.tcyfl.net...division=Bantam 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....

    # 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>");

 

}

     

Hope this helps you some



#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 August 2006 - 10:19 PM

Yeah, exactly what is it that you're trying to acccomplish?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users