CODEWITHSUNDEEP
phpmysqldatabasedownload
AnnaB2015
AnnaB2015

Reputation: 21

Download data from mysql database to a word document in php

I am trying to download the data saved within mysql database to a word document using php.

The code I have at the minute is meant to work as follows, Display all data within the student table of the database Click the download button which downloads this data onto a Microsoft word document.

However, it is only printing out on record of the student table and when you try to open the document it says the content is corrupt (when using google chrome, it opens fine in firefox).

Any help would be much appreciated. (see code below)

    <?php
include("dbConnect.php");
$dbQuery = $db->prepare("select * from student");
$dbQuery->execute();
while ($dbRow = $dbQuery->fetch(PDO::FETCH_ASSOC)) {
    $ID                 = $dbRow['ID'];
    $Forename           = $dbRow['Forename'];
    $Surname            = $dbRow['Surname'];
    $Email              = $dbRow['Email'];
    $B_number           = $dbRow['B_number'];
    $School             = $dbRow['School'];
    $Campus             = $dbRow['Campus'];
    $Research_Institute = $dbRow['Research_Institute'];
    $FTPT               = $dbRow['FTPT'];
}
$doc_body = "<h1>Students</h1>";
?>

    <head>
    <title>Students</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet"   href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://dunluce.infc.ulst.ac.uk/cw11ba/project/Project/mycss.css">

    </head>

    <form name="export_form" action="<?php
echo ($_SERVER['PHP_SELF']);
?>" method="post">
    <input type="submit" name="submit_docs" value="Export as MS Word" class="input-button" /> <a href="https://dunluce.infc.ulst.ac.uk/cw11ba/project/Project/admin.php"><button type="button" class= "btn btn-block">Go back to Admin Area</button></a>
    </form>

    <?php
if (isset($_POST['submit_docs'])) {
    header("Content-Type:application/msword");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("content-disposition: attachment;filename=test.docx");
}
echo "<html>";
echo "$doc_body";
echo "<table class=table table-striped table id=student>
                <tr>
                <th>ID</th><th>Forename</th><th>Surname</th><th>Email</th><th>B Number</th><th>School</th><th>Campus</th><th>Research Institute</th><th>FT/PT</th>
                </tr>
                  <tr><td>$ID</td><td>$Forename</td><td>$Surname</td> <td>$Email</td><td>$B_number</td><td>$School</td><td>$Campus</td><td>$Research_Institute</td><td>$FTPT</td>
                   </tr>;
            </table>";
echo "</html>";
?>

Upvotes: 1

Views: 13598

Answers (2)

x-ray
x-ray

Reputation: 3329

The code in your while loop overwrites the previous values so you end up with the values of the last entry in $ID, $Forename, ... . You need to generate the output in the while loop. In addition you have to make sure you don't send your website content when generating the word content.

Try a structure like this:

<?php 
include ("dbConnect.php");

function databaseOutput() {

  $dbQuery = $db->prepare("select * from student");
  $dbQuery->execute();

  while ($dbRow = $dbQuery->fetch(PDO::FETCH_ASSOC)) {
?>
    <tr>
      <td><?php echo $dbRow['ID']; ?></td>
      <td><?php echo $dbRow['Forename']; ?></td>
      <td><?php echo $dbRow['Surname']; ?></td>
      <td><?php echo $dbRow['Email']; ?></td>
      <td><?php echo $dbRow['B_number']; ?></td>
      <td><?php echo $dbRow['School']; ?></td>
      <td><?php echo $dbRow['Campus']; ?></td>
      <td><?php echo $dbRow['Research_Institute']; ?></td>
      <td><?php echo $dbRow['FTPT']; ?></td>
    </tr>
<?php    

  }

} // end of function databaseOutput()

