Grouping Results - PHP or MySQL? Need Help!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdrees
Forum Newbie
Posts: 7
Joined: Wed Dec 21, 2005 9:26 am

Grouping Results - PHP or MySQL? Need Help!!

Post by cdrees »

Hello Everyone,


I have a DB that I'm developing, that I've currently got functional, but I want to clean up the output.

Currently, I have a site that displays currently available courses, listed by date. It shows the following:

Course Date Course Title Notes
======================================================
2005-12-20 Course1 9am-5pm
2005-12-23 Course2 6pm-10pm
2005-12-26 Course1 9am-5pm
2005-12-30 Course1 9am-5pm


I'd like it to group by Course (if one is schedule), list the actual course title only once in my output, and then under each Course Title Heading list the dates and notes, like so:

Course One
===============================================
2005-12-20 9am-5pm
2005-12-26 9am-5pm
2005-12-30 9am-5pm

Course Two
===============================================
2005-12-23 6pm-10pm



I have three tables currently, one to hold the courses, one to hold locations, and one to hold scheduled dates:

PHP Code:
CREATE TABLE `academy_loc` (
`loc_id` int(5) NOT NULL auto_increment,
`location_st` varchar(5) collate latin1_general_ci NOT NULL,
`location_co` varchar(25) collate latin1_general_ci default NULL,
PRIMARY KEY (`loc_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;



CREATE TABLE `academy_main` (
`main_id` int(5) NOT NULL auto_increment,
`loc_id` int(5) NOT NULL,
`course_title` varchar(150) collate latin1_general_ci default NULL,
`course_desc` text collate latin1_general_ci,
`class_type` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`main_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=40 ;



CREATE TABLE `academy_sched` (
`sched_id` int(11) NOT NULL auto_increment,
`main_id` int(11) NOT NULL,
`course_date` date default NULL,
`sched_notes` text collate latin1_general_ci,
PRIMARY KEY (`sched_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=75 ;



I am thinking this is relatively simple to do, I've seen it done many times before, but I can't seem to get my head around it. Thanks so much!!

:wink:
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

The query could be something like:

Code: Select all

SELECT
   course_title,
   course_desc,
   course_date,
FROM
   academy_main,
   academy_sched
ORDER BY
   course_title,
   course_date
Then, when you're looping through the results, keep track of the last course_title. If the course_title in the current row isn't the same as the course_title in the last row, then you're at a new course - output the name of the course, then keep looping through the rows.

Make sense?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
cdrees
Forum Newbie
Posts: 7
Joined: Wed Dec 21, 2005 9:26 am

Making Sense

Post by cdrees »

[/quote]Then, when you're looping through the results, keep track of the last course_title. If the course_title in the current row isn't the same as the course_title in the last row, then you're at a new course - output the name of the course, then keep looping through the rows.


I understand what your saying (basically) and that very well might fit the bill... the looping output to html piece is where it gets hairy for me.. Once I see an example, I usually can work backwards from it and figure it out... So if you have a moment to jot out an example, it would be MUCH appreciated!! :)



Thanks in advance
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

ok, so say I've run the query and have the result set:

Code: Select all

$current_title = '';
while($row = mysql_fetch_assoc($results))
{
   if($row['title'] != $current_title)
   {
      echo "<strong>$row[title]</strong><br />";
   }
   $current_title = $row['title'];

   echo "$row[date]  $row[notes]";
}
Granted this isn't throwing the results into a table, but the logic is there.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
cdrees
Forum Newbie
Posts: 7
Joined: Wed Dec 21, 2005 9:26 am

Thanks

Post by cdrees »

Great.... I will work with this for awhile and see if I can get it working...

Thanks so much! :)



Christopher D. Rees
A+, Network+, Server+, MCSE, MCT
COPT Certified Law Enforcement Instructor
http://www.teamrees.com
cdrees
Forum Newbie
Posts: 7
Joined: Wed Dec 21, 2005 9:26 am

Solution Worked

Post by cdrees »

You're solution worked great... thanks so much. I tweaked it slightly, as noted in the code section below:

My next question however, is it possible (looping through the array) to display this information now into two main colums?

I'd like to have it like so (I know my spacing is going to compress.. it should look like a two colum report):

=================================================
Course Title 1 | Course Title 2
=================================================
Date 1 :: Time | Date 1 :: Time
Date 2 :: Time | Date 2 :: Time
etc.... | etc...
=================================================
Course Title 3 | Course Title 4
=================================================
Date 1 :: Time | Date 1 :: Time
Date 2 :: Time | Date 2 :: Time
etc.... | etc...


Code: Select all

if (mysql_num_rows($results) < 1) {
     //print message
     $display_block .= "<P>You have no users in the database.</p>";
     
     } else {

$display_block .= "
<table border=0 width=100% cellspacing=1>
	<tr>
		<td>&nbsp;</td>
		<td>&nbsp;</td>
	</tr>
";

$current_title = '';
while($row = mysql_fetch_assoc($results))
{
   if($row['course_title'] != $current_title)
   {
        $link_id = $row['main_id'];
        $link_title = $row['course_title'];
        $display_block .= "<tr>
         <td bgcolor=\"#FFFFFF\"><p align=\"left\"><br /><h4><a href=\"coursedetail.php?id=$link_id\">$link_title<h4></font></td>
         </tr>";
   }
   $current_title = $row['course_title'];
    $display_block .= "<tr>
         
         <td bgcolor=\"#FFFFFF\"><p align=\"left\">$row[course_date] :: $row[sched_notes]</font></p></td>
         
         </tr>";
} 

$display_block .= "</table>";

}
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Yes it's possible, but as you may have guessed, it makes the problem more complex. You can't rely on the course title anymore, you'll have to rely on a counter to tell you when to make a new line, and when to just make a new column.

For my money, I'd parse through the results and throw them all in an array - sort of mimicing the format you want. For example (in pseudo-code)

Code: Select all

$past_title = '';
$column = 0;
$row = 0;
for($loop_through_results)
{
   if($this_row_title != $past_title)
  {
    //increment the column we're on.
    //if we've hit our second column, wrap around to the 
    //first column on the next row
   
    $column ++;
    if($column > 1)
    {
       $row++;
       $column =  0;
    }
    else
    {
       $column = 1;
    }

    $past_title = $this_row_title;
  }

  $results[$row][$column][$past_title][] = array(date=>$this_row_date,time=>$this_row_time);
}
This would result in an array like:

Code: Select all

$results => [0][0]['Course Title 1'][0]['date'] = Date 1
                                       ['time'] = Time 1
                                    [1]['date'] = Date 2
                                       ['time'] = Time 1

            [0][1]['Course Title 2'][0]['date'] = Date 1
                                       ['time'] = Time 1
                                    [1]['date'] = Date 2
                                       ['time'] = Time 1   

            [1][0]['Course Title 3'][0]['date'] = Date 1
                                       ['time'] = Time 1
                                    [1]['date'] = Date 2
                                       ['time'] = Time 1

            [1][1]['Course Title 4'][0]['date'] = Date 1
                                       ['time'] = Time 1
                                    [1]['date'] = Date 2
                                       ['time'] = Time 1
You could then use a boat-load of foreach() loops to parse that and format it nicely.

Take some time to really think about this problem. I'm sure it can be done more efficiently - I'm not sure how right now though.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply