?
Solved

mysqli_fetch_field_direct to PDO

Posted on 2014-04-21
6
Medium Priority
?
874 Views
Last Modified: 2014-04-21
Hi,

Ive been looking for a pdo function that does the same things as the mysqli_fetch_field_direct all i can find at the moment is getColumnMeta but i read that it is experimental so i do not want to use it.

thanks

mysqli_fetch_field_direct  returns meta-data for a single field (column) in the result set, as an object.
0
Comment
Question by:J N
  • 4
  • 2
6 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40013014
There are a lot of things that are not implemented or not implemented very well in PDO.  For example, with MySQL and MySQLi you can use the data_seek() functions to retrieve rows from a results set without rerunning the query.  Not so with PDO.

My recommendation would be to run a query with something like "show columns full" if you want to get some of the meta-data.  What information are you looking for, specifically?
0
 
LVL 6

Author Comment

by:J N
ID: 40013022
Hi,

thanks for the info!

Essentially, i have a logs table and i want to be able to download it as an csv file.

I have done it this way but i made the switch to pdo which does not all the below function to work properly

$output = "";
	$table ="SELECT * FROM logs";
	$sql = mysqli_query($link, $table) or die(mysqli_error($link));
	$columns_total = mysqli_num_fields($sql);
	
	// Get The Field Name
	
	for ($i = 0; $i < $columns_total; $i++) {
		$heading = mysqli_fetch_field_direct($sql, $i);
	}
	
	// Get Records from the table
	print_r($heading);
	while ($row = mysqli_fetch_array($sql)) {
	for ($i = 0; $i < $columns_total; $i++) {
	$output .='"'.$row["$i"].'",';
	}
	$output .="\n";
	}
	
	// Download the file
	$date_file=date("F-j-Y-g-i-a");
	$filename = "logs-".$date_file.".csv";
	header('Content-type: application/csv');
	header('Content-Disposition: attachment; filename='.$filename);
	
	
	echo $output;

Open in new window


i changed this into
/ Fetch Record from Database
	$sql = "SELECT * FROM logs";
	$output = "";
	$query= $conn->query($sql)or die;
	$columns_total = $query->columnCount();
	
	// Get The Field Name
	
	for ($i = 0; $i < $columns_total; $i++) {
		$heading = $query->getColumnMeta($i);
	}
	
	
	print_r($heading);  
	// Get Records from the table
	
	while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
		for ($i = 0; $i < $columns_total; $i++) {
			$output .='"'.$row["$i"].'",';
		}
			$output .="\n";
	}
	
	// Download the file
	$date_file=date("F-j-Y-g-i-a");
	$filename = "logs-".$date_file.".csv";
	header('Content-type: application/csv');
	header('Content-Disposition: attachment; filename='.$filename);
	
	
	echo $output;

Open in new window


but i run into problems with the getColumnmeta

Any suggestions?
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40013034
I think you do not need to "know" the column count in order to create the CSV file. Please tell me a little more about the desired output... Do you need a header row giving the column names?

I'll see if I can come up with an example for you.  I've always preferred MySQLi to PDO for things like this, but it should be equally doable with PDO.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 40013086
Obviously this is untested code, since I do not have your data model to test with, but I think it represents a good place to start.  Please read it over and post back if you have questions.

<?php // demo/temp_jaymenagy.php
error_reporting(E_ALL);

/**
 * SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28416867.html
 *
 * CODE ASSUMES A CONNECTION TO THE PDO OBJECT IN $conn
 */

// PREPARE DATA FOR HEADERS (NOTE USE OF ISO-8601 DATE FORMAT!)
$datefile = date("Y-m-d\TH:i:s");
$filename = "logs-".$datefile.".csv";


// OPEN THE OUTPUT DATA SET
$fpw = fopen($filename, 'w');
if (!$fpw) trigger_error("UNABLE TO OPEN $fpw", E_USER_ERROR);


// RUN THE QUERY
$sql  = "SELECT * FROM logs";
$pdos = $conn->prepare($sql);
try
{
    $pdos->execute();
}
catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_ERROR);
}


// RETRIEVE THE DATA AND WRITE A TEMPORARY FILE
while ($row = $pdos->fetchAll(PDO::FETCH_ASSOC))
{
    // GET TOP ROW SO WE CAN GET COLUMN NAMES (ARRAY KEYS)
    $arr = current($row);
    $arr = array_keys($arr);
    fputcsv($fpw, $arr);

    // ROW BY ROW PROCESSING IS DONE HERE
    foreach ($row as $key => $arr)
    {
        fputcsv($fpw, $arr);
    }
}


// COPY THE FILE TO THE BROWSER OUTPUT STREAM
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=$filename");

readfile($filename);
unlink($filename);

Open in new window

0
 
LVL 6

Author Comment

by:J N
ID: 40013162
excellent thanks a lot!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40013398
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question