[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

[PHP/MySQL] Excel dump PHP script table format issues

I am using a PHP script that I found from the associated topic that can be located here:

http://www.fundisom.com/phparadise/php/databases/mySQL_to_excel

The script works great, but when the data is entered into our database, there are all sorts of characters added that are messing up how the data is displayed in EXCEL.  There is one column that when it has data in it, the way it is entered by the users is forcing the rest of the data to print two or three columns to the right, so they don't kine up to the titles anymore.  

The area of code that takes care of the carriage returns is below:

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);

Open in new window


I have updated it to this:

$schema_insert = preg_replace("/\r\n|\n\r|\n|\r|\s\s+/", " ", $schema_insert);

Open in new window


But then any tables that have no data or are set to null collapse and all the data moves over column to the left to accommodate this.  

Below is the whole section of code that populates the EXCEL spreadsheet.

	/*	FORMATTING FOR EXCEL DOCUMENTS ('.xls')   */
	//create title with timestamp:
	if ($Use_Title == 1)
	{
		echo("$title\n");
	}
	//define separator (defines columns in excel & tabs in word)
	$sep = "\t"; //tabbed character

	//start of printing column names as names of MySQL fields
&#9;for ($i = 0; $i < mysql_num_fields($result); $i++)
&#9;{
&#9;&#9;echo mysql_field_name($result,$i) . "\t";
&#9;}
&#9;print("\n");
&#9;//end of printing column names

&#9;//start while loop to get data
&#9;var_dump ($result);
&#9;while($row = mysql_fetch_row($result))
&#9;{
&#9;&#9;//set_time_limit(60); // HaRa
&#9;&#9;$schema_insert = "";
&#9;&#9;for($j=0; $j<mysql_num_fields($result);$j++)
&#9;&#9;{
&#9;&#9;&#9;if(!isset($row[$j]))
&#9;&#9;&#9;&#9;$schema_insert .= "NULL".$sep;
&#9;&#9;&#9;elseif ($row[$j] != "")
&#9;&#9;&#9;&#9;$schema_insert .= "$row[$j]".$sep;
&#9;&#9;&#9;else
&#9;&#9;&#9;&#9;$schema_insert .= "".$sep;
&#9;&#9;}
&#9;&#9;$schema_insert = str_replace($sep."$", "", $schema_insert);
&#9;&#9;//following fix suggested by Josue (thanks, Josue!)
&#9;&#9;//this corrects output in excel when table fields contain \n or \r
&#9;&#9;//these two characters are now replaced with a space
&#9;&#9;$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
&#9;&#9;$schema_insert .= "\t";
&#9;&#9;print(trim($schema_insert));
&#9;&#9;print "\n";
&#9;}

Open in new window


I was wondering if there was possibly a way that I could set the data from that particular column to be a string or add "" around it so that the extra characters in it will be ignored.  Below is a sample of the actual data that is causing the problem (With extra spaces included):

Delivered On:  Monday,  10/10/2011 at 11:46 A.M.
Left At:  Receiver
Signed By:  MARTIN
&#9;&#9;&#9;

Open in new window


Thanks in advance for any help!
0
prileyosborne
Asked:
prileyosborne
  • 7
  • 7
3 Solutions
 
Ray PaseurCommented:
I may be misunderstanding the question, but you might find this function useful.
http://php.net/manual/en/function.fputcsv.php

If you have some test data, please post it, thanks.
0
 
prileyosborneAuthor Commented:
Here is a sample of how the data is displaying. You will notice that some of them line up correctly, but the ones that have anything in the tracking_status column have everything moved over. I believe it is because of the extra spaces in the actual database entry.  Everything else works correctly.  As soon as they click the download link and it pings this page it triggers a download of the spreadsheet.

Thanks for any help you can offer. I am super close with my current version and I am hoping that there is a quick fix so that I don't have to start over. But starting over is not a huge issue either... :)  Just not my first choice! test-csv-export.csv
0
 
Ray PaseurCommented:
The general design pattern for copying a DB table to a CSV goes something like this...

