Re: [PHP-DB] Re: COUNT() returns 0 if there were no matching rows. .... really?!

2009-08-06 Thread Govinda
Here's an example (snip) from a var_dump of that  
$BuildPerUniqueDateArray:
(note that the 'aweber_7solar_aw' table does NOT have a record for  
the
date '2009-07-28', so I would expect to see that 1 to be a 0  
there.)


If a table doesn't have a record for a given date, I wouldn't expect  
to see 1

or 0 -- I would expect not to see any row at all for that date/table
combination. You're not looping through all possible dates, you're  
looping

through the result-set of your query.

Are you sure that the table in question doesn't have any 2009-07-28  
records?


You could add the following column to each SELECT to help  
troubleshoot:


  GROUP_CONCAT(date_column) AS `all_timestamps_for_date`

This will give your result-set an additional column, which will  
contain a
comma-separated list of all the records that GROUP BY is gathering  
together in

each row (and therefore all the records that COUNT() is counting).

I'm wondering if some sort of timezone discrepancy is maybe causing  
a timestamp

record to be attributed to 2009-07-28 unexpectedly.

Ben



Ben

I tried to implement your troubleshooting column like so:

$query = SELECT GROUP_CONCAT(date(solarLandingDateTime)) AS  
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,  
date(solarLandingDateTime) AS `uniqueDate`, 't7solar_landing' AS  
`tableAlias` FROM t7solar_landing GROUP BY date(solarLandingDateTime)  
UNION ALL SELECT GROUP_CONCAT(date(solarAweberConfDateTime)) AS  
`all_timestamps_for_date` COUNT(*) AS `CountRowsThisDateThisTBL`,  
date(solarAweberConfDateTime) AS `uniqueDate`, 'aweber_7solar_confirm'  
AS `tableAlias` FROM aweber_7solar_confirm GROUP BY  
date(solarAweberConfDateTime) UNION ALL SELECT  
GROUP_CONCAT(date(solarAWDateTime)) AS `all_timestamps_for_date`  
COUNT(*) AS `CountRowsThisDateThisTBL`, date(solarAWDateTime) AS  
`uniqueDate`, 'aweber_7solar_aw' AS `tableAlias` FROM aweber_7solar_aw  
GROUP BY date(solarAWDateTime) ORDER BY uniqueDate DESC LIMIT 300;


