This is my code to extract datas from database to csv file. It’s working fine but now that I put it in my program where the datas have addresses, thus having commas ( , ), I’m having problem in printing it.
This is how it looks like:
Name Address1 Address2 City Email Name1 123 Mcarthur San Juan City 12 Jones Manila
It should be like this:
Name Address1 Address2 City Email Name1 123 Mcarthur, San Juan City 12 Jones, Manila Manila sample@yahoo.com
The City and email are pushed to the other column. I searched the internet but all I saw was on imporitng datas with commas ( , ).
Plus, the warning and error message in php is also exporting inside the csv file. I don’t problem that much because once I fix this, I think there will be no warning.
Then this is my code:
if(isset($_POST['csvextract'])){ $name = "sample"; $file = $name.".csv"; $con = mysql_connect("localhost", "root"); if(!$con){ echo "Error connection"; } $select_db = mysql_select_db('outbound', $con); if(!$select_db){ echo "Error to select database"; } mysql_set_charset("utf8", $con); header("Content-type: text/csv; charset=UTF-8"); header('Content-disposition: attachment; filename='.basename($file)); $myquery = mysql_query("SELECT * FROM temp"); //While loop to fetch the records $contents = "Name, Company, Address1, Address2, City, Province, Postal Code, Contact No, Email, Commodity, Type, Weight, Length, Width, Height, Decreased Value, Special Instruction, Shipping Reference, Payment Method, Package No, Tracking Non"; while($row = mysql_fetch_array($myquery)) { $contents.=$row['0'].","; $contents.=$row['1'].","; $contents.=$row['2'].","; $contents.=$row['3'].","; $contents.=$row['4'].","; $contents.=$row['5'].","; $contents.=$row['6'].","; $contents.=$row['7'].","; $contents.=$row['8'].","; $contents.=$row['9'].","; $contents.=$row['10'].","; $contents.=$row['11'].","; $contents.=$row['12'].","; $contents.=$row['13'].","; $contents.=$row['14'].","; $contents.=$row['15'].","; $contents.=$row['16'].","; $contents.=$row['17'].","; $contents.=$row['18'].","; $contents.=$row['19'].","; $contents.=$row['20'].","; $contents.=$row['21']."n"; } $contents_final = chr(255).chr(254).mb_convert_encoding($contents, "UTF-16LE","UTF-8"); print $contents_final; }
Advertisement
Answer
I am not sure if you have this already working. But I made a little change to your code.
Here is the code:
if (isset($_POST['csvextract'])) { $name = "sample"; $file = $name . ".csv"; $delimiter = ','; $con = mysql_connect("localhost", "root"); if (!$con) { echo "Error connection"; exit; } $select_db = mysql_select_db('outbound', $con); if (!$select_db) { echo "Error to select database"; exit; } mysql_set_charset("utf8", $con); header("Content-type: text/csv; charset=UTF-8"); header('Content-disposition: attachment; filename=' . basename($file)); $myquery = mysql_query("SELECT * FROM temp"); //While loop to fetch the records $contents = "Name, Company, Address1, Address2, City, Province, Postal Code, Contact No, Email, Commodity, Type, Weight, Length, Width, Height, Decreased Value, Special Instruction, Shipping Reference, Payment Method, Package No, Tracking Non"; while ($row = mysql_fetch_array($myquery)) { for ($i = 0; $i < 22; $i++) { if (false === strpos($row[$i], $delimiter)) { $contents .= '"' . $row[$i] . '"'; } else { $contents.=$row[$i]; } if ($i === 21) { $contents .= "n"; } else { $contents .= $delimiter; } } } $contents_final = chr(255) . chr(254) . mb_convert_encoding($contents, "UTF-16LE", "UTF-8"); print $contents_final; }
I changed to following.
- I add a delimiter variable at the top of the script. By this you can swith easy between the delimiter, so if it change from , to ; you can do that by just editing 1 line.
- Instead of the all rows I used a for loop since every table is just a number.
- Add a check to see in the string contains the delimiter and if so put qoutes around it
- Check if the
$i
is 21 because then we need to go to the new line. - Add exit when there is not db connection or no database can be selected
If this does not work please let me know I will fix it. Notice I haven’t tried this code my self.