Hello,

I have an on-line MySQL database and wish to have a table in it
available for Excel.

I can use some php to get the data and then put it into Word and edit
the file to put each record onto a new line and then open the text
file in Excel.

Is there an easier way?!

Cheers

Geoff

PS the php was taken from the net and has the code below and includes

//if this parameter is included ($w=1), file returned will be in word
format ('.doc')
//if parameter is not included, file returned will be in excel format
('.xls')
if (isset($w) && ($w==1))
{
$file_type = "msword";
$file_ending = "doc";
}else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}

but not sure what is meant by the text begind the // ?

Cheers

Geoff


@require(dirname(__FILE__) . '/config.php');

$DB_Server = etc
$DB_Username =
$DB_Password =
$DB_DBName =

$DB_TBLName = "all_labs"; //your MySQL
Table Name
//$DB_TBLName, $DB_DBName, may also be commented out & passed to the
browser
//as parameters in a query string, so that this code may be easily
reused for
//any MySQL table or any MySQL database on your server

//DEFINE SQL QUERY:
//you can use just about ANY kind of select statement you want -
//edit this to suit your needs!
$sql = "Select * from $DB_TBLName";

//Optional: print out title to top of Excel or Word file with
Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on
$now_date";
/*

Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() .
"<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>"
.. mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" .
mysql_errno());

//if this parameter is included ($w=1), file returned will be in word
format ('.doc')
//if parameter is not included, file returned will be in excel format
('.xls')
if (isset($w) && ($w==1))
{
$file_type = "msword";
$file_ending = "doc";
}else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment;
filename=database_dump.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");

/* Start of Formatting for Word or Excel */

