Mysql export bit values to csv

Question

I have a database with this structure:

ID -- INT(11) -- Primary
post -- BIT(1)

This is just 2 columns , the database is way bigger. I've developed a plugin that needs to export this database to a .csv file using this code:

    if (!$this->conn->connect_error)
{
    $out_file = dirname(__DIR__,1)."/uploads/{$dir}/test.csv";
    $out_file = str_replace("\\","/",$out_file);
    $result = $this->conn->query("SELECT * INTO OUTFILE  '{$out_file}' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM module");
    if ($result==true)
        return 1;
    else
        return 0;
    
    
}

Then, when i check the .csv generated file, in the post column (BIT value) it shows nothing where 0 and this sign where is 1 []. then, I need to import it back into mysql table, by reading the file and just inserting the lines using a prepared statement like this:

   while (($data = fgetcsv($file,0,",")) !== FALSE)
    {
    
    //now we should insert 
    //prepared stmt 
    $ID = $data[0];
    $Prod_ID = $data[1];
    $desc = $data[2];
    $desc2 = $data[3];
    $tip_id = $data[4];
    $pret = $data[5];
    $post = $data[6];
    $prioritat = $data[7];
    $views = $data[8];
    $info = $data[9];
    $img = $data[10];
    $rgb = $data[11];
    $stock = $data[12];
    $date  = $data[13];
    $prep->execute();
}

But as you guess, this leads to an error because the data in .csv file from post column is not valid. I can not change the Bit to TINYINT, so my guess is that i need to somehow convert bit to int in the export .csv file and then convert the int to bit back when inserting. How can I do this?

See if https://stackoverflow.com/questions/14248554/cant-see-mysql-bit-field-value-when-using-select helps. — Nigel Ren, Jan 06 '21 at 12:51
convert the bit to an integer: SELECT ID, CAST(post AS UNSIGNED) FROM module — Alexander Dobernig, Jan 06 '21 at 13:07
@AlexanderDobernig this will work with select into outfile too? I think i just need to put all the columns in the query and throw that * from my current query yes? — DanCodes, Jan 06 '21 at 13:13
@NigelRen i still need .csv not .txt, but i tries that too and still no results. I am still messing with the cast function. — DanCodes, Jan 06 '21 at 13:14
@AlexanderDobernig and one more thing, if i somehow manage to get that cast function work properly, the insert function that i am currently using (reading the .csv and inserting using a prepared stmt) needs to be changed too or I can just simply let it as it is now? — DanCodes, Jan 06 '21 at 13:15
Sorry for not answering, was just eating lunch ;-) but I am happy to hear, that it works now. :-) — Alexander Dobernig, Jan 06 '21 at 14:02
It could be worth adding your solution as an answer so others can benefit from your work. — Nigel Ren, Jan 06 '21 at 14:22

score 0 · Answer 1 · answered Jan 06 '21 at 17:20

Solution! Thanks to AlexanderDobernig and NigelRen I just swaped the query

$result = $this->conn->query("SELECT * INTO OUTFILE  '{$out_file}' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM module");

with:

$result = $this->conn->query("SELECT ID,Prod_ID,Descriere,Descriere2,Tip_ID,Pret, Cast(post as UNSIGNED),prioritar,views,info,img,cod_rgb,stock,update_at INTO OUTFILE  '{$out_file}' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' FROM module");

and the insert function remains the same. Without casting , the bit value remains unreadable in that .csv file.

Mysql export bit values to csv

1 Answers1