Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

php/mysql export to excel

I am trying to adapt some code i found on the Internet to allow mySQL recordset to be exported to Excel.  I have adapted the code i found at <a href="http://www.designerstalk.com/forums/programming/8733-exporting-mysql-records-excel-webpage.html"> and adapted it with the recordset query by dreamweaver.

it is sending correctly and opening Excel correctly, however, gives me a bunch of errors in the columns that read "<b>Warning</b>:  mysql_result() expects parameter 1 to be resource, array given in <b>C:\wamp\www\district_contentDB\excelExport.php</b> on line <b>48</b><br />"

my code is attached.
<?php require_once('Connections/con_db_local.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}


$dateVar_rs_churchreport = "-1";
if (isset($_GET['dateID'])) {
  $dateVar_rs_churchreport = $_GET['dateID'];
}
mysql_select_db($database_con_db_local, $con_db_local);
$query_rs_churchreport = sprintf("SELECT tblchurch.churchID, tblchurch.churchName, tblchurchinfo.churchInfoID, tblchurchinfo.churchAttendance, tblchurchinfo.churchSSAttendance, tblchurchinfo.churchResponsibility, tblchurchinfo.dateID, tblchurchinfo.churchID, tblmonths.monthID, tblmonths.monthLabel FROM tblchurch, tblchurchinfo, tblmonths WHERE tblchurchinfo.dateID = %s AND tblchurchinfo.churchID = tblchurch.churchID    AND tblchurchinfo.dateID = tblmonths.monthID ORDER BY tblchurch.churchName ASC", GetSQLValueString($dateVar_rs_churchreport, "int"));
$rs_churchreport = mysql_query($query_rs_churchreport, $con_db_local) or die(mysql_error());
$row_rs_churchreport = mysql_fetch_assoc($rs_churchreport);
$totalRows_rs_churchreport = mysql_num_rows($rs_churchreport);

$csv_output = "Church Name\tWorship Attendance\tSS Attendance\tChurch Responsibility";
$csv_output .= "\r\n";

$i=0;
while ($i < $totalRows_rs_churchreport) {
$id= mysql_result($row_rs_churchreport,$i,"id");

$valuea = mysql_result($row_rs_churchreport,$i,"valuea");
$valueb = mysql_result($row_rs_churchreport,$i,"valueb");
$valuec = mysql_result($row_rs_churchreport,$i,"valuec");
$valued = mysql_result($row_rs_churchreport,$i,"valued");

$csv_output .= "$valuea|$valueb|$valuec|$valued\n";
++$i;
}

$mode="xls";
$type="excel";

header("Content-type: text/x-csv");  
header("Content-disposition: attachment; filename=". date("d-m-Y")."-export");
print $csv_output;

exit;

?>

Open in new window

0
axessJosh
Asked:
axessJosh
  • 8
  • 6
  • 2
  • +1
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
It's expecting "$con_db_local" instead of "$row_rs_churchreport".  The only reason it works anyway is because the mysql functions use the currently open connection when there is one.  If you look at the page for that function http://us.php.net/manual/en/function.mysql-result.php , they recommend that you use one of the other functions to retrieve your data.  It lists other options at the bottom.
0
 
axessJoshAuthor Commented:
should i use _fetch_array() or _fetch_rows()?

I'm not sure I understand what you are saying.  I have $con_db_local defined in the includeonce as well as in the SQL statement??
0
 
Dave BaldwinFixer of ProblemsCommented:
"$con_db_local" as you are using it should be the results of the "mysql_connect" statement, otherwise known as a 'resource'.  In fact if you print it out, that's what it says.  Your error message says that you are not using the name of a 'resource' in the statement on line 48 (and the ones past that).  "$con_db_local" is that resource.

As for which 'fetch' function works best, please read the page I linked above.  There are examples on the related pages that you can look at.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ray PaseurCommented:
Hmm... Not sure that Dreamweaver is a value-added item in this application.

Here is how I might export a MySQL table into a CSV file for use with Excel.
<?php // RAY_db_to_excel.php
error_reporting(E_ALL);
echo "<pre>\n";


// DEMONSTRATE HOW TO EXPORT A TABLE SO THAT IT CAN BE USED IN EXCEL


// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL GET ARGUMENT?
$table_name = '???';


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    die("HUH? $table_name HAS NO COLUMNS");
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    // var_dump($my_columns); ACTIVATE THIS TO SEE THE COLUMNS
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) die('DISASTER');

