CODEWITHSUNDEEP
phpmysqlpdo
Leo Chan
Leo Chan

Reputation: 4427

How to get the field data type and in pdo?

Below is some code i tried to do this, I am using mysql 5.0+ and innodb engine. Can i use getColumnMeta (experiment ?) and how to use it to get datatype metadata?

Thank you.

 $types = array(
    PDO::PARAM_BOOL => 'bool',
    PDO::PARAM_NULL     => 'null',
    PDO::PARAM_INT      => 'int',
    PDO::PARAM_STR      => 'string',
    PDO::PARAM_LOB      => 'blob',
);

try{

$sql = 'SELECT COUNT(*) FROM subscriber,list_sub WHERE list_sub.ListID =? AND list_sub.SubID = subscriber.SubID  ';

$stmt = $conn->prepare($sql);
$stmt->execute(array($list));
$meta = $stmt->getColumnMeta(0);
var_dump($meta);
}
catch(PDOException $e)
    {
    die ($e->getMessage()."<a href='view.php' onClick='window.location.reload()'> Back</a>"); 
    }

Upvotes: 2

Views: 3957

Answers (1)

Dan Bemowski
Dan Bemowski

Reputation: 5375

I have a PDO database wrapper class that I built, and here is my method within my class for doing this. I do not know how portable it is as I have never used oracle, but it works for my situation using MySql. This requires that the DBMS supports "SHOW COLUMNS FROM", so it should work with any DBMS that supports that.

As can be seen, I use a try/catch block to catch any PDO exceptions that may occur and log the errors.

    /**
 * @ getFieldType - gets the field data type for a specific field in a table.
 *
 * @ param type: text $table = The name oof a data table.
 * @ param type: text $field = A field in the data table.
 */
public static function getFieldType($table, $field) {
    $ret = false;
    $sql = "SHOW COLUMNS FROM `$table` WHERE Field = '$field'";
    
    try {
        $result = self::dbQueryRow($sql);
        $ret = $result['Type'];
    } catch (PDOException $e) {
        self::logError($e, "getFieldType", $sql);
    }
    
    return $ret;
} //End public static function getFieldType

The dbQueryRow and logError methods used here should be self explanatory.

Upvotes: 1

Related Questions