The three query solution looks like a good one; but here are some
notes...
SELECT @starttime = max(time)
There's a colon necessary in the syntax here -- SELECT @starttime :=
max(time)
SELECT *
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time in
You're absolutely right about the := not just = (Sorry!!)
Actually, I DID want to use an IN and not a BETWEEN. What I was doing
was picking the two boundary times around a given point in time so once I
got those numbers, I wanted to get both records. I could have written the
WHERE like this:
Actually, I DID want to use an IN and not a BETWEEN. What I was
doing was picking the two boundary times around a given point in time
so once I got those numbers, I wanted to get both records.
Ah, yes, I see I didn't really understand it the first time --
and using IDs instead of timestamps is
Ok, this may or may not be a tricky one I will try and be succinct in my
statement.
I have a database (mysql 4.0) with radius log entries for each day, we
receive emails about Acceptable Use Abuses and must figure out exactly
who was online with a certain IP address when the abuse occurred. As
[EMAIL PROTECTED] wrote:
Problem: Spam Abuse
IP of offender: 66.50.xxX.245
Date of offense: 2004-07-05
Time of offense: 16:15
Now if I query the database based on date and ip address, I get the
following:
Id Date Time Record TypeFull
Name IP
Jan Kirchhoff [EMAIL PROTECTED] wrote on 21/07/2004 13:08:38:
what a about the very simple approach?
This should be very fast if you habe indexes on ip, date, time and
record_type.
I would have though it would be best to have a composite index on
date/time/record_type, which is what both
I would try these 3 query statement to find the users for the bracketing
times (start and stop):
SELECT @starttime = max(time)
FROM logtable
WHERE IP='66.50.xxX.245'
AND Date='2004-07-05'
AND Time ='16:15:00'
AND RecordType = 'Start';
SELECT @endtime = min(time)
FROM
