| Trevor Gryffyn 2004-11-22, 3:55 pm |
| To say that your query is "never going to look like that" isn't entirely
true. I'm sure with enough CASE statements and the use of some
variables and such you could get your SQL output to look like that. SQL
is pretty powerful like that.
BUT.. Everyone who said "Use PHP to filter/display the data" is
correct. That's probably the preferred method of accomplishing this
goal. It's how I would do it at least. I don't think SQL is the best
for doing custom display of data, you just need to learn what data
you're getting back from the SQL statement and what you need to do to it
in order to get the information that you need and display it how you
want to.
Someone else mentioned the "Group By" not containing one of the fields,
that's a good call, but I'm wondering why "Group By" is used here at
all. There are no aggregate functions like SUM or MAX or anything, so
Group By isn't needed. If for some reasons you were getting duplicate
records, the Group By would elimate them (I believe) in this case, but a
SELECT DISTINCT ... Would do the same thing.
Actually, Group By wouldn't need to contain any/all of the SELECT fields
because there's no aggregate, so you can probably do any number of the
SELECT items in the Group By without an error. No aggregate means no
Group By required so you could do one, three, fifty items.. Doesn't
matter. Shouldn't produce an error, it'll just do a selective DISTINCT
instead of a total DISTINCT.
Sounds like GH thinks that "Group By" is a function that will create the
output that he's looking for. It's not really used for that. It's used
for telling SQL how to group data that's not part of the MAX or SUM or
AVG or other aggregate functions.
If you did:
SELECT A.`AttID` , MAX(S.`SessionDate`) , P.LastName, P.FirstName,
A.`Present`
FROM `Attendance` A, Sessions S, Participants P
WHERE S.SessionID =3D A.`Session` AND P.Part_ID =3D A.`Participant`
GROUP BY A.`AttID`, P.LastName, P.FirstName, A.Present, A.AttID
This will give, for each name and unique SessionDate, you one AttID, one
Last Name, one First Name, one Present... All for the latest
SessionDate. Because we're looking at the max SessionDate (greatest
value) and returns the info for that date value.
If someone attended more than one Session on the same day, it'll return
more than one line for that person with different AttID's.
Just some random thoughts.
-TG
> -----Original Message-----
> From: John Holmes [mailto:holmes072000@charter.net]=20
> Sent: Saturday, November 20, 2004 10:22 AM
> To: GH
> Cc: php-db@lists.php.net; mysql@lists.mysql.com
> Subject: Re: [PHP-DB] Optimize Query Output
>=20
>=20
> GH wrote:
> P.FirstName, A.`Present`
> P.FirstName
>=20
> Your query is never going to look like that. You use PHP to=20
> format the result set so it displays how you want it. As you
> loop through the returned rows, keep track of what the current
> first and last name are.=20
> If they change, then output them, otherwise output a blank cell.
>=20
> --=20
>=20
> ---John Holmes...
|