Solved

mysqli_fetch_field_direct to PDO

Posted on 2014-04-21
6
604 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:M. Jayme Nagy
  • 4
  • 2
6 Comments
 
LVL 108

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:M. Jayme Nagy
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 108

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 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:M. Jayme Nagy
ID: 40013162
excellent thanks a lot!
0
 
LVL 108

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now