Link to home
Create AccountLog in
Avatar of pixalax
pixalaxFlag for Poland

asked on

Getting MySql Table Field Names with PDO

Hello all,

 I was wondering how could I get mysql table field names with PDO (also table names would be great). I know how to do that in old school mysql but I have no idea how to do that using PDO.

Thank you for your time and concern in advance.
Avatar of psychic_zero
psychic_zero
Flag of Malaysia image


function getColumnNames(){

$sql = 'select column_name from information_schema.columns where lower(table_name)=lower(\''.$this->table.'\')';

#$sql = 'SHOW COLUMNS FROM ' . $this->table;

$stmt = $this->connection->prepare($sql);

try {    
      if($stmt->execute()){
         $raw_column_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

         foreach($raw_column_data as $outer_key => $array){
            foreach($array as $inner_key => $value){
                if (!(int)$inner_key){
                    $this->column_names[] = $value;
                }
            }
        }
     }

     return $this->column_names;

   } catch (Exception $e){
        return $e->getMessage(); //return exception
     }        
}

Open in new window

Avatar of Jagadishwor Dulal
function getColumnNames(){
             
        $sql = 'select column_name from information_schema.columns where lower(table_name)=lower(\''.$this->table.'\')';
        #$sql = 'SHOW COLUMNS FROM ' . $this->table;
         
        $stmt = $this->connection->prepare($sql);
             
        try {    
            if($stmt->execute()){
                $raw_column_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
                 
                foreach($raw_column_data as $outer_key => $array){
                    foreach($array as $inner_key => $value){
                                if (!(int)$inner_key){
                                    $this->column_names[] = $value;
                                }
                    }
                }
                }
                return $this->column_names;
            } catch (Exception $e){
                    return $e->getMessage(); //return exception
            }        
        }
Sorry - this is a better example.  HTH, ~Ray
<?php // RAY_mysql_example_7.php
error_reporting(E_ALL);
echo "<pre>\n"; // READABILITY FOR VAR-DUMPS


// DEMONSTRATE HOW TO GET INFORMATION ABOUT MySQL TABLES AND COLUMNS


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
if (!$dbcx = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $dbcx))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES




// FINDING THE NAMES OF OUR TABLES
$sql = "SHOW TABLES";

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
// MAN PAGE:http://php.net/manual/en/function.mysql-query.php
if (!$res = mysql_query($sql, $dbcx))
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-error.php
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// GET THE LIST OF TABLE NAMES
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
while ($show_tables = mysql_fetch_array($res))
{
    $my_tables[] = $show_tables[0];
}
if (empty($my_tables)) die('NO TABLES');
var_dump($my_tables);





// CHOOSE A TABLE AND GET THE COLUMN NAMES
$table_name = $my_tables[0];

$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    echo "<br/>$table_name HAS NO COLUMNS";
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    var_dump($my_columns);
}





// CHOOSE A TABLE AND GET THE CREATE TABLE STATEMENT FOR IT
$table_name = $my_tables[0];

$sql = "SHOW CREATE TABLE $table_name";
if (!$res = mysql_query($sql, $dbcx))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
while ($show_creates = mysql_fetch_assoc($res))
{
    $my_creates[] = $show_creates["Create Table"];
}
var_dump($my_creates);






// GET A LIST OF ALL THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($dbcx))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB LIST: ";
   echo "<br/> $errmsg <br/>";
   die();
}
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-object.php
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}
echo "<br/>LIST OF DB NAMES: ";
var_dump($db_names);

Open in new window

Avatar of pixalax

ASKER

Hello all,
 Thank you for your replies. I would like to do that with PDO since I am using PDO in my classes. Currently I am already fetching field names with Mysql. PDO doesn't accept such sql query I guess I am doing wrong.

This is my old school mysql style fetching fields method. There is nothing like "mysql_field_name ()" method in PDO I believe that's why I am currently fetching them old school.
protected static function fetch_fields () {
		
		$conn = mysql_connect(HOST, DB_USER, DB_PASSWORD);
		$db_select = mysql_select_db(DB_NAME, $conn);
		
		
		$query = "SELECT * FROM ".DB_PREFIX.static::$table_name." WHERE 0";
		$result = mysql_query($query);
		
		$field_count = mysql_num_fields( $result );
		   
		for ( $i = 0; $i < $field_count; $i++ ) {
			   
		$names[] = mysql_field_name( $result, $i );
			   
		}
		
		mysql_close($conn);
		unset($conn);
			   
 		return $names;
	}

Open in new window


How can I do exactly this with PDO? I guess this is better way to ask.
Try this query string and post back with what you get, OK?

$sql = "SHOW TABLES";
Avatar of pixalax

ASKER

Hello Ray,
Thank you for your help. Unfortunately I couldn't make it work. I can simply make a query like examples above;

 $raw_column_data = $stmt->fetchAll(PDO::FETCH_ASSOC);

Open in new window


But this will not work if there is no data in the table because there is simply nothing to fetch!

I guess I will keep my old school code for now. Seems like PHP is not that flexible as I thought or I just can not express exactly what I need. Since I can't get to fix problems I have to find other ways to do go around it.
Can you post your PDO example of something that works for you?  I'll try to work from that foundation and see if I can get you a good answer.
Avatar of pixalax

ASKER

Better to post all my codes to make it clear how I want to do it.
database.class.php
class Database {
	public $handler = NULL; // Database handler
	
	public function __construct () {
		try {
			$this->handler = new PDO("mysql:host=".HOST.";dbname=".DB_NAME."", DB_USER, DB_PASSWORD);
			
			//LETS CHOOSE ERROR MODE
			# PDO::ERRMODE_SILENT // IDEAL FOR DRY PROGRAMMING
			# PDO::ERRMODE_WARNING // USEFUL FOR DEBUGGING
			# PDO::ERRMODE_EXCEPTION // YOU SHOULD WANT IT IN MOST SITUATIONS
			$this->handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			
			//ECHO A MESSAGE SAYING WE HAVE CONNECTED FOR TESTING THE STRING
			//echo "Connected to database"; 
		}
		catch(PDOException $e) {  
		     echo __LINE__. " | " .$e->getMessage();
			 // LOG THE ERROR
			#file_put_contents('PDOErrors.txt', $e->getMessage(), FILE_APPEND);
		}
	}
	
	public function __destructor () {
		$this->handler = NULL;	 // SETTING HANDLER TO NULL CLOSES THE CONNECTION PROPERLY
	}
	
	public function query($sql) {
		$sth = $this->handler->query($sql); // STATEMENT HANDLER
		// RETURNS AN ASSOCIATIVE OBJECT THAT CAN BE DIRECTLY ACCESS OR LOOPED
		$sth->setFetchMode(PDO::FETCH_OBJ);
		$result = $sth->fetchAll();
		return $result;
	}
	
}
$db = new Database ();

Open in new window


databaseobjects.class.php
class DatabaseObjects {

// THIS IS STILL OLD SCHOOL MYSQL CONNECTION, CHANGE LATER WITH PDO
	protected static function fetch_fields () {
		
		$conn = mysql_connect(HOST, DB_USER, DB_PASSWORD);
		$db_select = mysql_select_db(DB_NAME, $conn);
		
		
		$query = "SELECT * FROM ".DB_PREFIX.static::$table_name." WHERE 0";
		$result = mysql_query($query);
		
		$field_count = mysql_num_fields( $result );
		   
		for ( $i = 0; $i < $field_count; $i++ ) {
			   
		$names[] = mysql_field_name( $result, $i );
			   
		}
		
		mysql_close($conn);
		unset($conn);
			   
 		return $names;
	}

}

Open in new window


user.class.php
class User extends DatabaseObjects {

// STATIC CLASS VARS
	static $table_name = "members";
	static $table_fields = array();
// FIELDS
	public $id;
	public $name;
	public $surname;
	public $username;
	public $city;
	public $email;
        public $address;

// PROTECTED FIELDS
	public $passwd;

function __construct () {
		self::$table_fields = static::fetch_fields();	
	}
}

Open in new window


this way I can fetch fields so later on I can use it in DatabaseObjects class's method while INSERTING a record in database easier. No need to repeat myself for any class & database table. One single loop will do the trick, all I have to do send an values as an array to INTERTING method with named placed holders.

 I believe this way would be the most secure way. Since I can sanitize values while preparing the array and also PDO will prevent itself from sql injections while I am using named placed holders.

 I am not an expert, it was more or less a month ago when I read on php.net what is "echo", so you can imagine I have no practice at all and often I have to check php.net for some built in functions to help me out in easiest way.

 Thank you for your time and concern.
