Export MySQL/Webpage to Word using PHP

blepblep · June 28, 2012

Hello people. Wondering could anyone help me on this one. I have Office 2003 so I cant use PHPWord.

I have a web page that returns results from my database. I also have a button that when it is clicked, goes to a page and performs a query. At the moment, I have my page downloading as a .doc file but it is all messed up when I opened it and very unreadable. I was wondering does anyone no a way I can basically download the page itself into a .doc file or at least have it formatted in a readable manner?

Here is my code, it is from an example I found online -

<?php
$DB_Server = "localhost";        //your MySQL Server
$DB_Username = "root";           //your MySQL User Name
$DB_Password = "vfr45tgbnhy6";   //your MySQL Password
$DB_DBName = "tc_tool";          //your MySQL Database Name
$DB_TBLName = "review";          //your MySQL Table Name

//$sql = "Select * from $DB_TBLName";

$sql = "SELECT review.*, mom.*, action.*, remark.* FROM review
LEFT JOIN mom on review.reviewId = mom.reviewId
LEFT JOIN remark on review.reviewId = remark.reviewId
LEFT JOIN action on review.reviewID = action.reviewId
WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Title = 1 to generate title, 0 not to use title
$Use_Title = 1;

//define date for title: EDIT this to create the time-format you need
$now_date = DATE('d-m-Y H:i');

//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";

