Print array in a file

Question

I’m entering data from a sqlite database into a file. I create the file with a name that the user chooses, and insert all table data.

The problem is that you only insert a table row into the file, while there are more rows in the table. And repeat the fields twice.

$base_hndl  =   new SQLite3($dir.$base);
$requete    =   "SELECT * FROM contact ORDER BY id desc";   
$resultat   =   $base_hndl->query($requete);    // 
$affiche    =   $resultat->fetchArray();// 
$nombreid = $affiche['id'];

$fp = fopen($namefile.'.csv', 'w');
$list = array();

for($i=1;$i<=$nombreid;$i++)
    {       
        $requete    =   "SELECT * FROM contact WHERE (id=$i)";  
        $resultat   =   $base_hndl->query($requete);    // 
        $affiche    =   $resultat->fetchArray();//


        $list = $affiche;
    }

fputcsv($fp,$list,$delimiter = ';');

fclose($fp);

Answer 1

The original problem of the topic, having only one row of the table being inserted, occurs because in this part of the code:

for( $i=1; $i <= $nombreid; $i++ ) {       

    // ...

    $list = $affiche; <--
}

At each iteration made the variable is overwritten with the query results made within the loop.

And since variables defined in an iteration block are not deleted after the iteration block is finished, the fputcsv() invoked outside the loop uses what it has.

It then remains to invoke the function within the loop as suggested by @Jorge B., which as comments produce an unwanted repeat.

I believe this is happening because of your for-loop that is set in a kind of illogical way (from the external point of view, after all I do not know your Application).

In a "normal" reading of the resource returned by Sqlite3result::fetchArray() you would have something like this:

while( $list = $resultat -> fetchArray() ) {

    // Do somethng
}

Invoking fputcsv() inside would already work, but still with duplicate results.

This duplicity occurs because, I don’t know why, functions of fetching data keeps, until today, data return in two ways: indexed and associative.

In the case of Sqlite, the default value SQLITE3_BOTH makes with that $list be something like that:

array (size=6)
  0 => int 1
  'id' => int 1
  1 => string 'Bruno' (length=5)
  'name' => string 'Bruno' (length=5)
  2 => int 26
  'age' => int 26

Manually set another return mode, associative for example (SQLITE3_ASSOC), already solves that duplicity problem.

Notice that I stand out that duplicity problem because, without knowing your Application, you inflict another duplicity perhaps unintentionally because of your ill-defined for-loop.

That:

$nombreid = $affiche['id'];

// ...

for( $i = 1; $i <= $nombreid; $i++ ) {

    // Do something
}

Will repeat EVERY loop instruction as many times as the pointer is positively tested as being less than or equal to $nombreid.

Analyze your comment in Jorge B’s reply. You have two records in the database plus the SQLITE3’s default behavior would already make you have 4 entries in your CSV.

But let’s imagine that this variable has as value 3, after all it comes from an ID and 3 is a valid ID. This, with the current setting of your for-loop, would make the entire statement repeat 3 times.

Should you nay were doing another query inside that loop, you would have 12 inputs (2 * 2 * 3), but since you are, you are repeating the duplicity of SQLITE3 three times and therefore six entries in the CSV.

Finally, as always, the simplest solution is the most correct one:

$resultat   =   $base_hndl -> query( $requete );

$fp = fopen( $namefile . 'csv', 'w' );

while( $list = $resultat -> fetchArray( SQLITE3_ASSOC ) ) {
    fputcsv( $fp, $list, $delimiter = ';' );
}

fclose( $fp );

For test purposes you may want to rebuild this CSV in an array to better view. In the manual you have a little function legal that allows you to do this.

Answer 2

That’s because you’re recording out of cycle for, just tape in:

$base_hndl  =   new SQLite3($dir.$base);
$requete    =   "SELECT * FROM contact ORDER BY id desc";   
$resultat   =   $base_hndl->query($requete);    
$affiche    =   $resultat->fetchArray(); 
$nombreid = $affiche['id'];

$fp = fopen($namefile.'.csv', 'w');
$list = array();

for($i=1;$i<=$nombreid;$i++)
{       
    $requete    =   "SELECT * FROM contact WHERE (id=$i)";  
    $resultat   =   $base_hndl->query($requete); 
    $affiche    =   $resultat->fetchArray();

    $list = $affiche;

    fputcsv($fp,$list,$delimiter = ';');//<----------------DENTRO DO CICLO
}

fclose($fp);