download dynamically generated HTML table as excel file

[raman]

I have a problem with the script for download of excel file.Initially I have a page query.html which has a form with action for another script called query.php.Here the user selects one value field from mysql database and one or multiple values from another field.These pass to query.php by post method.On submission the query.php produces a tabular output.Now I want the link to enable the download of the same output as an excel file thru the script downxls.php.However it shows error in the foreach loop.I am confused how to pass the same variables again to the scipt downxls.php as were passed to query.php

 

My downxls.php script is as under:

<?php

$orn=$_POST['Organism'];

$cag=$_POST['Category'];

$conn= mysql_connect("localhost","root","pichii13");

if(!$conn)

  {

    die('Could not connect:'.mysql_error());

  }

mysql_select_db("Protvirdb",$conn);

 

// file name to be appear in output name. User can change their file name

// but this will give him a option for file name.

$file = 'testExcelFile.xls';

// start buffring

ob_start();

// sample dynamically generated data

echo '<table border="1"> ';

echo '<tr><th>Category</th><th>Protein</th><th>Brief Description</th></tr>';

foreach($_POST['Category'] as $cag){

  $ret=mysql_query("SELECT * FROM cryptovir WHERE Organism='$orn' AND Category='$cag'");

  while($row=mysql_fetch_array($ret))

    {

    echo "<tr>";

    echo"<td>".$row['Category']."</td>";

    echo"<td>".$row['Name']."</td>";

    echo"<td>".$row['Brief_Description']."</td>";

    echo "</tr>";

  }

}

echo"</table>";

 

mysql_close($conn);

 

$content = ob_get_contents();

ob_end_clean();

header("Expires: 0");

header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");

header("Cache-Control: no-store, no-cache, must-revalidate");

header("Cache-Control: post-check=0, pre-check=0", false);

header("Pragma: no-cache");

header("Content-type: application/vnd.ms-excel;charset:UTF-8");

header('Content-length: '.strlen($content));

header('Content-disposition: attachment; filename='.basename($file));

// output all contents

echo $content;

exit; // If any tags/things not supported by excel will output then it will try to //open in office word

?>

 

 

My query.php script is as under:

<?php

$org=$_POST['Organism'];

$cat=$_POST['Category'];

$con= mysql_connect("localhost","root","pichii13");

if(!$con)

  {

    die('Could not connect:'.mysql_error());

  }

mysql_select_db("Protvirdb",$con);

foreach($_POST['Category'] as $cat){

  $ret=mysql_query("SELECT * FROM cryptovir WHERE Organism='$org' AND Category='$cat'");

  $count=mysql_num_rows($ret);

  if ($count!=0){

    echo"<p> The number of records retrieved by your query is $count</p>";

    echo "<table border='1'>

<tr>

<th>Category</th>

<th>Protein</th>

<th>Brief Description</th>

</tr>";

while($row=mysql_fetch_array($ret))

  {

    $name=$row['Name'];

    $key=$row['SNo'];

    echo "<tr>";

    echo"<td>".$row['Category']."</td>";

    echo"<td><a href=\"seq.php?sn=$key\">".$row['Name']."</a></td>";

    echo"<td>".$row['Brief_Description']."</td>";

    echo "</tr>";

  }

echo"</table><br>";

echo"<form name= 'savetable' method='post' action='downxls.php'>

<input type='submit' value='Download as excel file.'><input type='hidden' name='Organism' value='<?=$org?>'><input type='hidden' name='Category' value='<?=$cat?>'></form></td></form></table>";

}

  else

    {

    echo "<p> No records were found in the category $cat</p>";

  }

}

mysql_close($con);

?>

 

[Adam]

Use GET instead of POST ..

 

for example, on query.php the link to download it should be something like...

<a href="downloadxls.php?Organism=<?php print $orn; ?>&Category=<?php print $cag; ?>">

 

Then instead of using:

 

$orn=$_POST['Organism'];
$cag=$_POST['Category'];

 

... in downloadxls.php, use:

 

$orn=$_GET['Organism'];
$cag=$_GET['Category'];

 

... and you shouldn't have a problem.

 

Adam

[raman]

Thank you Mr.Adam but the solution did not help me , I get the same error that I was getting earlier:

 

