Print array in a file

0

I am inserting data from an sqlite database into a file. I create the file with a name that the user chooses, and I enter all the data in the table.

The problem is that you only insert a table row in the file, whereas there are more rows in the table. And repeat 2 times the fields.

$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);
    
asked by anonymous 25.11.2014 / 17:32

2 answers

1

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

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

    // ...

    $list = $affiche; <--
}

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

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

It then remains to invoke the function inside the loop as suggested by @Jorge B., which according to the comments produces an unwanted repetition.

I believe this is happening because of your for-loop that is defined in a way with no logic (from the external viewpoint, after all I do not know your application).

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

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

    // Do somethng
}

Invoke fputcsv () in there, by itself, would already work, but still with duplicate results.

This duplicity occurs because, I do not know why, fetching data functions has, to date, return data in two ways: indexed and associative.

In the case of SQLIte, the default value SQLITE3_BOTH causes that $ list to look something like this:

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), resolves this duplicity problem.

Notice that I highlight this 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
}

Repeat ALL loop statements as many times as the pointer is positively tested to be less than or equal to $ nombreid .

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

But let's imagine that this variable is 3 , after all it comes from an ID and 3 is a valid ID. This, with the current definition of your for-loop, would make the whole statement repeat 3 times.

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

Anyway, as always, the simplest solution is the most correct:

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

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

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

fclose( $fp );

For testing purposes you may want to rebuild this CSV in an array for better visualization. In the manual you have a cool function that lets you do that.

    
25.12.2014 / 22:26
0

This is because you are recording out of the for cycle, just record it inside:

$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);
    
25.11.2014 / 17:48