[PHP-DB] COUNT() returns 1, but should be 0
Hi all, I'm baffled by this. I've put together a rather simple newsletter + greeting card app for a company to send their clients personalized corporate e-mails each containing the URL link to the greeting card page. When the user successfully visits the view card page, my code writes the visit date to the cardlist db (MySQL). One of the admin pages (check_visits.php) lets the admin view a table listing of all the user names, email addresses, card sent date and card seen date. Above this table I display the total number of users and the total number of visits. Presently, the table has only 160 records. No cards have been sent yet and thus no visits, but I get a count of 1 for the number of visits ($visitcount) using my following code. It should be 0. The number of users ($usercount) is returned correctly at 160. Any ideas/advice would be much appreciated. Thanks, Lew --- MySQL: 3.23.54 PHP: 4.3.1 Linux --- Table structure for cardlist: `custid` int(10) unsigned NOT NULL auto_increment, `fullname` varchar(100) NOT NULL default '', `email` varchar(100) NOT NULL default '', `cardid` varchar(12) NOT NULL default '0', `sentdate` datetime default NULL, `visitdate` datetime default NULL, PRIMARY KEY (`custid`), UNIQUE KEY `email` (`email`) --- snipped code in check_visits.php $sql = SELECT COUNT(visitdate) FROM cardlist GROUP BY visitdate; $query = mysql_query($sql) or die(brbrdiv align='center' Cannot query the database.brbr . MySQL Error No. . mysql_errno() . : brbr . mysql_error() . brbr/div); $visitcount = mysql_num_rows($query); $sql = SELECT * FROM cardlist; $query = mysql_query($sql) or die(brbrdiv align='center' Cannot query the database.brbr . MySQL Error No. . mysql_errno() . : brbr . mysql_error() . brbr/div); $usercount = mysql_num_rows($query); echo pTotal number of users: $usercount/p; echo pTotal number of visits: $visitcount/pbr; // --- Should be zero. /snipped -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] COUNT() returns 1, but should be 0
On Mon, Dec 22, 2003 at 11:43:35AM +0900, Lew Mark-Andrews wrote: Table structure for cardlist: `visitdate` datetime default NULL, $sql = SELECT COUNT(visitdate) FROM cardlist GROUP BY visitdate; $visitcount = mysql_num_rows($query); echo pTotal number of visits: $visitcount/pbr; // --- Should be zero. I think that there is one visitdate (NULL), so one row is returned. HTH, Tobey Wheelock -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] COUNT() returns 1, but should be 0
Lew Mark-Andrews wrote: I get a count of 1 for the number of visits ($visitcount) using my following code. It should be 0. --- snipped code in check_visits.php $sql = SELECT COUNT(visitdate) FROM cardlist GROUP BY visitdate; $query = mysql_query($sql) or die(brbrdiv align='center' Cannot query the database.brbr . MySQL Error No. . mysql_errno() . : brbr . mysql_error() . brbr/div); $visitcount = mysql_num_rows($query); [snip] echo pTotal number of users: $usercount/p; echo pTotal number of visits: $visitcount/pbr; // --- Should be zero. /snipped That's because you're displaying how many rows were returned from the query, not the value that COUNT() returns. A query with COUNT() is always going to return at least one row. $visitcount = mysql_result($query,0); -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] COUNT() returns 1, but should be 0
John, That's because you're displaying how many rows were returned from the query, not the value that COUNT() returns. A query with COUNT() is always going to return at least one row. $visitcount = mysql_result($query,0); Yes, that was it! Thank you so much. Lew -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
