Převod MySQL tabulky do textového souboru a zpět

30. června 2003

•

Jedna z mála nevýhod databázového systému MySQL spočívá v omezené možnosti přístupu. Server přijímá a provádí jednotlivé instrukce, ale rychlá úprava několika údajů ve vícero tabulkách může být poměrně zdlouhavá. Občas zkrátka nastane situace, kdy je výhodnější editovat data jako textový soubor. Tuto situaci můžeme snadno vyřešit dvěma PHP skripty.

Princip celé aplikace je jednoduchý. Při zavolání skriptu down.php se všechny tabulky z databáze zkopírují do textových souborů. Každá tabulka bude promítnuta do jednoho souboru, který ponese název původní tabulky a bude mít příponu „.txt“ pro rychlejší editaci. Každý řádek (záznam) bude reprezentován jedním řádkem v souboru a jednotlivé položky budou odděleny svislítkem (může být však použit i jiný znak). Pokud zvolíte verzi s neomezenou možností editace, bude první řádek souboru obsahovat názvy jednotlivých sloupců, které budete moci měnit. Až dokončíte editaci, zavoláte skript up.php, který vezme data ze všech souborů a nahradí jimi původní obsah tabulek. Pokud jsme zvolili neomezenou možnost editace, budou všechny tabulky vymazány a vytvořeny znovu.

Verze s neomezenou možností editace

Verze s neomezenou možností editace (tedy verze, kde první řádek každého souboru obsahuje název sloupce) má oproti té druhé jednu značnou nevýhodu. Skript nezjišťuje datový typ sloupce, takže všechny sloupce, které budou vytvořeny při uploadu dat, budou mít typ TEXT. Samozřejmě, že tímto odpadá i možnost vytváření unikátních nebo primárních klíčů.

down.php

<?php
mysql_connect(„localhost“,“jmeno“,“heslo“);
mysql_select_db(„databaze“);

Nejdříve ze všeho se musíme připojit k databázi. Tyto údaje musíte samozřejmě upravit tak, aby odpovídali nastavení vašeho serveru.

$tables = mysql_list_tables(„databaze“);
$num_tables = mysql_num_rows($tables);
if($num_tables == 0) echo(„Zvolená databáze neobsahuje žádné tabulky !“);

Abychom mohli zahájit přepis, musíme nejprve získat seznam všech tabulek v databázi. Pokud databáze neobsahuje ani jednu tabulku, zobrazí se chybové hlášení.

for($itab=0; $itab<$num_tables; $itab++) {
        $table = mysql_fetch_row($tables);
        $result = mysql_query(„SELECT * FROM “ . $table[0] . „;“);
        $soubor = fopen(‚data/‘ . $table[0] . ‚.txt‘, ‚w‘);
        for ($i = 0; $i < mysql_num_fields($result); $i++) {
             if($i != 0) fwrite($soubor, „|“);
             fwrite($soubor, „“ . mysql_field_name($result,$i) . „“);
        }
        fwrite($soubor, „“);
         for ($i = 0; $i < mysql_num_rows($result); $i++) {
            echo(„“);
            $row_array = mysql_fetch_row($result);
            for ($j = 0; $j < mysql_num_fields($result); $j++) {
             if($j != 0) fwrite($soubor, „|“);
             $row_array[$j]=eregi_replace(„“, „“, $row_array[$j]);
             fwrite($soubor,“ . $row_array[$j] . “);
            }
            fwrite($soubor,““);
         }
    }
?>

Tento cyklus postupně prochází pole s názvy databází. Každou databázi si nejprve vyžádá s kompletním obsahem pomocí jednoduchého MySQL příkazu SELECT * FROM název_tabulky. Hned potom se pokusí vytvořit nový soubor v adresáři data. Ten musí být samozřejmě vytvořen ještě před prvním použitím skriptu ve stejném adresáři, ve kterém se nachází oba skripty. Pokud aplikace běží na unixovém systému, je třeba nejprve nastavit práva k adresáři data na 777.

Další operací je zjištění všech sloupců pomocí funkcí mysql_num_fields (ta zjistí počet sloupců, který využijeme pro zadání cyklu) a mysql_field_name (ta vrátí název sloupce, pokud napíšeme jeho číslo).

Nakonec probíhá postupné předávání obsahu tabulky do souboru pomocí kombinace dvou cyklů, kde se s přibývajícími řádky zvětšuje hodnota proměnné $i a v každém řádku probíhá orientace ve sloupcích pomocí proměnné $j. Z položky každé buňky musíme odstranit případná odřádkování (enter znamená přechod na další záznam). Tento kód nepočítá s možností, že by se v tabulce objevil znak |. Pokud tato možnost hrozí, je nutné stejným způsobem ošetřit i tento znak, například nahrazením za příslušnou entitu.

up.php

<?php
mysql_connect(„localhost“,“uzivatel“,“heslo“);
mysql_select_db(„databaze“);

Opět se nejprve připojíme k databázi.

Pro funkci pro procházení adresářů jsem se inspiroval jedním článkem Pavla Růžičky. Ta načte obsah adresáře data do pole, které budeme procházet.