it returns this error:
query failed: You have an error in your SQL syntax; check the manual  
that corresponds to your MySQL server version for the right syntax to  
use near 'COUNT(*) AS `CountRowsThisDateThisTBL`,  
date(solarLandingDateTime) AS `uniqueDat' at line 1



..So not to sit here helpless, I troubleshoot according to my current  
level of skill this way:

I added this lower down on my page:

$tableDump = SELECT solarAWDateTime FROM aweber_7solar_aw ORDER BY  
solarAWDateTime DESC;
$tableDumpResult = mysql_query($tableDump) or die(query failed:  
 .mysql_error());

echo hr /pre\n;
while ($row = mysql_fetch_assoc($tableDumpResult)) {
print_r($row);
}
echo /pre\n;
echo hr /\n;

it returns this:
Array
(
[solarAWDateTime] = 2009-08-06 13:33:57
)
Array
(
[solarAWDateTime] = 2009-08-06 09:41:54
)
Array
(
[solarAWDateTime] = 2009-08-06 06:06:55
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:27
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:25
)
Array
(
[solarAWDateTime] = 2009-08-05 16:19:02
)
Array
(
[solarAWDateTime] = 2009-08-05 06:55:58
)
Array
(
[solarAWDateTime] = 2009-08-04 06:46:42
)
...
Array
(
[solarAWDateTime] = 2009-07-30 06:48:56
)
Array
(
[solarAWDateTime] = 2009-07-29 16:11:20
)


I did not clip my paste here ^^^  the last entry is indeed '2009-07-29  
16:11:20'.


..so you can see there is no record in this table with a timestamp on  
the date '2009-07-28'.. so HOW in the world does my array get that  
element, for that date, as if the while loop was iterating on a record  
in this table with that date, when none exists??  (You still have my  
OP on this?.. to see the code and var_dump for that array I build from  
the iterating results of the original query?)



John Butler (Govinda)
govinda.webdnat...@gmail.com




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[PHP-DB] Re: Count unique visits in PHP/MySQL

2004-06-10 Thread Rui Cunha
take a look at this: 

http://otn.oracle.com/oramag/oracle/04-mar/o24asktom.html 

and search for the Analytics to the Rescue example. Instead of 3 seconds 
you want 1800 and instead of sum you want count.Don't forget to group by 
ip,of course... And you're done. No need for an extra table. 

Hope it helps you out. 

Rui Cunha 

Kim Steinhaug writes: 

Whatabout creating a table containing online users,
where you log every activity with IP, BrowserSession and Timestamp.
You also create a table to track the accual unique visits. 

So my logic to solve it : 

Update the online table like this (Some rough coding below, not tested at
all, read the logic).
30 minutes = 60sec*30 = 1800 

1delete from online where timestamp . (time() - 1800); // Delete
inactive users / uniqe ghosts or whatever
2Update online set timestamp = ' . time() . ' where ip=' . $ip . '
and browsersession = ' . $browsersession . ';
3If (!mysql_affected_rows()){
// Update the Unique visitor table
// Insert new entry with IP, Browsersession and time() into the
online database
} 

As far as my midnight brain would see it this would work nicely. 

--
--
Kim Steinhaug
--
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
--
www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
-- 

[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
ast.net...
I am making a PHP/MySQL traffic report page from a table that records some
user activity using PHP referrer information.
I have a table with three rows: IP, page_name, and timestamp. The IP row
records the user's IP address, page_name records the name of the page that
the user loaded, and the timestamp row records in Unix timestamp format the
time of day that the user requested the page.
I want to be able to count unique visits per IP according to Internet
Advertising Bureau standards, which count a Unique Visit as a log in by
the same IP once every thirty minutes.
IAB verbatim definition: Visit - One or more text and/or graphics
downloads from a site qualifying as at least one page, without 30
consecutive minutes of inactivity, which can be reasonably attributed to a
single browser for a single session. A browser must pull text or graphics
content to be considered a visit.
So I need to make a MySQL query that will count how many times an IP
logged a timestamp within a given time period.
For example, the publisher checking traffic could request a date between
May 1 and May 31, and I'd like to be able to return a page that counted
unique users (count distinct IP), pages viewed (list distinct pages) and how
many times they visited in that period. I have the first two down, but not
the unique visits. Any ideas? 

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[PHP-DB] Re: Count unique visits in PHP/MySQL

2004-06-09 Thread Kim Steinhaug
Whatabout creating a table containing online users,
where you log every activity with IP, BrowserSession and Timestamp.
You also create a table to track the accual unique visits.

So my logic to solve it :

Update the online table like this (Some rough coding below, not tested at
all, read the logic).
30 minutes = 60sec*30 = 1800

1delete from online where timestamp . (time() - 1800); // Delete
inactive users / uniqe ghosts or whatever
2Update online set timestamp = ' . time() . ' where ip=' . $ip . '
and browsersession = ' . $browsersession . ';
3If (!mysql_affected_rows()){
// Update the Unique visitor table
// Insert new entry with IP, Browsersession and time() into the
online database
}

As far as my midnight brain would see it this would work nicely.

--
--
Kim Steinhaug
--
There are 10 types of people when it comes to binary numbers:
those who understand them, and those who don't.
--
www.steinhaug.com - www.easywebshop.no - www.webkitpro.com
--

[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
ast.net...
 I am making a PHP/MySQL traffic report page from a table that records some
user activity using PHP referrer information.

 I have a table with three rows: IP, page_name, and timestamp. The IP row
records the user's IP address, page_name records the name of the page that
the user loaded, and the timestamp row records in Unix timestamp format the
time of day that the user requested the page.

 I want to be able to count unique visits per IP according to Internet
Advertising Bureau standards, which count a Unique Visit as a log in by
the same IP once every thirty minutes.

 IAB verbatim definition: Visit - One or more text and/or graphics
downloads from a site qualifying as at least one page, without 30
consecutive minutes of inactivity, which can be reasonably attributed to a
single browser for a single session. A browser must pull text or graphics
content to be considered a visit.

 So I need to make a MySQL query that will count how many times an IP
logged a timestamp within a given time period.

 For example, the publisher checking traffic could request a date between
May 1 and May 31, and I'd like to be able to return a page that counted
unique users (count distinct IP), pages viewed (list distinct pages) and how
many times they visited in that period. I have the first two down, but not
the unique visits. Any ideas?

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



[PHP-DB] Re: count

2002-04-11 Thread Ron Allen

I know how to get the results for the total number of records
select count(*) from $table
but how do I put them into a variable for me to use later???

David Robley [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
 In article [EMAIL PROTECTED],
 [EMAIL PROTECTED] says...
  I would like to count all the total records from a database.  How would
I go
  about doing that???

 Do a COUNT * on each table and total the results? Although the results
 may actually not mean much depending on your DB structure. Or do you
 really mean count records in a _table_?

 --
 David Robley
 Temporary Kiwi!

 Quod subigo farinam



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[PHP-DB] Re: count

2002-04-11 Thread David Robley

In article [EMAIL PROTECTED], 
[EMAIL PROTECTED] says...
 I know how to get the results for the total number of records
 select count(*) from $table
 but how do I put them into a variable for me to use later???
 
 David Robley [EMAIL PROTECTED] wrote in message
 [EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
  In article [EMAIL PROTECTED],
  [EMAIL PROTECTED] says...
   I would like to count all the total records from a database.  How would
 I go
   about doing that???
 
  Do a COUNT * on each table and total the results? Although the results
  may actually not mean much depending on your DB structure. Or do you
  really mean count records in a _table_?


Aliases. SELECT COUNT(*) AS howmany FROM table

Then use the variable $howmany

-- 
David Robley
Temporary Kiwi!

Quod subigo farinam

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[PHP-DB] Re: count

2002-04-11 Thread Ron Allen

What is the Aliases used for???

 Aliases. SELECT COUNT(*) AS howmany FROM table

 Then use the variable $howmany

 --
 David Robley
 Temporary Kiwi!

 Quod subigo farinam



-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[PHP-DB] Re: count

2002-04-11 Thread David Robley

In article [EMAIL PROTECTED], 
[EMAIL PROTECTED] says...
 What is the Aliases used for???
 
  Aliases. SELECT COUNT(*) AS howmany FROM table
 
  Then use the variable $howmany

At this stage I refer you to The Fine (mysql) Manual - or anything on the 
SQL language.

-- 
David Robley
Temporary Kiwi!

Quod subigo farinam

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[PHP-DB] Re: count from the results

2002-02-09 Thread Raymond Lilleodegard

Hi Barry!

you can do it like this for example:

 $query = SELECT * FROM artist WHERE artist_name LIKE 'b%' ORDER BY artist
ASC;

 $count = mysql_query(SELECT COUNT(artist) AS count FROM artist WHERE
artist_name LIKE 'b%',$db);
 $x = mysql_fetch_array($count);
 $result = mysql_query($query) or die(Select Failed!);
 $number = $x[count];
 echo h3Total Number Of Artists In \B\:nbsp;nbsp;;
 echo $number;
 echo brbr/h3;

 if (mysql_num_rows($result)) {
 echo table;
 echo trthArtists/th;
 while ($qry = mysql_fetch_array($result)){
 echo tr;
 echo td;
 echo $qry[artist];
 }}?


Barry Rumsey [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...
 I have the following code:
 $query = SELECT * FROM artist WHERE artist LIKE 'b%' ORDER BY artist
 ASC;
 $result = mysql_query($query) or die(Select Failed!);
 echo h3Total Number Of Artists In \B\:nbsp;nbsp;;
 echo mysql_num_rows($result);
 echo brbr/h3;
 if (mysql_num_rows($result)) {
 echo table;
 echo trthArtists/th;
 while ($qry = mysql_fetch_array($result)){
 echo tr;
 echo td;
 echo $qry[artist];
 }}?

 What I would like to know is how do I do a count on each result
 returned.e.g. Benny(4) , Bill(10)





-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php




[PHP-DB] Re: Count(*)

2002-01-24 Thread Barry Rumsey








  
  Thank you everyone. The "COUNT(*) AS c" worked great.
  
  ---Original Message---
  
  
  From: Kai Voigt
  Date: Friday, 25 
  January 2002 11:11:06 a.
  To: Barry Rumsey
  Cc: [EMAIL PROTECTED]
  Subject: Re: 
  Count(*)
  Barry Rumsey wrote: I have this small query on a 
  page:emit source="sql" host=xoops query= SELECT COUNT(*) FROM 
  xp_topics WHERE artist='artist' ORDER BY topicid DESC LIMIT 
  1"sql.artist;/emitUse "SELECT COUNT(*) AS C FROM 
  ..." instead. Then you can access thecount value as 
  sql.c;Kai-- dreiecksplatz 8, d-24105 kiel, 
  +49-431-22199869, http://k.123.org/.





	
	
	
	
	
	
	




 IncrediMail - Email has finally evolved - 
Click 
Here