Query returning 1 fewer row than expected

[eh270]

I've just started learning PHP and MySQL, and have succeeded 99% in building my first very basic website.  But one query that is supposed to return a member's transaction history returns one less record than it should, and I can't figure out why.

 

The site ( http://8h2o.com ) is very simple (though not yet fully polished from a UI perspective):  it lets you track your progress in drinking 8 glasses of water per day.  I have two tables, a user table and an  "actions" table that tracks the user's drinking activities.

 

the user table:

| users | CREATE TABLE `users` (
  `accessCode` varchar(10) NOT NULL,
  `email` varchar(30) NOT NULL,
  `password` varchar(60) NOT NULL,
  `firstName` varchar(30) NOT NULL,
  `lastName` varchar(30) NOT NULL,
  `createDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `lastAccessDate` timestamp NOT NULL default '0000-00-00 00:00:00',
  `status` varchar(10) NOT NULL,
  PRIMARY KEY  (`accessCode`),
  KEY `email` (`email`(3))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 

the accesscode is a unique alphanumeric string 8 characters long that i generate with a custom function. the actions table:

| actions | CREATE TABLE `actions` (
  `id` int(10) NOT NULL auto_increment,
  `accessCode` varchar(10) NOT NULL,
  `count` varchar(2) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 | 

 

when a user reaches their account page, they see the 8 glasses.  no "drinking" action is tracked until they actually click on a glass, at which point a record is created in the actions table for them that tracks the number of glasses they have drunk ("count").  to keep things simple, there is only one record per user per day, and the count variable is updated as they increase or decrease their consumption.  this all works as i expect it to.

 

now, below the 8 glasses displayed for the current day is a history table that should only show if there is any history to be shown -- meaning, they registered before today, and had some sort of activity.  so when the page loads, i call a function called "showHistory()" that lives in a separate file (included once at the top of the page) that connects to the database and attempts to retrieve any records associated with the user, excluding today.  for the sake of example, if my access code (again, this is a unique string, kind of like a user id) was W1234567, the query should read:

 

SELECT * FROM actions WHERE accessCode = "W1234567" AND date != CURDATE() ORDER BY date DESC;

 

this returns the correct number of records when i run it from a shell. but when it is called by the PHP page, it shows no records if there is exactly one record from before today, one record if there are exactly two from before today, etc. i suspect there is something wrong with the way i am looping through the results... also, i'd appreciate any tips on making this function more elegant and efficient.. but also want very much to get this working, and understand why it is not working.  thanks in advance for your help!

 

here is the function:

function showHistory($accessCode) {
global $db_username, $db_password, $db_host, $db_database;	
// connect to the database
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
// verify that the connection is established
if (DB::isError($connection)){
	die("Could not connect to the database: <br />".DB::errorMessage($connection));
} 
// retrieve any activity
$query = 'SELECT count,date FROM actions WHERE accessCode = "'.$accessCode.'" AND date != CURDATE() ORDER BY date DESC;';
$result = $connection->query($query);
// Check if the query returned an error
if (DB::isError($result)) {
  die ("Could not query the database: <br/>". $query. " ".DB::errorMessage($result));
}
$connection ->disconnect();
// check to see if anything is there
if (!$row = $result->fetchRow()) {
	// Doesn't exist, don't write any of the history table		
} else {
// write the table and fill it with the history
echo ('<h3 style="text-align:center;">My History</h3>');
echo ('<div class="r">');
echo ('<table class="history">');
while ($result_row = $result->fetchRow(DB_FETCHMODE_ASSOC)){
	echo '<tr><td class="';
	// set the css class to 'check' if the user drank 8 glasses that day, which displays a green check mark
	if ($result_row["count"] ==  { echo ('check'); } else { echo('nocheck'); }
	echo '">';
	echo "Drank <b>";
	echo $result_row["count"];
	echo " glasses</b> on </td><td>";
	$d = $result_row["date"];
	echo (date("M j", strtotime($d)));
	echo "</td></tr>";
}
echo ('</table>');
echo ('</div>');
}
}

 

edit:  oh, and here is a sample of the actions table:

 

+----+------------+-------+------------+
| id | accessCode | count | date       |
+----+------------+-------+------------+
|  7 | W00KRB4R   | 1     | 2008-10-15 | 
| 16 | W00KRB4R   | 2     | 2008-10-14 | 
| 17 | W00KRB4R   | 8     | 2008-10-13 | 
| 18 | W00KRB4R   | 3     | 2008-10-12 | 
+----+------------+-------+------------+

 

[PFMaBiSmAd]

The following line fetches and discards the first row from the result set -

 

   if (!$row = $result->fetchRow()) {

[eh270]

Aha... what i'm trying to do is see if anything was returned.  How woudl I do that then?

[AndyB]

mysql_num_rows() - http://ca.php.net/mysql_num_rows