Home > Archive > PHP DB > April 2004 > Re: [PHP-DB] logic problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] logic problem
|
|
| Brent Baisley 2004-04-21, 2:38 pm |
| I think your problem is that you are using time, when you are not
interested in time, just the date. I'm assuming your eventime column is
a timestamp field. Your goal is to select the badge id and the date,
distinct will then eliminate multiple visits and then you can group by
date to get the visitor count. The way I see it, the hardest part is
converting the eventime to a date or some unique string to represent
the date.
On Apr 21, 2004, at 1:13 PM, Adam Williams wrote:
> I'm using some proprietary software/hardware where when a visitor
> swipes
> their entry card, it is recorded in a mssql 2000 server. My boss
> wants a
> count of unique vistors for a range of dates. So, I need to have it
> give
> a count of unique vistors, meaning that I need to count all vists for a
> day as one visit (because if they go outside to smoke and come back and
> swipe their card again to get in, each one is a separate visit, but i
> need
> to count all visits by each person as one visit since i just want to
> know
> if they came at all each day, not how many times they came in).
>
> This is my SQL statement:
>
> select distinct count(convert( varchar,eventime, 110)) as count,
> convert( varchar,eventime,110) as date from events, badge wher
> events.cardnum = badge.id and devid = '1' and
> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and
> 'enddate'
> and type = '1' group by convert( varchar,events.eventime, 110)
>
> for reference, devid = '1' is the hardware device, where everytime it
> triggers, it means someone swiped their card to get in, and type = '1'
> means patron (because we have a type = 2 that is for staff and we jsut
> want to know how many patrons visited)
>
> When I execute this statement, its returning the result for each date
> of
> the total number of card swipes (so if a person comes in twice on a
> date,
> its recording it as 2 swipes, but I just need to know that they came to
> the building at all on this date, so I just need it to register that
> there
> was a count of atleast one for this card that was swiped)
>
> any suggestions? thanks
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
| |
| Daniel Clark 2004-04-21, 2:39 pm |
| How about :
SELECT convert( varchar,eventime,110) as date from events, badge,
count(convert( varchar,eventime, 110)) as count
WHERE events.cardnum = badge.id and devid = '1' and
convert( varchar, events.eventime, 110) BETWEEN '$startdate' and 'enddate'
AND type = '1'
GROUP BY convert( varchar,events.eventime, 110), badge
> I'm using some proprietary software/hardware where when a visitor swipes
> their entry card, it is recorded in a mssql 2000 server. My boss wants a
> count of unique vistors for a range of dates. So, I need to have it give
> a count of unique vistors, meaning that I need to count all vists for a
> day as one visit (because if they go outside to smoke and come back and
> swipe their card again to get in, each one is a separate visit, but i need
> to count all visits by each person as one visit since i just want to know
> if they came at all each day, not how many times they came in).
>
> This is my SQL statement:
>
> select distinct count(convert( varchar,eventime, 110)) as count,
> convert( varchar,eventime,110) as date from events, badge wher
> events.cardnum = badge.id and devid = '1' and
> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and 'enddate'
> and type = '1' group by convert( varchar,events.eventime, 110)
>
> for reference, devid = '1' is the hardware device, where everytime it
> triggers, it means someone swiped their card to get in, and type = '1'
> means patron (because we have a type = 2 that is for staff and we jsut
> want to know how many patrons visited)
>
> When I execute this statement, its returning the result for each date of
> the total number of card swipes (so if a person comes in twice on a date,
> its recording it as 2 swipes, but I just need to know that they came to
> the building at all on this date, so I just need it to register that there
> was a count of atleast one for this card that was swiped)
>
> any suggestions? thanks
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
| |
| Daniel Clark 2004-04-21, 2:39 pm |
| AND: any count >=1 shows they came in that day.
How about :
SELECT convert( varchar,eventime,110) as date from events, badge,
count(convert( varchar,eventime, 110)) as count
WHERE events.cardnum = badge.id and devid = '1' and
convert( varchar, events.eventime, 110) BETWEEN '$startdate' and 'enddate'
AND type = '1'
GROUP BY convert( varchar,events.eventime, 110), badge
> I'm using some proprietary software/hardware where when a visitor swipes
> their entry card, it is recorded in a mssql 2000 server. My boss wants a
> count of unique vistors for a range of dates. So, I need to have it give
> a count of unique vistors, meaning that I need to count all vists for a
> day as one visit (because if they go outside to smoke and come back and
> swipe their card again to get in, each one is a separate visit, but i need
> to count all visits by each person as one visit since i just want to know
> if they came at all each day, not how many times they came in).
>
> This is my SQL statement:
>
> select distinct count(convert( varchar,eventime, 110)) as count,
> convert( varchar,eventime,110) as date from events, badge wher
> events.cardnum = badge.id and devid = '1' and
> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and 'enddate'
> and type = '1' group by convert( varchar,events.eventime, 110)
>
> for reference, devid = '1' is the hardware device, where everytime it
> triggers, it means someone swiped their card to get in, and type = '1'
> means patron (because we have a type = 2 that is for staff and we jsut
> want to know how many patrons visited)
>
> When I execute this statement, its returning the result for each date of
> the total number of card swipes (so if a person comes in twice on a date,
> its recording it as 2 swipes, but I just need to know that they came to
> the building at all on this date, so I just need it to register that there
> was a count of atleast one for this card that was swiped)
>
> any suggestions? thanks
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
| |
| Adam Williams 2004-04-21, 2:40 pm |
| Yes eventtime is a timestamp field (whatever mssql's version of mysql's
NOW() is) and the convert( varchar,eventime,110) converts the timestamp to
a date MM-DD-YYYY.
my problem is that with the count, it counts each card swipe, and i only
need to count one card swipe from each patron per day. so if they swipe
their card multiple times, in my php script when i return the results, it
only returns one visit for each patron each day even if they have visited
two or more times.
On Wed, 21 Apr 2004, Brent Baisley wrote:
> I think your problem is that you are using time, when you are not
> interested in time, just the date. I'm assuming your eventime column is
> a timestamp field. Your goal is to select the badge id and the date,
> distinct will then eliminate multiple visits and then you can group by
> date to get the visitor count. The way I see it, the hardest part is
> converting the eventime to a date or some unique string to represent
> the date.
>
>
> On Apr 21, 2004, at 1:13 PM, Adam Williams wrote:
>
>
| |
| Adam Williams 2004-04-21, 3:32 pm |
| Yeah I basically had that with my previous SQL statement, I was grouping
by event.cardnum instead of counting the cardnums by date. I think what
I'm trying to do is beyond the scope of SQL and I'll have to write some
PHP to take the SQL statement results and feed them into an array and
count the distinct cardnums for each date and then spit it all into an
html table. thanks
On Wed, 21 Apr 2004, Daniel
Clark wrote:
> AND: any count >=1 shows they came in that day.
>
> How about :
>
> SELECT convert( varchar,eventime,110) as date from events, badge,
> count(convert( varchar,eventime, 110)) as count
> WHERE events.cardnum = badge.id and devid = '1' and
> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and 'enddate'
> AND type = '1'
> GROUP BY convert( varchar,events.eventime, 110), badge
>
>
>
>
| |
| Brent Baisley 2004-04-21, 4:37 pm |
| This seems too easy to not be able to do it with SQL. There must be
something we're missing in the query.
Try this:
select distinct badge.id,convert( varchar,eventime,110) as date,count(*)
from events, badge where
events.cardnum = badge.id and devid = '1' and
convert( varchar, events.eventime, 110) BETWEEN '$startdate' and
'enddate'
and type = '1'
group by date
In MySQL you don't need to specify a field for count(). And you should
have to convert the eventime field twice, you should be able to
reference the calc field in the group by.
On Apr 21, 2004, at 2:19 PM, Adam Williams wrote:
> Yeah I basically had that with my previous SQL statement, I was
> grouping
> by event.cardnum instead of counting the cardnums by date. I think
> what
> I'm trying to do is beyond the scope of SQL and I'll have to write some
> PHP to take the SQL statement results and feed them into an array and
> count the distinct cardnums for each date and then spit it all into an
> html table. thanks
>
> On Wed, 21 Apr 2004, Daniel
> Clark wrote:
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
| |
| Adam Williams 2004-04-21, 4:37 pm |
| Re-read my original email, I'm using mssql and not mysql. I'm sort
of close to having it working with the sql statement i posted earlier and
using PHP to figure out if the date has changed or not and to count the
cardnum if the date hasn't changed. I'm getting a number that is
different then the total number multiple visits by patrons, haven't
checked with the registration person if the # I am getting is right or
not, because for some of the very early dates when we were testing the
equipment, it returns a count of 0 visits by type 1 card users, even
though they have a count of 15 multiple entries that day, so something
weird is still going on. i think i'm going to have to end up using a
multidimensional array and having PHP loop through it or something...
On Wed, 21 Apr 2004, Brent Baisley wrote:
> This seems too easy to not be able to do it with SQL. There must be
> something we're missing in the query.
> Try this:
>
> select distinct badge.id,convert( varchar,eventime,110) as date,count(*)
> from events, badge where
> events.cardnum = badge.id and devid = '1' and
> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and
> 'enddate'
> and type = '1'
> group by date
>
> In MySQL you don't need to specify a field for count(). And you should
> have to convert the eventime field twice, you should be able to
> reference the calc field in the group by.
>
> On Apr 21, 2004, at 2:19 PM, Adam Williams wrote:
>
>
| |
| Daniel Clark 2004-04-21, 4:37 pm |
| How about
SELECT distinct badge.id, convert( varchar,eventime,110) as date
Shows all the badge numbers IN on that date. If the badge number is not
there, they didn't check in at ALL that day.
[color=darkred]
> This seems too easy to not be able to do it with SQL. There must be
> something we're missing in the query.
> Try this:
>
> select distinct badge.id,convert( varchar,eventime,110) as date,count(*)
> from events, badge where
> events.cardnum = badge.id and devid = '1' and
> convert( varchar, events.eventime, 110) BETWEEN '$startdate' and
> 'enddate'
> and type = '1'
> group by date
>
> In MySQL you don't need to specify a field for count(). And you should
> have to convert the eventime field twice, you should be able to
> reference the calc field in the group by.
>
> On Apr 21, 2004, at 2:19 PM, Adam Williams wrote:
>
| |
| Adam Williams 2004-04-21, 4:37 pm |
| Still doesn't work the way I want it. My boss is taking a look at it, she
knows SQL really well, I was trying to do this without her assistance, but
its just harder then I was expecting. thanks for the help tho
On Wed, 21 Apr 2004, Daniel Clark wrote:
> How about
>
> SELECT distinct badge.id, convert( varchar,eventime,110) as date
>
> Shows all the badge numbers IN on that date. If the badge number is not
> there, they didn't check in at ALL that day.
>
>
>
>
|
|
|
|
|