Export To Excel PHP

[dpalame]

I am using the following code:

 

if ($at4=="Void Detail Report"){

 

// Then, we decide what characters we don't want in the string to look for

$remove_these = array (',' , '.' );

$at1 = str_replace ( $remove_these , ' ' , $at1 );

$at1 = str_replace ( '  ' , ' ' , $at1 );

//echo $at1."<br/>";

 

// After we've cleaned the string, we break it down into seperate words

$at1 = explode ( ' ' , $at1 );

//echo $at1."<br/>";

 

// make connection to dbase

$dbhost = 'localhost';

$dbuser = 'admin_voidpro';

$dbpass = 'voidPro7252011';

$dbname = 'admin_voids';

//Connect to MySQL Server

mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database

mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String

 

// run query and close connection

$query = "select upc from wfItemSpecs WHERE (brand LIKE '%" .

join("%' OR brand LIKE '%", $at1) . "%') ORDER BY brand,upc ASC ";

 

$result=mysql_query($query);

 

$at1  = "";

while ($row = mysql_fetch_array($result))

{

  $at1 .= $row['upc'] . ",";

}

$at1 = rtrim($at1, ",");

 

//echo $at1."<br/>";

 

// Then, we decide what characters we don't want in the string to look for

$remove_these = array (',' , '.' );

$at1 = str_replace ( $remove_these , ' ' , $at1 );

$at1 = str_replace ( '  ' , ' ' , $at1 );

// echo $at1."<br/>";

 

// After we've cleaned the string, we break it down into seperate words

$at1 = explode ( ' ' , $at1 );

// echo $at1."<br/>";

 

// Then, we decide what characters we don't want in the string to look for

$remove_these2 = array ( ',' , '.' );

$at2 = str_replace ( $remove_these2 , ' ' , $at2 );

$at2 = str_replace ( '  ' , ' ' , $at2 );

// echo $at2."<br/>";

 

// After we've cleaned the string, we break it down into seperate words

$at2 = explode ( ' ' , $at2 );

// echo $at2."<br/>";

 

 

// Then, we decide what characters we don't want in the string to look for

$remove_these3 = array ( ',' , '.' );

$at3 = str_replace ( $remove_these3 , ' ' , $at3 );

$at3 = str_replace ( '  ' , ' ' , $at3 );

// echo $at3."<br/>";

 

// After we've cleaned the string, we break it down into seperate words

$at3 = explode ( ' ' , $at3 );

// echo $at3."<br/>";

 

 

//foreach ($at2 as $varat2)

//{

//$sql2 .= 'wfStoreList.region LIKE \'%' . $varat2 . '%\' OR ';

//}

//$sql2 = substr($sql2, 0, -4);

//var_dump($sql2)."<br/>";

 

//foreach ($at3 as $varat3)

//{

//$sql3 .= 'wfStoreList.store LIKE \'%' . $varat3 . '%\' OR ';

//}

//$sql3 = substr($sql3, 0, -4);

//var_dump($sql3)."<br/>";

 

 

 

foreach ($at1 as $var)

{

 

$query = "SELECT wfStoreList.*

FROM wfStoreList

LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND wfData.wfupc LIKE '%$var%'

WHERE wfData.wfstore IS NULL ORDER BY region,store ASC ";

 

$result=mysql_query($query);

 

 

$num=mysql_num_rows($result);

 

$querySpec = "SELECT * FROM wfItemSpecs WHERE upc LIKE '%$var%'";

$resultSpec=mysql_query($querySpec);

$rowSpec = mysql_fetch_array($resultSpec);

 

$brand=$rowSpec['brand'];

$description=$rowSpec['description'];

$pack=$rowSpec['pack'];

 

//Build Result String

 

$display_string = "<div style=\"width:840px;overflow:auto;\"><table class=\"itemList\">";

$display_string .= "<tr>";

$display_string .= "<th>Brand</th>";

$display_string .= "<th>UPC</th>";

$display_string .= "<th>Description</th>";

$display_string .= "<th>Pack</th>";

$display_string .= "<th>Region</th>";

$display_string .= "<th>Store</th>";

 

$display_string .= "</tr>";

 

 

 

if ($num == 0)

  echo "";

  else

for($i = 0; $i<$num; $i++){

($row = mysql_fetch_array($result));

if($i % 2) { //this means if there is a remainder

        $bgcolor='#ffffcc';

    } else { //if there isn't a remainder we will do the else

        $bgcolor='#fff';

    }

 

$display_string .= "<tr style=\"background-color:$bgcolor;border: 1px solid #999;\">";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$brand</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$var</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$description</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$pack</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[region]</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[store]</td>";

 

$display_string .= "</tr>";

 

}

 

 

if ($num == 0){

  echo "";

}else{

$display_string .= "</table></div>";

 

}

 

header("Content-type: application/msexcel");

header("Content-Disposition: attachment; filename=whole_foods_void_report.xls");

header("Pragma: no-cache");

header("Expires: 0");

print "$display_string\n$data";

 

 

}

 

 

 

}

 

