Button php export table data

Question

jppr03 0 Newbie Poster

15 Years Ago

Hi! I need a button in php code, that when i click on that button it export's my SELECT to a text file or excell file..

i searched.. there's a lot of program's that make that.. but i don't want that.. i want to make on my webpage a simple code button that when someone click there it save's the content of a table..

thanks a lot

php

3 Contributors
15 Replies
2K Views
3 Days Discussion Span
Latest Post 15 Years Ago Latest Post by jppr03

Atli 182 Posting Pro

15 Years Ago

Hey.

What data are you looking to export?
Fetching data from a form and writing it to a file is fairly simple. We just have no idea what sort of data you are using, and without knowing that we can't really say anything for certain.

We need to see your code. The form that contains your data.

Atli 182 Posting Pro

15 Years Ago

Ok, I see.

You can have PHP create whatever output you want out of that.

For example, to create a XML file and save it on the server, you can do:

// <snipped the DB code>

$output = '<?xml version="1.0" encoding="UTF-8"?><root>';
if(pg_num_rows($rs)>0)
{		  
    while($myrow = pg_fetch_assoc($rs))
    {
        $output .= sprintf('<row><latitude>%s</latitude><longitude>%s</longitude><altitude>%s</altitude><date>%s</date></row>',
            $myrow['latitude'], $myrow['longitude'], $myrow['altitude'], $myrow['data']);
        $output .= "\n";
    }
}
$output .= '</root>';

file_put_contents('/path/to/my/file.xml', $output);