Uhh, looking at the databaseObjects class... Any reason why you do not check to see if the queries worked?  I am wondering about this on line 10:

$query = "SELECT * FROM ".DB_PREFIX.static::$table_name." WHERE 0";

It appears that the variable $table_name is undefined, and that might cause the query to fail.  You would want to test the value in $result to see if it is FALSE, and if so, you would want to print the value of $query, along with the values in mysql_errno() and mysql_error().

Looking at the database class... Can you run this successfully?  I am sure the query will generate an error, but I just want to see if it gets through PDO to MySQL and comes back with something (probably "Table 'foo' not found").

$db = new Database ();
$thing = $db->query('SELECT foo FROM bar');
var_dump($thing);
Avatar of pixalax

ASKER

This is the problem, I can't pass this query to PDO, it generates error but this mysql style works.

The reason why I am using this sql;
$sql="SELECT * FROM {$table_name} WHERE 0";

Open in new window


instead of such query ;
$sql= "select column_name from information_schema.columns.....

Open in new window


 First query is a lot faster, specially if you work with lots of table fields, you can see the difference.

I thought about PDO::FETCH_CLASS but this actually doesn't assign any values to $user, when I try to echo $user->username, I just see blank page.
Avatar of pixalax

ASKER

I forgot to add, since I am calling User::find_all(); for example and User class is extended to DatabaseObjects, I can use it with late static binding "static::$table_name", to get called classes static $table_name variable.

user.class.php (line 3-5) Extending DatabaseObjects class
// STATIC CLASS VARS
	static $table_name = "members";

Open in new window


databaseobjects.class.php (line 10) static::$table_name, late static binding
$query = "SELECT * FROM ".DB_PREFIX.static::$table_name." WHERE 0";

Open in new window


This way I get to handle everything from databaseobjects class. If I want to overwrite some methods I can do that in other classes such as user class. This will also give me a chance to check how parent class would respond which would be way a lot flexible and clean coding ready to use in bigger projects easily.

 I create a class and a class file for each database table. My aim is to not repeat myself over and over again while classes can do exactly this for me and give me a chance to do more coding in short time.
Looking at the database class... Can you run this successfully?  I am sure the query will generate an error, but I just want to see if it gets through PDO to MySQL and comes back with something (probably "Table 'foo' not found").

$db = new Database ();
$thing = $db->query('SELECT foo FROM bar');
var_dump($thing);

Please run that and post the output of the var_dump() here, thanks.
Avatar of pixalax

ASKER

I am working on full database (building new system on our old system). That's why I created another table in database.

$thing = $db->query('SELECT id FROM members');
var_dump($thing);

Open in new window


