...

View Full Version : finding primary key, data types used in a table



chump2877
08-07-2006, 06:08 AM
Is there an easy way to get the name of the column that is the primary key of a MySQL table (using a MySQL query of some sort)?

Also, is there an easy way to get the data type that is associated with any given column name in a MySQL table (using a MySQL query of some sort)?

Thanks.

GJay
08-07-2006, 08:17 AM
If you take a look at the tables in the 'mysql' database, I'd assume it will involve them.
I know how to do it in postgres, and when I was figuring that out, some of the ADODB (a DB-abstraction class) came in really useful, as it has the queries for a number of databases for doing various things, primary key finding being one of them.
http://phplens.com/adodb I think, then take a look at drivers/adodb-mysql...

raf
08-07-2006, 08:36 AM
Is there an easy way to get the name of the column that is the primary key of a MySQL table (using a MySQL query of some sort)?
if you are using PHP:
http://www.php.net/manual/en/function.mysql-field-flags.php
[QUOTE=chump2877]
Also, is there an easy way to get the data type that is associated with any given column name in a MySQL table (using a MySQL query of some sort)?/QUOTE]
if you are using PHP:
http://www.php.net/manual/en/function.mysql-fetch-field.php

without PHP, you can just use the "describe" statement --> http://dev.mysql.com/doc/refman/5.1/en/describe.html

chump2877
08-07-2006, 05:13 PM
thanks for the good responses guys....

this PHP function ultimately did the trick for me...it puts all the table info into a multi-dimesnional array:


function getFields($tablename)
{
$fields = array();
$fullmatch = "/^([^(]+)(\([^)]+\))?(\s(.+))?$/";
$charlistmatch = "/,?'([^']*)'/";
$numlistmatch = "/,?(\d+)/";

$fieldsquery .= "DESCRIBE $tablename";
$result_fieldsquery = mysql_query($fieldsquery) or die(mysql_error());
while ($row_fieldsquery = mysql_fetch_assoc($result_fieldsquery))
{
$name = $row_fieldsquery['Field'];
$fields[$name] = array();
$fields[$name]["type"] = "";
$fields[$name]["args"] = array();
$fields[$name]["add"] = "";
$fields[$name]["null"] = $row_fieldsquery['Null'];
$fields[$name]["key"] = $row_fieldsquery['Key'];
$fields[$name]["default"] = $row_fieldsquery['Default'];
$fields[$name]["extra"] = $row_fieldsquery['Extra'];

$fulltype = $row_fieldsquery['Type'];
$typeregs = array();

if (preg_match($fullmatch, $fulltype, $typeregs))
{
$fields[$name]["type"] = $typeregs[1];
if ($typeregs[4]) $fields[$name]["add"] = $typeregs[4];
$fullargs = $typeregs[2];
$argsreg = array();
if (preg_match_all($charlistmatch, $fullargs, $argsreg))
{
$fields[$name]["args"] = $argsreg[1];
}
else
{
$argsreg = array();
if (preg_match_all($numlistmatch, $fullargs, $argsreg))
{
$fields[$name]["args"] = $argsreg[1];
}
}
}
else die("cant parse type: $fulltype");
}

return $fields;
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum