[SOLVED] Combine multiple entries for count

[bubbasheeko]

I have a database that takes in membership scans (logins).  I have to send out a report that contains the following information:

 

ClientID

Name

Login Dates

Login Count <- count for the current member for the given time area.

 

I have it pull the client information from two tables:  tbl_MemberLog and tbl_Members.

 

This is the query I am using:

 

SELECT tbl_MemberLog.ClientID, tbl_MemberLog.LogDate, tbl_Members.FirstName, tbl_Members.LastName FROM tbl_MemberLog, tbl_Members WHERE tbl_MemberLog.ClientID = tbl_Members.ClientID AND MONTH(tbl_MemberLog.LogDate) = "'. $month . '" AND YEAR(tbl_MemberLog.LogDate) = "'. $year . '"') or die(mysql_error());

 

So now, I am unsure whether I should proceed with a mysql option or a php option to count the given member log ins for the time period selected.

 

IE:

 

ClientID    Name    Member Logins  Last Login

 

000001    Kevin D.  36                  2009-01-06

 

 

So instead the ClientID being printed out 36 times along with the Clients name, etc....I only want it to list it once and put the count of the recorded logins under Member Logins.  I can not for the life of me remember how I can do that.

[bubbasheeko]

Okay, I have been able to figure out how to stop the ClientID from repeating - but now I have to figure out how to count how many times the client has swiped.  Preferably with the same query.

 

Here is the updated query to group the records:

 

SELECT tbl_MemberLog.ClientID, tbl_MemberLog.LogDate, tbl_Members.FirstName, tbl_Members.LastName FROM tbl_MemberLog, tbl_Members WHERE tbl_MemberLog.ClientID = tbl_Members.ClientID AND MONTH(tbl_MemberLog.LogDate) = "09" AND YEAR(tbl_MemberLog.LogDate) = "2008" GROUP BY `ClientID` ORDER BY `LogDate` DESC

 

So now for every ClientID that has more than one record - I need to count them.

[bubbasheeko]

Figured out that part.

 

SELECT tbl_MemberLog.ClientID, MAX(tbl_MemberLog.LogDate), tbl_Members.FirstName, tbl_Members.LastName, tbl_Members.HomePhone, COUNT(*) FROM tbl_MemberLog, tbl_Members WHERE tbl_MemberLog.ClientID = tbl_Members.ClientID AND MONTH(tbl_MemberLog.LogDate) = "09" AND YEAR(tbl_MemberLog.LogDate) = "2008" GROUP BY `ClientID` ORDER BY `LogDate` DESC

 

Not to mention I was able to pull the last date the member swiped by using MAX()