[Resolved] MySQL query, Regex problem - finding the minID and date for an entry

klatt

Hello. I have a table called hours set up like this:

hoursID (Unique, auto)-username-datetime_in-datetime_out

Now I am looking for all the times a user logged in between two certain dates (which I input from the user.

I have the functionality of a program set up but my queries return the incorrect values (well values different from what I intended :-))

Anyway: here are the queries I am using to find the minimum ID of a user at a certain date. The

select min(hoursid) from hours where datetime_in regexp '[20041201][0-9]*'
and username='klatt'

The values have been manually put in there to take the place of the variables as to allow testing through the mysql console.

I think the probelm is the regexp part :-/

You guys have been a great help too me.

TIA,

Frank

klatt

The date format is that of regular MySQL ISO 8601 (I think that's right)

2004-12-02 16:10:50

Roger_Ramjet

$sql = "SELECT * FROM hours WHERE username = '" . $_POST['user'] . "' AND datetime_in >= '". $_POST['start'] . "' AND datetime_in <= '" . $_POST['end'] . "'":

This just works on login, as you specified, If you want to know users who were already logged in when it turned midnight then you need another OR in there as well.

I have just used the values post because you SHOULD validate and transform the input before you submit it. Now if your javascript is not up to doing this in a function on the page, then OK do it in your script before you build your query. Don't waste server time submitting queries that are bound to fail. So the value in your sql should already be in MySQL date format.

Re-reading your post, I see that you want something like the first time a user logged in between those dates. OK, use this query as a subquery so that you can select from the entries returned by it.

klatt

Thanks a lot!

Changing the * to a min(hoursid) did just what a I wanted!!

Frank

klatt

Well a little revision here.

That can get me the first time somebody logs in. I need a limit though to say when to stop looking at logins for my script.

Doing the reverse doesn't quite work the same way :-/

Frank

klatt

Got it!

Just had to change the <= to a < to find the end. Thanks!

RESOLVED BOOYAH!