Skip to content
Advertisement

Extract datas with ‘,’ from database to csv file

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.

  1. 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.
  2. Instead of the all rows I used a for loop since every table is just a number.
  3. Add a check to see in the string contains the delimiter and if so put qoutes around it
  4. Check if the $i is 21 because then we need to go to the new line.
  5. 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.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement