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;
}
PHP - Manual: mysql_field_flags
2024-11-15
mysql_field_flags
(PHP 4, PHP 5)
mysql_field_flags — 从结果中取得和指定字段关联的标志
说明
mysql_field_flags(resource
$result, int $field_offset): stringmysql_field_flags() 返回指定字段的字段标志。每个标志都用一个单词表示，之间用一个空格分开，因此可以用 explode() 将其分开。
如果 MySQL 版本足够新，则会支持以下的标志："not_null", "primary_key", "unique_key", "multiple_key", "blob", "unsigned", "zerofill", "binary", "enum", "auto_increment", "timestamp"。
为向下兼容仍然可以使用 mysql_fieldflags()，但反对这样做。
add a note
User Contributed Notes 10 notes
amir at scrounch dot com ¶
18 years ago
cufarley at nirvanet dot net ¶
20 years ago
Using the "DESC TableName" command may also do the trick and is a bit shorter.
pike-php at kw dot nl ¶
16 years ago
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;
}
?>
play at arcadevillage dot com ¶
12 years ago
To really backup the database values, I made a little changement :
My code really looks like bomas 's code, but there is an important diffence :
<?php
$nbc = mysql_num_fields($req_table);
while ($ligne = mysql_fetch_array($req_table))
{
$insertions = "INSERT INTO $table VALUES(";
for ($i=0; $i<$nbc; $i++)
{
if ( $i > 0 ) $insertions .= ", ";
if ( !isset($ligne[$i]))
$insertions .= "NULL";
else
$insertions .= "'" . mysql_real_escape_string($ligne[$i]). "'";
}
$insertions .= ");";
$dumpsql[] = $insertions;
}
?>
$dumpslq is the variable where I put the insertion orders before writting them in a text file.
Before, I test that the values if not NULL into the field because it is the only way to make the difference, for example for string text fields, between NULL values and empty strings.
If you don't make this test, you should find empty string instead of NULL values when you do the backup.
simone dot t at betisgroup dot com ¶
19 years ago
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>";
}
jakemsr at jakemsr dot com ¶
19 years ago
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"])));
}
bomas at cities-of-faith dot com ¶
16 years ago
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]` ( ";
$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(",", ", ", $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
buttrose at unimelb dot edu dot au ¶
17 years ago
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
justin DOT flavin AT ntlworld DOT com ¶
19 years ago
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>";
}
jurgen at alienguitar dot com ¶
21 years ago
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 ;-)
官方地址：https://www.php.net/manual/en/function.mysql-field-flags.php
IT
PHP
编程语言
Linux
开发编程
科技
Elasticsearch
HTML/CSS/XML
网络
JAVA
NoSQL
数据库
面试
C/C++
Golang
Git
算法
操作系统
正则表达式
Redis
互联网
MySql
JavaScript
运维
软件
国际
架构设计
Mac OS
TCP/IP
Excel
Vim
Windows
Socket
Oracle
VR
MongoDB
Python
运营
MemCache
商业
硬件
电子
娱乐
设计
nginx
摄影
游戏
WordPress
HTTP
团建
数码电器
广告
--
php7 安装fileinfo扩展
preg_split — 通过一个正则表达式分隔字符串
php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题
[PHP] inet_pton/inet_ntop IP地址转换函数
array_multisort() 多字段排序
PHPMailer设置utf8 PHPMailer字符集CharSet
ADORecordSet对象
adodb手册
PHP 8.1
[鸟哥]PHP_INT_MIN 和 -9223372036854775808
opcache预加载
Composer的Packagist资源
mysql面试题
[鸟哥]PHP FFI详解 - 一种全新的PHP扩展方式
PHP7添加redis扩展
PHP获取指定函数定义在哪个文件中及行号
PHP mkdir()写出来的权限与mode值不符合
adodb连接mysql多个数据库的问题
利用php soap实现web service
ADOConnection 公用函数