Need help with exporting data to csv

raymak · May 25, 2014

Hello,

I created a web page that executes request from mysql and prints it on web page. I also created a export submit button to export mysql query in csv format file. When I select values and then click on export buttons, I get nothing and page gets refreshed.

Here is the link of my site: rahil.me/index.php

Here is the code:

These are the buttons:

<input type="submit" name="search" value="Search">
<input type="submit" name="export" value="Export" />

PHP code:

if (isset($_POST['export'])) {
			
	if (empty($_POST['service'])) {

            echo "Please select service in dropdown" . "</br>";

            }

        else {

            $service = $_POST['service'];

             }

         if (empty($_POST['environment'])) {

             echo "Please select Environment in dropdown" . "</br>";

             }

         else {

             $env = $_POST['environment'];

              }

        if ((!empty($service)) && (!empty($env))) {
				
	     $sql="SQLQUERY REMOVE";

	if (!mysqli_query($con,$sql)) {
	    die('Error: ' . mysqli_error($con));
	    }

		$result = mysqli_query($con,$sql);

				/*
				 * send response headers to the browser
				 * following headers instruct the browser to treat the data as a csv file called export.csv
				 */

				header('Content-Type: text/csv');
				header('Content-Disposition: attachment;filename=export.csv');

				/*
				 * output header row (if atleast one row exists)
				 */

				$row = mysqli_fetch_assoc($result);
				if ($row) {
					echocsv(array_keys($row));
				}

				/*
				 * output data rows (if atleast one row exists)
				 */

				while ($row) {
					echocsv($row);
					$row = mysqli_fetch_assoc($result);
				}

				/*
				 * echo the input array as csv data maintaining consistency with most CSV implementations
				 * - uses double-quotes as enclosure when necessary
				 * - uses double double-quotes to escape double-quotes 
				 * - uses CRLF as a line separator
				 */

				function echocsv($fields)
				{
					$separator = '';
					foreach ($fields as $field) {
						if (preg_match('/\\r|\\n|,|"/', $field)) {
							$field = '"' . str_replace('"', '""', $field) . '"';
						}
						echo $separator . $field;
						$separator = ',';
					}
					echo "\r\n";
				}
			
			}
			}

Edited May 25, 2014 by raymak

Ch0cu3r · May 25, 2014

Why are you executing the query twice here

if (!mysqli_query($con,$sql)) {
	die('Error: ' . mysqli_error($con));
}

$result = mysqli_query($con,$sql);

You only need to execute it once

$result = mysqli_query($con,$sql));

// check that the query executed
if(!$result))
    die('Error: ' . mysqli_error($con)); // will be better of using trigger_error instead

Is this code used to generate the headings for each column in the csv?

				/*
				 * output header row (if atleast one row exists)
				 */

				$row = mysqli_fetch_assoc($result);
				if ($row) {
					echocsv(array_keys($row));
				}

Instead you should use mysqli_fetch_fields, otherwise you will loose out on the first row

$headers = array();
foreach(mysqli_fetch_fields($result) as $field)
    $headers[] = $field->name;

echocsv($headers);

You're using the while loop incorrectly here.

				/*
				 * output data rows (if atleast one row exists)
				 */

				while ($row) {
					echocsv($row);
					$row = mysqli_fetch_assoc($result);
				}

mysqli_fetch_assoc should be used as the condition

				while (row = mysqli_fetch_row($result)) {
					echocsv($row);
				}

Edited May 25, 2014 by Ch0cu3r

raymak · May 25, 2014

Ch0cu3r,

Thanks for your reply. I modified the code as per your suggestion. Both old and new code work same. However, when I save the code in separate file, I am able to download csv to my computer, but when I add that code to index.php file where user will click on export button and execute the code, it doesnt work. It just refreshes the page upon the click.

Below code is added in index.php file.

