sql statement ....

Anon

i need to show todays and yesterday's hits to my site from different users in a table:

USER_ID TODAYS HITS YESTERDAYS HITS

1023 54 46
1024 122 139
...

currently i am using one select statement to get todays hits for each user and within the while statement i have another sql for yesterdays hits...this is not very nice and i was wondering if there was a better way to do this...

select from hits where date=$today
...
while ($db->next_record()) {
$id=$db->f('user_id');
select from hits where date=$yesterday and user_id=$id
...
}

is there a way to have this in a single sql statement? thanks

Anon

I don't understand your setup. According to your table listing:

USER_ID TODAYS HITS YESTERDAYS HITS

1023 54 46
1024 122 139

you don't have a 'date' field.

But your sql statements use a date field.

rrijnders

What is the table structure?

rrijnders

I think that's the table he wants to <i>output</i>...

Anon

'hits' table structure:

id, datetime, user_id

rrijnders

where do you store the number of hits?

rrijnders

Well, going off the assumption that this is like a log file that tracks every hit with a date and time stamped record, try:

<font color=blue>
SELECT
CAST ( SUM( CASE WHEN (date=$yesterday) THEN 1 ELSE 0 END) AS INT) AS yesterdayhits,
CAST ( SUM( CASE WHEN (date=$today) THEN 1 ELSE 0 END) AS INT) AS todayhits
FROM hits
</font>

I don't know what database you are running, but this works on DB2, PostgreSQL, and MS SQL Server (that I have tested so far).

-- Rich

Anon

thanks alot

my MySql version doesnt support CAST but your code worked fine without it 🙂