if ($_POST['submit_docs']) { // word output

  header("Content-Type:application/msword");
  header("Expires: 0");
  header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  header("content-disposition: attachment;filename=test.docx");

?>
<html>
  <body>
    <h1>Students</h1>
    <table>
      <tr>
        <th>ID</th><th>Forename</th><th>Surname</th><th>Email</th><th>B Number</th><th>School</th><th>Campus</th><th>Research Institute</th><th>FT/PT</th>
      </tr>
      <?php databaseOutput(); ?>
    </table>
  </body>
</html>
<?php

  exit; // end of word output

}
?>
<html>
  <head>
    <title>Students</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet"   href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
    <link rel="stylesheet" href="https://dunluce.infc.ulst.ac.uk/cw11ba/project/Project/mycss.css">
  </head>
  <body>
    <form name="export_form" action="<?php echo($_SERVER['PHP_SELF']);?>" method="post">
      <input type="submit" name="submit_docs" value="Export as MS Word" class="input-button" /> <a href="https://dunluce.infc.ulst.ac.uk/cw11ba/project/Project/admin.php"><button type="button" class= "btn btn-block">Go back to Admin Area</button></a>
    </form>
    <table class="table table-striped" id="student">
      <tr>
        <th>ID</th><th>Forename</th><th>Surname</th><th>Email</th><th>B Number</th><th>School</th><th>Campus</th><th>Research Institute</th><th>FT/PT</th>
      </tr>
      <?php databaseOutput(); ?>
    </table>
  </body>
</html>

I did not test this, but something similar to this should work.

Upvotes: 1

lov2code
lov2code

Reputation: 394

I would change my headers to this: (NOT TESTED but should work)

header("Content-type: application/vnd.ms-word");
header("Content-Disposition: attachment;Filename=test.doc");

As well add this line right between <html> tag and $doc_body:

<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">

This should give you an older version which will be compatible with more browsers, it will simply update the file if needed. Hope this helps!

Full code modified:

<?php
if(isset($_POST['submit_docs']))
 {
    header("Content-type: application/vnd.ms-word");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Disposition: attachment;Filename=test.doc");
 }

    $pri = "<html>";
    $pri .= "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
    $pri .= $doc_body;
    $pri .= "<table class=table table-striped table id=student>";
    $pri .= "<tr>";
    $pri .= "<th>ID</th><th>Forename</th><th>Surname</th><th>Email</th><th>B Number</th><th>School</th><th>Campus</th><th>Research Institute</th><th>FT/PT</th>";
    $pri .= "</tr>";
    $pri .= "<tr><td>".$ID."</td><td>".$Forename."</td><td>".$Surname."</td> <td>".$Email."</td><td>".$B_number."</td><td>".$School."</td><td>".$Campus."</td><td>".$Research_Institute."</td><td>".$FTPT."</td>";
    $pri .= "</tr>";
    $pri .= "</table>";
    $pri .= "</html>";

echo $pri;
    ?>

This way you are escaping all white space, as well as outputting the php variables and not risking that they get interpretted ad text.

LOOPING THROUGH THE RESULTS AND OUTPUTTING THEM ALL INTO THE WORD FILE: Remove the while loop from the top of your document and replace this line:

$pri .= "<tr><td>".$ID."</td><td>".$Forename."</td><td>".$Surname."</td> <td>".$Email."</td><td>".$B_number."</td><td>".$School."</td><td>".$Campus."</td><td>".$Research_Institute."</td><td>".$FTPT."</td>";

with this:

while ($dbRow = $dbQuery->fetch(PDO::FETCH_ASSOC)) 
{
        $ID       = $dbRow['ID'];
        $Forename = $dbRow['Forename'];
        $Surname  = $dbRow['Surname'];
        $Email  = $dbRow['Email'];
        $B_number = $dbRow['B_number'];
        $School = $dbRow['School'];
        $Campus = $dbRow['Campus'];
        $Research_Institute = $dbRow['Research_Institute'];
        $FTPT = $dbRow['FTPT'];

        $pri .= "<tr><td>".$ID."</td><td>".$Forename."</td><td>".$Surname."</td> <td>".$Email."</td><td>".$B_number."</td><td>".$School."</td><td>".$Campus."</td><td>".$Research_Institute."</td><td>".$FTPT."</td>";

    }

Should work like a charm :)

Upvotes: 0

Related Questions