// GET THE ROWS OF DATA
$sql = "SELECT * FROM $table_name";
$res = mysql_query($sql);
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE DATA SET
while ($row = mysql_fetch_row($res))
{
    // WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";

Open in new window

0
 
hernst42Commented:
Line 48
Should be
$id= mysql_result($rs_churchreport,$i,"id");
instead of
$id= mysql_result($row_rs_churchreport,$i,"id");
0
 
axessJoshAuthor Commented:
Great piece of code Ray!

If i wanted to specify the MySQL statement a bit more to be able to retrieve church names instead of church IDs would i do that on line 67?
0
 
axessJoshAuthor Commented:
To clarify,

how can I redefine the data that goes into each column on the Excel page?  I currently can put the data i need where i want it on the page for the user, how can I get the data correctly inserted into the appropriate column on the Excel sheet?
0
 
Ray PaseurCommented:
Try that code sample I posted above.  Then post the link to the output file so we can click it and download the CSV.  Once you have that CSV, please post an Excel spreadsheet here that show what you want to have different.  Thanks, ~Ray
0
 
axessJoshAuthor Commented:
I adapted the code a bit to get the results to match the output i am getting in my current result query on the web portal i've built.  

the excel sheet has the columns pulled accurately, however, i need the data to show in the correct row.  there is a problem that i am not getting the Year Label (I need to add that to my SQL statement)

Hopefully I have explained myself well enough.
exportExcel.php
excel-outputSample.xls
0
 
axessJoshAuthor Commented:
oops, uploaded the wrong file. excelExport.php
0
 
Ray PaseurCommented:
Here is the script that was attached to ID:35486762.  With small scripts like this it is easier to use the code snippet instead of a file attachment - that way we get to see the code with line numbers, etc.

I have deconstructed the query on line 95 and it looks like this:

SELECT
tblchurch.churchID,
tblchurch.churchName,
tblchurchinfo.churchInfoID,
tblchurchinfo.churchAttendance,
tblchurchinfo.churchSSAttendance,
tblchurchinfo.churchResponsibility,
tblchurchinfo.dateID,
tblchurchinfo.churchID,
tblchurchinfo.yearID,
tblmonths.monthID,
tblmonths.monthLabel

FROM
tblchurchinfo,
tblchurch,
tblmonths,
tblyears

WHERE
tblchurchinfo.dateID = %s // THIS IS AN INTEGER FROM SOMWHERE

AND
tblchurchinfo.churchID = tblchurch.churchID  

AND
tblchurchinfo.dateID = tblmonths.monthID

AND tblyears.yearID = tblchurchinfo.yearID

ORDER BY
tblchurch.churchName ASC

The results set from a query appears in a set of rows.  These rows are associative arrays.  You can print out an associative array with a construct like this one:

echo "<pre>"; // MAKE IT EASY TO READ
var_dump($row); // VISUALIZE THE DATA ELEMENTS

From what I am seeing in your XLS file, it looks like the script selects the column titles from $table_name = 'tblchurchinfo'; but then later it selects many values from multiple tables.  The script I posted as an example was designed to be a general solution for dumping one table only, so here is my suggestion...

1. Decide what column titles you might want (or none if that is OK) and create those in an array.  Pass this array to fputcsv() in lieu of the columns that are collected and written at line 60.
2. Print out the rows from the query at line 95 with var_dump() so you can see where the data elements are located.
3. Re-arrange the elements from these rows into an array that works with fputcsv().

I think it is mostly a matter of data movement and you will get a good XLS file once you get the information arranged into the right columns.  That will happen when you get the data from the rows and move into a new array that is ordered to match your expectations for the columns in the spreadsheet.

Best of luck with it, ~Ray
<?php 
// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL GET ARGUMENT?
$table_name = 'tblchurchinfo';
$table_name2 = 'tblchurch';
$table_name3 = 'tblmonths';
$table_name4 = 'tblyears';

// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "district_contentdb";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "root";
$db_word = "xxx";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'export to excel' . '.csv';
//'EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    die("HUH? $table_name HAS NO COLUMNS");
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    // var_dump($my_columns); ACTIVATE THIS TO SEE THE COLUMNS
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) die('DISASTER');
//"SELECT * FROM $table_name"
// GET THE ROWS OF DATA
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}
$dateVar_rs_churchreport = "-1";
if (isset($_GET['dateChoice'])) {
  $dateVar_rs_churchreport = $_GET['dateChoice'];
}
$sql = sprintf("SELECT tblchurch.churchID, tblchurch.churchName, tblchurchinfo.churchInfoID, tblchurchinfo.churchAttendance, tblchurchinfo.churchSSAttendance, tblchurchinfo.churchResponsibility, tblchurchinfo.dateID, tblchurchinfo.churchID, tblchurchinfo.yearID, tblmonths.monthID, tblmonths.monthLabel FROM tblchurchinfo, tblchurch, tblmonths, tblyears WHERE tblchurchinfo.dateID = %s AND tblchurchinfo.churchID = tblchurch.churchID  AND tblchurchinfo.dateID = tblmonths.monthID AND tblyears.yearID = tblchurchinfo.yearID ORDER BY tblchurch.churchName ASC", GetSQLValueString($dateVar_rs_churchreport, "int"));
$res = mysql_query($sql);
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE DATA SET
while ($row = mysql_fetch_row($res))
{
    // WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";
?>

Open in new window

0
 
axessJoshAuthor Commented:
I actually went another direction with some code i found that works to get the data into the spreadsheet where I want it, however I cannot figure out how to repeat the data I want.  It displays the first set of data only and I don't know how to implement a Loop to pull all the data from the recordset.
<?php
require_once('Connections/con_db_local.php'); ?>
<?php
//initialize the session
if (!isset($_SESSION)) {
  session_start();
}

if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $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;
}
}

