Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,845
    Thanks
    21
    Thanked 157 Times in 148 Posts

    Question finding primary key, data types used in a table

    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.
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :-)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!
    ♪♪ …Need Web Hosting For My YouTube-To-Mp3 Conversion Software? Check Here !!… ♪♪

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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...

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by chump2877
    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/functio...ield-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/functio...etch-field.php

    without PHP, you can just use the "describe" statement --> http://dev.mysql.com/doc/refman/5.1/en/describe.html
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #4
    Senior Coder chump2877's Avatar
    Join Date
    Dec 2004
    Location
    the U.S. of freakin' A.
    Posts
    2,845
    Thanks
    21
    Thanked 157 Times in 148 Posts
    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:

    PHP Code:
    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;
       } 
    Regards, R.J.

    ---------------------------------------------------------

    Help spread the word! Like my YouTube-to-Mp3 Conversion Script on Facebook !! :-)
    [Related videos and tutorials are also available at my YouTube channel and on Dailymotion]
    Get free updates about new software version releases, features, and bug fixes!
    ♪♪ …Need Web Hosting For My YouTube-To-Mp3 Conversion Software? Check Here !!… ♪♪


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •