Jump to content

[SOLVED] mysql to xml spreadsheet

Spatz

By Spatz
in PHP Coding Help

Recommended Posts

Spatz Newbie

Spatz

Posted
Posted

Hi all

 

I have and issue where i need to download data from a mysql database to an xml excel spreadsheet

(mac compatible).

 

I have managed to pull a script together which dose all i need but its painfully slow.

 

require("../../../../cnn/cnn.php");

/*ADD EXTRA FORM OPTIONS/QUESTIONS FROM */
$sql_options="SELECT * FROM klarents.options WHERE show_id =  \"$_SESSION[show_id]\" AND (f_type = 'checkbox' OR f_type = 'radio' OR f_type = 'text' OR f_type = 'textarea') ";
$opt_results = mysql_query($sql_options, $conn)or die(mysql_error());
/*COUNT AVALIBLE OPTIONS*/
$opt_cells=mysql_num_rows($opt_results);

$cells= $opt_cells + 26;

/*GET EXHIBITOR & USER DATA*/
$sql = "SELECT exhibitors.ex_id, exhibitors.user_id, exhibitors.comp_name, exhibitors.alpha, exhibitors.stand_no, exhibitors.addr1, exhibitors.addr2, exhibitors.addr3, exhibitors.town_city, exhibitors.county, exhibitors.country, exhibitors.postcode, exhibitors.tel, exhibitors.fax, exhibitors.email, exhibitors.website, exhibitors.description, exhibitors.locked, exhibitors.proofed, users.first, users.last, users.full_name, users.user_tel, users.email, users.username, users.magic 
FROM klarents.exhibitors, users WHERE exhibitors.show_id = \"$_SESSION[show_id]\" AND users.user_id = exhibitors.user_id 
ORDER BY exhibitors.comp_name ";	
$results = mysql_query($sql, $conn)or die(mysql_error());

$rows = mysql_num_rows($results) +1;

echo "<?xml version=\"1.0\"?>\n"; 
echo "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n"; 
echo " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n"; 
echo " xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n"; 
echo " xmlns:html=\"http://www.w3.org/TR/REC-html40\"\n"; 
echo " xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\n"; 
echo " <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\n"; 
echo "  <Author>Publishing Events</Author>\n"; 
echo "  <LastAuthor>Publishing Events</LastAuthor>\n"; 
echo "  <Created>2008-06-13T09:42:52Z</Created>\n"; 
echo "  <Company>Publishing Events Ltd</Company>\n"; 
echo "  <Version>11.1025</Version>\n"; 
echo " </DocumentProperties>\n"; 
echo " <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">\n"; 
echo "  <AllowPNG/>\n"; 
echo " </OfficeDocumentSettings>\n"; 
echo " <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n"; 
echo "  <WindowHeight>18680</WindowHeight>\n"; 
echo "  <WindowWidth>21600</WindowWidth>\n"; 
echo "  <WindowTopX>-20</WindowTopX>\n"; 
echo "  <WindowTopY>-20</WindowTopY>\n"; 
echo "  <Date1904/>\n"; 
echo "  <AcceptLabelsInFormulas/>\n"; 
echo "  <ProtectStructure>False</ProtectStructure>\n"; 
echo "  <ProtectWindows>False</ProtectWindows>\n"; 
echo " </ExcelWorkbook>\n"; 
echo " <Styles>\n"; 
echo "  <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n"; 
echo "   <Alignment ss:Vertical=\"Bottom\"/>\n"; 
echo "   <Borders/>\n"; 
echo "   <Font ss:FontName=\"Verdana\"/>\n"; 
echo "   <Interior/>\n"; 
echo "   <NumberFormat/>\n"; 
echo "   <Protection/>\n"; 
echo "  </Style>\n"; 
echo " </Styles>\n"; 
echo " <Worksheet ss:Name=\"Sheet1\">\n";

/*SHOW EXHB HEADERS*/
echo "<Table ss:ExpandedColumnCount=\"".$cells."\" ss:ExpandedRowCount=\"".$rows."\" x:FullColumns=\"1\" x:FullRows=\"1\">";
echo "<Row>";
echo "<Cell><Data ss:Type=\"String\">Ex_id</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">user_id</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">comp_name</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">alpha</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">stand no</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">addr1</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">addr2</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">addr3</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">town_city</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">county</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">country</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">postcode</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">tel</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">fax</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">email</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">website</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">description</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">locked</Data></Cell>";	
echo "<Cell><Data ss:Type=\"String\">Ready For Page</Data></Cell>";	

/*USER HEADERS*/
echo "<Cell><Data ss:Type=\"String\">First Name</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">Surname</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">Full Name</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">User Tel</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">email</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">Username</Data></Cell>";
echo "<Cell><Data ss:Type=\"String\">Password</Data></Cell>";


/*LOOP THROUGH OPTIONS*/
mysql_free_result($opt_results);
$opt_results = mysql_query($sql_options, $conn)or die(mysql_error());
while($opt_rs=mysql_fetch_array($opt_results)){
	echo "<Cell><Data ss:Type=\"String\">".$opt_rs['f_name']."</Data></Cell>";
}

echo "</Row>$$";

/*DATA ROWS*/
($rs['locked'] == 1) ? $locked = "Yes" : $locked = "No" ;
while($rs=mysql_fetch_array($results)){
	//IF THERE IS PROOFED DATA GET FROM PROOFED TABLE
	if($rs['proofed']==1){
	/*GET EXHIBITOR & USER DATA*/
	$sql = "SELECT proofed_exbs.ex_id, proofed_exbs.user_id, proofed_exbs.comp_name, proofed_exbs.alpha, proofed_exbs.stand_no, proofed_exbs.addr1, proofed_exbs.addr2, proofed_exbs.addr3, proofed_exbs.town_city, proofed_exbs.county, proofed_exbs.country, proofed_exbs.postcode, proofed_exbs.tel, proofed_exbs.fax, proofed_exbs.email, proofed_exbs.website, proofed_exbs.description, proofed_exbs.final_proof, users.first, users.last, users.full_name, users.user_tel, users.email, users.username, users.magic 
FROM klarents.proofed_exbs, users WHERE proofed_exbs.ex_id = \"$rs[0]\" AND users.user_id = proofed_exbs.user_id LIMIT 1";	
	$p_results = mysql_query($sql, $conn)or die(mysql_error());
	$rsp = mysql_fetch_array($p_results);

	/*SHOW EXHB DATA*/
	echo "<Row>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['ex_id']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['user_id']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['comp_name']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['alpha']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['stand_no']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['addr1']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['addr2']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['addr3']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['town_city']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['county']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['country']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['postcode']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['tel']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['fax']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['14']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['website']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['description']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">Yes</Data></Cell>";	
	echo "<Cell><Data ss:Type=\"String\">".$rsp['final_proof']."</Data></Cell>";	

	/*USER DATA*/
	echo "<Cell><Data ss:Type=\"String\">".$rsp['firspt']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['last']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['full_name']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['user_tel']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['email']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['username']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rsp['magic']."</Data></Cell>";

	/*GET EXHIBITOR OPTIONS AND OPTION VALUES*/


	/*LOOP THROUGH OPTIONS*/
	mysql_free_result($opt_results);
	$opt_results = mysql_query($sql_options, $conn)or die(mysql_error());

	/*LOOP THROUGH OPTIONS*/
	while($opt_rs=mysql_fetch_array($opt_results)){

		$sql_option_value="SELECT * FROM option_values_proof WHERE opt_id = \"$opt_rs[opt_id]\" AND ex_id = \"$rs[ex_id]\" ";
		$results_option_value=mysql_query($sql_option_value, $conn)or die(mysql_error());
		$opt_val_rs=mysql_fetch_array($results_option_value);

			echo "<Cell><Data ss:Type=\"String\">".$opt_val_rs['opt_value']."</Data></Cell>";

		}
	echo "</Row>";
	}else{

	/*SHOW EXHB DATA*/
	echo "<Row>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['ex_id']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['user_id']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['comp_name']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['alpha']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['stand_no']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['addr1']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['addr2']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['addr3']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['town_city']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['county']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['country']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['postcode']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['tel']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['fax']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['14']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['website']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['description']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$locked."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">No</Data></Cell>";	

	/*USER DATA*/
	echo "<Cell><Data ss:Type=\"String\">".$rs['first']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['last']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['full_name']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['user_tel']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['email']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['username']."</Data></Cell>";
	echo "<Cell><Data ss:Type=\"String\">".$rs['magic']."</Data></Cell>";

	/*GET EXHIBITOR OPTIONS AND OPTION VALUES*/


	/*LOOP THROUGH OPTIONS*/
	mysql_free_result($opt_results);
	$opt_results = mysql_query($sql_options, $conn)or die(mysql_error());

	/*LOOP THROUGH OPTIONS*/
	while($opt_rs=mysql_fetch_array($opt_results)){

		$sql_option_value="SELECT * FROM option_values WHERE opt_id = \"$opt_rs[opt_id]\" AND ex_id = \"$rs[ex_id]\" ";
		$results_option_value=mysql_query($sql_option_value, $conn)or die(mysql_error());
		$opt_val_rs=mysql_fetch_array($results_option_value);

			echo "<Cell><Data ss:Type=\"String\">".$opt_val_rs['opt_value']."</Data></Cell>";

		}

	echo "</Row>";
	}
}
echo "</Table>";
mysql_free_result($results);