if (isset($_POST['export'])) {

	if (empty($_POST['service'])) {

		echo "Please select service in dropdown" . "</br>";

	}

	else {

		$service = $_POST['service'];

	}

	if (empty($_POST['environment'])) {

		echo "Please select Environment in dropdown" . "</br>";

	}

	else {

		$env = $_POST['environment'];

	}

	if ((!empty($service)) && (!empty($env))) {
	
	$sql="SELECT Query Removed";

	$result = mysqli_query($con,$sql);
	
	if (!result) {
	
		die('Error: ' . mysqli_error($con));
	
	}

	/*
	 * send response headers to the browser
	 * following headers instruct the browser to treat the data as a csv file called export.csv
	 */
	 
	header('Content-Type: text/csv');
	header('Content-Disposition: attachment;filename=export.csv');

	/*
	 * output header row
	 */
	 
	 $headers = array();
	 foreach (mysqli_fetch_fields($result) as $field) {
		$headers[] = $field->name;
	 }
	 
	 echocsv($headers);
	 
	/*
	 * echo the input array as csv data maintaining consistency with most CSV implementations
	 * - uses double-quotes as enclosure when necessary
	 * - uses double double-quotes to escape double-quotes 
	 * - uses CRLF as a line separator
	 */
	
	while ($row = mysqli_fetch_row($result)) {
	
		echocsv($row);
	
	}
	 
	function echocsv($fields)
	{
		$separator = '';
		foreach ($fields as $field) {
			if (preg_match('/\\r|\\n|,|"/', $field)) {
				$field = '"' . str_replace('"', '""', $field) . '"';
			}
			echo $separator . $field;
			$separator = ',';
		}
		echo "\r\n";
	}
}
}

Edited May 25, 2014 by raymak

Ch0cu3r · May 26, 2014

Weird it seems for some reason the script is not receiving the POST data.

What is the output of

printf('<pre>%s</pre>', print_r($_POST, true));

raymak · May 26, 2014

So do I need to put the above code inside if isset statement? Or all the way at the end of the code?

raymak · May 26, 2014

I put it inside if isset statement, and here is the result:

Array
(
[service] => 3
[environment] => 1
[status] => Active
[export] => Export
)

Ch0cu3r · May 26, 2014

Okay, so the POST data is reaching index.php but the problem then is your code posted in post #3 is not being ran at all it seems. There is most likely a flaw in your codes logic, can you post (or attach) index.php in full so we can take a look further.

raymak · May 26, 2014

Here is my index.php code:

