Question

Locked

PHP -> Excel

By emily_thess ·
Hello everyone!

Thats my first post :) I am sending this because I have a problem with transfering data from MySQL to Excel by using php. I have found many open source classes in the web and I tried some of them. Althought the .xsl file is created, when I open the fileinexcell, I see that all the data are thrown in the first cell instead of having the table format ofspreadsheets that we all know.

Please if help me if you know what is going wrong.
Thank you :)


I have Windows XP Professionall
and using PHP Version 5.2.6 and Microsoft Office 2003.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Get Data From MySQL Database

Using PHP you can run a MySQL SELECT query to fetch the data out of the database. You have several options in fetching information from MySQL. PHP provide several functions for this. The first one is mysql_fetch_array()which fetch a result row as an associative array, a numeric array, or both.
Below is an example of fetching data from MySQL, the table contact have three columns, name, subject and message.
Example : select.php
Source code : select.phps, contact.txt


<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
echo "Name :{$row['name']} <br>" .
"Subject : {$row['subject']} <br>" .
"Message : {$row['message']} <br><br>";
}

include 'closedb.php';
?>


The while() loop will keep fetching new rows until mysql_fetch_array() returns FALSE, which means there are no more rows to fetch. The content of the rows are assigned to the variable $row and the values in row are then printed. Always remember to put curly brackets when you want to insert an array value directly into a string.
In above example I use the constant MYSQL_ASSOC as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index . Personally I think it's more informative to use $row['subject'] instead of $row[1].
PHP also provide a function called mysql_fetch_assoc() which also return the row as an associative array.
<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
echo "Name :{$row['name']} <br>" .
"Subject : {$row['subject']} <br>" .
"Message : {$row['message']} <br><br>";
}

include 'closedb.php';
?>

You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
echo "Name :{$row[0]} <br>" .
"Subject : {$row[0]} <br>" .
"Message : {$row[0]} <br><br>";
}

include 'closedb.php';
?>

Using the constant MYSQL_NUM with mysql_fetch_array() gives the same result as the function mysql_fetch_row().
There is another method for you to get the values from a row. You can use list(), to assign a list of variables in one operation.

<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while(list($name,$subject,$message)= mysql_fetch_row($result))
{
echo "Name :$name <br>" .
"Subject : $subject <br>" .
"Message : $row <br><br>";
}

include 'closedb.php';
?>

In above example, list() assign the values in the array returned by mysql_fetch_row() into the variable $name, $subject and $message.
Of course you can also do it like this
<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_row($result))
{
$name = $row[0];
$subject = $row[1];
$message = $row[2];


echo "Name :$name <br>" .
"Subject : $subject <br>" .
"Message : $row <br><br>";
}

include 'closedb.php';
?>

So you see you have lots of choices in fetching information from a database. Just choose the one appropriate for your program

Freeing the memory ?
In some cases a query can return large result sets. As this results are stored in memory there's a concern about memory usage. However you do not need to worry that you will have to call this function in all your script to prevent memory congestion. In PHP all results memory is automatically freed at the end of the script's execution.
But you are really concerned about how much memory is being used for queries that return large result sets you can use mysql_free_result(). Calling this function will free all memory associated with the result identifier ( $result ).
Using the above example you can call mysql_free_result() like this :

<?php
include 'config.php';
include 'opendb.php';
$query = "SELECT name, subject, message FROM contact";
$result = mysql_query($query);
while($row = mysql_fetch_row($result))
{
...
}

mysql_free_result($result);

include 'closedb.php';
?>

Convert MySQL Query Result To Excel
Using PHP to convert MySQL query result to Excel format is also common especially in web based finance applications. The finance data stored in database are downloaded as Excel file for easy viewing. There is no special functions in PHP to do the job. But you can do it easily by formatting the query result as tab separated values or put the value in an HTML table. After that set the content type to application/vnd.ms-excel
Example : convert.php
Source : convert.php, students.txt
<?php
include 'library/config.php';
include 'library/opendb.php';
$query = "SELECT fname, lname FROM students";
$result = mysql_query($query) or die('Error, query failed');
$tsv = array();
$html = array();
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
$tsv[] = implode("\t", $row);
$html[] = "<tr><td>" .implode("</td><td>", $row) . "</td></tr>";
}
$tsv = implode("\r\n", $tsv);
$html = "<table>" . implode("\r\n", $html) . "</table>";
$fileName = 'mysql-to-excel.xls';
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$fileName");
echo $tsv;
//echo $html;
include 'library/closedb.php';
?>

In the above example $tsv is a string containing tab separated values and $html contain an HTML table. I use implode() to join the values of $row with tab to create a tab separated string.
After the while loop implode() is used once again to join the rows using newline characters. The headers are set and the value of $tsv is then printed. This will force the browser to save the file as mysql-to-excel.xsl
Try running the script in your own computer then try commenting echo $tsv and uncomment echo $html to see the difference.

Thanks to:
http://www.php-mysql-tutorial.com/php-mysql-select.php
For the answer to this problem.

Please post back if you have any more problems or questions.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums