mysql_fetch_row
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.
mysql_field_flags
(PHP 4, PHP 5)
mysql_field_flags — 결과로부터 특정 필드와 관련된 플래그를 반환
설명
string mysql_field_flags
( resource $result
, int $field_offset
)
mysql_field_flags()는 지정된 필드의 플래그를 반환한다. 플래그는 공백문자로 구분된 하나의 플래그당 하나의 단어로 구성되며, explode()를 사용하여 반환되는 값을 분리할 수 있다.
인수
- result
-
mysql_query() 호출을 통한 결과 resource.
- field_offset
-
The numerical field offset. The field_offset starts at 0. If field_offset does not exist, an error of level E_WARNING is also issued.
반환값
결과와 연관된 플래그 문자열을 반환하고, 실패하면 FALSE를 반환한다.
MySQL의 버전이 이들을 지원한다면, 다음 플래그들을 볼 수 있다: "not_null", "primary_key", "unique_key", "multiple_key", "blob", "unsigned", "zerofill", "binary", "enum", "auto_increment", "timestamp".
예제
Example #1 mysql_field_flags() 예제
<?php
$result = mysql_query("SELECT id,email FROM people WHERE id = '42'");
if (!$result) {
echo 'Could not run query: ' . mysql_error();
exit;
}
$flags = mysql_field_flags($result, 0);
echo $flags;
print_r(explode(' ', $flags));
?>
위 예제의 출력 예시:
not_null primary_key auto_increment
Array
(
[0] => not_null
[1] => primary_key
[2] => auto_increment
)
주의
Note:
하위 호환을 위하여, 다음의 권장하지 않는 별칭을 사용할 수 있습니다: mysql_fieldflags()
add a note
User Contributed Notes
mysql_field_flags
play at arcadevillage dot com
03-Aug-2009 07:07
Bob
05-May-2009 03:00
To parse an enum/set type correctly, including strings like ENUM('a\'b','c'), here is an almost pcre-free way that works well.
<?php
function parse_enum_values($typestr)
{
// remove ENUM/SET part
$typestr = preg_replace('/^(enum|set)/i', '', $typestr);
// trim '(', ')', ' off the ends of the string and then explode it on ','
return trim(explode("','", $typestr), "(')");
}
?>
It works because explode is looking for ',' - not ',\'.
dev at rgbworld dot com
13-Apr-2008 03:45
With regard to get_fields() by pike-php at kw dot nl
For 'enum and 'set' the code improperly parses single quote marks that should be allowed in the elements.
For example EMUM('a\'s', 'b') is parsed into 3 array elements when it should be 2.
Wrong:
[args] => Array
(
[0] => a
[1] => s
[2] => b
)
It should give:
[args] => Array
(
[0] => a's
[1] => b
)
I am not good with preg, but if someone could provide a solution, that would be great. Thank you!
bomas at cities-of-faith dot com
08-Sep-2005 01: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]` ( ";
$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
pike-php at kw dot nl
14-Jul-2005 04: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
20-Oct-2004 07: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
17-Aug-2003 10: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 04: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
06-Dec-2002 10: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 12: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 05:36
Using the "DESC TableName" command may also do the trick and is a bit shorter.
jurgen at alienguitar dot com
10-Jun-2001 04: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 ;-)