[SOLVED] Custom Mysql row count function

[bigdspbandj]

I am trying to write a function which takes the argument and uses it for the WHERE clause. This particular query is for counting the number of records in the data base that match the WHERE clause. For some reason the result keeps coming up as 0.

 

// Create an array of all status names (appear exactly as the values in the database appear)

   $status_names = array('NEW', 'Proofed', 'Approved', 'Printed', 'Inserted', 'Mailed', 'Hold', 'Canceled');

// Create function for grabbing the SQL counts for each field "job_status" with the vlue of "$status_name"
// "job_number" is the unique ID field

   function get_stat_total($status_name) {
      $sql = 'SELECT job_number FROM job_tracking WHERE job_status = "$status_name"';
      $result = mysql_query($sql) or die(mysql_error());
      $num_rows = mysql_num_rows($result);
      return $num_rows;
   }

// call get_stat_total function with argument of "NEW" for testing purposes
   
   get_stat_total("NEW");

 

What I need the array for is my foreach loop:

 

// Create loop for navigation dl
   echo '<dl id="statusNav">';
   foreach ($status_names as $status_name) {
      echo '<dl id="statusNav"><dt><strong>+</strong><a href="job_status?job_status='.$status_name.'" title="'.$status_name.'">'.$status_name.' <em>';
      get_stat_total($status_name);
      echo '</em></a></dt>';
   }
   echo '</dl>';

[trq]

Your query is within single quotes, therefore the variable in your where clause is not being interpolated. Also, a better syntax would be.....

 

function get_stat_total($status_name) {
  $sql = "SELECT job_number FROM job_tracking WHERE job_status = '$status_name'";
  if ($result = mysql_query($sql)) {
    return mysql_num_rows($result);
  }
  return false;
}

 

A function should never (IMO) call die, if you want the script to die on error it should be done in the calling code.

[bigdspbandj]

I don't get any results with that query. Is there anything to know when using a query in a function and calling that function later? Should I include the db connect in the function?

[trq]

Should I include the db connect in the function?

 

You don't normally need to. Lets do some debugging first....

 

<?php

function get_stat_total($status_name) {
  $sql = "SELECT job_number FROM job_tracking WHERE job_status = '$status_name'";
  if ($result = mysql_query($sql)) {
    return mysql_num_rows($result);
  } else {
    echo mysql_error();
  }
}

?>

[bigdspbandj]

I got it.

 

The problem wasn't with the query (well that was an issue too, and thanks for the tips on the syntax), but the real issue was with the calling of the function.

 

I was calling it like: job_stat_total($status_name);

 

When I needed to echo the function. Thank you very much for your help!