//create MySQL connection
$Connect = @MYSQL_CONNECT($DB_Server, $DB_Username, $DB_Password)
or DIE("Couldn't connect to MySQL:<br>" . MYSQL_ERROR() . "<br>" . MYSQL_ERRNO());
//select database
$Db = @MYSQL_SELECT_DB($DB_DBName, $Connect)
or DIE("Couldn't select database:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());
//execute query
$result = @MYSQL_QUERY($sql,$Connect)
or DIE("Couldn't execute query:<br>" . MYSQL_ERROR(). "<br>" . MYSQL_ERRNO());

//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
IF (ISSET($w) && ($w==1))
{
$file_type = "msword";
$file_ending = "doc";
}ELSE {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
HEADER("Content-Type: application/$file_type");
HEADER("Content-Disposition: attachment; filename=database_dump.doc");
HEADER("Pragma: no-cache");
HEADER("Expires: 0");

/*    Start of Formatting for Word or Excel    */

IF (ISSET($w) && ($w==1)) //check for $w again
{
/*    FORMATTING FOR WORD DOCUMENTS ('.doc')   */
//create title with timestamp:
IF ($Use_Title == 1)
{
	ECHO("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character

WHILE($row = MYSQL_FETCH_ROW($result))
{
	//set_time_limit(60); // HaRa
	$schema_insert = "";
	FOR($j=0; $j<mysql_num_fields($result);$j++)
	{
	//define field names
		$field_name = MYSQL_FIELD_NAME($result,$j);
		//will show name of fields
		$schema_insert .= "$field_name:\t";
		IF(!ISSET($row[$j])) {
		$schema_insert .= "NULL".$sep;
	}
	ELSEIF ($row[$j] != "") {
	$schema_insert .= "$row[$j]".$sep;
		}
		ELSE {
		$schema_insert .= "".$sep;
		}
		}
		$schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
		$schema_insert .= "\t";
		PRINT(TRIM($schema_insert));
		//end of each mysql row
		//creates line to separate data from each MySQL table row
		PRINT "\n----------------------------------------------------\n";
}
}ELSE{
/*    FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
//create title with timestamp:
IF ($Use_Title == 1)
     {
         ECHO("$title\n");
     }
     //define separator (defines columns in excel & tabs in word)
     $sep = "\t"; //tabbed character

//start of printing column names as names of MySQL fields
	FOR ($i = 0; $i < MYSQL_NUM_FIELDS($result); $i++)
	{
	ECHO MYSQL_FIELD_NAME($result,$i) . "\t";
}
PRINT("\n");
//end of printing column names

//start while loop to get data
WHILE($row = MYSQL_FETCH_ROW($result))
	{
	//set_time_limit(60); // HaRa
	$schema_insert = "";
	FOR($j=0; $j<mysql_num_fields($result);$j++)
	{
	IF(!ISSET($row[$j]))
	$schema_insert .= "NULL".$sep;
	ELSEIF ($row[$j] != "")
	$schema_insert .= "$row[$j]".$sep;
	ELSE
		$schema_insert .= "".$sep;
	}
		$schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
		//following fix suggested by Josue (thanks, Josue!)
		//this corrects output in excel when table fields contain \n or \r
		//these two characters are now replaced with a space
		$schema_insert = PREG_REPLACE("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
		$schema_insert .= "\t";
		PRINT(TRIM($schema_insert));
		PRINT "\n";
	}
}

?>

And here is how it opens up the file -

And when it opens -

If anyone could help me I'd be thankful as I'm under a bit of pressure to get this working before tomorrow.

SalientAnimal · June 28, 2012

I'm not sure if I am understanding your question 100%, but have you tried opening the page in Notepad++?

It's much easier to use when trying to read your code.

memfiss · June 28, 2012

maybe u need to put encoding in header

ManiacDan · June 28, 2012

Why does this have to be a DOC file? This is a data table, why not output it in the data table reader (excel)? Failing that, serve it in plaintext. Doc is the wrong format for this.

redarrow · June 28, 2012

I think your in trouble, as i no word is not just binary it also has it own architecture so it imposable to work with as a flat file.

dont hold me to that but i am kind of 50% sure i am right.

good luck

blepblep · June 28, 2012

@SalientAnimal - I need it to download into a Microsoft Word file. I dont understand what you mean about have I tried opening the page in Notepad++?

@memfiss - How do I do that?

@ManiacDan - Originally it was meant to be outputted to an Excel file but I was asked to change it to Word. How do I serve it in plaintext? Is it not possible to format the page?

Failing that could I use something like PHPExcel?

@php-real-degree - I dont understand what you mean by it is a flat file. Hopefully it is possible!

Barand · June 28, 2012

Save your output as an html file. Word should open that.

blepblep · June 28, 2012

@Barand I tried this -

//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=database_dump.html");

And it opens it as HTML. Is that what you mean by my output?

ManiacDan · June 28, 2012

@ManiacDan - Originally it was meant to be outputted to an Excel file but I was asked to change it to Word. How do I serve it in plaintext? Is it not possible to format the page?

Why did they ask you to change it to word? What are they trying to do with it? Do they really want an excel table in a word document? What possible purpose does that serve?

"Serve plaintext" can either mean:

1) Serve a normal HTML page with <pre> tags to format your data

2) Serve a text/plain header then dump the output formatted like console output

Ask them what they're trying to accomplish and what format they need the data in. "just put a table in word" is not an answer.

blepblep · June 28, 2012

They dont come from a technical background so they dont realise if stuff will work/wont work! Would it just be easier and better to download it to Excel so yeah?

I'll try explain what I want better. See the image below, there my search results.

I have a button under the results that says 'Export'. When Export is clicked that goes to another page and performs a query, then a file is downloaded with the relevant information under each heading. This way will also be accepted but if I am to do it that way it needs to be formatted, eg. Document Title has to be in bold.

I have it downloading using the following code, but cannot format it. The information goes in under the relevant headers too so really I just need to figure out how to format it somehow.

include 'connect_db.php';

function xlsBOF() { 
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
    return; 
} 

function xlsEOF() { 
    echo pack("ss", 0x0A, 0x00); 
    return; 
} 

function xlsWriteNumber($Row, $Col, $Value) { 
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); 
    echo pack("d", $Value); 
    return; 
} 

function xlsWriteLabel($Row, $Col, $Value ) { 
    $L = strlen($Value); 
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); 
    echo $Value; 
return; 
}


$query = "
SELECT review.*, mom.*, action.*, remark.* FROM review 
LEFT JOIN mom on review.reviewId = mom.reviewId
LEFT JOIN remark on review.reviewId = remark.reviewId
LEFT JOIN action on review.reviewID = action.reviewId
WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

$db = mysql_query($query);

    // Send Header
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=$reviewForum-$sec.xls ");
// ??�????????�?????????
    header("Content-Transfer-Encoding: binary ");

                xlsBOF(); 

			// Formats table headers in Excel file
			xlsWriteLabel(0,0,"Review ID");
                xlsWriteLabel(0,1,"Review Forum");
                xlsWriteLabel(0,2,"xlsument Title");
                xlsWriteLabel(0,3,"xlsument Number");
                xlsWriteLabel(0,4,"xlsument Type");
                xlsWriteLabel(0,5,"Project Name");
                xlsWriteLabel(0,6,"Author Name");
                xlsWriteLabel(0,7,"Chairperson");
                xlsWriteLabel(0,8,"Revision");
                xlsWriteLabel(0,9,"CDM Link");
                xlsWriteLabel(0,10,"Main Requirement ID");
                xlsWriteLabel(0,11,"MARS Link");
                xlsWriteLabel(0,12,"Checklist Link");
                xlsWriteLabel(0,13,"1/3 Internal Link");
                xlsWriteLabel(0,14,"Impacted Products");
                xlsWriteLabel(0,15,"Abstract");
                xlsWriteLabel(0,16,"Scope");
                xlsWriteLabel(0,17,"Impacted Products 2");
                xlsWriteLabel(0,18,"Review Class");
                xlsWriteLabel(0,19,"Review Type");
                xlsWriteLabel(0,20,"Earliest Date");
                xlsWriteLabel(0,21,"Latest Date");
                xlsWriteLabel(0,22,"Previous TC Reviews");
                xlsWriteLabel(0,23,"Required Attendees");
                xlsWriteLabel(0,24,"Optional Attendees");
                xlsWriteLabel(0,25,"Information Only");
                xlsWriteLabel(0,26,"Review Duration");
                xlsWriteLabel(0,27,"xlsument Abstract");
                xlsWriteLabel(0,28,"Result");
                xlsWriteLabel(0,29,"Date");

			xlsWriteLabel(4, 0, "Minutes of Meeting:");
                xlsWriteLabel(5, 0, "Review ID");
                xlsWriteLabel(5, 1, "Quality Ranking of Review");
                xlsWriteLabel(5, 2, "Correct xlsument Template");
                xlsWriteLabel(5, 3, "Internally Reviewed");
                xlsWriteLabel(5, 4, "Requirements Covered");
                xlsWriteLabel(5, 5, "Correct Storage Library");
                xlsWriteLabel(5, 6, "Reports Described");
                xlsWriteLabel(5, 7, "Change Requests Included");
                xlsWriteLabel(5, 8, "Opened Issues Addressed");
                xlsWriteLabel(5, 9, "xlsument Available");
                xlsWriteLabel(5, 10, "Statement Problem Chapter");
                xlsWriteLabel(5, 11, "Major Comments");
                xlsWriteLabel(5, 12, "Result");
                xlsWriteLabel(5, 13, "Number of Major Comments");
                xlsWriteLabel(5, 14, "Number of Minor Comments");
                xlsWriteLabel(5, 15, "Next Review Forum");
                xlsWriteLabel(5, 16, "Reason for Cancellation");
                xlsWriteLabel(5, 17, "Reason for Re-Review");
                xlsWriteLabel(5, 18, "Other Comments");
                xlsWriteLabel(5, 19, "Date");
                xlsWriteLabel(5, 20, "Time");
                xlsWriteLabel(5, 21, "Venue");
                xlsWriteLabel(5, 22, "Attendees");
                xlsWriteLabel(5, 23, "Email Comments");
                xlsWriteLabel(5, 26, "Sign Off");
			xlsWriteLabel(5, 27, "Review ID");
			xlsWriteLabel(5, 28, "Number");
			xlsWriteLabel(5, 29, "Location");
			xlsWriteLabel(5, 30, "Severity");
			xlsWriteLabel(5, 31, "Responsible");
			xlsWriteLabel(5, 32, "Status");
			xlsWriteLabel(5, 33, "Remark");
			xlsWriteLabel(5, 34, "Comment");
			xlsWriteLabel(5, 35, "No.");
			xlsWriteLabel(5, 36, "Location");
			xlsWriteLabel(5, 37, "Responsible");
			xlsWriteLabel(5, 38, "Status");
			xlsWriteLabel(5, 39, "Action");
			xlsWriteLabel(5, 40, "Comment");
			xlsWriteLabel(5, 41, "Review ID");

                $xlsRow = 1;
			$xlsRow2 = 6;

			/*
			$result = mysql_fetch_row($db);

			xlsWriteLabel(1,1 $result['reviewForum']);
			xlsWriteLabel(1,1 $result['xlsTitle']);

		   while( list
			(

			// Result section
			$reviewId, $reviewForum, $xlsTitle, $xlsNumber, $xlsType, $projectName, $authorName, $chairPerson, $revision, $cdmLink, $mainReqId, $marsLink, $checklistLink, $linkInternalOneThird, $impactedProducts, $abstract, $scope,
			$impactedProducts2, $reviewClass, $reviewType, $earliestDate, $latestDate, $previousTcReviews, $requiredAttendees, $optionalAttendees, $informationOnly, $reviewDuration, $xlsAbstract, 

			// MOM section
			$reviewId, $qualityRankingOfReview, $correctxlsTemplate, $internallyReviewed, $requirementsCovered, $correctStorageLibrary, $reportsDescribed, $changeRequestsIncluded, $openIssuesAddressed, $xlsAvailable, $statementProblemChapter,
			$majorComments, $result, $numberOfMajorComments, $numberOfMinorComments, $nextReviewForum, $reasonForCancellation, $reasonForReReview, $otherComments, $date, $time, $venue, $attendees, $emailComments,$signOff,

			// Remark Section
			$reviewId, $number, $location, $severity, $responsible, $status, $remark, $comment,

			// Action Section
			$number, $location, $responsible, $status, $action, $comment, $reviewId				
			) = mysql_fetch_row($db))

			{
					// Takes values from database and prints them to Excel file accordingly

					  // Review
					  xlsWriteNumber($xlsRow, 0, "$reviewId");
                          xlsWriteLabel($xlsRow, 1, "$reviewForum");
                          xlsWriteLabel($xlsRow, 2, "$xlsTitle");
                          xlsWriteLabel($xlsRow, 3, "$xlsNumber");
                          xlsWriteLabel($xlsRow, 4, "$xlsType");
                          xlsWriteLabel($xlsRow, 5, "$projectName");
                          xlsWriteLabel($xlsRow, 6, "$authorName");
                          xlsWriteLabel($xlsRow, 7, "$chairPerson");
                          xlsWriteLabel($xlsRow, 8, "$revision");
                          xlsWriteLabel($xlsRow, 9, "$cdmLink");
                          xlsWriteLabel($xlsRow, 10, "$mainReqId");
                          xlsWriteLabel($xlsRow, 11, "$marsLink");
                          xlsWriteLabel($xlsRow, 12, "$checklistLink");
                          xlsWriteLabel($xlsRow, 13, "$linkInternalOneThird");
                          xlsWriteLabel($xlsRow, 14, "$impactedProducts");
                          xlsWriteLabel($xlsRow, 15, "$abstract");
                          xlsWriteLabel($xlsRow, 16, "$scope");
                          xlsWriteLabel($xlsRow, 17, "$impactedProducts2");
                          xlsWriteLabel($xlsRow, 18, "$reviewClass");
                          xlsWriteLabel($xlsRow, 19, "$reviewType");
                          xlsWriteLabel($xlsRow, 20, "$earliestDate");
                          xlsWriteLabel($xlsRow, 21, "$latestDate");
                          xlsWriteLabel($xlsRow, 22, "$previousTcReviews");
                          xlsWriteLabel($xlsRow, 23, "$requiredAttendees");
                          xlsWriteLabel($xlsRow, 24, "$optionalAttendees");
                          xlsWriteLabel($xlsRow, 25, "$informationOnly");
                          xlsWriteLabel($xlsRow, 26, "$reviewDuration");
                          xlsWriteLabel($xlsRow, 27, "$xlsAbstract");
                          xlsWriteLabel($xlsRow, 28, "$result");
                          xlsWriteLabel($xlsRow, 29, "$date");

					  // MOM
                          xlsWriteNumber($xlsRow2, 0, "$reviewId");
                          xlsWriteLabel($xlsRow2, 1, "$qualityRankingOfReview");
                          xlsWriteLabel($xlsRow2, 2, "$correctxlsTemplate");
                          xlsWriteLabel($xlsRow2, 3, "$internallyReviewed");
                          xlsWriteLabel($xlsRow2, 4, "$requirementsCovered");
                          xlsWriteLabel($xlsRow2, 5, "$correctStorageLibrary");
                          xlsWriteLabel($xlsRow2, 6, "$reportsDescribed");
                          xlsWriteLabel($xlsRow2, 7, "$changeRequestsIncluded");
                          xlsWriteLabel($xlsRow2, 8, "$openIssuesAddressed");
                          xlsWriteLabel($xlsRow2, 9, "$xlsAvailable");
                          xlsWriteLabel($xlsRow2, 10, "$statementProblemChapter");
                          xlsWriteLabel($xlsRow2, 11, "$majorComments");
                          xlsWriteLabel($xlsRow2, 12, "$result");
                          xlsWriteLabel($xlsRow2, 13, "$numberOfMajorComments");
                          xlsWriteLabel($xlsRow2, 14, "$numberOfMinorComments");
                          xlsWriteLabel($xlsRow2, 15, "$nextReviewForum");
                          xlsWriteLabel($xlsRow2, 16, "$reasonForCancellation");
                          xlsWriteLabel($xlsRow2, 17, "$reasonForReReview");
                          xlsWriteLabel($xlsRow2, 18, "$otherComments");
                          xlsWriteLabel($xlsRow2, 19, "$date");
                          xlsWriteLabel($xlsRow2, 20, "$time");
                          xlsWriteLabel($xlsRow2, 21, "$venue");
                          xlsWriteLabel($xlsRow2, 22, "$attendees");
                          xlsWriteLabel($xlsRow2, 23, "$emailComments");
                          xlsWriteLabel($xlsRow2, 24, "$signOff");
					  
					  // Remark
                          xlsWriteLabel($xlsRow2, 25, "$reviewId");
                          xlsWriteLabel($xlsRow2, 26, "$number");
                          xlsWriteLabel($xlsRow2, 27, "$location");
                          xlsWriteLabel($xlsRow2, 28, "$severity");
                          xlsWriteLabel($xlsRow2, 29, "$responsible");
                          xlsWriteLabel($xlsRow2, 30, "$status");
                          xlsWriteLabel($xlsRow2, 31, "$remark");
                          xlsWriteLabel($xlsRow2, 32, "$comment");
					  
					  // Action
					  xlsWriteLabel($xlsRow2, 33, "$number");
					  xlsWriteLabel($xlsRow2, 34, "$location");
					  xlsWriteLabel($xlsRow2, 35, "$responsible");
					  xlsWriteLabel($xlsRow2, 36, "$status");
					  xlsWriteLabel($xlsRow2, 37, "$action");
					  xlsWriteLabel($xlsRow2, 38, "$comment");
					  xlsWriteLabel($xlsRow2, 39, "$reviewId");
  
                    $xlsRow++;
				$xlsRow2++;
                    }

                     xlsEOF();
                 exit();

?>

Barand · June 28, 2012

@Barand I tried this -
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=database_dump.html");
And it opens it as HTML. Is that what you mean by my output?

What I meant was output your query results as an HTML page with data in a an HTML table (as you would when outputting to a browser) and save as .html file.

From Word you can then open that html file and it should display as a word table.

ManiacDan · June 28, 2012

They dont come from a technical background so they dont realise if stuff will work/wont work!

That wasn't my question. I asked what they were trying to accomplish. Why doesn't excel work? What do they actually need?

blepblep · June 29, 2012

That wasn't my question. I asked what they were trying to accomplish. Why doesn't excel work? What do they actually need?

They want to be able to read the full results that are returned. Excel at the moment can be used instead of word. They need the results that are search and displayed on screen to export to an excel file.

Is this possible to have the Excel formatted or will I need to use something like PHPExcel?

deathadder · June 29, 2012

as barand has said multiple times PUT IT INTO A HTML TABLE, MAKE IT DOWNLOAD AS .HTML AND OPEN THE .HTML WITH WORD and it will display a word table

blepblep · June 29, 2012

as barand has said multiple times PUT IT INTO A HTML TABLE, MAKE IT DOWNLOAD AS .HTML AND OPEN THE .HTML WITH WORD and it will display a word table

I don't no how to do that. This is all new to me so I am learning as I'm going along which is why what I am asking may seem stupid to people on here.

Edit - Would it be something like in this link? http://forums.devshed.com/php-development-5/display-results-from-query-in-html-table-4095.html

Barand · June 29, 2012

Here's a sample of a simple way to do it

<?php

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"sample.html\"");
header("Pragma: no-cache");
header("Expires: 0");

?>
<html>
<head>
<title>sample table output</title>
<meta name="author" content="barand">
<style type="text/css">
    th {
        background-color: #369;
        color: white;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 700;
    }
    td {
        background-color: #eee;
        color: black;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 300;
    }
</style>
</head>
<body>
    <?php
        include("testDBconnect.php");     // connect to DB
        include("baaGrid.php");           // easy data tables

        $sql = "SELECT ee_id, firstname, lastname FROM employees";
        $g = new baaGrid ($sql);
        $g->display();    
    ?>
</body>
</html>

blepblep · June 29, 2012

Here's a sample of a simple way to do it

<?php

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"sample.html\"");
header("Pragma: no-cache");
header("Expires: 0");

?>
<html>
<head>
<title>sample table output</title>
<meta name="author" content="barand">
<style type="text/css">
    th {
        background-color: #369;
        color: white;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 700;
    }
    td {
        background-color: #eee;
        color: black;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 300;
    }
</style>
</head>
<body>
    <?php
        include("testDBconnect.php");     // connect to DB
        include("baaGrid.php");           // easy data tables

        $sql = "SELECT ee_id, firstname, lastname FROM employees";
        $g = new baaGrid ($sql);
        $g->display();    
    ?>
</body>
</html>

Thanks Barand. I understand the CSS side of what you posted, and the connecting to database part but what I dont no how to do is get the data from my query and insert it to a certain position IE under a header? Say I have Review ID as a header then under that I want the review id, I don't no how to put it in under there.

Sorry aswell but in the code what do you mean easy data tables?

include("baaGrid.php");           // easy data tables

Here is what I have so far -

mysql_connect("$host", "$username", "$password")or die("cannot connect");

mysql_select_db("$db_name")or die("cannot select DB");
  
$user_result = "SELECT review.*, mom.*, action.*, remark.* FROM review
LEFT JOIN mom on review.reviewId = mom.reviewId
LEFT JOIN remark on review.reviewId = remark.reviewId
LEFT JOIN action on review.reviewID = action.reviewId
WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";
$qry = mysql_query($user_result) OR die(mysql_error());

$user_array = mysql_fetch_assoc($qry);
echo "<center>";
echo "<table CELLPADDING=10 border = 0 >";
echo "<tr>";
echo "<th>Review ID</th>
<tr>".$user_array['reviewId']."</tr>";

echo "</tr>";
echo "</table>";

mysql_close();
?>

And it takes the data from the DB and shows it on screen. This isnt much use though as ideally I want it downloading automatically to a word document.

Thanks for the help so far!

EDIT - I have added this to the file -

header("Expires: 0");
header("Cache-control: private");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Description: File Transfer");
header("Content-Type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=export.doc");

And it downloads straight away to a word doc and opens it also, woo! Can anyone tell me if that is a ok way to go about it or should i do it differently?

Barand · June 29, 2012

I incorporated your code into mine. Because you have used "*" in the SELECT clause (bad practice) there is no way of knowing your column names so I can't add more for you.

The initial headers will cause it to download instead of displaying

<?php

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"sample.html\"");
header("Pragma: no-cache");
header("Expires: 0");

?>
<html>
<head>
<title>sample table output</title>
<meta name="author" content="barand">
<style type="text/css">
    th {
        background-color: #369;
        color: white;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 700;
    }
    td {
        background-color: #eee;
        color: black;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 300;
    }
    h1 {
        color: black;
        font-family: sans-serif;
        font-size: 12pt;
        font-weight: 700;    
    }
</style>
</head>
<body>
    <?php
        mysql_connect("$host", "$username", "$password")or die("cannot connect");

        mysql_select_db("$db_name")or die("cannot select DB");
          
        $user_result = "SELECT review.*, mom.*, action.*, remark.* FROM review
        LEFT JOIN mom on review.reviewId = mom.reviewId
        LEFT JOIN remark on review.reviewId = remark.reviewId
        LEFT JOIN action on review.reviewID = action.reviewId
        WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

        $qry = mysql_query($user_result) OR die(mysql_error());

        echo "<h1>Reviews</h1>";
        echo "<table CELLPADDING=10 border = 0 >";
        echo "<tr>
                <th>Review ID</th>
              <tr>";
              
        while ($user_array = mysql_fetch_assoc($qry)) {
            echo "<tr>
                  <td>{$user_array['reviewId']}</td>
                  </tr>";
        }

        echo "</table>";

        mysql_close();
    ?>
</body>
</html>

blepblep · June 29, 2012

I incorporated your code into mine. Because you have used "*" in the SELECT clause (bad practice) there is no way of knowing your column names so I can't add more for you.

The initial headers will cause it to download instead of displaying

<?php

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=\"sample.html\"");
header("Pragma: no-cache");
header("Expires: 0");

?>
<html>
<head>
<title>sample table output</title>
<meta name="author" content="barand">
<style type="text/css">
    th {
        background-color: #369;
        color: white;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 700;
    }
    td {
        background-color: #eee;
        color: black;
        font-family: sans-serif;
        font-size: 10pt;
        font-weight: 300;
    }
    h1 {
        color: black;
        font-family: sans-serif;
        font-size: 12pt;
        font-weight: 700;    
    }
</style>
</head>
<body>
    <?php
        mysql_connect("$host", "$username", "$password")or die("cannot connect");

        mysql_select_db("$db_name")or die("cannot select DB");
          
        $user_result = "SELECT review.*, mom.*, action.*, remark.* FROM review
        LEFT JOIN mom on review.reviewId = mom.reviewId
        LEFT JOIN remark on review.reviewId = remark.reviewId
        LEFT JOIN action on review.reviewID = action.reviewId
        WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

        $qry = mysql_query($user_result) OR die(mysql_error());

        echo "<h1>Reviews</h1>";
        echo "<table CELLPADDING=10 border = 0 >";
        echo "<tr>
                <th>Review ID</th>
              <tr>";
              
        while ($user_array = mysql_fetch_assoc($qry)) {
            echo "<tr>
                  <td>{$user_array['reviewId']}</td>
                  </tr>";
        }

        echo "</table>";

        mysql_close();
    ?>
</body>
</html>

Thank you so much Barand, that is exactly how I am wanting to display it!!

Here is my headers: Review Forum, Review ID, Document Title, Document Number, Document Type, Project Name, Author Name, Chairperson.

I have a question, how you have coded it to display is fine, but when I try to add another row such as Review Forum, it appears inside the Review ID like this -

Is there a way I could have it lining up beside each other so they will appear like this -(Color doesnt matter but just the aligning right beside each other is what I am after)

And my code for the first picture -

      echo "<h1>Reviews</h1>";
        echo "<table CELLPADDING=10 border = 0 >";
        echo "<tr>
                <th>Review Forum</th>
              <tr>";
        
        echo "<table CELLPADDING=10 border = 0 >";
        echo "<tr>
                <th>Review ID</th>
              <tr>";

              
        while ($user_array = mysql_fetch_assoc($qry)) {
            echo "<tr>
                  <td>{$user_array['reviewForum']}</td>
                  </tr>";
            echo "<tr>
        		<td>{$user_array['reviewId']}</td>
        		</tr>";
        }

        echo "</table>";

smoseley · June 29, 2012

Note: To get a line break in Word, you may have to terminate a line with "\r\n" <--- MS uses the Carriage Return character in addition to the Line Feed character to indicate a new line.

Barand · June 29, 2012

you will need to adjust the column names

    <?php
        mysql_connect("$host", "$username", "$password")or die("cannot connect");

        mysql_select_db("$db_name")or die("cannot select DB");
          
        $user_result = "SELECT review.*, mom.*, action.*, remark.* FROM review
        LEFT JOIN mom on review.reviewId = mom.reviewId
        LEFT JOIN remark on review.reviewId = remark.reviewId
        LEFT JOIN action on review.reviewID = action.reviewId
        WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

        $qry = mysql_query($user_result) OR die(mysql_error());

        echo "<h1>Reviews</h1>";
        echo "<table CELLPADDING=10 border = 0 >";
        // header row
        echo "<tr>                                                  
                <th>Review Forum</th>
                <th>Review ID</th>
                <th>Document Title</th>
                <th>Document Number</th>
                <th>Document Type</th>
                <th>Project Name</th>
                <th>Author</th>
                <th>Chairperson</th>
              <tr>";
              
        while ($user_array = mysql_fetch_assoc($qry)) {
            // data row
            echo "<tr>                                                
                  <td>{$user_array['reviewForum']}</td>
                  <td>{$user_array['reviewId']}</td>
                  <td>{$user_array['docTitle']}</td>
                  <td>{$user_array['docNumber']}</td>
                  <td>{$user_array['docType']}</td>
                  <td>{$user_array['project']}</td>
                  <td>{$user_array['author']}</td>
                  <td>{$user_array['chairperson']}</td>
                  </tr>";
        }

        echo "</table>";

        mysql_close();
    ?>

You really need to master the rudiments of HTML, particularly table structure in this case, before tackling PHP

blepblep · June 29, 2012

you will need to adjust the column names

    <?php
        mysql_connect("$host", "$username", "$password")or die("cannot connect");

        mysql_select_db("$db_name")or die("cannot select DB");
          
        $user_result = "SELECT review.*, mom.*, action.*, remark.* FROM review
        LEFT JOIN mom on review.reviewId = mom.reviewId
        LEFT JOIN remark on review.reviewId = remark.reviewId
        LEFT JOIN action on review.reviewID = action.reviewId
        WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

        $qry = mysql_query($user_result) OR die(mysql_error());

        echo "<h1>Reviews</h1>";
        echo "<table CELLPADDING=10 border = 0 >";
        // header row
        echo "<tr>                                                  
                <th>Review Forum</th>
                <th>Review ID</th>
                <th>Document Title</th>
                <th>Document Number</th>
                <th>Document Type</th>
                <th>Project Name</th>
                <th>Author</th>
                <th>Chairperson</th>
              <tr>";
              
        while ($user_array = mysql_fetch_assoc($qry)) {
            // data row
            echo "<tr>                                                
                  <td>{$user_array['reviewForum']}</td>
                  <td>{$user_array['reviewId']}</td>
                  <td>{$user_array['docTitle']}</td>
                  <td>{$user_array['docNumber']}</td>
                  <td>{$user_array['docType']}</td>
                  <td>{$user_array['project']}</td>
                  <td>{$user_array['author']}</td>
                  <td>{$user_array['chairperson']}</td>
                  </tr>";
        }

        echo "</table>";

        mysql_close();
    ?>

You really need to master the rudiments of HTML, particularly table structure in this case, before tackling PHP

Thanks a million Barand, I really appreciate it. I'm a java/c++ programmer in college and I'm currently on work placement. And where I am working I needed to learn HTML, CSS, JavaScript and PHP over the past few months, so everyday is a learning day really!

blepblep · June 29, 2012

I've come across a bit of a problem and I have a feeling I no how to fix it but it is a longer way, but I am wondering is there a way I solve it without creating more queries. I have another row underneath my first results and it is viewable when I click expand. I am trying to do the same thing in Word but without the expand, so just have it displaying but on another row. This is how it is on the website at the moment -

My code at the moment is like this -

echo "<h1>TC Tool Minutes of Meeting</h1>";
        echo "<table>";
        // header row
        echo "<tr>                                                  
                <th>Review Forum</th>
                <th>Review ID</th>
                <th>Document Title</th>
                <th>Document Number</th>
                <th>Document Type</th>
                <th>Project Name</th>
                <th>Author Name</th>
                <th>Chairperson</th>
              <tr>";
        
                
        while ($user_array = mysql_fetch_assoc($qry)) {
            // data row
            echo //"tr> <- Puts a space under Review forum and its value.
            	"                                                
                  <td>{$user_array['reviewForum']}</td>
                  <td>{$user_array['reviewId']}</td>
                  <td>{$user_array['docTitle']}</td>
                  <td>{$user_array['docNumber']}</td>
                  <td>{$user_array['docType']}</td>
                  <td>{$user_array['projectName']}</td>
                  <td>{$user_array['authorName']}</td>
                  <td>{$user_array['chairPerson']}</td>
                 ";
        }
        echo "</table>"; 
        
        echo "<table>";
        echo "<tr> 
                <th>Revision</th>
                <th>CDM Link</th>
               	<th>Main Requirement ID</th>
                <th>Mars Link</th>
               	<th>Checklist Link</th>
               	<th>Link Internal 1/3</th>
                <th>Impacted Products</th>
                <th>Scope</th>
                <th>Impacted Products 2</th>
                <th>Review Class</th>
                <th>Review Type</th>
                <th>Earliest Date</th>
                <th>Latest Date</th>
                <th>Previous TC Reviews</th>
                <th>Required Attendees</th>
                <th>Optional List</th>
                <th>Information Only</th>
        		<th>Review Duration</th>
                <th>Document Abstract</th>
              </tr>";

        while ($user_array = mysql_fetch_assoc($qry)) {
        	// data row
        	echo //"<tr> <- Puts a space under Review forum and its value.
        	"
        	<td>{$user_array['revision']}</td>
        	<td>{$user_array['cdmLink']}</td>
        	<td>{$user_array['mainReqId']}</td>
        	<td>{$user_array['marsLink']}</td>
        	<td>{$user_array['checklistLink']}</td>
        	<td>{$user_array['linkInternalOneThird']}</td>
        	<td>{$user_array['impactedProducts']}</td>
        	<td>{$user_array['abstract']}</td>
        	<td>{$user_array['impactedProducts2']}</td>
        	<td>{$user_array['reviewClass']}</td>
        	<td>{$user_array['reviewType']}</td>
        	<td>{$user_array['earliestDate']}</td>
        	<td>{$user_array['latestDate']}</td>
        	<td>{$user_array['previousTcReviews']}</td>
        	<td>{$user_array['requiredAttendees']}</td>
        	<td>{$user_array['optionalAttendees']}</td>
        	<td>{$user_array['informationOnly']}</td>
        	<td>{$user_array['reviewDuration']}</td>
        	<td>{$user_array['docAbstract']}</td>
        	";
        	}
        echo "</table>";

And I get this in word when the above code runs -

So it is calling the

mysql_fetch_array

twice.

Is there a way I can get it to run without creating another query and inserting it into the second table? Even have the second results inside the while loop but printing out in Word the same as my web page?

Barand · June 29, 2012

let's swap things round a little

    <?php
        mysql_connect("$host", "$username", "$password")or die("cannot connect");

        mysql_select_db("$db_name")or die("cannot select DB");
          
        $user_result = "SELECT review.*, mom.*, action.*, remark.* FROM review
        LEFT JOIN mom on review.reviewId = mom.reviewId
        LEFT JOIN remark on review.reviewId = remark.reviewId
        LEFT JOIN action on review.reviewID = action.reviewId
        WHERE review.reviewId IN ( ".$_POST['reviewIds']." )";

        $qry = mysql_query($user_result) OR die(mysql_error());

echo "<h1>TC Tool Minutes of Meeting</h1>";
while ($user_array = mysql_fetch_assoc($qry)) {

        echo "<table width='100%'>";
        // header row
        echo "<tr>                                                  
                <th>Review Forum</th>
                <th>Review ID</th>
                <th>Document Title</th>
                <th>Document Number</th>
                <th>Document Type</th>
                <th>Project Name</th>
                <th>Author Name</th>
                <th>Chairperson</th>
              <tr>";
        
                
            // data row
            echo //"tr> <- Puts a space under Review forum and its value.
                "                                                
                  <td>{$user_array['reviewForum']}</td>
                  <td>{$user_array['reviewId']}</td>
                  <td>{$user_array['docTitle']}</td>
                  <td>{$user_array['docNumber']}</td>
                  <td>{$user_array['docType']}</td>
                  <td>{$user_array['projectName']}</td>
                  <td>{$user_array['authorName']}</td>
                  <td>{$user_array['chairPerson']}</td>
                 ";
        
        echo "</table>"; 
        
        echo "<table width='100%'>";
        echo "<tr> 
                <th>Revision</th>
                <th>CDM Link</th>
                   <th>Main Requirement ID</th>
                <th>Mars Link</th>
                   <th>Checklist Link</th>
                   <th>Link Internal 1/3</th>
                <th>Impacted Products</th>
                <th>Scope</th>
                <th>Impacted Products 2</th>
                <th>Review Class</th>
                <th>Review Type</th>
                <th>Earliest Date</th>
                <th>Latest Date</th>
                <th>Previous TC Reviews</th>
                <th>Required Attendees</th>
                <th>Optional List</th>
                <th>Information Only</th>
                <th>Review Duration</th>
                <th>Document Abstract</th>
              </tr>";

            // 2nd data row
            echo "<tr>
            <td>{$user_array['revision']}</td>
            <td>{$user_array['cdmLink']}</td>
            <td>{$user_array['mainReqId']}</td>
            <td>{$user_array['marsLink']}</td>
            <td>{$user_array['checklistLink']}</td>
            <td>{$user_array['linkInternalOneThird']}</td>
            <td>{$user_array['impactedProducts']}</td>
            <td>{$user_array['abstract']}</td>
            <td>{$user_array['impactedProducts2']}</td>
            <td>{$user_array['reviewClass']}</td>
            <td>{$user_array['reviewType']}</td>
            <td>{$user_array['earliestDate']}</td>
            <td>{$user_array['latestDate']}</td>
            <td>{$user_array['previousTcReviews']}</td>
            <td>{$user_array['requiredAttendees']}</td>
            <td>{$user_array['optionalAttendees']}</td>
            <td>{$user_array['informationOnly']}</td>
            <td>{$user_array['reviewDuration']}</td>
            <td>{$user_array['docAbstract']}</td>
            </tr>";
        echo "</table>";
        }

        mysql_close();
    ?>

Barand · June 29, 2012

Note

The first data row above needs <tr>...</tr> around it.

Sign In

Export MySQL/Webpage to Word using PHP

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information