Solved

mysqli_fetch_field_direct to PDO

Posted on 2014-04-21
6
676 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 110

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 110

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 110

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 110

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use session variables in php? 22 89
JSON decode 5 43
MySQL - Adding an incremented value to a duplicate string 30 51
Code planning methods/tools? 5 55
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo‚Ķ
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

737 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