Page 1 of 1

Data from Mysql as CSV file

Posted: Sat Apr 19, 2003 7:26 am
by Ashcan
I want to write what I thought would be simple function for CSV dumping of values.
everything works fine except it's inserting the $fin_str into the file as
$r=['field_name'] text instead of the actual values.

Code: Select all

<?php
$table = 'products'; 
$fields = mysql_query( "select * from $table"); 

// generate field list and $fin_str string 

$columns = mysql_num_fields( $fields ); 

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

          $part_str = '$r[' . "'" . mysql_field_name( $fields, $i ) . "']" ; 
     $str = $str . $part_str . ","; 
//also tried 
//$part_str = "$r[" . "'" . mysql_field_name( $fields, $i ) . "']" ; 
//but got unexpected "/" error     

}      


$fin_str = substr( $str, 0, -3 ); 

// write file of data 
 $fp = fopen ("data/" . "$table.csv", "a"); 
 while ($r = mysql_fetch_array($fields)) 
        { 
           fwrite($fp,$fin_str."\n"); 
        } 
fclose($fp); 

?>
can anyone see what I'm doing wrong?

Posted: Sat Apr 19, 2003 9:06 am
by volka
can you please explain step by step what you want to do?

Posted: Sat Apr 19, 2003 12:02 pm
by patrikG
http://www.php.net/manual/en/function.fgetcsv.php

There are some examples how to write CSV. :)

Posted: Sat Apr 19, 2003 3:22 pm
by pootergeist

Code: Select all

$fields = mysql_query( "select * from $table"); 
while($row = mysql_fetch_array($fields))
 {
 foreach($row as $f_data)
  {
  $str .= $f_data.', ';
  }
 $str = substr($str,0,-2).'
';
 }
should do the trick.

Posted: Sun Apr 20, 2003 1:03 am
by Ashcan
thanks for the responses,

pootergeist - thats managed to get the values into a CSV file however its duplicating all of the values.

ie. a,a,b,b,c,c,d,d,e,e,f,f etc.
instead of a,b,c,d,e,f
so I get 10 values instead of 5

if I modify this to a foreach($row as $f_data => $value)

I get 0,a,1,b,2,c etc.

I can't see how this is duplicating so any ideas on how to rectify this?
I also had to add a "\n" to end the rows

it now looks like this.

Code: Select all

<?php
$fields = mysql_query( "select * from $table"); 
$fp = fopen ("data/" . "$table.csv", "a"); 

while($row = mysql_fetch_array($fields)) 
{ 
foreach($row as $row_of_data) 
	{
	$str .=$row_of_data.",";    
  	} 
                $str = substr($str,0,-2)."\n"; 
}
fwrite($fp,$str); 
fclose($fp); 
?>



volka - step by step , I was trying to
a. get the number of fields within a table
b. build the required $str string via a "for" sequence"
c. generate an array with the appropriate number of CSV values
d. input them directly into a CSV file via a while statement.

I want this to work as a function called generate_csv($table_name) so I cant predefine any numbers of variables or use predefined arrays.

Posted: Sun Apr 20, 2003 11:13 am
by gyardleydn
See: mysql_fetch_array ( resource result [, int result_type])

With the foreach loop you will want to define result_type as MYSQL_ASSOC or MYSQL_NUM. The default, MYSQL_BOTH, results in the duplicates. Also you could use mysql_fetch_assoc() or mysql_fetch_row() as a substitute.

Posted: Sun Apr 20, 2003 6:57 pm
by Ashcan
yeha.. finally- its alive !!

thanks heaps for that.