CSV output

[cabernet]

I experiment with a new script
but cannot figure why I cannot output data from DB
thank you

<?php
//common_db.inc
$dbhost = 'aaaaa';
$dbusername = 'bbbbbb';
$dbuserpassword = 'cccccc';
$default_dbname = 'ddddd';

$MYSQL_ERRNO = '';
$MYSQL_ERROR = '';

function db_connect() {
global $dbhost, $dbusername, $dbuserpassword, $default_dbname;
global $MYSQL_ERRNO, $MYSQL_ERROR;

$link_id = mysql_connect($dbhost, $dbusername, $dbuserpassword);
if(!$link_id) {
$MYSQL_ERRNO = 0;
$MYSQL_ERROR = &quot;Connection failed to the host $dbhost.&quot;;
return 0;
}
else if(empty($dbname) && !mysql_select_db($default_dbname)) {
$MYSQL_ERRNO = mysql_errno();
$MYSQL_ERROR = mysql_error();
return 0;
}
else return $link_id;
}

function sql_error() {
global $MYSQL_ERRNO, $MYSQL_ERROR;

if(empty($MYSQL_ERROR)) {
$MYSQL_ERRNO = mysql_errno();
$MYSQL_ERROR = mysql_error();
}
return &quot;$MYSQL_ERRNO: $MYSQL_ERROR&quot;;
}




$result = mysql_query(&quot;select bus_name, first_name, last_name, fax,user_name from bol_ag where fax is not null,'$link_id' &quot;
$fp = fopen(&quot;./dump.sql&quot;, &quot;a&quot;

while($row = mysql_fetch_array($result)) {
fwrite($fp, &quot;insert into table &quot;.
&quot;values ('$row[col1]', '$row[col2],'$row[col3],'$row[col4],'$row[col5]')\n&quot;
}

fclose($fp);

// Reading the data back in

$arr = file(&quot;./dump.sql&quot;

for($i=0; $i<sizeof($arr); $i++) {
mysql_query($arr[$i]);
// execute each line as SQL statement
}

$csv_output = &quot;column 1, column 2, column 3, column 4, column 5&quot;;
$csv_output .= &quot;\n&quot;;
$result = mysql_query(&quot;select bus_name, first_name, last_name, fax,user_name from bol_ag where fax is not null, '$link_id' &quot;

while($row = mysql_fetch_array($result)) {
$csv_output .= &quot;$row[col1],$row[col2],$row[col3],$row[col4],$row[col5]\n&quot;;
}

header(&quot;Content-type: application/vnd.ms-excel&quot;
header(&quot;Content-disposition: csv&quot; . date(&quot;Y-m-d&quot . &quot;.xls&quot;
print $csv_output;
exit;
?>

 

[sleipnir214]

For one thing, your MySQL connection is created inside a function. But I see no code in your script which actually invokes the function to perform the connection.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cabernet]

I added that line:
$link_id = db_connect ('dddd'); (which is $default_dbname)
above:
$ result = mysql_query(&quot;SELECT.....etc.

and I had an error by the end of the following line which is changed here and and a few lines below
$result = mysql_query(&quot;select bus_name, first_name, last_name, fax,user_name from bol_ag where fax is not null&quot;,$link_id);
($link_id was in between ' ' )

but still not outputing data
I got the XL doc but it comes w/out data

thanks

 

[Bastien]

try writing the code this way for the writing to the file, I believe you needed to add some quotes and concatenators to make the sentence read correctly...

Though I am not sure you will end up with a CSV file as excel may treat the entire line as 1 field and not separate the fields by columns. As the insert into table values ( is the same for each insert, a true CSV file only needs to hold the data and not the insert portion of the statement. You can simply insert the data values from the array into the sql statement to reload the file.


while($row = mysql_fetch_array($result)) {
fwrite($fp, &quot;insert into table &quot;.
&quot;values ('&quot;.$row[col1].&quot;', '&quot;.$row[col2].&quot;,'&quot;.$row[col3].&quot;,'&quot;.$row[col4].&quot;,'.&quot;$row[col5].&quot;')\n&quot;
}



Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[cabernet]

Bastien
Thanks
I will try later (will be away most of the day)

regards

Henry

 

[KarveR]

heres one that works, currently been serving me well for a year or more:


*notes:
1) Was coded for use with REGISTER_GLOBALS=1
so you may need to change the globals to use $_POST[] or $_GET[] (original uses POST)
2) required arguments are a file name ($query -nfc why, cant remember as we unlink it anyway lol) and a query($sql).


this will run the query and display the results and a

<?

if(!isset($sql)){
  echo &quot;No Query&quot;;
  exit;
        }else{
        $sql=stripslashes($sql);
        $file=$query.&quot;.csv&quot;;
}

header(&quot;Content-Type: application/vnd.ms-excel&quot;);
header(&quot;Content-Disposition: attachment;filename=&quot;.$file );
header('Pragma: no-cache');
header('Expires: 0');

$db=&quot;database&quot;;
mysql_select_db($db,$connection);
$result = mysql_query($sql,$connection);
if($result){

        $columns=@mysql_num_fields($result);
for ($i = 0; $i < mysql_num_fields($result); $i++) {

        print &quot;\&quot;&quot;.mysql_field_name($result,$i).&quot;\&quot;,&quot;;

}
echo &quot;\n&quot;;
        
while ($myrow = mysql_fetch_array($result)){
        for ($i = 0; $i < ($columns); $i++) {
                echo &quot;\&quot;&quot;.$myrow[$i].&quot;\&quot;,&quot;;
        }
                echo &quot;\n&quot;;
}
}else{
echo &quot;No results&quot;;
}

?>

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[cabernet]

Thank you carver it works fine