Page 1 of 1
COUNT Query NOT working
Posted: Tue Feb 10, 2004 3:07 pm
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!
Posted: Tue Feb 10, 2004 4:22 pm
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 />';
}
Posted: Tue Feb 10, 2004 4:59 pm
by THEMADGEEK
For some reason that returns a blank screen...
Posted: Wed Feb 11, 2004 8:50 am
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!
Posted: Wed Feb 11, 2004 10:50 am
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?
Posted: Wed Feb 11, 2004 2:49 pm
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...
Posted: Thu Feb 12, 2004 10:35 am
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?