echo "  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n"; 
echo "  \n"; 
echo "   <Print>\n"; 
echo "    <ValidPrinterInfo/>\n"; 
echo "    <PaperSizeIndex>0</PaperSizeIndex>\n"; 
echo "    <HorizontalResolution>-4</HorizontalResolution>\n"; 
echo "    <VerticalResolution>-4</VerticalResolution>\n"; 
echo "   </Print>\n"; 
echo "   \n"; 
echo "   <ShowPageLayoutZoom/>\n"; 
echo "   <PageLayoutZoom>100</PageLayoutZoom>\n"; 
echo "   <Selected/>\n"; 
echo "   <Panes>\n"; 
echo "    <Pane>\n"; 
echo "     <Number>3</Number>\n"; 
echo "     <ActiveRow>1</ActiveRow>\n"; 
echo "     <ActiveCol>3</ActiveCol>\n"; 
echo "    </Pane>\n"; 
echo "   </Panes>\n"; 
echo "   <ProtectObjects>False</ProtectObjects>\n"; 
echo "   <ProtectScenarios>False</ProtectScenarios>\n"; 
echo "  \n"; 
echo "  </WorksheetOptions>\n"; 
echo " </Worksheet>\n"; 
echo " <Worksheet ss:Name=\"Sheet2\">\n"; 
echo "  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n"; 
echo "   <Print>\n"; 
echo "    <ValidPrinterInfo/>\n"; 
echo "    <PaperSizeIndex>0</PaperSizeIndex>\n"; 
echo "    <HorizontalResolution>-4</HorizontalResolution>\n"; 
echo "    <VerticalResolution>-4</VerticalResolution>\n"; 
echo "   </Print>\n"; 
echo "   <ShowPageLayoutZoom/>\n"; 
echo "   <PageLayoutZoom>100</PageLayoutZoom>\n"; 
echo "   <ProtectObjects>False</ProtectObjects>\n"; 
echo "   <ProtectScenarios>False</ProtectScenarios>\n"; 
echo "  </WorksheetOptions>\n"; 
echo " </Worksheet>\n"; 
echo " <Worksheet ss:Name=\"Sheet3\">\n"; 
echo "  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">\n"; 
echo "   <Print>\n"; 
echo "    <ValidPrinterInfo/>\n"; 
echo "    <PaperSizeIndex>0</PaperSizeIndex>\n"; 
echo "    <HorizontalResolution>-4</HorizontalResolution>\n"; 
echo "    <VerticalResolution>-4</VerticalResolution>\n"; 
echo "   </Print>\n"; 
echo "   <ShowPageLayoutZoom/>\n"; 
echo "   <PageLayoutZoom>100</PageLayoutZoom>\n"; 
echo "   <ProtectObjects>False</ProtectObjects>\n"; 
echo "   <ProtectScenarios>False</ProtectScenarios>\n"; 
echo "  </WorksheetOptions>\n"; 
echo " </Worksheet>\n"; 
echo "</Workbook>\n";

 

Quite a chunk i know but im banging my head trying to get this to download faster. I believe there may be a potential bottle neck when getting the additional option values but i have tested the sql statements and they dont seem to cause too much trouble or take too long.

 

Still finding my feet in php/mysql  so if anyone has the time to comment it would be greatly appreciated

 

Manny Thanks

Link to comment
https://forums.phpfreaks.com/topic/132314-solved-mysql-to-xml-spreadsheet/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.