<?php include('inc/header.php'); ?>
    <div id="results">
        <?php
            include('dbcon.php');
        ?>
        <form id="searchform" action="index.php" method="post">
	    <h1>Search</h1>
	    <div id="service">
                <label for="service">Service:</label>
                    <select id="service" name="service" class="searchoption">
                    <option value="">-- Select Service Name --</option>
                    <?php
                        $resultservice = mysqli_query($con,"Select * from services") ?>
                    <?php
                         while ($line = mysqli_fetch_array($resultservice)) {
                    ?>

                    <option value="<?php echo $line['serviceid'];?>"> <?php echo $line['service'];?> </option>

                    <?php
                    }
                    ?>
                    </select> <br />
	    </div>
	    <div id="env">
               <label for="environment">Environment:</label>
                   <select id="environment" name="environment" class="searchoption">
                   <option value="">-- Select Environment --</option>
                   <?php
                       $resultdomain = mysqli_query($con,"Select * from evn") ?>
                   <?php
                       while ($line = mysqli_fetch_array($resultdomain)) {
                    ?>

                   <option value="<?php echo $line['envid'];?>"> <?php echo $line['env'];?> </option>

                   <?php
                   }
                   ?>
                   </select><br />
	    </div>
	    <div id="status">
                <label for="status">Status:</label>
                    <select id="status" name="status" class="searchoption">
                    <option value="Active">Active</option>
                    <option value="Inactive">Inactive</option>
                    </select><br />
	    </div>
	    <div id="button">
                <input type="reset" name="reset">
                <input type="submit" name="search" value="Search">
				<input type="submit" name="export" value="Export" />
	    </div>
        </form>
	<?php

            if (isset($_POST['search'])) {

                if (empty($_POST['service'])) { ?>
				
					<div id="message">

                    <?php echo "Please select service in dropdown" . "</br>"; ?>
					
					</div>
            <?php }
			
                else {
				
                    $service = $_POST['service'];
				
                 }

                if (empty($_POST['environment'])) { ?>

					<div id="message">		
					
               	    <?php echo "Please select Environment in dropdown" . "</br>"; ?>
					
					</div>
				
                <?php }

                else {

                    $env = $_POST['environment'];

                }

                if ((!empty($service)) && (!empty($env))) {
		
				$sql="SELECT servers.ServerName, servers.alias, servers.IPAddress, vip.vipaddress, evn.env, domains.domainname, services.service, tier.tier, os.os, servers.platform, servers.virtualenv, url.url, servers.status FROM servers.servers LEFT OUTER JOIN servers.vip ON (servers.vipaddress = vip.vipid) LEFT OUTER JOIN servers.evn ON (servers.environment = evn.envid) LEFT OUTER JOIN servers.domains ON (servers.Domain = domains.domainid) LEFT OUTER JOIN servers.services ON (servers.service = services.serviceid) LEFT OUTER JOIN servers.tier ON (servers.tier = tier.tierid) LEFT OUTER JOIN servers.os ON (servers.os = os.osid) LEFT OUTER JOIN servers.url ON (servers.endpointurl = url.urlid) where servers.environment = $env and servers.service = $service and servers.status = '$_POST[status]'";

                if (!mysqli_query($con,$sql)) {
				die('Error: ' . mysqli_error($con));
				}

                $mydata = mysqli_query($con,$sql);
                $rowcount = mysqli_num_rows($mydata);

                if ($rowcount >= 1) {
                echo "<table id='main-data-table' border=1>
                  <tr>
                        <th> + </th>
                        <th>Server Name</th>
                        <th>IP Address</th>
                        <th>Service</th>
                        <th>Tier</th>
                  </tr>";
                while ($record = mysqli_fetch_array($mydata)) {
                echo "<tr class='main mainrow'>";
                echo "<td><a href='#' class='main'>+</a></td>";
                echo "<td>" . $record['ServerName'] . "</td>";
                echo "<td>" . $record['IPAddress'] . "</td>";
				echo "<td>" . $record['service'] . "</td>";
                echo "<td>" . $record['tier'] . "</td>";
                echo "</tr>";

                //echo "<table border=1>";
                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Server . Name . "</td>";
                echo "<td>" . $record['ServerName'] . "</td>";
                echo "<td class='innerleftcol'>" . IP . Address . "</td>";
                echo "<td>" . $record['IPAddress'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Alias . "</td>";
                echo "<td>" . $record['alias'] . "</td>";
                echo "<td class='innerleftcol'>" . Environment . "</td>";
                echo "<td>" . $record['env'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . VIP . Address . "</td>";
                echo "<td>" . $record['vipaddress'] . "</td>";
                echo "<td class='innerleftcol'>" . Domain . Name . "</td>";
                echo "<td>" . $record['domainname'] . "</td>";
                echo "</tr>";
		
                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Service . "</td>";
                echo "<td>" . $record['service'] . "</td>";
                echo "<td class='innerleftcol'>" . Tier . "</td>";
                echo "<td>" . $record['tier'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . OS . "</td>";
                echo "<td>" . $record['os'] . "</td>";
                echo "<td class='innerleftcol'>" . EndPoint . "</td>";
                echo "<td>" . $record['url'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Platform . "</td>";
                echo "<td>" . $record['platform'] . "</td>";
                echo "<td class='innerleftcol'>" . Virtual . "</td>";
                echo "<td>" . $record['virtualenv'] . "</td>";
                echo "</tr>";
                }
                echo "</table>";
                ?>
				<div id="message">
				<?php
					echo "$rowcount record/s found";
				?>
				</div>
				<?php
				}
				else {
				?>
				<div id="message">
					<?php echo "No records found"; ?>
				</div>
				<?php
				}

				}
				else {

				exit();

				}

				mysqli_close($con);
				}
				
			if (isset($_POST['export'])) {
			printf('<pre>%s</pre>', print_r($_POST, true));
                if (empty($_POST['service'])) { ?>
				
					<div id="message">

                    <?php echo "Please select service in dropdown" . "</br>"; ?>
					
					</div>
            <?php }
			
                else {
				
                    $service = $_POST['service'];
				
                 }

                if (empty($_POST['environment'])) { ?>

					<div id="message">		
					
               	    <?php echo "Please select Environment in dropdown" . "</br>"; ?>
					
					</div>
				
                <?php }

                else {

                    $env = $_POST['environment'];

                }

                if ((!empty($service)) && (!empty($env))) {

				$sql="SELECT servers.ServerName, servers.alias, servers.IPAddress, vip.vipaddress, evn.env, domains.domainname, services.service, tier.tier, os.os, servers.platform, servers.virtualenv, url.url, servers.status FROM servers.servers LEFT OUTER JOIN servers.vip ON (servers.vipaddress = vip.vipid) LEFT OUTER JOIN servers.evn ON (servers.environment = evn.envid) LEFT OUTER JOIN servers.domains ON (servers.Domain = domains.domainid) LEFT OUTER JOIN servers.services ON (servers.service = services.serviceid) LEFT OUTER JOIN servers.tier ON (servers.tier = tier.tierid) LEFT OUTER JOIN servers.os ON (servers.os = os.osid) LEFT OUTER JOIN servers.url ON (servers.endpointurl = url.urlid) where servers.environment = $env and servers.service = $service and servers.status = '$_POST[status]'";
				
				$result = mysqli_query($con,$sql);
				
				if (!result) {
				
					die('Error: ' . mysqli_error($con));
				
				}

				/*
				 * send response headers to the browser
				 * following headers instruct the browser to treat the data as a csv file called export.csv
				 */
				 
				header('Content-Type: text/csv');
				header('Content-Disposition: attachment;filename=export.csv');			



				/*
				 * output header row (if atleast one row exists)
				 */
				 
				 $headers = array();
				 foreach (mysqli_fetch_fields($result) as $field) {
					$headers[] = $field->name;
				 }
				 
				 echocsv($headers);

				/*
				 * echo the input array as csv data maintaining consistency with most CSV implementations
				 * - uses double-quotes as enclosure when necessary
				 * - uses double double-quotes to escape double-quotes 
				 * - uses CRLF as a line separator
				 */
				
				while ($row = mysqli_fetch_row($result)) {
				
					echocsv($row);
				
				}
				 
				function echocsv($fields)
				{
					$separator = '';
					foreach ($fields as $field) {
						if (preg_match('/\\r|\\n|,|"/', $field)) {
							$field = '"' . str_replace('"', '""', $field) . '"';
						}
						echo $separator . $field;
						$separator = ',';
					}
					echo "\r\n";
				}				
			
			}
			}

				
    ?>
    </div>
<?php include('inc/footer.php'); ?>

Ch0cu3r · May 26, 2014

Okay, took a quick look at your code and I think the problem is with the call to header() on line 245 and 246. You cannot use header() after any output has been sent to the browser. When PHP get to this point it is most probably halted execution of script. If you enable error reporting it'll most likely spit out a Headers already sent error message.

What you need to do is move the code responsible for exporting the data to csv (lines 195 to 289) so it is before any output. example

<?php
include('dbcon.php');

/* move code responsible for exporting data to csv on lines 195 to 289 to here, so it is before the line below */

include('inc/header.php'); ?>
    <div id="results">
        <form id="searchform" action="" method="post">
...

EDIT: Notice moved the include for dbcon.php so it before the export csv code. You will need to do that otherwise your queries will fail :tease-03:

Edited May 26, 2014 by Ch0cu3r

raymak · May 26, 2014

Thanks for your quick response.

I did apply the changes as per your suggestion. However, when I click on export button, it shows me page cannot be disabled.

Here is the modified code:

<?php
include('dbcon.php');
	if (isset($_POST['export'])) {
		if (empty($_POST['service'])) { ?>
		
			<div id="message">

			<?php echo "Please select service in dropdown" . "</br>"; ?>
			
			</div>
	<?php }
	
		else {
		
			$service = $_POST['service'];
		
		 }

		if (empty($_POST['environment'])) { ?>

			<div id="message">		
			
			<?php echo "Please select Environment in dropdown" . "</br>"; ?>
			
			</div>
		
		<?php }

		else {

			$env = $_POST['environment'];

		}

		if ((!empty($service)) && (!empty($env))) {

		$sql="SELECT servers.ServerName, servers.alias, servers.IPAddress, vip.vipaddress, evn.env, domains.domainname, services.service, tier.tier, os.os, servers.platform, servers.virtualenv, url.url, servers.status FROM servers.servers LEFT OUTER JOIN servers.vip ON (servers.vipaddress = vip.vipid) LEFT OUTER JOIN servers.evn ON (servers.environment = evn.envid) LEFT OUTER JOIN servers.domains ON (servers.Domain = domains.domainid) LEFT OUTER JOIN servers.services ON (servers.service = services.serviceid) LEFT OUTER JOIN servers.tier ON (servers.tier = tier.tierid) LEFT OUTER JOIN servers.os ON (servers.os = os.osid) LEFT OUTER JOIN servers.url ON (servers.endpointurl = url.urlid) where servers.environment = $env and servers.service = $service and servers.status = '$_POST[status]'";
		
		$result = mysqli_query($con,$sql);
		
		if (!result) {
		
			die('Error: ' . mysqli_error($con));
		
		}

		/*
		 * send response headers to the browser
		 * following headers instruct the browser to treat the data as a csv file called export.csv
		 */
		 
		header('Content-Type: text/csv');
		header('Content-Disposition: attachment;filename=export.csv');			



		/*
		 * output header row (if atleast one row exists)
		 */
		 
		 $headers = array();
		 foreach (mysqli_fetch_fields($result) as $field) {
			$headers[] = $field->name;
		 }
		 
		 echocsv($headers);

		/*
		 * echo the input array as csv data maintaining consistency with most CSV implementations
		 * - uses double-quotes as enclosure when necessary
		 * - uses double double-quotes to escape double-quotes 
		 * - uses CRLF as a line separator
		 */
		
		while ($row = mysqli_fetch_row($result)) {
		
			echocsv($row);
		
		}
		 
		function echocsv($fields)
		{
			$separator = '';
			foreach ($fields as $field) {
				if (preg_match('/\\r|\\n|,|"/', $field)) {
					$field = '"' . str_replace('"', '""', $field) . '"';
				}
				echo $separator . $field;
				$separator = ',';
			}
			echo "\r\n";
		}				
	
	}
	}
?>

<?php include('inc/header.php'); ?>
    <div id="results">
        <form id="searchform" action="index.php" method="post">
	    <h1>Search</h1>
	    <div id="service">
                <label for="service">Service:</label>
                    <select id="service" name="service" class="searchoption">
                    <option value="">-- Select Service Name --</option>
                    <?php
                        $resultservice = mysqli_query($con,"Select * from services") ?>
                    <?php
                         while ($line = mysqli_fetch_array($resultservice)) {
                    ?>

                    <option value="<?php echo $line['serviceid'];?>"> <?php echo $line['service'];?> </option>

                    <?php
                    }
                    ?>
                    </select> <br />
	    </div>
	    <div id="env">
               <label for="environment">Environment:</label>
                   <select id="environment" name="environment" class="searchoption">
                   <option value="">-- Select Environment --</option>
                   <?php
                       $resultdomain = mysqli_query($con,"Select * from evn") ?>
                   <?php
                       while ($line = mysqli_fetch_array($resultdomain)) {
                    ?>

                   <option value="<?php echo $line['envid'];?>"> <?php echo $line['env'];?> </option>

                   <?php
                   }
                   ?>
                   </select><br />
	    </div>
	    <div id="status">
                <label for="status">Status:</label>
                    <select id="status" name="status" class="searchoption">
                    <option value="Active">Active</option>
                    <option value="Inactive">Inactive</option>
                    </select><br />
	    </div>
	    <div id="button">
                <input type="reset" name="reset">
                <input type="submit" name="search" value="Search">
				<input type="submit" name="export" value="Export" />
	    </div>
        </form>
	<?php

            if (isset($_POST['search'])) {

                if (empty($_POST['service'])) { ?>
				
					<div id="message">

                    <?php echo "Please select service in dropdown" . "</br>"; ?>
					
					</div>
            <?php }
			
                else {
				
                    $service = $_POST['service'];
				
                 }

                if (empty($_POST['environment'])) { ?>

					<div id="message">		
					
               	    <?php echo "Please select Environment in dropdown" . "</br>"; ?>
					
					</div>
				
                <?php }

                else {

                    $env = $_POST['environment'];

                }

                if ((!empty($service)) && (!empty($env))) {
		
				$sql="SELECT servers.ServerName, servers.alias, servers.IPAddress, vip.vipaddress, evn.env, domains.domainname, services.service, tier.tier, os.os, servers.platform, servers.virtualenv, url.url, servers.status FROM servers.servers LEFT OUTER JOIN servers.vip ON (servers.vipaddress = vip.vipid) LEFT OUTER JOIN servers.evn ON (servers.environment = evn.envid) LEFT OUTER JOIN servers.domains ON (servers.Domain = domains.domainid) LEFT OUTER JOIN servers.services ON (servers.service = services.serviceid) LEFT OUTER JOIN servers.tier ON (servers.tier = tier.tierid) LEFT OUTER JOIN servers.os ON (servers.os = os.osid) LEFT OUTER JOIN servers.url ON (servers.endpointurl = url.urlid) where servers.environment = $env and servers.service = $service and servers.status = '$_POST[status]'";

                if (!mysqli_query($con,$sql)) {
				die('Error: ' . mysqli_error($con));
				}

                $mydata = mysqli_query($con,$sql);
                $rowcount = mysqli_num_rows($mydata);

                if ($rowcount >= 1) {
                echo "<table id='main-data-table' border=1>
                  <tr>
                        <th> + </th>
                        <th>Server Name</th>
                        <th>IP Address</th>
                        <th>Service</th>
                        <th>Tier</th>
                  </tr>";
                while ($record = mysqli_fetch_array($mydata)) {
                echo "<tr class='main mainrow'>";
                echo "<td><a href='#' class='main'>+</a></td>";
                echo "<td>" . $record['ServerName'] . "</td>";
                echo "<td>" . $record['IPAddress'] . "</td>";
				echo "<td>" . $record['service'] . "</td>";
                echo "<td>" . $record['tier'] . "</td>";
                echo "</tr>";

                //echo "<table border=1>";
                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Server . Name . "</td>";
                echo "<td>" . $record['ServerName'] . "</td>";
                echo "<td class='innerleftcol'>" . IP . Address . "</td>";
                echo "<td>" . $record['IPAddress'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Alias . "</td>";
                echo "<td>" . $record['alias'] . "</td>";
                echo "<td class='innerleftcol'>" . Environment . "</td>";
                echo "<td>" . $record['env'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . VIP . Address . "</td>";
                echo "<td>" . $record['vipaddress'] . "</td>";
                echo "<td class='innerleftcol'>" . Domain . Name . "</td>";
                echo "<td>" . $record['domainname'] . "</td>";
                echo "</tr>";
		
                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Service . "</td>";
                echo "<td>" . $record['service'] . "</td>";
                echo "<td class='innerleftcol'>" . Tier . "</td>";
                echo "<td>" . $record['tier'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . OS . "</td>";
                echo "<td>" . $record['os'] . "</td>";
                echo "<td class='innerleftcol'>" . EndPoint . "</td>";
                echo "<td>" . $record['url'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Platform . "</td>";
                echo "<td>" . $record['platform'] . "</td>";
                echo "<td class='innerleftcol'>" . Virtual . "</td>";
                echo "<td>" . $record['virtualenv'] . "</td>";
                echo "</tr>";
                }
                echo "</table>";
                ?>
				<div id="message">
				<?php
					echo "$rowcount record/s found";
				?>
				</div>
				<?php
				}
				else {
				?>
				<div id="message">
					<?php echo "No records found"; ?>
				</div>
				<?php
				}

				}
				else {

				exit();

				}

				mysqli_close($con);
				}				
    ?>
    </div>
<?php include('inc/footer.php'); ?>

Ch0cu3r · May 26, 2014

First comment out the two header lines on 52 and 53. Next enable error reporting, change the first lines of you script to this

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

include('dbcon.php')

if (isset($_POST['export'])) {
...

What output do you get now?

raymak · May 26, 2014

After enabling reporting.

Here is the error I get:

Warning: main(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/vhost/www/rahil.me/public_html/index.php on line 45

Notice: Use of undefined constant result - assumed 'result' in /home/vhost/www/rahil.me/public_html/index.php on line 45

Warning: main(): It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected the timezone 'UTC' for now, but please set date.timezone to select your timezone. in /home/vhost/www/rahil.me/public_html/index.php on line 70

Fatal error: Call to undefined function echocsv() in /home/vhost/www/rahil.me/public_html/index.php on line 70

Ch0cu3r · May 26, 2014

On line 45 you have left off the $ before result

if (!$result) {

As for the last error move the function definition so it comes before the ~~while loop~~ lines 66 ( echocsv($headers); )

For the the timezone error(s) that can solved by defining a default timezone for the date.timezone setting in the php.ini. If you dont have access to the php.ini then I think you can safely ignore it. But if you're doing anything with dates in your code then you will need to define a default timezone before using any date based functions.

Edited May 26, 2014 by Ch0cu3r

ginerjm · May 26, 2014

You need to establish the proper timezone for your appl. Add this at the top of every script:

date_default_timezone_set("America/New_York")

Use the appropriate name for your own timezone. Look here:

http://www.php.net/manual/en/timezones.php

raymak · May 26, 2014

I updated PHP.INI file with correct timezone.

I also modified the code as per Ch0cu3r suggestion on post 13. And now I get below error.

Fatal error: Call to undefined function echocsv() in /home/vhost/www/rahil.me/public_html/index.php on line 70

ginerjm · May 26, 2014

What do you think that message is telling you????

Ch0cu3r · May 26, 2014

@ginerjm if you look at the OP's code (post #10) you'll see the function is actually being defined

@raymak What line is line 70? And what line is that function defined?

Edited May 26, 2014 by Ch0cu3r

raymak · May 26, 2014

RIght now on line 70, this code is defined:

echocsv($headers);

Here is the full code after all the modification:

<?php

ini_set('display_errors', 1);
error_reporting(E_ALL);

include('dbcon.php');
	if (isset($_POST['export'])) {
		if (empty($_POST['service'])) { ?>
		
			<div id="message">

			<?php echo "Please select service in dropdown" . "</br>"; ?>
			
			</div>
	<?php }
	
		else {
		
			$service = $_POST['service'];
		
		 }

		if (empty($_POST['environment'])) { ?>

			<div id="message">		
			
			<?php echo "Please select Environment in dropdown" . "</br>"; ?>
			
			</div>
		
		<?php }

		else {

			$env = $_POST['environment'];

		}

		if ((!empty($service)) && (!empty($env))) {

		$sql="SELECT servers.ServerName, servers.alias, servers.IPAddress, vip.vipaddress, evn.env, domains.domainname, services.service, tier.tier, os.os, servers.platform, servers.virtualenv, url.url, servers.status FROM servers.servers LEFT OUTER JOIN servers.vip ON (servers.vipaddress = vip.vipid) LEFT OUTER JOIN servers.evn ON (servers.environment = evn.envid) LEFT OUTER JOIN servers.domains ON (servers.Domain = domains.domainid) LEFT OUTER JOIN servers.services ON (servers.service = services.serviceid) LEFT OUTER JOIN servers.tier ON (servers.tier = tier.tierid) LEFT OUTER JOIN servers.os ON (servers.os = os.osid) LEFT OUTER JOIN servers.url ON (servers.endpointurl = url.urlid) where servers.environment = $env and servers.service = $service and servers.status = '$_POST[status]'";
		
		$result = mysqli_query($con,$sql);
		
		if (!$result) {
		
			die('Error: ' . mysqli_error($con));
		
		}

		/*
		 * send response headers to the browser
		 * following headers instruct the browser to treat the data as a csv file called export.csv
		 */
		 
		/* header('Content-Type: text/csv');
		header('Content-Disposition: attachment;filename=export.csv');		*/	



		/*
		 * output header row (if atleast one row exists)
		 */
		 
		 $headers = array();
		 foreach (mysqli_fetch_fields($result) as $field) {
			$headers[] = $field->name;
		 }
		 
		 echocsv($headers);

		/*
		 * echo the input array as csv data maintaining consistency with most CSV implementations
		 * - uses double-quotes as enclosure when necessary
		 * - uses double double-quotes to escape double-quotes 
		 * - uses CRLF as a line separator
		 */
		 
		function echocsv($fields)
		{
			$separator = '';
			foreach ($fields as $field) {
				if (preg_match('/\\r|\\n|,|"/', $field)) {
					$field = '"' . str_replace('"', '""', $field) . '"';
				}
				echo $separator . $field;
				$separator = ',';
			}
			echo "\r\n";
		}	
		
		while ($row = mysqli_fetch_row($result)) {
		
			echocsv($row);
		
		}
	}
	}
?>

<?php include('inc/header.php'); ?>
    <div id="results">
        <form id="searchform" action="index.php" method="post">
	    <h1>Search</h1>
	    <div id="service">
                <label for="service">Service:</label>
                    <select id="service" name="service" class="searchoption">
                    <option value="">-- Select Service Name --</option>
                    <?php
                        $resultservice = mysqli_query($con,"Select * from services") ?>
                    <?php
                         while ($line = mysqli_fetch_array($resultservice)) {
                    ?>

                    <option value="<?php echo $line['serviceid'];?>"> <?php echo $line['service'];?> </option>

                    <?php
                    }
                    ?>
                    </select> <br />
	    </div>
	    <div id="env">
               <label for="environment">Environment:</label>
                   <select id="environment" name="environment" class="searchoption">
                   <option value="">-- Select Environment --</option>
                   <?php
                       $resultdomain = mysqli_query($con,"Select * from evn") ?>
                   <?php
                       while ($line = mysqli_fetch_array($resultdomain)) {
                    ?>

                   <option value="<?php echo $line['envid'];?>"> <?php echo $line['env'];?> </option>

                   <?php
                   }
                   ?>
                   </select><br />
	    </div>
	    <div id="status">
                <label for="status">Status:</label>
                    <select id="status" name="status" class="searchoption">
                    <option value="Active">Active</option>
                    <option value="Inactive">Inactive</option>
                    </select><br />
	    </div>
	    <div id="button">
                <input type="reset" name="reset">
                <input type="submit" name="search" value="Search">
				<input type="submit" name="export" value="Export" />
	    </div>
        </form>
	<?php

            if (isset($_POST['search'])) {

                if (empty($_POST['service'])) { ?>
				
					<div id="message">

                    <?php echo "Please select service in dropdown" . "</br>"; ?>
					
					</div>
            <?php }
			
                else {
				
                    $service = $_POST['service'];
				
                 }

                if (empty($_POST['environment'])) { ?>

					<div id="message">		
					
               	    <?php echo "Please select Environment in dropdown" . "</br>"; ?>
					
					</div>
				
                <?php }

                else {

                    $env = $_POST['environment'];

                }

                if ((!empty($service)) && (!empty($env))) {
		
				$sql="SELECT servers.ServerName, servers.alias, servers.IPAddress, vip.vipaddress, evn.env, domains.domainname, services.service, tier.tier, os.os, servers.platform, servers.virtualenv, url.url, servers.status FROM servers.servers LEFT OUTER JOIN servers.vip ON (servers.vipaddress = vip.vipid) LEFT OUTER JOIN servers.evn ON (servers.environment = evn.envid) LEFT OUTER JOIN servers.domains ON (servers.Domain = domains.domainid) LEFT OUTER JOIN servers.services ON (servers.service = services.serviceid) LEFT OUTER JOIN servers.tier ON (servers.tier = tier.tierid) LEFT OUTER JOIN servers.os ON (servers.os = os.osid) LEFT OUTER JOIN servers.url ON (servers.endpointurl = url.urlid) where servers.environment = $env and servers.service = $service and servers.status = '$_POST[status]'";

                if (!mysqli_query($con,$sql)) {
				die('Error: ' . mysqli_error($con));
				}

                $mydata = mysqli_query($con,$sql);
                $rowcount = mysqli_num_rows($mydata);

                if ($rowcount >= 1) {
                echo "<table id='main-data-table' border=1>
                  <tr>
                        <th> + </th>
                        <th>Server Name</th>
                        <th>IP Address</th>
                        <th>Service</th>
                        <th>Tier</th>
                  </tr>";
                while ($record = mysqli_fetch_array($mydata)) {
                echo "<tr class='main mainrow'>";
                echo "<td><a href='#' class='main'>+</a></td>";
                echo "<td>" . $record['ServerName'] . "</td>";
                echo "<td>" . $record['IPAddress'] . "</td>";
				echo "<td>" . $record['service'] . "</td>";
                echo "<td>" . $record['tier'] . "</td>";
                echo "</tr>";

                //echo "<table border=1>";
                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Server . Name . "</td>";
                echo "<td>" . $record['ServerName'] . "</td>";
                echo "<td class='innerleftcol'>" . IP . Address . "</td>";
                echo "<td>" . $record['IPAddress'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Alias . "</td>";
                echo "<td>" . $record['alias'] . "</td>";
                echo "<td class='innerleftcol'>" . Environment . "</td>";
                echo "<td>" . $record['env'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . VIP . Address . "</td>";
                echo "<td>" . $record['vipaddress'] . "</td>";
                echo "<td class='innerleftcol'>" . Domain . Name . "</td>";
                echo "<td>" . $record['domainname'] . "</td>";
                echo "</tr>";
		
                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Service . "</td>";
                echo "<td>" . $record['service'] . "</td>";
                echo "<td class='innerleftcol'>" . Tier . "</td>";
                echo "<td>" . $record['tier'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . OS . "</td>";
                echo "<td>" . $record['os'] . "</td>";
                echo "<td class='innerleftcol'>" . EndPoint . "</td>";
                echo "<td>" . $record['url'] . "</td>";
                echo "</tr>";

                echo "<tr class='data showhide'>";
                echo "<td>" . "</td>";
                echo "<td class='innerleftcol'>" . Platform . "</td>";
                echo "<td>" . $record['platform'] . "</td>";
                echo "<td class='innerleftcol'>" . Virtual . "</td>";
                echo "<td>" . $record['virtualenv'] . "</td>";
                echo "</tr>";
                }
                echo "</table>";
                ?>
				<div id="message">
				<?php
					echo "$rowcount record/s found";
				?>
				</div>
				<?php
				}
				else {
				?>
				<div id="message">
					<?php echo "No records found"; ?>
				</div>
				<?php
				}

				}
				else {

				exit();

				}

				mysqli_close($con);
				}				
    ?>
    </div>
<?php include('inc/footer.php'); ?>

ginerjm · May 26, 2014

You haven't defined the function yet when you make the call.

ginerjm · May 26, 2014

And the function is buried inside a block of code of an if statement. Never done that myself - suggest you move the function for the sake of clarity at the least.

raymak · May 27, 2014

Ch0cu3r,

Sorry I didn't move function right above echocsv($headers);. After moving the function, I was able to see data in the header. However, it didn't prompt me for downloadable, but it did display information in the header of the page.

Thanks for all your help.

Ray

Ch0cu3r · May 27, 2014

However, it didn't prompt me for downloadable,

Make sure you have removed the comments from the two header() lines you commented out on post #11

raymak · May 27, 2014

Thank you

Now I was able to download csv file. However, I receive all html code inside CSV file. I think I should put the code after closing tag of </html>?

raymak · May 27, 2014

I think I solved the problem for html code. I added exit(); end of the code and it stopped adding html code in the csv file.

Thanks everyone for your support.

Ch0cu3r · May 27, 2014

Glad you go it sorted. :thumb-up:

Sign In

Need help with exporting data to csv

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