COUNT Query NOT working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

COUNT Query NOT working

Post by THEMADGEEK »

I am trying to do a COUNT on records that match a certain criteria and I keep getting a "0" and I know there are at least 2 that match.

Here it is, I can't see what's wrong I've used this formula before with different specifications.

FYI - $today = date(Y-m-d);

Code: Select all

$sql = "SELECT COUNT(game_id) FROM mytable WHERE field1 = 'n' && date < '$today' && field2 = '$var1' || field3 = '$var1'";
$result = mysql_query($sql); 
$count = mysql_result($result, 0);

echo "$count";
Thanks for helping!
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

try this and see if it works :

Code: Select all

$sql = "SELECT COUNT(game_id) as repetitions FROM mytable WHERE field1 = 'n' and date < '".$today."' and field2 = '".$var1."' or field3 = '".$var1."' group by date having repetitions > 0"; 

$result = mysql_query($sql) or die(mysql_error());  // <-- if there is an error, post it.

while($row = mysql_fetch_array($result))
{
   echo $row['repetitions'];
   echo '<br />';
}
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Post by THEMADGEEK »

For some reason that returns a blank screen...
Illusionist
Forum Regular
Posts: 903
Joined: Mon Jan 12, 2004 9:32 pm

Post by Illusionist »

Code: Select all

$sql = "SELECT game_id FROM mytable WHERE field1 = 'n' && date < '$today' && field2 = '$var1' || field3 = '$var1'";
$result = mysql_query($sql);
$count = mysql_num_rows($result);
echo $count;
I'm almost certain that that will work!
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Post by THEMADGEEK »

Still didn't work. However I think I've narrowed the problem down and it may be in the database itself rather than the code. Everything works except the date...

I currently have the field type set as 'date' and it auto-formats as "2004-02-11" and it seems that when I do a < or > than a date it returns 0. Is there a better way to store dates so I can do the simple math with them?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

yeah, the default format for mysql is YYYY-MM-DD

however, if you have a variable assigned as a date ( ie, $date = '2004-03-02" ) then your query should work fine.

date < '".$today."'

if, however, you have a different format, here is the link you need to look at for conversions :

http://us4.php.net/date

if you want to alter the mysql date and convert it to a different format, you may find this link useful :

http://www.mysql.com/doc/en/Date_and_ti ... tions.html



hope this helps...
THEMADGEEK
Forum Newbie
Posts: 21
Joined: Thu Oct 30, 2003 6:04 pm

Post by THEMADGEEK »

OK That cleared up the whole 'date' part of my query. Thanks, for some reason I was swapping the month and day. Duh...

Moving on, I still get wierd numbers returned when I do counts. I have a page where I want to count rows with different requirements. I have saved each query in it's own file then I include them in the page where they are supposed to go. I don't know if that's the best way but I didn't want to have conflicts so I separated them.

In my table these are the columns and the type I'm concerned with in order:

date (date), field_1 (char), field_2 (char), cancelled (enum y,n)

Here is the query (they all look like this only with different date and cancelled requirements)

Code: Select all

$connection = mysql_connect("$DbHostName", "$DbUserName", "$DbPassWord");
$sql = "select * from games where field_1 = '$var' || field_2 = '$var' && cancelled = 'y'";
$result = mysql_query($sql); 
$count = mysql_num_rows($result); 
echo $count;
This particular query gives me a 2 when there are NO records that match those particular specifications. And what really gets me is if I say cancelled = 'n' it works fine...

What am I missing?
Post Reply