NYCPHP Meetup

[Dan Cech]

The reason you are only getting results for students who were absent at 
least once is that your WHERE a.attended = 2 is cutting the result set 
down to only those students who missed at least one class.

To retrieve a list of students and the number of classes attended, 
missed and excused you should use:

SELECT		l.first, l.last,
		count(a.attended) AS attended,
		count(b.attended) AS missed,
		count(c.attended) AS excused
FROM		login l
LEFT JOIN	attendance a ON a.student=l.id AND a.attended=1
LEFT JOIN	attendance b ON b.student=l.id AND b.attended=2
LEFT JOIN	attendance c ON c.student=l.id AND c.attended=3
WHERE		l.section=1
GROUP BY	l.id

That will give you a result showing the number of classes attended, 
missed and excused for each student in section 1.

Dan

Christopher R. Merlo wrote:
> On 2004-02-09 19:09 -0500, Mitch Pirtle <mitchy at spacemonkeylabs.com> wrote:
> 
> 
>>>Basically, I want to select every student, and the amount of times
>>>he/she was absent (including never).  But I only get the students who
>>>were absent at least once.  I can't think of another way to get that,
>>>other than what feels like it would be a severe kludge.
>>
>>Would that kludge include the use of mysql_num_rows?  You could just add 
>>that to your processing, or perhaps play about with SUM() and GROUP BY...
> 
> 
> Yeah, essentially a second query within the foreach of the first
> query.  I suppose it'll work, but then I won't be able to sort the
> first query by amount of absences.  If that's the best solution, then
> so be it.
> 
> I've been reading ppl's advice on devshed and dbforums about using a
> left join instead of just "from login, attendace".  I think I'm
> following their advice, and it's not working.  This query:
> 
> mysql> select l.first, l.last, count( a.attended )
>     -> from attendance as a left join login as l
>     -> on ( l.id = a.student )
>     -> where a.attended = 2 and l.section = 1
>     -> group by l.id
>     -> ;
> 
> still only gives me the rows where the count > 0.  I've also switched
> the order of the tables in the from line without success.
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk



>From hans not junk at nyphp.com  Mon Feb  9 22:19:11 2004
Return-Path: <hans not junk at nyphp.com>
Received: from ehost011-1.exch011.intermedia.net (unknown [64.78.21.3])
	by virtu.nyphp.org (Postfix) with ESMTP id 04C15A87E1
	for <talk at lists.nyphp.org>; Mon,  9 Feb 2004 22:19:11 -0500 (EST)
X-MimeOLE: Produced By Microsoft Exchange V6.5.6944.0
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: RE: [nycphp-talk] NEW PHundamentals Question
Date: Mon, 9 Feb 2004 19:19:07 -0800
Message-ID: <41EE526EC2D3C74286415780D3BA9F87772542 at ehost011-1.exch011.intermedia.net>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [nycphp-talk] NEW PHundamentals Question
Thread-Index: AcPvg8JBGoc/vU9hS8WtT34v86yNOQAAIUjg
From: "Hans Zaunere" <hans not junk at nyphp.com>
To: "NYPHP Talk" <talk at lists.nyphp.org>
X-BeenThere: talk at lists.nyphp.org
X-Mailman-Version: 2.1.2
Precedence: list
Reply-To: NYPHP Talk <talk at lists.nyphp.org>
List-Id: NYPHP Talk  <talk.lists.nyphp.org>
List-Unsubscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
	<mailto:talk-request at lists.nyphp.org?subject=unsubscribe>
List-Archive: <http://lists.nyphp.org/pipermail/talk>
List-Post: <mailto:talk at lists.nyphp.org>
List-Help: <mailto:talk-request at lists.nyphp.org?subject=help>
List-Subscribe: <http://lists.nyphp.org/mailman/listinfo/talk>,
	<mailto:talk-request at lists.nyphp.org?subject=subscribe>
X-List-Received-Date: Tue, 10 Feb 2004 03:19:11 -0000


> check the http referer?
> or is that so obvious that there's an easy way around it?

That's very easy to forge.

> > That's one method that's growing in popularity.
> > Prior to using this method, was there another method that you've =
used?
> >
> > Jeff
> >
> > jon baer wrote:
> >> it can be found on your own forum ... i have used it also in the =
past:
> >>
> >> http://forums.nyphp.org/index.php?act=3DReg&CODE=3D00
> >>
> >> the security code confirmation / dynamic image input is pretty much =
like
> >> visually signing a form + preventing 3rd party dumping.  as per for
> >> accounting to the real valid information that was sent in, id like =
to
> >> see that code :-)

I think it's key to remember that a browser is only skin deep... so to =
speak.  For instance, consider someone telnets to port 80 and issues raw =
HTTP requests.  How can this be handled?  Or even easier, suppose =
someone is using LiveHTTPHeaders (the Mozilla plugin).  It makes forging =
HTTP requests and replay attacks trivial.

I'm not ready to make any recommendations yet... just wanted to throw =
some things on the table :)


H