// query the data 

$dateVar_rs_churchreport = "-1";
if (isset($_GET['dateID'])) {
  $dateVar_rs_churchreport = $_GET['dateID'];
}
$yearVar_rs_churchreport = "-1";
if (isset($_GET['yearID'])) {
  $yearVar_rs_churchreport = $_GET['yearID'];
}
mysql_select_db($database_con_db_local, $con_db_local);
$query_rs_churchreport = sprintf("SELECT tblchurch.churchID, tblchurch.churchName, tblchurchinfo.churchInfoID, tblchurchinfo.churchAttendance, tblchurchinfo.churchSSAttendance, tblchurchinfo.churchResponsibility, tblchurchinfo.dateID, tblchurchinfo.churchID, tblmonths.monthID, tblmonths.monthLabel FROM tblchurch, tblchurchinfo, tblmonths WHERE tblchurchinfo.dateID = %s AND tblchurchinfo.churchID = tblchurch.churchID    AND tblchurchinfo.dateID = tblmonths.monthID AND tblchurchinfo.yearID = %s ORDER BY tblchurch.churchName ASC", GetSQLValueString($dateVar_rs_churchreport, "int"),GetSQLValueString($yearVar_rs_churchreport, "int"));
$rs_churchreport = mysql_query($query_rs_churchreport, $con_db_local) or die(mysql_error());
$row_rs_churchreport = mysql_fetch_assoc($rs_churchreport);
$totalRows_rs_churchreport = mysql_num_rows($rs_churchreport);

// create excel file
$filename = "excelreport.xls";
 
// assign variables from recordset
$var1 = ($row_rs_churchreport['churchName']); 
$var2 = $row_rs_churchreport['churchAttendance'];
$var3 = $row_rs_churchreport['churchSSAttendance'];
$var4 = $row_rs_churchreport['churchResponsibility'];