while (list($key, $file) = each($ret))
{
   list($key, $isdir) = each($dir);
   if($isdir==0)
   {
      $radky = file(“ . $file . “);
      $tableid = substr($file, 0, strlen($file) – 4);
      $tableid = eregi_replace(‚^data/‘, “, $tableid);
      if(trim($tableid) != “) {
        $mysqlnadpis=eregi_replace(„\|“,“ TEXT,
             „,$radky[0]);
        mysql_query(„DROP TABLE IF EXISTS “ . $tableid . „;“);
        mysql_query(„CREATE TABLE “ . $tableid . “ (
             “ . $mysqlnadpis . „TEXT
             );“);
        echo(„CREATE TABLE “ . $tableid . “ (
             “ . $mysqlnadpis . „TEXT
             );“);
        for($i=1;$i<Count($radky);$i++) {
           $mysqlradek=eregi_replace(„\|“,“‚,'“,$radky[$i]);
           mysql_query(„INSERT INTO “ . $tableid . “ VALUES (‚“ . $mysqlradek . „‚);“);
        }
      }
   }
}
exit();
?>

Hlavní část je vlastně stejná, jako u skriptu down.php, jen místo toho, aby se informace z databáze psaly do souboru, probíhá čtení souboru a zápis dat do databáze. Jelikož jsme ve verzi s neomezenou editací, musíme nejprve celou starou tabulku odstranit, pak vytvořit novou podle prvního řádku souboru (všechny sloupce budou mít datový typ TEXT) a nakonec už jen po jednotlivých řádcích nahrajeme do databáze nová data.

Omezená editace

Abychom mohli v databázi používat více datových typů a další nastavení jednotlivých sloupců stačí zakázat jejich editaci. Ve skriptu down.php to bude znamenat jen vymazání části, která zapisuje první řádek souboru:

<?php
    mysql_connect(„localhost“,“uzivatel“,“heslo“);
    mysql_select_db(„databaze“);
    $tables = mysql_list_tables(„databaze“);
    $num_tables = mysql_num_rows($tables);
    if($num_tables == 0) echo(„Zvolená databáze neobsahuje žádné tabulky !“);
    for($itab=0; $itab<$num_tables; $itab++) {
        $table = mysql_fetch_row($tables);
        $result = mysql_query(„SELECT * FROM “ . $table[0] . „;“);
        $soubor = fopen(‚data/‘ . $table[0] . ‚.txt‘, ‚w‘);
         for ($i = 0; $i < mysql_num_rows($result); $i++) {
            echo(„“);
            $row_array = mysql_fetch_row($result);
            for ($j = 0; $j < mysql_num_fields($result); $j++) {
             if($j != 0) fwrite($soubor, „|“);
             $row_array[$j]=eregi_replace(„“, „“, $row_array[$j]);
             fwrite($soubor,“ . $row_array[$j] . “);
            }
            fwrite($soubor,““);
         }
    }
?>

A ve skriptu up.php nahrazení a znovuvytvoření tabulky přepíšeme pouhým vymazáním všech dat v tabulce:

<?php
    mysql_connect(„localhost“,“uzivatel“,“heslo“);
    mysql_select_db(„databaze“);
function GetDirArray($sPath,&$ret,&$dir)
{
   global $time0, $MAXTIME, $timeover;
   if ((time()-$time0)>$MAXTIME) { $timeover = 1; return; }
   $handle=opendir($sPath);
   while ($file = readdir($handle))
   {
      $polozky[count($polozky)] = $file;
   }
   closedir($handle);
   sort($polozky);
   while (list($key, $val) = each($polozky))
   {
      if ($val != „.“ && $val != „..“)
      {
         $path = str_replace(„//“,“/“,$sPath.$val);
         $path2 = substr($path,2);
         $ret[count($ret)] = $path2;
         if (is_dir($sPath.“/“.$val))
         {
            $dir[count($dir)] = 1;
            GetDirArray($sPath.“/“.$val.“/“,$ret,$dir);
         }
         else
         {
            $dir[count($dir)] = 0;
         }
      }
   }
}
$MAXTIME = 60;
$time0 = time();
$timeover = 0;
// $cwd = getcwd();
$cwd = getcwd();
echo „<H1>Adresar \“$cwd\“</H1>\n“;
GetDirArray(„./data/“,$ret,$dir);
if ($timeover) echo „Provadeni skriptu preruseno, uplynul maximalni povoleny cas ($MAXTIME sek.)<br><br>\n\n“;
reset($dir);
reset($ret);
while (list($key, $file) = each($ret))
{
   list($key, $isdir) = each($dir);
   if($isdir==0)
   {
      $radky = file(“ . $file . “);
      $tableid = substr($file, 0, strlen($file) – 4);
      $tableid = eregi_replace(‚^data/‘, “, $tableid);
      if(trim($tableid) != “) {
        mysql_query(„DELETE FROM “ . $tableid . „;“);
        for($i=0;$i<Count($radky);$i++) {
           $mysqlradek=eregi_replace(„\|“,“‚,'“,$radky[$i]);
           mysql_query(„INSERT INTO “ . $tableid . “ VALUES (‚“ . $mysqlradek . „‚);“);
        }
      }
   }
}
exit();
?>