if (isset($w) && ($w==1)) //check for $w again
{
/* FORMATTING FOR WORD DOCUMENTS ('.doc') */
//create title with timestamp:
if ($Use_Title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character

while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j])) {
$schema_insert .= "NULL".$sep;
}
elseif ($row[$j] != "") {
$schema_insert .= "$row[$j]".$sep;
}
else {
$schema_insert .= "".$sep;
}
}
$schema_insert = str_replace($sep."$", "",
$schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table
row
print
"\n----------------------------------------------------\n";
}
}else{
/* 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
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names

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

?>


<?php
/*_____________________END___OF___THE___CODE_______ _______________


get more code from http://www.fundisom.com/phparadise/


__________________________________________________ _________________*/
?>



</body>
</html>

Geoff Cox wrote:
> Hello,
>
> I have an on-line MySQL database and wish to have a table in it
> available for Excel.
>
> I can use some php to get the data and then put it into Word and edit
> the file to put each record onto a new line and then open the text
> file in Excel.
>
> Is there an easier way?!
>
> Cheers
>
> Geoff

<code snipped>

Export to a .csv file then import into Excel.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Geoff Cox wrote:
> Hello,
>
> I have an on-line MySQL database and wish to have a table in it
> available for Excel.
>
> I can use some php to get the data and then put it into Word and edit
> the file to put each record onto a new line and then open the text
> file in Excel.
>
> Is there an easier way?!
Both phpMyAdmin and the Mysql Query Browser have "Export to Excel"
functionality.

On Sun, 25 May 2008 19:47:57 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>Geoff Cox wrote:
>> Hello,
>>
>> I have an on-line MySQL database and wish to have a table in it
>> available for Excel.
>>
>> I can use some php to get the data and then put it into Word and edit
>> the file to put each record onto a new line and then open the text
>> file in Excel.
>>
>> Is there an easier way?!
>Both phpMyAdmin and the Mysql Query Browser have "Export to Excel"
>functionality.
>

Thanks Paul.

Does phpMyAdmin have to be installed on the server?

Cheers

Geoff

Geoff Cox wrote:
> On Sun, 25 May 2008 19:47:57 +0100, "Paul Lautman"
> <paul.lautman@btinternet.com> wrote:
>
>>Geoff Cox wrote:
>>> Hello,
>>>
>>> I have an on-line MySQL database and wish to have a table in it
>>> available for Excel.
>>>
>>> I can use some php to get the data and then put it into Word and
>>> edit the file to put each record onto a new line and then open the
>>> text file in Excel.
>>>
>>> Is there an easier way?!
>>Both phpMyAdmin and the Mysql Query Browser have "Export to Excel"
>>functionality.
>>
>
> Thanks Paul.
>
> Does phpMyAdmin have to be installed on the server?
>
No, it can be installed elsewhere and pointed to the server. But if you're
gonna do that, you might as well use the Query Browser

On Sun, 25 May 2008 20:49:30 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>Geoff Cox wrote:
>> On Sun, 25 May 2008 19:47:57 +0100, "Paul Lautman"
>> <paul.lautman@btinternet.com> wrote:
>>
>>>Geoff Cox wrote:
>>>> Hello,
>>>>
>>>> I have an on-line MySQL database and wish to have a table in it
>>>> available for Excel.
>>>>
>>>> I can use some php to get the data and then put it into Word and
>>>> edit the file to put each record onto a new line and then open the
>>>> text file in Excel.
>>>>
>>>> Is there an easier way?!
>>>Both phpMyAdmin and the Mysql Query Browser have "Export to Excel"
>>>functionality.
>>>
>>
>> Thanks Paul.
>>
>> Does phpMyAdmin have to be installed on the server?
>>
>No, it can be installed elsewhere and pointed to the server. But if you're
>gonna do that, you might as well use the Query Browser

Paul,

very odd - I have just tried the MySQL Browser and carried out a
select operation and it is finding from a version of a table which no
longer exists! At least it isn't there when I use SSH to access the
database ....

I cannot get the current data from that table using MySQL Browser!

any idea what is going on?

Cheers

Geoff

Geoff Cox wrote:
> On Sun, 25 May 2008 20:49:30 +0100, "Paul Lautman"
> <paul.lautman@btinternet.com> wrote:
>
>>Geoff Cox wrote:
>>> On Sun, 25 May 2008 19:47:57 +0100, "Paul Lautman"
>>> <paul.lautman@btinternet.com> wrote:
>>>
>>>>Geoff Cox wrote:
>>>>> Hello,
>>>>>
>>>>> I have an on-line MySQL database and wish to have a table in it
>>>>> available for Excel.
>>>>>
>>>>> I can use some php to get the data and then put it into Word and
>>>>> edit the file to put each record onto a new line and then open the
>>>>> text file in Excel.
>>>>>
>>>>> Is there an easier way?!
>>>>Both phpMyAdmin and the Mysql Query Browser have "Export to Excel"
>>>>functionality.
>>>>
>>>
>>> Thanks Paul.
>>>
>>> Does phpMyAdmin have to be installed on the server?
>>>
>>No, it can be installed elsewhere and pointed to the server. But if
>>you're gonna do that, you might as well use the Query Browser
>
> Paul,
>
> very odd - I have just tried the MySQL Browser and carried out a
> select operation and it is finding from a version of a table which no
> longer exists! At least it isn't there when I use SSH to access the
> database ....
>
> I cannot get the current data from that table using MySQL Browser!
>
> any idea what is going on?
>
> Cheers
>
> Geoff

I'm afraid that with the limited information you have proffered, I have no
idea.

On Sun, 25 May 2008 22:10:20 +0100, "Paul Lautman"
<paul.lautman@btinternet.com> wrote:

>> very odd - I have just tried the MySQL Browser and carried out a
>> select operation and it is finding from a version of a table which no
>> longer exists! At least it isn't there when I use SSH to access the
>> database ....
>>
>> I cannot get the current data from that table using MySQL Browser!
>>
>> any idea what is going on?
>>
>> Cheers
>>
>> Geoff
>
>I'm afraid that with the limited information you have proffered, I have no
>idea.

Paul,

It was a tall order! I think there may be a problem at the hosting
server.

Cheers

Geoff

On Sun, 25 May 2008 19:28:43 +0100, Geoff Cox wrote:

> Hello,
>
> I have an on-line MySQL database and wish to have a table in it
> available for Excel.
>
> I can use some php to get the data and then put it into Word and edit
> the file to put each record onto a new line and then open the text file
> in Excel.
>
> Is there an easier way?!
>
> Cheers
>
> Geoff
>
> PS the php was taken from the net and has the code below and includes
> ...

Hi -

You point out that you cannot make sense of the bit after the '//'.

That's a comment that hints at how to get your files in Excel format,
instead of Word format.

Work through the code both directions until you discover where $w is
equated to 'isset'. There should/will be either an opposite of 'isset'
along with a way to equate that value to $w OR a way to just NOT equate
'isset' to $w (impliedly setting the opposite condition).

That is all on the client side, so you should be able to make the
changes, or find individual who can/will.

HTH