// build spreadsheet columns and display variables 
$contents = "Church Name \t Worship Attendance \t SS Attendance \t Responsibility \t \n ";
$content = "$var1 \t $var2 \t $var3 \t $var4 \n";

header ('Content-type: application/ms-excel');
header ('Content-Disposition: attachment; filename='.$filename);

echo  $contents;
echo $content;

Open in new window

0
 
Ray PaseurCommented:
When you start from a Dreamweaver perspective, you almost always wind up with PHP code that is really ugly (and often ineffectual).  Not that Dreamweaver is not useful, it's just not easy to use it in the larger context of PHP + MySQL data base backed web sites.

Here is the usual way to implement a loop that reads rows from a results set.  You use the while() instruction.  
http://us3.php.net/manual/en/control-structures.while.php

And if you want to learn more about how to write good and effective PHP code, this book is a winner.
http://www.sitepoint.com/books/phpmysql4/
while ($row_rs_churchreport = mysql_fetch_assoc($rs_churchreport))
{
    /* PROCESS EACH ROW ONE-AT-A-TIME HERE */
}

Open in new window

0
 
axessJoshAuthor Commented:
yea, I use dreamweaver for simplicity but realize that it does make customizing the code a bit more difficult.  I was thinking over the weekend about removing the DW code and starting with a basic SQL syntax.

I added the code you provided but still only get one record exported to excel.  Is there something else i am missing in the export code to get the additional records?  It also is not always pulling the first record, one query pulled the second record, another the first.

thanks for your continued help!
0
 
Ray PaseurCommented:
Try printing out the value of $totalRows_rs_churchreport so you can see how many rows you are getting from the query.
0
 
axessJoshAuthor Commented:
$totalRows_rs_churchinfo is showing 2 rows, however the while loop is only giving back one record.  It almost seems as if the while loop needs to be in my $var1 code but I'm not sure.  My code is below.
// assign variables from recordset
$var1 = $row_rs_churchinfo['churchName']; 
$var2 = $row_rs_churchinfo['churchAttendance'];
$var3 = $row_rs_churchinfo['churchSSAttendance'];
$var4 = $row_rs_churchinfo['churchResponsibility'];

// build spreadsheet columns and display variables 
$contents = "Church Name \t Worship Attendance \t SS Attendance \t Responsibility \t \n ";
$content1 = "$var1 \t $var2 \t $var3 \t $var4 \t\n";

//header ('Content-type: application/ms-excel');
//header ('Content-Disposition: attachment; filename='.$filename);
?>

<?php
echo  $contents;
while ($row_rs_churchinfo = mysql_fetch_assoc($rs_churchinfo)){
echo $content1;
}

Open in new window

0
 
Ray PaseurCommented:
OK, at this point I am going to recommend that you hire a professional to help you get this sorted out.  The code fragments we have here do not seem to have a strong enough logical structure and a pro will be able to get you past the obstacles in no time at all.  Obviously I have not tested the code snippet because I do not have your data base, but I think the logic flow needs to go something more like this.

If you're still relatively new to PHP this is a really good book to help you develop an understanding of how PHP and MySQL can be used together.
http://www.sitepoint.com/books/phpmysql4/

Best regards, ~Ray
// build spreadsheet columns and display variables 
$contents = "Church Name \t Worship Attendance \t SS Attendance \t Responsibility \t \n ";
echo  $contents;

while ($row_rs_churchinfo = mysql_fetch_assoc($rs_churchinfo))
{
    // assign variables from recordset
    $var1 = $row_rs_churchinfo['churchName']; 
    $var2 = $row_rs_churchinfo['churchAttendance'];
    $var3 = $row_rs_churchinfo['churchSSAttendance'];
    $var4 = $row_rs_churchinfo['churchResponsibility'];
    $content1 = "$var1 \t $var2 \t $var3 \t $var4 \t\n";
    
    echo $content1;
}

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now