Hiding row if field is null

[kahodges]

I have a php script that will produce an Excel spread sheet from data stored in a mysql database. It works perfect. The problem I have is getting the script to not print or display a row if a certain field is empty. If a date is entered into the "Quitdate" field for an employee, then that employee is terminated, and we don't want to display his phone information on this spreadsheet. The code below is still producing the terminated employee's info. Here is the code I have so far:

<?php
include 'config.php';
require_once 'Spreadsheet/Excel/Writer.php';

$query  = "SELECT GevityNo, employee_name, Sex, nextel, ssNumber, BirthDate, Hire_Date, QuitDate, PayRate, street_address, City, state, zip_code, home_phone, cellphonenum  FROM employees ORDER BY `employee_name` ";
$result = mysql_query($query) or die('Error, query failed');



$num_rows = mysql_num_rows($result);

if($num_rows > 0){

$row_count = 1;

while($row = mysql_fetch_array($result)){

if($row_count == 1){

  //
  if ($QuitDate===false)
  {
    // Active employees
    $sql .=" where ( `QuitDate` is null or trim(`QuitDate`) = '' ) ";
  }
  else
  {
    // Terminated employees
    $sql .=" where ( `QuitDate` is not null and trim(`QuitDate`) <> '' )";
  } 
  // if ($terminated===false
  //

// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
$format_bold =& $workbook->addFormat();
$format_bold->setBold();

// sending HTTP headers
$workbook_name = $row['employees'] . 'phone_list.xls';
$workbook->send($workbook_name);

// Creating a worksheet
$worksheet =& $workbook->addWorksheet('phone_list');

// The actual data
$worksheet->setColumn(0,0,30);
$worksheet->setColumn(0,1,14);
$worksheet->setColumn(0,2,14);
$worksheet->setColumn(0,3,14);
$worksheet->write(0, 0, 'Name', $format_bold);
$worksheet->write(0, 1, 'Nextel', $format_bold, $format_left);
$worksheet->write(0, 2, 'Home', $format_bold, $format_left);
$worksheet->write(0, 3, 'Cell', $format_bold, $format_left);
}

$worksheet->write($row_count, 0, $row['employee_name']);
$worksheet->write($row_count, 1, $row['nextel']);
$worksheet->write($row_count, 2, $row['home_phone']);
$worksheet->write($row_count, 3, $row['cellphonenum']);
$row_count++;
}
}
// Let's send the file
$workbook->close(); //
?>

[premiso]

I see where you have the $sql inside the while loop, but the SQL statement has already run and you are not re-using that $sql anywhere...

 

That will need to be applied to the initial query for it to work like you want to or you need to add logic inside the while loop that states if quitdate is_null or trim is equaled to empty string then continue; else add it to the spreadsheet....

[kahodges]

I see where you have the $sql inside the while loop, but the SQL statement has already run and you are not re-using that $sql anywhere...

 

That will need to be applied to the initial query for it to work like you want to or you need to add logic inside the while loop that states if quitdate is_null or trim is equaled to empty string then continue; else add it to the spreadsheet....

 

Can you help me with how to apply it to the initial query? Or adding logic inside the while loop, so this will work?

Thanks for your help.

[premiso]

<?php
include 'config.php';
require_once 'Spreadsheet/Excel/Writer.php';

if ($QuitDate===false) {
    // Active employees
    $sql =" ( `QuitDate` is null or trim(`QuitDate`) = '' ) ";
}else {
// Terminated employees
    $sql =" ( `QuitDate` is not null and trim(`QuitDate`) <> '' ) ";
} 

$query  = "SELECT GevityNo, employee_name, Sex, nextel, ssNumber, BirthDate, Hire_Date, QuitDate, PayRate, street_address, City, state, zip_code, home_phone, cellphonenum  FROM employees WHERE $sql ORDER BY `employee_name` ";
$result = mysql_query($query) or die('Error, query failed');

$num_rows = mysql_num_rows($result);

if($num_rows > 0) {
$row_count = 1;
while($row = mysql_fetch_array($result)){
	if($row_count == 1) {
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$format_bold =& $workbook->addFormat();
		$format_bold->setBold();

		// sending HTTP headers
		$workbook_name = $row['employees'] . 'phone_list.xls';
		$workbook->send($workbook_name);

		// Creating a worksheet
		$worksheet =& $workbook->addWorksheet('phone_list');

		// The actual data
		$worksheet->setColumn(0,0,30);
		$worksheet->setColumn(0,1,14);
		$worksheet->setColumn(0,2,14);
		$worksheet->setColumn(0,3,14);
		$worksheet->write(0, 0, 'Name', $format_bold);
		$worksheet->write(0, 1, 'Nextel', $format_bold, $format_left);
		$worksheet->write(0, 2, 'Home', $format_bold, $format_left);
		$worksheet->write(0, 3, 'Cell', $format_bold, $format_left);
	}

	$worksheet->write($row_count, 0, $row['employee_name']);
	$worksheet->write($row_count, 1, $row['nextel']);
	$worksheet->write($row_count, 2, $row['home_phone']);
	$worksheet->write($row_count, 3, $row['cellphonenum']);
	$row_count++;
}
}

