PHP function returns ZERO

Question

I'm having trouble with this PHP function. It keeps returning zero, but I know the SQL statement works because I've queried it myself. Any ideas what I'm doing wrong? The last line makes no sense to me...I'm editing this code that someone else wrote. This function should return a number in the hundreds, assuming the date is in March. Thanks!

 function getCountBetweenDays($day1,$day2,$service)
{
 global $conn;
 if ($service==1){
$query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";}
 elseif($service==2){
$query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";}
 elseif($service==3){
$query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";}
$result = mysql_query($query,$conn);
 $num = mysql_fetch_array ($result);
 return $num['NUM'];
}

• Felix Kling: Am I missing something or is there really no difference in your queries?
• VolkerK: In all three cases $query contains the same string ...is that intentional?
• Mark Byers: What are the values of $day1, $day2 and $service? Can you echo them?
• Vidar Vestnes: what are the values of $day1 and $day2 , they should be like ex '2010年03月31日' ... if not your query-result will be empty... also, the trailing ; in your query is not needed...
• Felix Kling: @hypnocode: Actually with ZERO, do you mean 0 (number zero) or NULL ? Or maybe even an empty string?

Answer 1

Try some debug output in your function, e.g.

function getCountBetweenDays($day1,$day2,$service)
{
 global $conn;
 switch($service) {
 case 1:
 $query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59'";
 break;
 case 2:
 $query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59'";
 break;
 case 3:
 $query = "SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59'";
 break;
 default:
 die('unknown value for $service');
 }
 echo '<pre>Debug: $query=', htmlspecialchars($query), '</pre>';
 $result = mysql_query($query,$conn) or die('mysql_query failed: '.htmlspecialchars(mysql_error($conn)));
 echo '<pre>Debug: numrows=', mysql_num_rows($result), '</pre>';
 $num = mysql_fetch_array($result);
 return $num['NUM'];
}

• ant: this covers too much of his code but still +1 he will get the error eventually at the end, he'd be faster of changing "suspicious" bits of code
• VolkerK: I hope it "resembles" the original function just enough to be "comprehensible" ;-) Keep in mind that you usually neither want to bail out so quickly (using die()) nor reveal the actual mysql error string to each and every user in production code ...it's just an example.
• user305527: And thanks for adding the switch...I never knew you could do that in PHP.
• user305527: Thanks! This is getting me closer...for some reason it's just passing in a year for the date. I'll let you know if I find a solution.
• user305527: Thank you very much, this helped me find out what was going wrong. I posted my solution. I would vote this up, but I don't have 15 rep points yet.

Answer 2

For starters, add

if (!$result) echo mysql_error(); 

after each mysql_query() call to see whether there are any errors. I'm pretty sure there are.

• ant: @Pekka sorry still upset at someone down voting me in like 20 sec .. didn't read it properly
• user305527: Should I put this after EVERY mysql_query() to see the error?
• user305527: Nothing, I put them after each one.
• VolkerK: If you don't like that use PDO ( docs.php.net/pdo ) and set $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. Then an exception is thrown each time something goes wrong. Not everybody likes exceptions, but imo at least it's a bit harder to "ignore" them or to forget the error handling.
• user305527: Thanks, I'll add it to my list of things to try.

Answer 3

Your queries are not formatter properly they are :

"SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59';";

They should be :

"SELECT COUNT(*) as NUM FROM `items` WHERE `modified` BETWEEN '$day1 00:00:00' AND '$day2 23:59:59'"; 

You have an extra semi-colon in each query. and use or die mysql_error() to print out the error.

Also this part :

$num = mysql_fetch_array ($result);
 return $num['NUM'];

I'd replaced with :

$num = mysql_fetch_array ($result);
extract($num);
 return $NUM;//if this is your field name

• Tyler Carter: That is proper SQL. It delimited queries. While it is not necessary, it is not the cause of the problem.
• Felix Kling: This shouldn't be a problem. ; is the query deliminator.
• ant: why the minus vote I'm offering a man few solutions, give guy something instead of just minusing voted dammit
• Felix Kling: @c0mrade: I agree that some people are very fast in downvoting other answers...sometimes this is really ridiculous.
• ant: yea .. so far I've given up offering some more solutions .. I simply don't care anymore to write anythign .. peace

Answer 4

Fixed it...dumb mistake. I'm so new to PHP and MySQL, sorry.

getCountBetweenDays(2010年3月1日,2010年3月28日,CONT_ALL_SERVICE);

should have been:

getTweetCountBetweenDays('2010-3-1','2010-3-28',CONT_ALL_SERVICE);

Thanks all for the help and I now know how to debug properly!

Answer 5

You're trying to reference a numeric index [n] with an associative index ['s']. Either specify this to your query

mysql_fetch_array($result, MYSQL_BOTH)

Or just do

mysql_fetch_assoc($result);

Which will let you reference indices by association

Answer 6

return $num['NUM'];

means return the 'num' part of the array; Change 'NUM' to 1 or 2 and then try

• Tyler Carter: mysql_fetch_array() returns an array with both column numbers AND column names. mysql_fetch_assoc() returns only an array with column names.
• Pekka: @NebNeb Nope, mysql_fetch_array fetches both the numeric and the associative part by default.
• Felix Kling: By default, mysql_fetch_array returns the result as numerical and associative array. So the array access should work imo.