I found a bug in my website where the user’s most recent login was not being recorded. I discovered this by seeing more recent dates in the searches table compared to what I’m seeing in the users table. The problem I ran into is that the date column in the searches table uses the date and time. The date column in the users table is setup to only show the date. So in the query below I formatted the date in the search column to look like the date column in the users table. Unfortunately, the database still sees the dates in the search column to be greater, simply because there is also a time as part of the date. Can someone help me tweak my query so I can really find the users that have a more recent date in the searches table compared to the users table?
Thanks!
-- Accounts where the date in search table is more recent than the users table.
SELECT
u.date,
u.uID,
u.firstname,
u.visited,
u.last as lastLogin,
date_format(s.date, '%Y-%m-%d') as searchDate
FROM users u
INNER
JOIN searches s
ON u.uID = s.author
WHERE
s.date > u.last
GROUP BY
u.uID
ORDER BY u.last
limit 100;
Thanks Rudy, I took out that line and the query runs fine. But I’m still stumped on how to resolve the fact that the searches table is using full dates, including the time, and the users table is just storing dates as 2011-05-12. Because of this difference, the query is showing a lot of rows where it thinks the date in the searches table is more recent, I’m guessing because the time is included. Should I do an update on the users table and convert all of the last login dates to use the kind of date with time? Or is there a better way to get around this?
Once I can get this worked out, then I plan on using the following query to fix this mess. Does this look good to you?
UPDATE users u, searches s
SET u.last = s.date
WHERE s.date=(select date from searches where author =u.uID order by date desc limit 1) AND
s.date > u.last AND
u.uID = s.author
One date column is used in the users table. This records the last login date. The other datetime column is in the searches table. It records when a search was conducted by the website user.
Yes, they are out of sync. I found a bug in my website code where the last login date was not being updated in the users table. No telling how long that bug has been there. I have since fixed in and now it updates when people login. So the goal is to find their most recent visit date based on the searches they have conducted. Then copy the date of the search over into the users table.
Ok, here is the one I’ve been working on today that will do that very thing:
-- Accounts where the date in search table is more recent than the users table.
SELECT
u.uID,
u.date as registerDate,
u.firstname,
u.last as usersVisit,
date_format(s.date, '%Y-%m-%d') as searchesVisit
FROM users u
INNER
JOIN searches s
ON u.uID = s.author
WHERE
s.date=(select date from searches where author =u.uID order by date desc limit 1) AND
s.date > u.last
ORDER BY u.uID
limit 100;
no, that’s not what i was after, i wanted just the most recent search date for each userid, it’s just a simple one-table query using the searches table
Here is what I came up with. Remember, however, that s.date is a datetime and u.last is just a date. So I’m wondering how we will proceed next to accurately campare the two. Also, why is MAX used in this case when grouping it by author should only return one row per user.
SELECT s.author
, u.firstname
, u.last
, MAX(s.date) AS searchDate
FROM users u
INNER JOIN
searches s
on u.uID = s.author
GROUP
BY author;
Rudy, how come when I run the following query I get an error that says, “Unknown column s.date in field list”? I wanted to not only show the date of their last login (u.last) but also the date of the most recent search (s.date)
SELECT u.uID
, u.last
, s.date
FROM users u
INNER
JOIN (SELECT author, MAX(date) AS latest FROM searches GROUP BY author ) AS s
ON s.author = u.uID;
Ok, I got the query fixed by changing latest to date.
SELECT u.uID
, u.last
, s.date
FROM users u
INNER
JOIN (SELECT author, MAX(date) AS date FROM searches GROUP BY author ) AS s
ON s.author = u.uID
WHERE
s.date > u.last;
I also added a check in the WHERE clause. The problem is that the s.date is a datetime and u.last is just a date. So the query is returning incorrect results thinking that dates are greater when they really aren’t. As you can see from the top of this thread, I tried to convert the datetime to just a date using “date_format(s.date, ‘%Y-%m-%d’) as searchDate” but that didn’t work.
Before we can move on to the update, I gotta figure out what I’m doing wrong with the date compare.
you shoulda changed it the other way around – it’s still the same query, but the intent of what you’re doing is clearer
SELECT u.uID
, u.last
, s.[COLOR="Blue"]latest[/COLOR]
FROM users u
INNER
JOIN ( SELECT author
, [COLOR="blue"]MAX(date) AS latest[/COLOR]
FROM searches
GROUP
BY author ) AS s
ON s.author = u.uID
WHERE s.[COLOR="blue"]latest [/COLOR]> u.last
that’s not a problem, and you don’t need to do any conversion
please show a few rows of results which you think are wrong