It prompts for an excel file but in the file instead of the desired data it is written : Invalid argument for foreach on line 19 in downxls.php.

The headings show alright in the excel table- Category, Protein and Brief Description.

 

Instead of showing up as a link I wanted it to look like a button , therefore I had used the <form> tag with input type=submit and <input type=hidden> to pass variables from the script query.php to downxls.php.

 

Can someone tell where I am wrong ?

[F1Fan]

Troubleshooting is much easier on a webpage than in an Excel document. You can just keep refreshing until you fix it. So, for now, comment out your header stuff and fix the PHP problems. Then, once you have all that working, un-comment the headers and go from there.

 

Once you see the errors on your page, post them here.

 

Also, posing your code in the code tags makes it easier to follow.

[raman]

I get the same errror on the webpage :

Invalid argument for foreach on line 19, script /usr/local/apache2/htdocs/downxls.php.

 

Another thing I am intrigued about is if once I have generated a HTML table by pulling data from a MYSQL database in a script(query.php) does it need to be generated again for its download as an excel file in another script (downxls.php) ?

 

Or can the data be stored and passed on the next script directly?

[F1Fan]

OK, I noticed that you have $cag declared up at the top as equal to $_POST['Category'], but then you're not using it.

 

That error is basically saying that $_POST['Category'] is not an array. Where is that coming from? Have you validated that $_POST['Category'] is making it to your file and is an array? Maybe that's what you're asking. Looking at the code, it appears that it is making it to your file, BUT it's not an array, it's just a single category value. Why do you have a foreach loop there?

[raman]

I have a foreach loop for category because I want  multiple categories to be taken.In the HTML page I have made a checkbox , so multiple categories can be selected.

My HTML code is like this :

<form action="query.php" method ="post">

<input type="checkbox" name="Category[]" value="blabla1">

<input type="checkbox" name="Category[]" value="blabla2">

 

I have checked that all the categories are being taken and it does give the correct output in the HTML table,only in the download script it fails.

[F1Fan]

I see that you listed your checkbox idea in you previous post, but you do not have one in your whole code listed in your first post. Please list all of your code.

[raman]

The very first post had the code of 2 scripts -query.php and downxls.php .

The HTML code I gave in the last post was from query.html which is the initial page with a form having action as query.php and this page then has a form with action downxls.php.

 

In the first page one entry in the field Organism is chosen(thru dropdown) and multiple entries in the field Category are chosen(thru checkbox).

[F1Fan]

OK, try these two things:

 

First, replace these lines of yours:

<?php
$orn=$_POST['Organism'];
$cag=$_POST['Category'];
$conn= mysql_connect("localhost","root","pichii13");
if(!$conn)
  {
    die('Could not connect:'.mysql_error());
  }
mysql_select_db("Protvirdb",$conn);

// file name to be appear in output name. User can change their file name
// but this will give him a option for file name.
$file = 'testExcelFile.xls';
// start buffring
ob_start();
// sample dynamically generated data
echo '<table border="1"> ';
echo '<tr><th>Category</th><th>Protein</th><th>Brief Description</th></tr>';
foreach($_POST['Category'] as $cag){
?>

 

with this:

<?php
$orn=$_POST['Organism'];
$cags=$_POST['Category'];
$conn= mysql_connect("localhost","root","pichii13");
if(!$conn)
  {
    die('Could not connect:'.mysql_error());
  }
mysql_select_db("Protvirdb",$conn);

// file name to be appear in output name. User can change their file name
// but this will give him a option for file name.
$file = 'testExcelFile.xls';
// start buffring
ob_start();
// sample dynamically generated data
echo '<table border="1"> ';
echo '<tr><th>Category</th><th>Protein</th><th>Brief Description</th></tr>';
foreach($cags as $cag){

 

I've changed the "$cag=$_POST['Category'];" line with "$cags=$_POST['Category'];" and the "foreach($_POST['Category'] as $cag){" with "foreach($cags as $cag){."

 

I've noticed that sometimes PHP doesn't like it when the $_POST variable is a multidimensional array. But, if you just assign another variable to it, it seems to work.

 

Second, if that didn't work, do a print_r on $_POST and $cags and see what is actually getting passed to your file.