[SOLVED] Retrieving last login from activity log table

[TGWSE_GY]

I have a table in my database called ActivityLog and it holds the following data CustID, StartTime, Date, EndTime, and IPAddress.

 

 

Well there can be multiple records with the same CustID, what I am wondering is if there is a way to grabbing the most recent record in the table after I pull all of the records with CustID. Then display to the user by echoing?

 

Thanks

[Ken2k7]

Is there a way to tell what the most recent record is with your set up? I would just add a PRIMARY KEY.

[TGWSE_GY]

A Primary key, how would that help a key  cannot be duplicated.

[.josh]

assuming 'Date' is what constitutes having the "most recent":

 

select * from ActivityLog where CustID = '$custID' order by `Date` desc limit 1

 

notice the backticks on `Date`.  That's because date is a reserved word.  If you want to make a column name a reserved word, you must put backticks around it.  Also, that only works in mysql. So it's not going to work in some other database.  I suggest you change that column name to something else, as it's bad programming to use reserved words for things.

[Ken2k7]

A Primary key, how would that help a key  cannot be duplicated.

I said *ADD* a PRIMARY KEY.

[.josh]

I don't really see how adding a primary key would help out here...if you're thinking to order by highest key..well he's already filtering by CustID and really what's the difference between ordering by a primary key vs. the date he's already tracking?

[Ken2k7]

The problem is I don't know what starttime, endtime and date mean. It could be a table for reminders. And sorting by those doesn't guarantee the most recent entry.

[.josh]

I assumed he meant most recent entry for the CustID, not the most recently added row to the table. 

[Ken2k7]

Right. But how would you get the most recent entry for CustID?

 

I have this in mind -

SELECT * FROM ActivityLog WHERE CustID = '3' ORDER BY id DESC LIMIT 1

 

It's possible we're thinking about this in 2 different perspectives.

[.josh]

Well I posted my take already, see the bolded red:

 

assuming 'Date' is what constitutes having the "most recent":

 

select * from ActivityLog where CustID = '$custID' order by `Date` desc limit 1

 

notice the backticks on `Date`.  That's because date is a reserved word.  If you want to make a column name a reserved word, you must put backticks around it.  Also, that only works in mysql. So it's not going to work in some other database.  I suggest you change that column name to something else, as it's bad programming to use reserved words for things.

 

Ordering by the "id" (I assume this is where you mean to have the primary key, or at least some kind of globally incremented value) would show the most recent one entered, but it would not tell you when it was entered.  Sure, when you're selecting *, all those date fields will be selected, so he will have that info, but the point is, if his goal is to find the most recent entry, ordering by some "id" is not necessary. Just order by the column that constitutes having the "most recent date" (I'm assuming it's the `Date` column he has).

[Ken2k7]

I don't recall the OP asked for when the row was inserted. But yours work if that is the case.

[.josh]

I have a table in my database called ActivityLog and it holds the following data CustID, StartTime, Date, EndTime, and IPAddress.

 

 

Well there can be multiple records with the same CustID, what I am wondering is if there is a way to grabbing the most recent record in the table after I pull all of the records with CustID. Then display to the user by echoing?

 

Thanks

 

I interpreted the part in red as "when".  But even still, a column with timestamp (or a date type column acting in same principle) would act as your incremented column just the same.

[TGWSE_GY]

I got to Crayon thanks xD