CODEWITHSUNDEEP
phpmysqlsqlpdo
Mitesh Panchal
Mitesh Panchal

Reputation: 61

How to get query columns and its datatype info formed in sql query using pdo php

I am trying to fetch the column's information from the query like its dataType, name and tableName, length isNull or its primary key.

For example if the query is 

select Name, price*version as revenue , color as colorType from carsRev

then it should return columns name like Name revenue colorType and their dataType.

I am using getColumnMeta

 $stmt = $this->_connection->prepare($query);
            $stmt->execute();
            foreach ( range ( 0, $stmt->columnCount() - 1 ) as $column_index )        { $meta [] = $stmt->getColumnMeta ( $column_index );
            }
            print_r($meta);

but its not giving derived column like revenue.

Upvotes: 1

Views: 149

Answers (1)

b2vincent
b2vincent

Reputation: 620

Curious to learn about PDO->getColumnMeta, I reproduced your test with the following elements.

In my configuration, PHP is 5.3.10 and MySQL is 5.5.21.

SQL to create and feed the table :

CREATE TABLE `carsrev` (
  `name` varchar(11) NOT NULL,
  `color` varchar(10) NOT NULL,
  `version` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `carsrev` (`name`, `color`, `version`, `price`) VALUES
  ('Cevalo', 'Blue', 1, '10.00'),
  ('Catz', 'Red', 2, '201.50');

PHP Code : 

ini_set('display_errors', 'On');
error_reporting(E_ALL);

$connect = new PDO('mysql:host=localhost;dbname=database', 'login', 'password'); 

$query =  "select name, price*version as revenue , color as colorType from carsrev";

$stmt = $connect->prepare($query);

$stmt->execute();

foreach ( range( 0, $stmt->columnCount() - 1 ) as $column_index ) { 
    $meta [] = $stmt->getColumnMeta ( $column_index );
}

print_r($meta);

I get the following result (reformatted to be more readable) :

Array ( [0] => Array ( [native_type] => VAR_STRING [pdo_type] => 2 [flags] => 
    Array ( [0] => not_null [1] => primary_key ) [table] => carsrev [name] => name [len] => 33 [precision] => 0 ) 

    [1] => Array ( [native_type] => NEWDECIMAL [pdo_type] => 2 [flags] => 
    Array ( [0] => not_null ) [table] => [name] => revenue [len] => 22 [precision] => 2 ) 

    [2] => Array ( [native_type] => VAR_STRING [pdo_type] => 2 [flags] => 
    Array ( [0] => not_null ) [table] => carsrev [name] => colorType [len] => 30 [precision] => 0 ) )

As you see, I can get the name and the format (NEWDECIMAL) of the derived column.

Upvotes: 1

Related Questions