Run the query
Get the column names
Use fputcsv() to write the column names
Iterate over the results set row by row
Use fputcsv() to write each row of data.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Ray PaseurCommented:
Here's a teaching example.  Read it over and see if it make sense.  Please post back if you have any questions, ~Ray
<?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("WTF? $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
 
Ray PaseurCommented:
In case you want to select less than all with SELECT * and want to SELECT columns by name, you can still make this work.  Just make the query, then retrieve the first row with mysql_fetch_assoc().  Take the array keys from that row and use them as the column names.  Then use mysql_data_seek(0) to reset the results pointer and copy the data into the CSV file.
0
 
prileyosborneAuthor Commented:
Ok, so I got this to work, which was not hard since you gave me most of the code to use :), but I still get an issue with a few columns being misplaced. it looks like both the the columns that would be the '0' column in the table arrays (I am using two different tables and joining them to get the results) are the ones missing.  So I just need the first column in each table to print.

Below is my code:

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

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM ibmaap";
$sql2 = "SHOW COLUMNS FROM ibmaap_aircard";
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("WTF? $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
}

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

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

// GET THE ROWS OF DATA
$sql = "SELECT ibmaap.site_id, ibmaap.serial, ibmaap.router_sn, ibmaap.router_mac, ibmaap.aircard_esn_hex, ibmaap.aircard_static_ip, ibmaap.address, ibmaap.city, ibmaap.state, ibmaap.zip, ibmaap.tracking, ibmaap.tracking_status, ibmaap_aircard.esn_dec, ibmaap_aircard.mdn, ibmaap_aircard.msl, ibmaap_aircard.msid, ibmaap_aircard.ip 
			FROM ibmaap, ibmaap_aircard
			WHERE ibmaap.aircard_static_ip = ibmaap_aircard.ip";
$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


And here is the result of my merged array var dump:
array(20) {
  [0]=>
  string(2) "id"
  [1]=>
  string(7) "site_id"
  [2]=>
  string(6) "serial"
  [3]=>
  string(9) "router_sn"
  [4]=>
  string(10) "router_mac"
  [5]=>
  string(15) "aircard_esn_hex"
  [6]=>
  string(17) "aircard_static_ip"
  [7]=>
  string(7) "address"
  [8]=>
  string(4) "city"
  [9]=>
  string(5) "state"
  [10]=>
  string(3) "zip"
  [11]=>
  string(8) "tracking"
  [12]=>
  string(15) "tracking_status"
  [13]=>
  string(6) "status"
  [14]=>
  string(7) "esn_hex"
  [15]=>
  string(7) "esn_dec"
  [16]=>
  string(3) "mdn"
  [17]=>
  string(3) "msl"
  [18]=>
  string(4) "msid"
  [19]=>
  string(2) "ip"
}

Open in new window


So it is dealing with all the data correctly except for it is dropping the two 0 columns.  Thanks so much!
0
 
Ray PaseurCommented:
I cannot test this because I do not have your test data, so please activate the var_dump() statements for these variables and post the results back here.

$my_columns
$my_columns2
$joined_result
 and the first two lines in $row

Thanks, ~Ray
0
 
prileyosborneAuthor Commented:
Thanks so much Ray, Here are those Var Dumps:

$my_columns
array(14) {
  [0]=>
  string(2) "id"
  [1]=>
  string(7) "site_id"
  [2]=>
  string(6) "serial"
  [3]=>
  string(9) "router_sn"
  [4]=>
  string(10) "router_mac"
  [5]=>
  string(15) "aircard_esn_hex"
  [6]=>
  string(17) "aircard_static_ip"
  [7]=>
  string(7) "address"
  [8]=>
  string(4) "city"
  [9]=>
  string(5) "state"
  [10]=>
  string(3) "zip"
  [11]=>
  string(8) "tracking"
  [12]=>
  string(15) "tracking_status"
  [13]=>
  string(6) "status"
}

Open in new window


$my_columns2
array(6) {
  [0]=>
  string(7) "esn_hex"
  [1]=>
  string(7) "esn_dec"
  [2]=>
  string(3) "mdn"
  [3]=>
  string(3) "msl"
  [4]=>
  string(4) "msid"
  [5]=>
  string(2) "ip"
}

Open in new window


$joined_result
array(20) {
  [0]=>
  string(2) "id"
  [1]=>
  string(7) "site_id"
  [2]=>
  string(6) "serial"
  [3]=>
  string(9) "router_sn"
  [4]=>
  string(10) "router_mac"
  [5]=>
  string(15) "aircard_esn_hex"
  [6]=>
  string(17) "aircard_static_ip"
  [7]=>
  string(7) "address"
  [8]=>
  string(4) "city"
  [9]=>
  string(5) "state"
  [10]=>
  string(3) "zip"
  [11]=>
  string(8) "tracking"
  [12]=>
  string(15) "tracking_status"
  [13]=>
  string(6) "status"
  [14]=>
  string(7) "esn_hex"
  [15]=>
  string(7) "esn_dec"
  [16]=>
  string(3) "mdn"
  [17]=>
  string(3) "msl"
  [18]=>
  string(4) "msid"
  [19]=>
  string(2) "ip"
}