Creating a Excell file is a bit more complicated (Microsoft don't play well with others), but Excell should be able to read basic XML files like these.

K0ns3rv 0 Junior Poster in Training

15 Years Ago

Ok, I see.
You can have PHP create whatever output you want out of that.
For example, to create a XML file and save it on the server, you can do:
// <snipped the DB code>

$output = '<?xml version="1.0" encoding="UTF-8"?><root>';
if(pg_num_rows($rs)>0)
{		  
    while($myrow = pg_fetch_assoc($rs))
    {
        $output .= sprintf('<row><latitude>%s</latitude><longitude>%s</longitude><altitude>%s</altitude><date>%s</date></row>',
            $myrow['latitude'], $myrow['longitude'], $myrow['altitude'], $myrow['data']);
        $output .= "\n";
    }
}
$output .= '</root>';

file_put_contents('/path/to/my/file.xml', $output);
Creating a Excell file is a bit more complicated (Microsoft don't play well with others), but Excell should be able to read basic XML files like these.

Creating a simple exel file is fairly simple, you will get the idea if you save an exel sheet as html and open it.
Basicly you can create an .xls file and fill it with html.

//K0ns3rv

Atli 182 Posting Pro

15 Years Ago

Also, forgot to mention.

If you would rather send the file to the user, to allow him to download it, you can do this instead of the file_put_contents .

header('content-type: text/xml');
header('content-disposition: attachment; filename=data_export.xml');
echo $output;

This prompts a download box in all the major browsers.

Edited 15 Years Ago by Atli because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jppr03 0 Newbie Poster · Answer 1 · 2009-11-07T17:36:28+00:00

jppr03 0 Newbie Poster

15 Years Ago

please.. anyone know? :s

jppr03 0 Newbie Poster · Answer 2 · 2009-11-07T19:12:11+00:00

thanks a lot for reply

$db = new Banco();

	$id =  $_REQUEST['id'];
	$datai = $_REQUEST['iData'];
	$dataf = $_REQUEST['fData'];

	$rs = $db->Query("SELECT *
					  FROM localizacao                          
				      WHERE (local_clien_id='$id') AND
							  ((data>=to_date('$datai', 'dd/mm/yyyy')) AND 
							   (data<=to_date('$dataf', 'dd/mm/yyyy')+1))
				      ORDER BY local_id asc");
		
		if(pg_num_rows($rs)>0)
		{		  
			while($myrow = pg_fetch_assoc($rs)) { 
				printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", $myrow['latitude'], $myrow['longitude'], $myrow['altitude'], $myrow['data']); 
			}
		}

OUTPUT:

http://i.imagehost.org/0085/example.jpg

i wanted to save that table content that show's in the figure.. latitude, longitude and data, to a text file or excell file...

you know now what i mean?

jppr03 0 Newbie Poster · Answer 3 · 2009-11-07T21:25:16+00:00

thanks for helping Alti ..

how i make that, but with a button, like a button then when i click on it, it save's my content to that XML file?

K0ns3rv 0 Junior Poster in Training · Answer 4 · 2009-11-07T21:27:22+00:00

I have the code for saving a file then sending it to the user, I'd be glad to help you, but i won't be back for 30 min, so stick around if you havn't gotten an answer.

//K0ns3rv

Atli 182 Posting Pro · Answer 5 · 2009-11-07T21:37:59+00:00

Consider this code.
It has a form where a user enters the start and end date, and the PHP code compiles a XML file with all the data from a table that were created in that time-frame.

I created it for a MySQL database, but you can easily re-write the database part to fit your database. I just don't have anything else to test on my end.

<?php
if(isset($_POST['StartDate'], $_POST['EndDate']))
{
    $dbLink = new mysqli('localhost', 'usr', 'pwd', 'dbName');

    // Fetch the data
    $start = $dbLink->real_escape_string($_POST['StartDate']);
    $end = $dbLink->real_escape_string($_POST['EndDate']);
    $sql = "SELECT * FROM `news`
            WHERE `Created` BETWEEN '{$start}' AND '{$end}'";
    $result = $dbLink->query($sql);

    // Create the XML output
    $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n";
    if($result->num_rows > 0)
    {
        while($myrow = $result->fetch_assoc())
        {
            $output .= "\t<row>\n";
            foreach($myrow as $_name => $_value)
            {
                $output .= "\t\t<$_name>$_value</$_name>\n";
            }
            $output .= "\t</row>\n";
        }
    }
    $output .= "</root>";

    // Send it as a XML file for download
    header('content-type: text/xml');
    header('content-disposition: attachment; filename=data_export.xml');
    echo $output;
    exit;
}
else
{
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Data Export Thingie</title>
    </head>
    <body>
        <h1>Export some dataz!</h1>
        <form action="?" method="post">
            <label for="StartDate">Start Date:</label>
            <input type="text" name="StartDate" id="StartDate" value="YYYY-MM-DD HH:MM" /><br />
            <label for="EndDate">End Date:</label>
            <input type="text" name="EndDate" id="EndDate" value="YYYY-MM-DD HH:MM" /><br />
            <input type="submit" />
        </form>
    </body>
</html>
<?php
}
?>

Does that help at all?

jppr03 0 Newbie Poster · Answer 6 · 2009-11-09T15:51:48+00:00

Consider this code.
It has a form where a user enters the start and end date, and the PHP code compiles a XML file with all the data from a table that were created in that time-frame.

I created it for a MySQL database, but you can easily re-write the database part to fit your database. I just don't have anything else to test on my end.

<?php
if(isset($_POST['StartDate'], $_POST['EndDate']))
{
    $dbLink = new mysqli('localhost', 'usr', 'pwd', 'dbName');

    // Fetch the data
    $start = $dbLink->real_escape_string($_POST['StartDate']);
    $end = $dbLink->real_escape_string($_POST['EndDate']);
    $sql = "SELECT * FROM `news`
            WHERE `Created` BETWEEN '{$start}' AND '{$end}'";
    $result = $dbLink->query($sql);

    // Create the XML output
    $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n";
    if($result->num_rows > 0)
    {
        while($myrow = $result->fetch_assoc())
        {
            $output .= "\t<row>\n";
            foreach($myrow as $_name => $_value)
            {
                $output .= "\t\t<$_name>$_value</$_name>\n";
            }
            $output .= "\t</row>\n";
        }
    }
    $output .= "</root>";

    // Send it as a XML file for download
    header('content-type: text/xml');
    header('content-disposition: attachment; filename=data_export.xml');
    echo $output;
    exit;
}
else
{
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Data Export Thingie</title>
    </head>
    <body>
        <h1>Export some dataz!</h1>
        <form action="?" method="post">
            <label for="StartDate">Start Date:</label>
            <input type="text" name="StartDate" id="StartDate" value="YYYY-MM-DD HH:MM" /><br />
            <label for="EndDate">End Date:</label>
            <input type="text" name="EndDate" id="EndDate" value="YYYY-MM-DD HH:MM" /><br />
            <input type="submit" />
        </form>
    </body>
</html>
<?php
}
?>

Does that help at all?

i tried.. but it gives me an error on header line's.. and do not save..

Warning: Cannot modify header information - headers already sent by (output started at /home/gpss2009/public_html/_classes/historicodatas.php:7) in /home/gpss2009/public_html/_classes/historicodatas.php on line 107

Warning: Cannot modify header information - headers already sent by (output started at /home/gpss2009/public_html/_classes/historicodatas.php:7) in /home/gpss2009/public_html/_classes/historicodatas.php on line 108

any idea? this code when i click submit it open's a download box? i wanted that..

i would want something very basic.. like text file or so.. just want to click on that button and show's up a download box to save my SELECT'ed content to that txt file... don't need to be formated.. or anything... just want to download all stuff that i made from my Select instruction...

Atli 182 Posting Pro · Answer 7 · 2009-11-09T16:33:51+00:00

i tried.. but it gives me an error on header line's.. and do not save..

Those errors are shown when you use the header functions after adding to the output stream.

In other words; you can not use the header functions after printing anything from PHP, or from inside a HTML file. Even a single white-space before the opening <?php line in my example code will produce that error.

For example, the following two examples will produce your error:

<?php
    echo "Redirecting you to Google in 3 seconds...";
    header('Refresh: 3; url=http://www.google.com');
?>

<p>Redirecting you to Google in 3 seconds...</p>
<?php
    header('Refresh: 3; url=http://www.google.com');
?>

While this one will work:

<?php
    header('Refresh: 3; url=http://www.google.com');
    echo "Redirecting you to Google in 3 seconds...";
?>

this code when i click submit it open's a download box? i wanted that..

Yes. It sends you the data as a XML formatted file, which Excell should be able to work with.

jppr03 0 Newbie Poster · Answer 8 · 2009-11-09T19:24:58+00:00

my page code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Projecto</title>
<meta content="text/html; charset=iso-8859-1" http-equiv="Content-Type" />
<meta name="description" content="FW MX DW MX HTML" />
<script language="JavaScript" type="text/JavaScript"> 
<!-- 
function MM_reloadPage(init) { //reloads the window if Nav4 resized 
if (init==true) with (navigator) {if ((appName=="Netscape")&&(parseInt(appVersion)==4)) { 
document.MM_pgW=innerWidth; document.MM_pgH=innerHeight; onresize=MM_reloadPage; }} 
else if (innerWidth!=document.MM_pgW || innerHeight!=document.MM_pgH) location.reload(); 
} 
MM_reloadPage(true); 
//--> 
</script>
<link rel="stylesheet" type="text/css" href="ssheet1.css" />
<meta name="GENERATOR" content="MSHTML 8.00.6001.18813" />
</head>
<body topmargin="50" bgcolor="#666666" marginheight="50">
<div align="center"><table border="0" cellspacing="0" cellpadding="0" width="700">
<tbody>
<tr><!-- Shim row, height 1. -->
<td width="700"><img border="0" alt="" width="700" height="1" src="../images/spacer.gif" /></td>
<td><img border="0" alt="" width="1" height="1" src="../images/spacer.gif" /></td></tr>
<tr><!-- row 1 -->
<td><img border="0" name="about_r1_c1" alt="" width="700" height="36" usemap="#about_r1_c1Map" src="../images/about_r1_c1.bmp" /></td>
<td><img border="0" alt="" width="1" height="36" src="../images/spacer.gif" /></td></tr>
<tr><!-- row 2 -->
<td><img border="0" name="about_r2_c1" alt="" width="700" height="15" src="../images/about_r2_c1.gif" /></td>
<td><img border="0" alt="" width="1" height="15" src="../images/spacer.gif" /></td></tr>
<tr><!-- row 3 -->
<td><img border="0" name="about_r3_c1" alt="" width="700" height="12" src="../images/about_r3_c1.gif" /></td>
<td><img border="0" alt="" width="1" height="12" src="../images/spacer.gif" /></td></tr>
<tr><!-- row 4 -->
<td height="336" align="center" valign="top" background="../images/about_r3_c1.gif">
<blockquote class="maintxt">
<div>
  <p><img src="../images/logotipo.bmp" width="115" height="86" align="left"><br>
    <br>
    </p>
  <p>&nbsp;</p>
  <p><span class="heading"><strong><font size="5">Escolha a data</font></strong></span>
    </br><br>
  </p>
  </br>

  
<?

function __autoload($classe){
		require_once "../_classes/".$classe.".php";	
	}
	
	$db = new Banco();
	
	//Recebe os parametros passados por GET
	$id =  $_REQUEST['id'];
	
	$rs = $db->Query("SELECT * 
			 FROM cliente, localizacao                          
             WHERE clien_id = '$id' AND  (clien_id = local_clien_id)");

	$row = pg_fetch_assoc($rs);	
		
	if(isset($_POST['btnSubmit']) && $_POST['btnSubmit'])
	{
		$dataf = $_POST['fData'];
		$datai = $_POST['iData'];
		
		?>
        <SCRIPT LANGUAGE="JavaScript">
                      window.location="mostrarhistorico.php?id=<?php echo $id;?>&iData=<?php echo $datai;?>&fData=<?php echo $dataf;?>";
        </script>
        <?php	
	}

	if(isset($_POST['btnGravar']) && $_POST['btnGravar'])
	{
		$dataf = $_POST['fData'];
		$datai = $_POST['iData'];
		
		$rsa = $db->Query("SELECT * FROM localizacao WHERE (local_clien_id='$id') AND
						 ((data>=to_date('$datai', 'dd/mm/yyyy')) AND 
						 (data<=to_date('$dataf', 'dd/mm/yyyy')+1))
				          ORDER BY local_id asc");
						  
		$rowa = pg_fetch_assoc($rsa);
		
		// Create the XML output
		$output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n";
		if(pg_num_rows($rsa)>0)
		{
			while($myrow = pg_fetch_assoc($rsa))
			{
				$output .= "\t<row>\n";
				foreach($myrow as $_name => $_value)
				{
					$output .= "\t\t<$_name>$_value</$_name>\n";
				}
				$output .= "\t</row>\n";
			}
		}
		$output .= "</root>";

		// Send it as a XML file for download
		header('content-type: text/xml');
		header('content-disposition: attachment; filename=data_export.xml');
		echo $output;
		exit;
	}
?>   
	        </div>
        <form action="historicodatas.php" method="post">
          <div align="center">
           <table width="374">				

                <tr><td width="106"><strong>Data Inicial: &nbsp;&nbsp;&nbsp;&nbsp;</strong></td><td width="256">&nbsp;&nbsp;<input type="text" name="iData" value="<?=$_REQUEST["iData"]?>"  />
                   (dd/mm/yyyy)</td></tr>
                                 <tr><td width="106"><strong>Data Final: &nbsp;&nbsp;&nbsp;&nbsp;</strong></td><td width="256">&nbsp;&nbsp;<input type="text" name="fData" value="<?=$_REQUEST["fData"]?>"  />
                   (dd/mm/yyyy)</td></tr>
				<input type="hidden" id="id" name="id" value="<?=$_REQUEST["id"]?>" />
			<tr>			
			</table>
            <br>
            <table width="282">
              <tr>
              <td width="146">&nbsp;<input type="submit" value="Pesquisar" name="btnSubmit" id="btnSubmit" /></td>
			  <td width="156">&nbsp;<input type="submit" value="Gravar" name="btnGravar" id="btnGravar" /></td>
			  <td width="124"><input type="button" value="Voltar Atras" onclick="location.href='http://gpssafety.heliohost.org/_classes/historico.php'" /></td>
              </tr>
            </table>
            </br> 
          </div>
        </form>

</blockquote></td>
<td><img border="0" alt="" width="1" height="205" src="../images/spacer.gif" /></td></tr>
<tr><!-- row 5 -->
<td><img border="0" name="about_r5_c1" alt="" width="700" height="28" src="../images/about_r5_c1.gif" /></td>
<td><img border="0" alt="" width="1" height="28" src="../images/spacer.gif" /></td></tr></tbody></table>
<table>
<tbody>
<tr>
<td colspan="3">
<div></div></center></td></tr></tbody></table></div>
<map name="about_r1_c1Map">
<area shape="rect" alt="Principal" target="_self" coords="22,16,84,33" href="http://gpssafety.heliohost.org/" />
<area shape="rect" alt="Info Cliente" target="_self" coords="169,16,211,32" href="cliente.php" />
<area shape="rect" alt="Criar Cliente" target="_self" coords="216,16,248,32" href="criaruser.php" />
<area shape="rect" alt="Apagar Dispositivo" target="_self" coords="432,16,474,34" href="apagardispositivo.php" />
<area shape="rect" alt="Criar Dispositivo" target="_self" coords="393,16,422,33" href="criardispositivo.php" />
<area shape="rect" alt="Info Dispositivo" target="_self" coords="323,16,381,33" href="dispositivo.php" />
<area shape="rect" alt="Apagar Cliente" target="_self" coords="255,16,301,32" href="apagaruser.php" />
<area shape="rect" alt="Localizacoes" target="_self" coords="498,17,567,33" href="mostrarlocalizacoes.php" />
<area shape="rect" alt="Actual" target="_self" coords="574,17,618,34" href="localactual.php" />
<area shape="rect" alt="Historico" target="_self" coords="628,17,681,34" href="historico.php" /></map>
<style>
BODY {SCROLLBAR-FACE-COLOR: #95A193; SCROLLBAR-HIGHLIGHT-COLOR: #95A193; SCROLLBAR-SHADOW-COLOR: #999999; SCROLLBAR-3DLIGHT-COLOR: #FFFFFF; SCROLLBAR-ARROW-COLOR: #FFFFFF; SCROLLBAR-TRACK-COLOR: #666666; SCROLLBAR-DARKSHADOW-COLOR: #666666; }</style>
</body>
</html>
</html>

Error message:

Warning: Cannot modify header information - headers already sent by (output started at /home/gpss2009/public_html/_classes/historicodatas.php:7) in /home/gpss2009/public_html/_classes/historicodatas.php on line 107

Warning: Cannot modify header information - headers already sent by (output started at /home/gpss2009/public_html/_classes/historicodatas.php:7) in /home/gpss2009/public_html/_classes/historicodatas.php on line 108

what should i do? i don't have white spaces i think..

jppr03 0 Newbie Poster · Answer 9 · 2009-11-09T20:31:26+00:00

Ok i finnaly got it.. i changed it to the begging.. it must be before any html code..
now it works fine..

thanks a lot Atli...

only could you give me some help in not showing the collumn name's but a name that i choose?

<local_id>26</local_id>
<latitude>38.5206816</latitude>
<longitude>-9.0205233</longitude>

it save's like this..
i wanted something like

ID: 26
Lat: 38.5206816
Long: -9.0205233

it's on this part of the code i must do that.. but how? :S

while($myrow = pg_fetch_assoc($rsa))
			{
				$output .= "\t<row>\n";
				foreach($myrow as $_name => $_value)
				{
					$output .= "\t\t<$_name>$_value</$_name>\n";
				}
				$output .= "\t</row>\n";
			}

thanks a lot again

Atli 182 Posting Pro · Answer 10 · 2009-11-10T00:06:00+00:00

Sure. You can just swap out the column name for an alias.
Like:

// A list of column names and their aliases.
$aliases = array(
#   'column_name'   => 'alias',
    'local_id'      => 'ID',
    'latitude'      => 'Lat',
    'longitude'     => 'Long'
);

while($myrow = pg_fetch_assoc($rsa))
{
    $output .= "\t<row>\n";
    foreach($myrow as $_name => $_value)
    {
        // Try to use an alias for this column name from the $aliases array. 
        // If there is no alias for this column, just use the column name itself.
        ($name = @$aliases[$_name]) or $name = $_name;
        
        $output .= "\t\t<{$name}>{$_value}</{$name}>\n";
    }
    $output .= "\t</row>\n";
}

jppr03 0 Newbie Poster · Answer 11 · 2009-11-10T00:27:22+00:00

thanks a lot for your help Atli

i really appreciate this forum and people who help here.. ;)

have a nice day!