Skip to content
View in the app

A better way to browse. Learn more.

Web Designer Forum

A full-screen app on your home screen with push notifications, badges and more.

To install this app on iOS and iPadOS
  1. Tap the Share icon in Safari
  2. Scroll the menu and tap Add to Home Screen.
  3. Tap Add in the top-right corner.
To install this app on Android
  1. Tap the 3-dot menu (⋮) in the top-right corner of the browser.
  2. Tap Add to Home screen or Install app.
  3. Confirm by tapping Install.

Help - SQL to Excel Script Not Working

Featured Replies

HI

 

 

I have a script I am working on. I want it to export Data from my DB to an Excel Spreadsheet.

 

<?php require_once('Connections/Connection.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
 if (PHP_VERSION < 6) {
   $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
 }

 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 switch ($theType) {
   case "text":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;    
   case "long":
   case "int":
     $theValue = ($theValue != "") ? intval($theValue) : "NULL";
     break;
   case "double":
     $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
     break;
   case "date":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;
   case "defined":
     $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
     break;
 }
 return $theValue;
}
}

mysql_select_db($database_Connection, $Connection);
$query_testdata = "SELECT * FROM testdata";
$testdata = mysql_query($query_testdata, $Connection) or die(mysql_error());
$row_testdata = mysql_fetch_assoc($testdata);
$totalRows_testdata = mysql_num_rows($testdata);mysql_select_db($database_Connection, $Connection);
$query_testdata = "SELECT * FROM testdata";
$testdata = mysql_query($query_testdata, $Connection) or die(mysql_error());
$row_testdata = mysql_fetch_assoc($testdata);
$totalRows_testdata = mysql_num_rows($testdata);
$query_testdata = "SELECT * FROM testdata ORDER BY ID DESC";
$testdata = mysql_query($query_testdata, $Connection) or die(mysql_error());
$row_testdata = mysql_fetch_assoc($testdata);
$totalRows_testdata = mysql_num_rows($testdata);

//feed the final array to our formatting function...
$contents = getExcelData($testdata);

$filename = "myExcelFile.xls";

//prepare to give the user a Save/Open dialog...
header ("Content-type: application/octet-stream");
header ("Content-Disposition: attachment; filename=".$filename);

//setting the cache expiration to 30 seconds ahead of current time. an IE 8 issue when opening the data directly in the browser without first saving it to a file
$expiredate = time() + 30;
$expireheader = "Expires: ".gmdate("D, d M Y G:i:s",$expiredate)." GMT";
header ($expireheader);

//output the contents
echo $contents;
exit;
?>
<?php
function getExcelData($data){
   $retval = "";
   if (is_array($data)  && !empty($data))
   {
    $row = 0;
    foreach(array_values($data) as $_data){
     if (is_array($_data) && !empty($_data))
     {
         if ($row == 0)
         {
             // write the column headers
             $retval = implode("\t",array_keys($_data));
             $retval .= "\n";
         }
          //create a line of values for this row...
             $retval .= implode("\t",array_values($_data));
             $retval .= "\n";
             //increment the row so we don't create headers all over again
             $row++;
      }
    }
   }
 return $retval;
}

mysql_free_result($testdata);
?>

 

 

I have two Issues.

 

It does not seem to work. When I export it, I just seems to create a blank excel doc. Not Sure Why?

 

I would also really like to be able to add a title to my excel doc, before my data shows

 

I hope someone can help me and I would really appreciate it.

Create an account or sign in to comment

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.