Query problems

JF1980

Hi guys, I am having some trouble with a few SQL queries, I thought somebody may be able to tell me what is wrong...

This first one is supposed to pick out a user id if the user has a globally enabled photo, memo and is an active user, it also makes sure that users who have already been selected (and then inserted in to another table are not picked.

$usersql = "SELECT user.id FROM user, memory, photo, fordisplay 
WHERE user.status=$activeuserflag AND
 (memory.userid=user.id AND memory.status=$globalflag) AND 
(photo.userid=user.id AND photo.status=$globalflag) AND
 (user.id <> fordisplay.user) GROUP BY id LIMIT 1";

The problem is that it still picks users who have already been picked previously and inserted in to the fordisplay table. The (user.id <> fordisplay.user) was supposed to stop this from happening I though? This has been driving me crazy today.

The second problem Im having is a problem of logic. I have poll tables, I hold the datetime I want the poll to start, and the datetime I want the poll to finish. Now, how can I display any gaps in the poll schedules? E.g.say I have 4 polls with the following attributes:

Start: 12:00:00 01-04-2004 End: 12:00:00 10-04-2004
Start: 00:00:00 05-04-2004 End: 12:00:00 11-04-2004
Start: 14:00:00 11-04-2004 End: 12:00:00 12-04-2004
Start: 12:00:00 12-04-2004 End: 12:00:00 13-04-2004

So who would I display that there is a gap in the scheduling between polls 2 and 3? I don't think I can do this with mySQL I would have to load ALL data and work it out with PHP form what I can see? Correct? Any ideas?

Thanks,

steadyguy

First question ...

You only need to know the id once, so there's no real point in selecting it from all the tables, instead, you might want to use JOINs. This also makes your WHERE clause easier to read. Also let's use some aliases to keep things a little more legible:

SELECT u.id
FROM user u
LEFT JOIN memory m
    ON m.id = u.id
LEFT JOIN photo p
    ON p.id = u.id
LEFT JOIN fordisplay f
    ON f.id = u.id
WHERE
    m.status = {status_flag} AND
    p.status = {status_flag} AND
    f.user IS NOT NULL
LIMIT 1

If I understand you properly, that should work ...

Thought I had a solution for the second problem ... but it's not working out right ... will get back to you.

JF1980

Very helpful thank you.

I thought that using a cross table query as I had, was basically joining the tables but with a different syntax?

It does work but when there are no more 'fresh' users, it goes back to picking the first user who fits the requirements, ignoring the <> part. I have added an ORDER BY rand() statement to this, so I guess it's not such a bad thing it reusing the old users once there are no fresh ones, better than running out.

The second is very helpful thanks.

lol, I must have read the post b4 you edited it..

steadyguy

Well ... the query I had posted does work as expected, but it might produce some unexpected results - such as a single result row when there is only one poll in the table. I think there might be a way to get it to work properly all the time ...

JF1980

Can you repost it then pls?

steadyguy

SELECT a.id, a.start, a.end
FROM polls a
LEFT JOIN polls b ON a.start = b.end
WHERE b.id IS NULL

With your sample data, this should return rows 1 and 3. You'll just have to remember that the first row in the results is the very first poll you held (which, when you think about it, does meet your requirement: there is indeed a space between it and any preceding event: albeit an infinite large one 😃 )