mysql_field

add a note User Contributed Notes

bomas at cities-of-faith dot com
08-Sep-2005 04:52


well, to make a complete backup of your database, i suggest this code:



//open database here

$tab_status = mysql_query("SHOW TABLE STATUS");

while($all = mysql_fetch_assoc($tab_status)):

    $tbl_stat[$all[Name]] = $all[Auto_increment];

endwhile;

unset($backup);

$tables = mysql_list_tables('cofadmin');

while($tabs = mysql_fetch_row($tables)):

    $backup .= "--\n--Tabel structuur voor `$tabs[0]`\n--\n\nDROP IF EXISTS TABLE `$tabs[0]`\nCREATE TABLE IF NOT EXISTS `$tabs[0]` (&nbsp;";

    $res = mysql_query("SHOW CREATE TABLE $tabs[0]");

    while($all = mysql_fetch_assoc($res)):

        $str = str_replace("CREATE TABLE `$tabs[0]` (", "", $all['Create Table']);

        $str = str_replace(",", ",&nbsp;", $str);

        $str2 = str_replace("`) ) TYPE=MyISAM ", "`)\n ) TYPE=MyISAM ", $str);

        $backup .= $str2." AUTO_INCREMENT=".$tbl_stat[$tabs[0]].";\n\n";

    endwhile;

    $backup .= "--\n--Gegevens worden uitgevoerd voor tabel `$tabs[0]`\n--\n\n";

    $data = mysql_query("SELECT * FROM $tabs[0]");

    while($dt = mysql_fetch_row($data)):

        $backup .= "INSERT INTO `$tabs[0]` VALUES('$dt[0]'";

        for($i=1; $i<sizeof($dt); $i++):

            $backup .= ", '$dt[$i]'";

        endfor;

        $backup .= ");\n";

    endwhile;

    $backup .= "\n-- --------------------------------------------------------\n\n";

endwhile;

echo $backup;



this displayes your data the same way as phpmyadmin does.



hope it helps some of you guys

Greetz

pike-php at kw dot nl
15-Jul-2005 07:55


ok, sorry for the code bloat :) but this is how I 

get the full power of mysql's DESCRIBE table statement, in 

an associative array, including defaults, enum values, float radix et all.



it assumes mysql returns the type as 

   "type[(arg[,arg..])] [ add]"

like

   "float(20,6) unsigned"

   "enum('yes','no')"

etc



<?



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 args: $fullargs");

                }

            } else die("cant parse type: $fulltype");



        }

        return $fields;

            

    }



?>

buttrose at unimelb dot edu dot au
21-Oct-2004 10:26


This function is essential for writing a generic table editor (ie one that just takes the name of the table and works out what fields it has, types, sizes etc.). Unfortunately, I am using psotgreSQL not mySql. Postgres has field_type and field_size functions  but not as far as I can tell an equivalent of the mysql_field_flags() function. Without it, there is no way I can do generic ADDs and INSERTs. 



Anyone know a workaround to get this information (eg is the field a primary key? Can it be NULL? Is it auto_increment?) in Postgres?



Cheers



Rob Buttrose

amir at scrounch dot com
18-Aug-2003 01:39


returns primary keys of a table using 'show keys'


although it is possible to use desc, show keys offers possible enhancements such a getting sequence in index along with it





function getPrimaryKeyOf($table, $link) {


  $pk = Array(); 





  $sql = 'SHOW KEYS FROM `'.$table.'`'; 


  $res = mysql_query($table, $link) or die(mysql_error()); 


  while ($row = mysql_fetch_assoc($res)) { 


    if ($row['Key_name']=='PRIMARY') 


      array_push($pk, $row['Column_name']); 


  } 


  return $pk; 


}

simone dot t at betisgroup dot com
10-Apr-2003 07:20


Another examples :



####################################



function field_keys($host, $user, $password, $database, $field ) {

   $db_link = mysql_connect($host, $user, $password) or die ("error connect");

   mysql_select_db($database,$db_link);

   $query="DESC $field";

   $results=mysql_query($query);

   $i=0;

   while ($row=mysql_fetch_array($results)) {

         if ($row[Key]=="PRI") {

            $array_keys[$i]=$row[Field];

         }

         $i++;

  }

   return $array_keys;

}



####################################



//Example of Main...

$tmp = field_keys("localhost", "myuser", "mypassword", "mydb", "field_name" );



// ...loop through array...

foreach ( $tmp as $array_tmp){

    print "<br>";

    print $array_tmp;

    print "<br>";

}

justin DOT flavin AT ntlworld DOT com
07-Dec-2002 02:36


Sometimes, when writing a generic function or class, you want your script to be able to determine what the primary key of a table is. 

/* usual MySQL stuff */

$query="DESC UsersTable";

$results=mysql_query($query);



while ($row=mysql_fetch_array($results))

{

if ($row[Type]="PRI")

    {

print "I found the primary key! <br>";

$UserKey=$row[Field];

print $row[Field];

/* drop out , as we've found the key */    

exit;

    }

}



..... later on we might have something like



< some sort of loop through records >



print "<a href='View_User_record.php?userkey=$UserKey'> Users Name </a>";



<end loop>



What's also interesting is the useful data you can get from

a DESC query.



The following prints out the array values grabbed by mysql_fetch_array on a DESC query - VERY useful stuff!!!



/* usual MySQL stuff */

$query="DESC UsersTable";

$results=mysql_query($query);



while ($row=mysql_fetch_array($results))

{

print "<pre>";

print_r ($row);

print "</pre>";

}

jakemsr at jakemsr dot com
31-Oct-2002 04:32


I didn't find anything to get the valid values for

ENUM or SET column types, so I came up with the

following



function mysql_enum_values($table, $field)

{

    $sql = "SHOW COLUMNS FROM $table LIKE '$field'";

    $sql_res = mysql_query($sql)

        or die("Could not query:\n$sql");

    $row = mysql_fetch_assoc($sql_res);

    mysql_free_result($sql_res);

    return(explode("','",

        preg_replace("/.*\('(.*)'\)/", "\\1",

            $row["Type"])));

}

cufarley at nirvanet dot net
11-Jul-2001 08:36


Using the "DESC TableName" command may also do the trick and is a bit shorter.

jurgen at alienguitar dot com
11-Jun-2001 07:50


The previous problem to get the default values of a column:


Use the following query and parse the 'Default' column:





"SHOW COLUMNS FROM TableName"





or for a single entry:





"SHOW COLUMNS FROM TableName LIKE 'column'"





It will give you also values for Type,  Null, Key and Extra (check with mysql program first, so you see what you get ;-)

add a note

mysql_field_flags

说明