[RESOLVED] Query Results Issue - Incorrect Number

[obrienkev]

Hi all,

The below doesn't display all the query results. If I echo $query it displays the correct number of times.

However, not all results are printed in excel sheet - results vary. Any ideas why this is?

Thanks.

// loop through query results
foreach($ffnumber as $k => $v)
{
//echo $k .' => '.$v;

// Perform Query
$query = "SELECT * ".
			   "FROM class AS cl ".
				 "INNER JOIN ".
				 "training AS t ".
				 "ON cl.class_ID = t.class_ID ".
				 "INNER JOIN ".
				 "course AS c ".
				 "ON cl.course_ID = c.course_ID ".
				 "INNER JOIN ".
				 "location AS l ".
				 "ON cl.location_ID = l.location_ID ".
				 "INNER JOIN ".
				 "instructor AS i ".
				 "ON cl.instructor_ID = i.instructor_ID ".
				 "WHERE t.ffnumber = '$v' ".
				 "ORDER BY cl.class_date ".
				 "DESC";
			//echo '<p>'.$query.'</p>';
$result = mssql_query($query) or die('Select Query Error');


$items = array();

while ($row = mssql_fetch_assoc($result))
{
$items[] = $row;
}
//print_r($items);

$currentRow=4;

foreach ($items as $item)
{
         $array_to_write = array($item['ffnumber'], $item['firstname'], $item['surname'], $item['course_name'], $item['class_start'], $item['class_end'], $item['location_name'], $item['instructor_name'], $item['attended'], $item['passed']);
				 $cart->writeRow($currentRow, 0, $array_to_write);
         $currentRow++;
}

} // end foreach

[planetsim]

Is there a reason you have the foreach than an in each loop create a Query than have a while loop in the foreach loop? Seems inefficient to me, sorry for not answer your question.

[obrienkev]

Sorry. I don't understand what you mean. Could you explain how I can make it more efficient.

Thanks.

[obrienkev]

Hi all,

This issue was never solved and has arisen again.

Any ideas why results from database are varying?

Thanks.

[bpat1434]

You initially set $currentRow to 4, then increment from there. Why? Is that part of the layout of the excel sheet (headers in first 3 rows)?

[obrienkev]

my problem seems to be with this inner foreach loop that loops through query results array...

foreach ($items as $item)
{
         $array_to_write = array($item['ffnumber'], $item['firstname'], $item['surname'], $item['course_name'], $item['class_start'], $item['class_end'], $item['location_name'], $item['instructor_name'], $item['attended'], $item['passed']);
         $cart->writeRow($currentRow, 0, $array_to_write);
         $currentRow++;
}

Here is the whole code...

<?php
require_once "Spreadsheet/Excel/Writer.php";


// Create Workbook
$xls =& new Spreadsheet_Excel_Writer();

// HTTP Headers
$xls->send('Staff Attendance Report.xls');

// Create Worksheet
$cart =& $xls->addWorksheet('Staff Report');

/* FORMATTING HEADER   */

// title text
$titleText = 'Staff Attendance Report';

// create format object
$titleFormat =& $xls->addFormat();

// set the font family
$titleFormat->setFontFamily('Arial');

// set the text to bold
$titleFormat->setBold();

// set the text size
$titleFormat->setSize('16');

// set the text color
$titleFormat->setColor('navy');

// set bottom border width to thick
$titleFormat->setBottom(2);

// set the color of the bottom border
$titleFormat->setBottomColor('navy');

// set the alignment to the special merge value
$titleFormat->setAlign('merge');

// add the title to the top left cell of the worksheet,
// passing it the title string and the form object
$cart->write(0,0,$titleText,$titleFormat);

// add empty cells to merge with
$cart->write(0,1,'',$titleFormat);
$cart->write(0,2,'',$titleFormat);
$cart->write(0,3,'',$titleFormat);

// the row height
$cart->setRow(0,30);

// Set the column width
$cart->setColumn(0,20,20);

/*   End Header Formatting   */


/*    Column Headings FORMATTING    */

