Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

mysqli_fetch_field_direct to PDO

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
J N
Asked:
J N
  • 4
  • 2
1 Solution
 
Ray PaseurCommented:
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
 
J NUnicorn wranglerAuthor Commented:
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
 
Ray PaseurCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Ray PaseurCommented:
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
 
J NUnicorn wranglerAuthor Commented:
excellent thanks a lot!
0
 
Ray PaseurCommented:
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now