// Let's send the file
$workbook->close(); //
?>

[kahodges]

<?php
include 'config.php';
require_once 'Spreadsheet/Excel/Writer.php';

if ($QuitDate===false) {
    // Active employees
    $sql =" ( `QuitDate` is null or trim(`QuitDate`) = '' ) ";
}else {
// Terminated employees
    $sql =" ( `QuitDate` is not null and trim(`QuitDate`) <> '' ) ";
} 

$query  = "SELECT GevityNo, employee_name, Sex, nextel, ssNumber, BirthDate, Hire_Date, QuitDate, PayRate, street_address, City, state, zip_code, home_phone, cellphonenum  FROM employees WHERE $sql ORDER BY `employee_name` ";
$result = mysql_query($query) or die('Error, query failed');

$num_rows = mysql_num_rows($result);

if($num_rows > 0) {
$row_count = 1;
while($row = mysql_fetch_array($result)){
	if($row_count == 1) {
		// Creating a workbook
		$workbook = new Spreadsheet_Excel_Writer();
		$format_bold =& $workbook->addFormat();
		$format_bold->setBold();

		// sending HTTP headers
		$workbook_name = $row['employees'] . 'phone_list.xls';
		$workbook->send($workbook_name);

		// Creating a worksheet
		$worksheet =& $workbook->addWorksheet('phone_list');

		// The actual data
		$worksheet->setColumn(0,0,30);
		$worksheet->setColumn(0,1,14);
		$worksheet->setColumn(0,2,14);
		$worksheet->setColumn(0,3,14);
		$worksheet->write(0, 0, 'Name', $format_bold);
		$worksheet->write(0, 1, 'Nextel', $format_bold, $format_left);
		$worksheet->write(0, 2, 'Home', $format_bold, $format_left);
		$worksheet->write(0, 3, 'Cell', $format_bold, $format_left);
	}

	$worksheet->write($row_count, 0, $row['employee_name']);
	$worksheet->write($row_count, 1, $row['nextel']);
	$worksheet->write($row_count, 2, $row['home_phone']);
	$worksheet->write($row_count, 3, $row['cellphonenum']);
	$row_count++;
}
}

// Let's send the file
$workbook->close(); //
?>

 

We had them backwards, but it helped me understand more of what was going on, and got it working. Thank you, thank you, thank you. Here's the working code:

<?php
include 'config.php';
require_once 'Spreadsheet/Excel/Writer.php';
if ($QuitDate===false) {

  // Active employees
  $sql =" ( `QuitDate` is not null and trim(`QuitDate`) <> '' ) ";
  }else {
    // Terminated employees
    $sql =" ( `QuitDate` is null or trim(`QuitDate`) = '' ) ";
    }

    $query  = "SELECT GevityNo, employee_name, Sex, nextel, ssNumber, BirthDate, Hire_Date, QuitDate, PayRate, street_address, City, state, zip_code, home_phone, cellphonenum  FROM employees WHERE $sql ORDER BY `employee_name` ";
    $result = mysql_query($query) or die('Error, query failed');
    $num_rows = mysql_num_rows($result);if($num_rows > 0) {
      $row_count = 1;
      while($row = mysql_fetch_array($result)){		
        if($row_count == 1) {			
          // Creating a workbook			
          $workbook = new Spreadsheet_Excel_Writer();			
          $format_bold =& $workbook->addFormat();			
          $format_bold->setBold();			
          // sending HTTP headers			
          $workbook_name = $row['employees'] . 'phone_list.xls';			
          $workbook->send($workbook_name);			
          // Creating a worksheet			
          $worksheet =& $workbook->addWorksheet('phone_list');			
          // The actual data			
          $worksheet->setColumn(0,0,30);			
          $worksheet->setColumn(0,1,14);			
          $worksheet->setColumn(0,2,14);			
          $worksheet->setColumn(0,3,14);			
          $worksheet->write(0, 0, 'Name', $format_bold);			
          $worksheet->write(0, 1, 'Nextel', $format_bold, $format_left);			
          $worksheet->write(0, 2, 'Home', $format_bold, $format_left);			
          $worksheet->write(0, 3, 'Cell', $format_bold, $format_left);		}		
          $worksheet->write($row_count, 0, $row['employee_name']);		
          $worksheet->write($row_count, 1, $row['nextel']);		
          $worksheet->write($row_count, 2, $row['home_phone']);		
          $worksheet->write($row_count, 3, $row['cellphonenum']);		
          $row_count++;	}}
          // Let's send the file
          $workbook->close(); //?>