Open in new window


$row (some data edited)
array(17) {
  [0]=>
  string(6) "109453"
  [1]=>
  string(4) "1000"
  [2]=>
  string(14) "MM102517"
  [3]=>
  string(12) "00301f99"
  [4]=>
  string(8) "6BC"
  [5]=>
  string(14) "11.111.210.116"
  [6]=>
  string(25) "50 N Apopka Viand Rd"
  [7]=>
  string(7) "Orndo"
  [8]=>
  string(2) "FL"
  [9]=>
  string(5) "328"
  [10]=>
  string(18) "1Z428V20974"
  [11]=>
  string(83) "Delivered On:Friday,10/07/2011 at 12:02P.M.
Left At:Receiver
Signed By:HARRS
			"
  [12]=>
  string(11) "09663100"
  [13]=>
  string(10) "5474454"
  [14]=>
  string(6) "2707"
  [15]=>
  string(10) "54109"
  [16]=>
  string(14) "11.111.210.116"
}
array(17) {
  [0]=>
  string(6) "108605"
  [1]=>
  string(4) "1001"
  [2]=>
  string(14) "MM1002481"
  [3]=>
  string(12) "00301f51"
  [4]=>
  string(8) "60D5E"
  [5]=>
  string(14) "11.111.210.118"
  [6]=>
  string(19) "701 W Demter St."
  [7]=>
  string(5) "Nes"
  [8]=>
  string(2) "IL"
  [9]=>
  string(5) "604"
  [10]=>
  string(18) "1Z420340867193"
  [11]=>
  string(91) "Delivered On:  Thursday,  10/06/2011 at 10:37 A.M.
Left At:  Front Desk
Signed By:  JOE
			"
  [12]=>
  string(11) "09663518"
  [13]=>
  string(10) "5416500"
  [14]=>
  string(6) "673"
  [15]=>
  string(10) "541056"
  [16]=>
  string(14) "11.111.210.118"
}

Open in new window


Let me know if this helps!
0
 
prileyosborneAuthor Commented:
Just checking back to see if anyone can help. Thanks so much to Ray for his help so far!
0
 
prileyosborneAuthor Commented:
Just checking to see if anyone has an answer for this one!
0
 
prileyosborneAuthor Commented:
I haven't gotten a response to this question and it is kind of odd that the admins haven't closed it. Anyway, if anyone can answer this,that would be fantastic! Thanks -

peter
0
 
Ray PaseurCommented:
I don't really have time to work on this any more, but I can suggest a couple of things you might try.  When you run the query "show columns" you get the names of the columns from that table.  There are 20 columns.  Now have a quick look at this code snippet.
// GET THE ROWS OF DATA
$sql = "SELECT 
  ibmaap.site_id
, ibmaap.serial
, ibmaap.router_sn
, ibmaap.router_mac
, ibmaap.aircard_esn_hex
, ibmaap.aircard_static_ip
, ibmaap.address
, ibmaap.city
, ibmaap.state
, ibmaap.zip
, ibmaap.tracking
, ibmaap.tracking_status
, ibmaap_aircard.esn_dec
, ibmaap_aircard.mdn
, ibmaap_aircard.msl
, ibmaap_aircard.msid
, ibmaap_aircard.ip 
FROM ibmaap, ibmaap_aircard
WHERE ibmaap.aircard_static_ip = ibmaap_aircard.ip";

Open in new window

There you can see that the script has a hard-coded query that asks for only 17 columns.  So what I might do would be to use the column names you got from "show columns" instead of hard-coding the names.

Another alternative: Create one temporary table from the query that joins these two tables, and then use the algorithm in ID:36995113
0
 
prileyosborneAuthor Commented:
Thanks for the feedback!  That is perfect. The solution that shows the columns was exactly what I needed. Thanks again!
0
 
Ray PaseurCommented:
Great, glad you got a good answer.  Thanks for the points, thanks for using EE and Happy New Year 2012! ~Ray
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now