ec Posted March 29, 2008 Share Posted March 29, 2008 Is there anyway of grouping the results by detention.detentiondate so that each date is only shown once? $query = "SELECT detention.detentiondate, detention.pupilno, detention.reason, detention.teacherid, pupil.firstname, pupil.lastname FROM detention, pupil WHERE detention.detentiondate >= '$_SESSION[startdate]' AND detention.detentiondate <= '$_SESSION[enddate]' AND detention.pupilno = pupil.pupilno ORDER BY detention.detentiondate, pupil.lastname, pupil.firstname, pupil.pupilno "; $result = mysql_query($query); if ($result) { while ($array= mysql_fetch_assoc($result)) { echo "<br />"; print "$array[detentiondate]<br />"; print "$array[pupilno]: <i>$array[firstname] $array[lastname]</i><br />"; print "<b>Reason:</b> $array[reason]<br />"; print "<b>Allocated By:</b> $array[teacherid]<br />"; } } ?> Link to comment Share on other sites More sharing options...
mwasif Posted March 29, 2008 Share Posted March 29, 2008 Are you looking for this? Link to comment Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Share Posted March 29, 2008 Lets try a JOIN instead and define the GROUP BY <?php $query = "SELECT detention.detentiondate, detention.pupilno, detention.reason, detention.teacherid, pupil.firstname, pupil.lastname FROM `detention` LEFT JOIN `pupil` on (detention.pupilno = pupil.pupilno) WHERE detention.detentiondate >= '$_SESSION[startdate]' AND detention.detentiondate <= '$_SESSION[enddate]' ORDER BY detention.detentiondate, pupil.lastname, pupil.firstname, pupil.pupilno GOUP BY detention.detentionid "; $result = mysql_query($query) or die(mysql_error()."<br /><br />".$query); ?> Check my spelling. Link to comment Share on other sites More sharing options...
ec Posted March 29, 2008 Author Share Posted March 29, 2008 mwasif, yes that is similiar to what i am looking for but it's not working even when i've changed it...have i done any thing wrong? $query = "SELECT detention.detentiondate, detention.pupilno, detention.reason, detention.teacherid, pupil.firstname, pupil.lastname FROM detention, pupil WHERE detention.detentiondate >= '$_SESSION[startdate]' AND detention.detentiondate <= '$_SESSION[enddate]' AND detention.pupilno = pupil.pupilno ORDER BY detention.detentiondate, pupil.lastname, pupil.firstname, pupil.pupilno "; $result = mysql_query($query); $current_date = ""; while($rs = mysql_fetch_array($result)) { if($current_date!=$rs["detentiondate"]) { $current_date=$rs["detentiondate"]; print "$array[detentiondate]<br />"; } print "$array[pupilno]: <i>$array[firstname] $array[lastname]</i><br />"; print "<b>Reason:</b> $array[reason]<br />"; print "<b>Allocated By:</b> $array[teacherid]<br />"; } ?> Link to comment Share on other sites More sharing options...
ec Posted March 29, 2008 Author Share Posted March 29, 2008 cooldude832 I get nothing if i did it ur way.....I'M SO CONFUSEDDD AHHHHHHHHH Link to comment Share on other sites More sharing options...
keeB Posted March 29, 2008 Share Posted March 29, 2008 Post the schema for your database so I can quickly replicate it on my end and help you with a solution. Link to comment Share on other sites More sharing options...
ec Posted March 29, 2008 Author Share Posted March 29, 2008 -- phpMyAdmin SQL Dump -- version 2.10.3 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Mar 29, 2008 at 07:16 PM -- Server version: 5.0.45 -- PHP Version: 5.2.3 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `e1420022_echan334` -- -- -------------------------------------------------------- -- -- Table structure for table `academicyear` -- CREATE TABLE `academicyear` ( `startdate` date NOT NULL, `enddate` date NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `accesslevel` -- CREATE TABLE `accesslevel` ( `position` varchar(30) collate latin1_general_ci NOT NULL, `level` varchar(1) collate latin1_general_ci NOT NULL, PRIMARY KEY (`position`), UNIQUE KEY `position` (`position`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `detention` -- CREATE TABLE `detention` ( `pupilno` varchar(6) collate latin1_general_ci NOT NULL, `detentiondate` date NOT NULL, `reason` text collate latin1_general_ci NOT NULL, `teacherid` varchar(3) collate latin1_general_ci NOT NULL, PRIMARY KEY (`pupilno`,`detentiondate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `detentiondates` -- CREATE TABLE `detentiondates` ( `detentiondate` date NOT NULL, PRIMARY KEY (`detentiondate`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `members` -- CREATE TABLE `members` ( `firstname` varchar(100) collate latin1_general_ci default NULL, `lastname` varchar(100) collate latin1_general_ci default NULL, `teacherid` varchar(100) collate latin1_general_ci NOT NULL, `passwd` varchar(32) collate latin1_general_ci NOT NULL default '', `level` int(1) NOT NULL, PRIMARY KEY (`teacherid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `pupil` -- CREATE TABLE `pupil` ( `pupilno` varchar(4) collate latin1_general_ci NOT NULL, `firstname` varchar(15) collate latin1_general_ci NOT NULL, `lastname` varchar(15) collate latin1_general_ci NOT NULL, `contactname` varchar(20) collate latin1_general_ci NOT NULL, `addresslineone` varchar(30) collate latin1_general_ci NOT NULL, `addresslinetwo` varchar(30) collate latin1_general_ci NOT NULL, `addresslinethree` varchar(30) collate latin1_general_ci NOT NULL, `city` varchar(30) collate latin1_general_ci NOT NULL, `county` varchar(30) collate latin1_general_ci NOT NULL, `postcode` varchar(10) collate latin1_general_ci NOT NULL, `country` varchar(30) collate latin1_general_ci NOT NULL, `phone` varchar(13) collate latin1_general_ci NOT NULL, PRIMARY KEY (`pupilno`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `teacher` -- CREATE TABLE `teacher` ( `teacherid` varchar(3) collate latin1_general_ci NOT NULL, `position` varchar(30) collate latin1_general_ci NOT NULL, PRIMARY KEY (`teacherid`), UNIQUE KEY `id` (`teacherid`), KEY `id_2` (`teacherid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; Link to comment Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Share Posted March 29, 2008 echo out the query it should work it should pull all Detentions with the date range defined by those sessions all grouped under the Detention ID I have used a very similar query on Events and User data the only way it returns 0 s rows if 0 detentions exist in that date range. The left join simply attempts to fit in any user data that falls in the User = Detention Users Without the join if the UserData doesn't match the Events data (or users is empty) the whole query is empty Link to comment Share on other sites More sharing options...
ec Posted March 29, 2008 Author Share Posted March 29, 2008 I don't have a field called detention id...but anyways.. do u mean echo it as I have been printing it before or do i need to arrange the print in any weird way? thanks Link to comment Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Share Posted March 29, 2008 I don't have a field called detention id...but anyways.. do u mean echo it as I have been printing it before or do i need to arrange the print in any weird way? thanks You really need a primary key for the detention tables called detentionid that is why my query is failing and if you are error reporting it as I wrote it should error when I say echo it out take what is echoed out with all the php variables applied and take it into phpmyadmin and tweak it to work right. Link to comment Share on other sites More sharing options...
ec Posted March 29, 2008 Author Share Posted March 29, 2008 but that's no the problem i'm having....it shows me the correct information with my original code...it just doesn't show it like this Detention Date <detentiondate> <pupilno><firstname><lastname> Reason: <reason> Issued By:<teacherid> <pupilno><firstname><lastname> Reason: <reason> Issued By:<teacherid> <detentiondate> <pupilno><firstname><lastname> Reason: <reason> Issued By:<teacherid> etc.. which is the way i need it to show Link to comment Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Share Posted March 29, 2008 Change my group by to be Group by Detention Date Link to comment Share on other sites More sharing options...
ec Posted March 29, 2008 Author Share Posted March 29, 2008 that's what i did at the start... Link to comment Share on other sites More sharing options...
cooldude832 Posted March 29, 2008 Share Posted March 29, 2008 that's what i did at the start... and it always returns 0 rows? Link to comment Share on other sites More sharing options...
Barand Posted March 29, 2008 Share Posted March 29, 2008 this has now been solved in the PHP Help forum http://www.phpfreaks.com/forums/index.php/topic,189855.msg852202.html#msg852202 No doubt EC will want to apologise for wasting people's time by double-posting the same question in two forums. Link to comment Share on other sites More sharing options...
Recommended Posts