Output
array(3) { [0]=> object(stdClass)#6 (1) { ["id"]=> string(1) "1" } [1]=> object(stdClass)#7 (1) { ["id"]=> string(1) "2" } [2]=> object(stdClass)#8 (1) { ["id"]=> string(1) "3" } }
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of pixalax

ASKER

Thank you Ray for your patience, you are really helpful.

Output
array(6) { [0]=> object(stdClass)#6 (6) { ["Field"]=> string(2) "id" ["Type"]=> string(7) "int(11)" ["Null"]=> string(2) "NO" ["Key"]=> string(3) "PRI" ["Default"]=> NULL ["Extra"]=> string(14) "auto_increment" } [1]=> object(stdClass)#7 (6) { ["Field"]=> string(16) "site_kategori_id" ["Type"]=> string(7) "int(11)" ["Null"]=> string(2) "NO" ["Key"]=> string(0) "" ["Default"]=> NULL ["Extra"]=> string(0) "" } [2]=> object(stdClass)#8 (6) { ["Field"]=> string(8) "forum_id" ["Type"]=> string(7) "int(11)" ["Null"]=> string(2) "NO" ["Key"]=> string(0) "" ["Default"]=> string(1) "0" ["Extra"]=> string(0) "" } [3]=> object(stdClass)#9 (6) { ["Field"]=> string(8) "site_adi" ["Type"]=> string(12) "varchar(255)" ["Null"]=> string(2) "NO" ["Key"]=> string(0) "" ["Default"]=> NULL ["Extra"]=> string(0) "" } [4]=> object(stdClass)#10 (6) { ["Field"]=> string(8) "site_url" ["Type"]=> string(12) "varchar(255)" ["Null"]=> string(2) "NO" ["Key"]=> string(0) "" ["Default"]=> NULL ["Extra"]=> string(0) "" } [5]=> object(stdClass)#11 (6) { ["Field"]=> string(6) "koruma" ["Type"]=> string(6) "int(1)" ["Null"]=> string(2) "NO" ["Key"]=> string(0) "" ["Default"]=> string(1) "1" ["Extra"]=> string(0) "" } }
Great!  One more test...  Note that the var_dump() output is easier to read if you echo "<pre>" before the dump.

$db = new Database ();
$thing = $db->query('SHOW TABLES');
echo "<pre>";
var_dump($thing);

Please run that and post the output of the var_dump() here, thanks.
Avatar of pixalax

ASKER

Hello Ray,
Thanks once again, here is the output (please note that I am already working with few tables from old system)
array(18) {
  [0]=>
  object(stdClass)#6 (1) {
    ["Tables_in_admin"]=>
    string(12) "str_bankalar"
  }
  [1]=>
  object(stdClass)#7 (1) {
    ["Tables_in_admin"]=>
    string(12) "str_firmalar"
  }
  [2]=>
  object(stdClass)#8 (1) {
    ["Tables_in_admin"]=>
    string(10) "str_icerik"
  }
  [3]=>
  object(stdClass)#9 (1) {
    ["Tables_in_admin"]=>
    string(23) "str_icerik_kategorileri"
  }
  [4]=>
  object(stdClass)#10 (1) {
    ["Tables_in_admin"]=>
    string(11) "str_menuler"
  }
  [5]=>
  object(stdClass)#11 (1) {
    ["Tables_in_admin"]=>
    string(12) "str_moduller"
  }
  [6]=>
  object(stdClass)#12 (1) {
    ["Tables_in_admin"]=>
    string(18) "str_para_birimleri"
  }
  [7]=>
  object(stdClass)#13 (1) {
    ["Tables_in_admin"]=>
    string(14) "str_siparisler"
  }
  [8]=>
  object(stdClass)#14 (1) {
    ["Tables_in_admin"]=>
    string(20) "str_siparisler_sepet"
  }
  [9]=>
  object(stdClass)#15 (1) {
    ["Tables_in_admin"]=>
    string(21) "str_site_kategorileri"
  }
  [10]=>
  object(stdClass)#16 (1) {
    ["Tables_in_admin"]=>
    string(11) "str_siteler"
  }
  [11]=>
  object(stdClass)#17 (1) {
    ["Tables_in_admin"]=>
    string(10) "str_upload"
  }
  [12]=>
  object(stdClass)#18 (1) {
    ["Tables_in_admin"]=>
    string(20) "str_urunler_kategori"
  }
  [13]=>
  object(stdClass)#19 (1) {
    ["Tables_in_admin"]=>
    string(16) "str_uye_gruplari"
  }
  [14]=>
  object(stdClass)#20 (1) {
    ["Tables_in_admin"]=>
    string(10) "str_uyeler"
  }
  [15]=>
  object(stdClass)#21 (1) {
    ["Tables_in_admin"]=>
    string(17) "str_uyeler_online"
  }
  [16]=>
  object(stdClass)#22 (1) {
    ["Tables_in_admin"]=>
    string(21) "str_yonetici_gruplari"
  }
  [17]=>
  object(stdClass)#23 (1) {
    ["Tables_in_admin"]=>
    string(12) "str_yorumlar"
  }
}
Avatar of pixalax

ASKER

Thank you for your help Ray. You saved my day and my hopes.

working replacement
[code]
protected static function fetch_fields () {
            global $db;
            
            $query = "SHOW COLUMNS FROM ".DB_PREFIX.static::$table_name;
            
            $objects = $db->query($query);

            $names=array();
            foreach ($objects as $object) {
                  $names[]=$object->Field;      
            }
return $names;
      }
[/code]
Thanks for the points!  Glad you're getting the results you need.  Best, ~Ray