I exports all my data to excel, but in between tables I get header errors because the header() is inside the foreach loop.  Anybody have any ideas on how to get these header errors out?  Thanks for your help.

[requinix]

I get header errors because the header() is inside the foreach loop.

If you get errors because they're inside the loop then...

 

And can somebody remove those login credentials?

[dpalame]

If I move the headers to the outside of the loop I only get the last table in the array.  How do I circle back?  Thanks for your help and where is the modify button for my previous post.  Can't find it anywhere.

[requinix]

You did move just the headers, right? What code did you end up with?

 

And it's too late to modify your post - you have a window of, like, 30 seconds after posting to edit it. I hope I'm not exaggerating that too much.

The button would be in the top-right corner.

[dpalame]

Yes thank you for your help.  Here is the code:

 

foreach ($at1 as $var)

{

 

$query = "SELECT wfStoreList.*

FROM wfStoreList

LEFT JOIN wfData ON wfStoreList.store=wfData.wfstore AND wfData.wfupc LIKE '%$var%'

WHERE wfData.wfstore IS NULL ORDER BY region,store ASC ";

 

$result=mysql_query($query);

 

 

$num=mysql_num_rows($result);

 

$querySpec = "SELECT * FROM wfItemSpecs WHERE upc LIKE '%$var%'";

$resultSpec=mysql_query($querySpec);

$rowSpec = mysql_fetch_array($resultSpec);

 

$brand=$rowSpec['brand'];

$description=$rowSpec['description'];

$pack=$rowSpec['pack'];

 

//Build Result String

 

$display_string = "<div style=\"width:840px;overflow:auto;\"><table class=\"itemList\">";

$display_string .= "<tr>";

$display_string .= "<th>Brand</th>";

$display_string .= "<th>UPC</th>";

$display_string .= "<th>Description</th>";

$display_string .= "<th>Pack</th>";

$display_string .= "<th>Region</th>";

$display_string .= "<th>Store</th>";

 

$display_string .= "</tr>";

 

 

 

if ($num == 0)

  echo "";

  else

for($i = 0; $i<$num; $i++){

($row = mysql_fetch_array($result));

if($i % 2) { //this means if there is a remainder

        $bgcolor='#ffffcc';

    } else { //if there isn't a remainder we will do the else

        $bgcolor='#fff';

    }

 

$display_string .= "<tr style=\"background-color:$bgcolor;border: 1px solid #999;\">";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$brand</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$var</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$description</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$pack</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[region]</td>";

$display_string .= "<td style=\"white-space:normal;font-weight:bold;vertical-align:middle;\">$row[store]</td>";

 

$display_string .= "</tr>";

 

}

 

 

if ($num == 0){

  echo "";

}else{

$display_string .= "</table></div>";

 

}

 

}

header("Content-type: application/msexcel");

header("Content-Disposition: attachment; filename=whole_foods_void_report.xls");

header("Pragma: no-cache");

header("Expires: 0");

print "$display_string\n$data";

 

 

}

 

That only returns the results for the last variable in the array.  Any insight?  Thanks