PHP | fputcsv : generated file only populated with field header, but no records

Question

I can't figure out why this code only generate a file with the field headers, but no records.

File content :

"Article ID",Shoppergroupname,"Promotion Price",VAT-Code,"Article Currency","Promotion Start Date","Promotion End Date"

As you can see, no record are "exported" and database table is not tempty !

By the way I also would like to get rid of the field header quotes.

Would appreciate if someone would help me to solve this issue. Thanks in advance.

Marc

SOLUTION PROVIDED BY @KateMihalikova

// Create connection
$conn = new mysqli($databasehost, $databaseusername, $databasepassword, $databasename);

// Check connection
if (mysqli_connect_errno()) {
exit('Connect failed: '. mysqli_connect_error());
}
echo "Connected successfully | ";

// Create filename
date_default_timezone_set('Europe/Zurich');
$today = date("YmdHis"); 
$csvname = "WS_PRICE_IMPORT-".$today.".csv";
$csvfullname = '/var/.../'.$csvname; 


// create a file pointer connected to the output stream
$output = fopen($csvfullname, 'a+');

// output the column headings
fputcsv($output, array('Article ID', 'Shoppergroupname', 'Promotion Price', 'VAT-Code', 'Article Currency', 'Promotion Start Date', 'Promotion End Date'));

 // fetch the data
$sql = "SELECT `Article ID`, `Shoppergroupname`, `Promotion Price`, `VAT-Code`, `Article Currency`, `Promotion Start Date`, `Promotion End Date` FROM jos_temppriceimport";
$result = $conn->query($sql);

if (!$result) {
echo "Unable to execute query in the database : " . mysql_error();
exit;
}

if ($result->num_rows == 0) {
echo "No record found, no record to export in CSV.";
exit;
}

// loop over the rows, outputting them
while ($row = $result->fetch_row()) fputcsv($output, $row);

PS: Fieldname with space are delivered by the dataprovider.

What's the `if ($conn->query($row) ....` code block supposed to do? a) it looks like pdo or mysqli while the other code uses the deprecated mysql_* extension. b) $row must be false/null after the while loop. — VolkerK, Nov 02 '15 at 04:53
You are still mixing mysql and mysqli, try `$result->fetch_assoc()` instead of `mysql_fetch_assoc($sql)`. — Kate Miháliková, Nov 02 '15 at 05:12
@KateMihalikova thank you correction made, but query still not work — marcq, Nov 02 '15 at 05:27
It's possible that your code fails on `mysql_num_rows`, change it to `$result->num_rows`. You can also add some `echo`s here and there to help with debugging. — Kate Miháliková, Nov 02 '15 at 05:34
@KateMihalikova thanks I just made a print_r of the resultats : mysqli_result Object ( [current_field] => 0 [field_count] => 7 [lengths] => [num_rows] => 4 [type] => 0 ) I have 4 records in my database table. But this records are still not exported in the CSV file, only the field header are. — marcq, Nov 02 '15 at 06:05
Change fputcsv to var_dump. If it works, the problem is in fputcsv itself. — Kate Miháliková, Nov 02 '15 at 06:10
@KateMihalikova thanks Var_dump output is : `resource(4) of type (stream) array(7) { [0]=> string(10) "Article ID" [1]=> string(16) "Shoppergroupname" [2]=> string(15) "Promotion Price" [3]=> string(8) "VAT-Code" [4]=> string(16) "Article Currency" [5]=> string(20) "Promotion Start Date" [6]=> string(18) "Promotion End Date" }` Records are found but not exported... — marcq, Nov 02 '15 at 06:19
OK, try to save some other data to csv (i.e. save the header twice) to see if it saves correctly. You also can try changing fetch_assoc to fetch_row. — Kate Miháliková, Nov 02 '15 at 06:32
@KateMihalikova thanks again, tried to export users change fetch_assoc to fetch_row but only the headers are still exported `Name,Username,Email` — marcq, Nov 02 '15 at 06:52
What about simple `fputcsv($output, array("TEST"));` after the `while` loop, does it save into the file? — Kate Miháliková, Nov 02 '15 at 07:40
Let's try `while ($row = $result->fetch_row($result)) {fputs($output, "TEST1\n"); fputs($output, "TEST2" . array_join("+", $row) . "\n"); fputcsv($output, array("TEST3");}` — Kate Miháliková, Nov 02 '15 at 08:01
@KateMihalikova thank you for your time Kate. New while loop doesn't generate anything. File isn't created... — marcq, Nov 02 '15 at 08:10
OK, I've tried running it myself now and the only error thrown was that you need to change `$result->fetch_row($result)` to `$result->fetch_row()`. http://phpfiddle.org/main/code/jcbx-7c0n — Kate Miháliková, Nov 02 '15 at 08:27
@KateMihalikova thanks a lot it is working now, I appreciate, I've learn a few things. Take care. — marcq, Nov 02 '15 at 08:38
I'm glad we worked it out, please mark the answer which helped you the most as accepted. — Kate Miháliková, Nov 02 '15 at 09:01

score 1 · Answer 1 · answered Nov 02 '15 at 05:11

1

You are looping over your SQL statement. You need to loop through your result instead.

while ($row = mysqli_fetch_assoc($result)) fputcsv($output, $row);

answered Nov 02 '15 at 05:11

Andy Noelker

10,949
6
35
47

score 1 · Accepted Answer · answered Nov 02 '15 at 08:59

The main problem in your code was mixing of procedural mysql, procedural mysqli, and object-oriented mysqli. They are similar, but can't be mixed.

We sort out that the main problem was in while loop.

while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

after iterating a little, there was a problem with object-oriented notation, where object methods are used instead of functions with attributes, but that attribute was left there. http://phpfiddle.org/main/code/jcbx-7c0n

while ($row = $result->fetch_row($result)) fputcsv($output, $row); // forgotten attribute
while ($row = $result->fetch_row()) fputcsv($output, $row);        // working just fine

Yes my code was a "mess" ! I'm a newbie, so I need to improve my skills, what I could do today thanks to you. The next time I will write my queries more carefully, appropriately and respect conventions. Cheers. — marcq, Nov 02 '15 at 09:27

PHP | fputcsv : generated file only populated with field header, but no records

2 Answers2