PHP function returns ZERO.

Question

Hi guys, 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'];

}

Answer 1

return $num['NUM'];

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

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.

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

Answer 4

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'];
}

Answer 5

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 6

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