Count number of rows in table and get latest row

[dc_jt]

Hi

 

Ive got a table called course_login which takes the staff_id, course_id, ip and login_date of every time a user logs into my system.

 

However I want to count how many times a certain user has logged in and when the last login was. Therefore would I do something like this to get the amount of times?

 

"SELECT COUNT(course_login_id) AS num FROM course_login WHERE staff_id = $iStaffId";

 

Not too sure what the best way is to get the latest one? Is this right?

 

"SELECT * from course_login WHERE staff_id = $iStaffId ORDER by login_date DESC, limit 0,1"

 

Thanks

[mmarif4u]

Hi

 

Ive got a table called course_login which takes the staff_id, course_id, ip and login_date of every time a user logs into my system.

 

However I want to count how many times a certain user has logged in and when the last login was. Therefore would I do something like this to get the amount of times?

 

"SELECT COUNT(course_login_id) AS num FROM course_login WHERE staff_id = $iStaffId";

 

Not too sure what the best way is to get the latest one? Is this right?

 

"SELECT * from course_login WHERE staff_id = $iStaffId ORDER by login_date DESC, limit 0,1"

 

Thanks

 

Almost u get it.

But if u use the

mysql_num _rows($result)

it will also puul out the number of rows for user.

 

Second Use

desc with limit 1

Hope this will help.

[ToonMariner]

"SELECT COUNT(course_login_id) AS num, login_date FROM course_login WHERE staff_id = $iStaffId ORDER by login_date DESC";

 

first row of results will give youthe info you need.

[dc_jt]

Thanks for the help, however how would I return the number ?

 

And for some reason it is printing out DESC LIMIT 0,11 when I do:

 

	public function GetCourseLogins($iStaffId, $iCourseId)
{
	$sSql = "SELECT COUNT($this->sPrimaryKey) AS num FROM $this->sTableName
	WHERE course_login.staff_id = '$iStaffId' 
	& course_login.course_id = '$iCourseId'
	ORDER by login_date DESC
	LIMIT 0,1";
	print($sSql);
	return (int) mysql_fetch_object(mysql_query($sSql, $this->oDb->GetConnection()));

[dc_jt]

Ignore the last reply. I have sorted it, thanks