Re: [PHP-DB] Re: COUNT() returns 0 if there were no matching rows. .... really?!
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
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
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
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
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
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
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
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(*)
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