$colHeadingFormat =& $xls->addFormat();
$colHeadingFormat->setBold();
$colHeadingFormat->setFontFamily('Arial');
$colHeadingFormat->setBold();
$colHeadingFormat->setSize('10');
$colHeadingFormat->setAlign('center');

// An array with the data for the column headings
$colNames = array('FFNumber', 'First Name', 'Surname', 'Course', 'Class Begins', 'Class Ends', 'Location', 'Instructor', 'Attended Exam', 'Passed' );

// Add all the column headings with a single call
// leaving a blank row to look nicer
$cart->writeRow(2,0,$colNames,$colHeadingFormat);

// Cell group to Freeze
$freeze = array(3,0,4,0);

$cart->freezePanes($freeze);

/*  End Column Headings Formatting    */


// set variable
$ffnumber = $_POST['ffnumber'];

// Connect to DB
include('../../db_connect.php');

// FORMAT FFNUMBER DETAILS
foreach($ffnumber as $k => $m)
{

//echo $k.' => '.$m.'<br />';

// Perform Query
$query = "SELECT * ".
			   "FROM class AS cl ".
				 "INNER JOIN ".
				 "training AS t ".
				 "ON cl.class_ID = t.class_ID ".
				 "INNER JOIN ".
				 "course AS c ".
				 "ON cl.course_ID = c.course_ID ".
				 "INNER JOIN ".
				 "location AS l ".
				 "ON cl.location_ID = l.location_ID ".
				 "INNER JOIN ".
				 "instructor AS i ".
				 "ON cl.instructor_ID = i.instructor_ID ".
				 "WHERE t.ffnumber = '$m' ".
				 "ORDER BY cl.class_start ".
				 "DESC";
			//echo '<p>'.$query.'</p>';
$result = mssql_query($query) or die('Select Query Error');
//echo '<p>R:'.$num_results = mssql_num_rows($result).'</p>';

$items = array();

while ($row = mssql_fetch_assoc($result))
{
$items[] = $row;
}
//print_r($items);


$currentRow=4;

foreach ($items as $item)
{
         $array_to_write = array($item['ffnumber'], $item['firstname'], $item['surname'], $item['course_name'], $item['class_start'], $item['class_end'], $item['location_name'], $item['instructor_name'], $item['attended'], $item['passed']);
         $cart->writeRow($currentRow, 0, $array_to_write);
         $currentRow++;
}

} // end foreach

$titleText .= date('dS M Y');

$xls->close();

?>

[twilliamsen]

you are not suppling us something... i just can't figure it out.

[twilliamsen]

humor me... change this

$currentRow=4;

to this

$currentRow=1;

[twilliamsen]

Can you compare the data in the DB and what is being displayed to verify what you are missing?

this isn't sitting well either

$currentRow=4; 

foreach ($items as $item) 
{ 
         $array_to_write = array($item['ffnumber'], $item['firstname'], $item['surname'], $item['course_name'], $item['class_start'], $item['class_end'], $item['location_name'], $item['instructor_name'], $item['attended'], $item['passed']); 
         $cart->writeRow($currentRow, 0, $array_to_write); 
         $currentRow++; 
} 

} // end foreach 

[obrienkev]

$currentRow is set to 4 as this is the 4th row in the excel sheet. The content written at the top of this php page is the excel page heading, etc so this is just a marker of where to begin.

All above array values are displayed but the loop is inconsistent - all row results are not displayed.

Any thoughts?

[bpat1434]

You and I are gonna feel really stupid about this....

Let's take a simpler look at your code:

foreach($ffnumber as $k=>$m)
$currentrow = 4
foreach($items as $item){
$currentrow++;

Do you notice anything? Each time you go to execute a new query, your current row gets reset to 4. Try moving the $currentrow=4; statement to outside the main foreach loop so your code is:

$currentrow = 4;
foreach($ffnumber as $k=>$m) {

That should fix your problems.

Told you we were gonna feel stupid....

[obrienkev]

oh yeah :o

thanks for spotting that!!