[RESOLVED] probs with result : I'm getting named key and number key

[steamPunk]

Hi

I don't know if the thread title really makes sense but basically i have a query that returns the results array and then i loop through it to output a csv file

the problem is that I use the db table col names as col names in the csv and when i loop through it outputs the col name and the col number for each one - - then it outputs each variable value twice, too, like this :

client_id;0;client_nom;1;client_prenom;2;client_dob;3;client_email;4;client_titre;5;client_adresse;6;client_cp;7;client_ville;8;client_tel;9;client_pays;10;client_lang;11;client_date;12;client_how;13;

83;83;Johnson;Johnson;Susan;Susan;0000-00-00;0000-00-00;[e-mail];[e-mail];ms;ms;kuykuyfk uyfkuyfky fkyf kuyfkuyfk;kuykuyfk uyfkuyfky fkyf kuyfkuyfk;12345;12345;London;London;12 34 56 78 90;12 34 56 78 90;England;England;en;en;2008-11-17;2008-11-17;blah;blah;

the query code is this :

function selectQuery($conn,$sql){

op($sql);
$out = array();
$sth = $conn -> prepare($sql);
$sth->execute();
$res = $sth->fetchAll();
$sth->closeCursor();
//print_r($res);

if (!$res) {
	op("no res");
	//print_r($conn->errorInfo());
}else{
	$i = 1;						
	foreach ($res as $row) {
		$out[$i] = array();
		foreach($row as $k => $v){												
			$out[$i][$k] = $v;
		}	
		$i++;
	}		
}	
return $out;	
}

and the csv outputting is this :

		$sql = "SELECT * FROM `client` WHERE ".$sqlPartsToGetTotalResults;
		$export = selectQuery($conn,$sql);

	$header = "";
	foreach($export[1] as $k => $v){
		$header .= $k.";";
	}
	$header .= "\r\n";

	$data = "";

	for($c=1;$c<=count($export);$c++){
		foreach($export[$c] as $k => $v){
			$data .= $v.";";
		}
		$data .= "\r\n";
	}

	header("Content-type: application/octet-stream");
	header("Content-Disposition: attachment; filename=export.txt");
	header("Pragma: no-cache");
	header("Expires: 0");
	print($header."\n".$data);
	exit;

what would i need to change so that i just output the named key/value pairs ?

thanks

PS is there any way to force the output file into UTF-8 mode ?

[Weedpacket]

Yes; the [man]PDOStatement.fetchAll/man method is behaving as advertised. You want to look at its fetch_style parameter.

[steamPunk]

thanks for your reply, Mr packet

the thing i don't get is if I do

	
$sth->execute();
$res = $sth->fetchAll();

then $res is an array containing all the rows but with the number and column name indexes

but if I do

	
$sth->execute();
$res = $sth->fetch(PDO::FETCH_ASSOC);

then $res is just 1 row from the results set and I can't use the same code

is there not a thing like $sth->fetchAll(); that will return all the rows with just the column name indexes ??

Looking at the documentation it would seem that there isn't, and I will have to write a new function to deal with it ... oh well

[Weedpacket]

steampunk wrote:

then $res is just 1 row from the results set and I can't use the same code

Well yes, that's what happens when you switch from fetchAll() to fetch().

is there not a thing like $sth->fetchAll(); that will return all the rows with just the column name indexes ??

Yes, it's called fetchAll(); use its fetch_style parameter.

[steamPunk]

aha

previously when i read the parameters for PDOStatement->fetchAll in the manual i didn't see the paramater PDO::FETCH_ASSOC mentioned :

 fetch_style

Controls the contents of the returned array as documented in PDOStatement::fetch(). Defaults to PDO::FETCH_BOTH.

To return an array consisting of all values of a single column from the result set, specify PDO::FETCH_COLUMN. You can specify which column you want with the column-index parameter.

To fetch only the unique values of a single column from the result set, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_UNIQUE.

To return an associative array grouped by the values of a specified column, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_GROUP.

so I had assumed that it wasn't an option.

I've just tried it and yes it is an option - so I'm sorry if I appeared dense !

thanks

[Weedpacket]

Ah, you just missed dereferencing the pointer:

Controls the contents of the returned array as documented in PDOStatement::fetch().[Emphasis mine]