Query Result + Array Problem

[obrienkev]

Why won't this return any results?

$result = mysql_query($query) or die(mysql_error());
$num_results = mysql_num_rows($result);

$items = array();

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

$items[] =
array('Client'=>$row['client'], 'Entity'=>$row['entity'], 'Subfund'=>$row['subfund']);
}

// tracker
$currentRow = 4;

// Loop Through Results
foreach ($items as $item)
{
         $cart->writeRow($currentRow,0,$item);
        $currentRow++;
}

Cheers,

Kevin.

[devinemke]

1. is your query actually returning any rows? check the value of $num_results to make sure.

2. you have not shown us what $cart->writeRow() does exactly.

[obrienkev]

Ran some echos:

Using echo $num_results displays: 1

Using the echo $row['client'] displays the table data.

Using the print_r($items) displays this:

Array
(
[0] => Array
(
[Client] => Abbey NationalK
[Entity] => Segregated Portfolio
[Subfund] => Spyre
)

So guess problem with foreach loop??
I left out the $cart variable and echoed $item but just printed Array

Any ideas??

)

[devinemke]

Originally posted by obrienkev
Any ideas??

i again repeat point #2 from my ealier post: what does $cart->writeRow() exactly do?

[obrienkev]

It writes to an excelsheet but that shouldn't matter.

I left it out of the loop and echoed $items but returned 0 values.

Code:

while ($row = mysql_fetch_assoc($result))
{			
$items[] = 
array('Client'=>$row['client'], 'Entity'=>$row['entity'], 'Subfund'=>$row['subfund']);
echo $row['client'].", ".$row['entity'].", ".$row['subfund']."<br />";
}
echo "<pre>";
print_r($items);
echo "</pre>";

// Loop Through Results
foreach ($items as $item=>$value)
{		
		echo $item.'=>'.$value.'<br />';
}

[devinemke]

$result = mysql_query($query) or die(mysql_error());
$items = array();
while ($row = mysql_fetch_assoc($result)) {$items[] = $row;} 
foreach ($items as $value)
{
	foreach ($value as $filed_name => $value_name)
	{
		echo $filed_name . ' = ' . $value_name . '<br>';
	}
	echo '<br>';
}

[obrienkev]

Thanks devinmke,

Why does that work and not the other??

Anyway now that the loop is working I want to pass the data into an excelsheet.
The excelsheet opens with the title text, etc. but no data.

Why is this?
I would be very grateful if you could help me here.

My code in full:

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


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

// HTTP Headers
$xls->send("test.xls");

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

/* FORMATTING HEADER   */

// title text
$titleText = 'M-List '.date('dS M Y');

// 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,3,15);

/*   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('Client', 'Entity', 'Subfund', 'Director', 'Accounting Manager');
$colNames = array('Client', 'Entity', 'Subfund', 'Director');

// 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    */


// Declare Variables
$client = $_POST['client'];
$contact = $_POST['contact'];
$director = $_POST['director'];
$domicile = $_POST['domicile'];
$entity = $_POST['entity'];
$manager = $_POST['manager'];
$subfund = $_POST['subfund'];
$code = $_POST['code'];
$accounting = $_POST['accounting'];
$ta = $_POST['ta'];
$trustee = $_POST['trustee'];
$custodee = $_POST['custodee'];
$mid_office = $_POST['mid_office'];

$submit = $_POST['submit'];


// Check user got here legally
if (!$submit)
{
 	 echo 'You did not press submit!';
	 exit;
}


// Connect to DB
@ $db = mysql_pconnect('localhost', 'k28775_pfpc', 'pfpcint'); 

if (!$db)
{
 	 echo 'Could not connect to Database';
	 exit;
}

// Select DB
mysql_select_db('k28775_excel');

// Perform Query
$query="select * 
  from client as c
inner
  join subfund as s
    on c.client_ID = s.client_ID
inner
  join director as d
    on s.director_ID = d.director_ID
inner
  join entity as e
    on s.entity_ID = e.entity_ID
inner
  join contact as co
    on s.contact_ID = co.contact_ID
inner
  join manager as m
    on s.manager_ID = m.manager_ID
inner
  join domicile as dm
    on s.domicile_ID = dm.domicile_ID
 where c.client = '$client'";


// Check what menus selected

if($director!="none") 
{
$query .=" and d.director='$director' and d.director_ID=s.director_ID";
}

if($entity!="none") 
{
$query .=" and e.entity='$entity' and e.entity_ID=s.entity_ID";
}

if($contact!="none") 
{
$query .=" and co.name='$contact' and co.contact_ID=s.contact_ID";
}

if($manager!="none") 
{
$query .=" and m.manager='$manager' and m.manager_ID=s.manager_ID";
}

$result = mysql_query($query) or die(mysql_error());
$num_results = mysql_num_rows($result);

$items = array();

while ($row = mysql_fetch_assoc($result)) 
{
     $items[] = $row;
}

// tracker
$currentRow = 4;

foreach ($items as $value)
{
    foreach ($value as $filed_name => $value_name)
    {
        $cart->writeRow($currentRow,0,$filed_name);
        $currentRow++;
    }
}

$xls->close();
?>

[obrienkev]

Anyone please. Should be something minor.
Really appreciate any help. Pretty sure it has to do with the loop but not sure what.

If I use this code without populating from database it works fine:

$items=array(
      array('description'=>'book', 'price'=>34, 'number'=>1),
      array('description'=>'cd', 'price'=>20, 'number'=>2),
);

$currentRow=4;

foreach ($items as $item)
{
      $cart->writeRow($currentRow, 0, $item);
      $currentRow++;
}

Thanks.

[obrienkev]

Hi all,

I could really do with some help here.

Please.

Thanks.

Kevin.