Informazioni sulle tabelle

Gli argomenti di questa pagina

  • Prima di iniziare
  • Notizie ed informazioni sugli oggetti di un DB
  • Elenco delle tabelle in un DB: SHOW TABLES
  • Controllare se una tabella esiste
  • Informazioni sulle tabelle del DB con INFORMATION_SCHEMA
  • Query ed esecuzione per mostrare le informazioni sulle tabelle
  • Informazioni sulle tabelle del DB con SHOW TABLE STATUS
  • Differenze tra INFORMATION_SCHEMA e SHOW TABLE STATUS

Prima di iniziare

Innanzitutto verificare che esista il file che contiene i dati di connessione al DB.
In questo file verranno indicate le sole quattro variabili necessarie a stabilire la connessione al DB prima di compiere qualsiasi azione ed è questo file che includeremo in tutte le pagine che debbono lavorare col DB. Questi dati vengono forniti dall'host che abbiamo scelto per il nostro sito.

Il file db_login.php
1
2
3
4
5
6
 		<?php
$Host = "hostname";
$User = "username";
$Password = "password";
$DBName = "DBName";
?>

Questo che segue è un frammento di codice che sarà necesario per eseguire la connessione al DB ed alla esecuzione delle Query.

Questo frammento di codice va inserito nella pagina che deve eseguire operazioni col DB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 		<?php
include ('res/db_login.php');
if (!$link = mysql_connect($Host, $User, $Password)) {
    echo mysql_error() . "<br />\n";
} else {
    if (!mysql_select_db($DBName)) {
        echo mysql_error() . "<br />\n";
    } else {
        mysql_set_charset("utf8"); 
        //    esecuzione della Query
        $result = mysql_query($Query);
        if (!$result) {
            echo mysql_error() . "<br />\n";
        } else {
            echo "Query eseguita<br />\n";
            //    qui vengono eseguiti altri eventuali lavori
        }
    }
}
@mysql_close ($Link);
?>

Se si apre una connessione a MySQL all'interno del file PHP è possibile chiuderla con la funzione mysql_close(). Anche se non tutti i programmatori usano chiudere esplicitamente la connessione perchè lo trovano inutile, in quanto le connessioni MySQL sono solitamente chiuse per default alla fine dello script, è buona norma fare in modo che tutte le risorse vengano liberate dopo l'utilizzo del DB, usando la funzione mysql_close(). Ciò assicurerà che la connessione venga chiusa nella linea esatta nello script in cui si dice di chiudere.

Chiusura della connessione al DB
1
2
3
 		<?php
mysql_close ($link);
?> 

Notizie ed informazioni sugli oggetti di un DB

Prima di passare alle Query sui dati è bene mostrare come ottenere le informazioni sugli oggetti del DB in special modo:

  • Elenco delle tabelle del DB
  • Elenco dei campi delle tabelle
  • Tipi di campi
  • Numero dei record in una tabella
  • Numero di record restituiti da una Query

Elenco delle tabelle in un DB: SHOW TABLES

La Query é semplice e, scritta come nell'esempio, restituisce un elenco completo delle tabelle presenti nel DB

1
2
3
 		<?php
$Query = "SHOW TABLES";
?>

Se non si desidera l'elenco completo delle tabelle presenti nel DB si può usare un filtro basato sull'operatore LIKE per limitare l'elenco alle sole tabelle che soddisfano il filtro specificato nell'operatore LIKE. Per esempio:
vogliamo un elenco delle tabelle che iniziano col prefisso sql_
e che sono quelle usate per questo tutorial. Ecco la Query modificata per soddisfare questa richiesta e quello sotto è il risultato:

La Query che useremo in questo esempio:

1
2
3
 		<?php
$Query = " SHOW TABLES LIKE 'sql_%' ";
?>

Lo script che verrà usato per l'esecuzione della Query non cambia dalla struttura degli altri script simili e, nell'esempio, esegue questa seconda Query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 		<?php
include ('res/db_login.php');
if (!$link = mysql_connect($Host, $User, $Password)) {
    echo mysql_error() . "<br />\n";
} else {
    if (!mysql_select_db($DBName)) {
        echo mysql_error() . "<br />\n";
    } else {
        mysql_set_charset("utf8"); 
        $result = mysql_query($Query);
        echo mysql_num_rows($result) . " tabelle trovate<br />\n";
        while($row = mysql_fetch_array($result)) {
            echo $row[0]."<br />";
        }
    }
}
@mysql_close ($Link);
?>

In questo esempio vengono visualizzate le sole tabelle che iniziano col prefisso sql_.

18 tabelle trovate
sql_agenda
sql_articoli
sql_citta
sql_citta_2
sql_comuni
sql_comuni_full
sql_downloads
sql_lib_prestiti
sql_libri
sql_mag_acquisti
sql_mag_articoli
sql_mag_fornitori
sql_mag_vendita
sql_ore_lavorative
sql_province
sql_rubrica
sql_rubrica_indicizzata
sql_rubrica_nuova

Controllare se una tabella esiste

A volte prima di eseguire delle operazioni occorre sapere se una tabella esiste.
Per verificare l'esistenza di una tabella esistono diverse metodologie, ma quella basata sull'operatore LIKE forse è la più semplice. Nel filro che diamo all'operatore LIKE indichiamo il nome completo della tabella. Se la Query restituisce almeno una riga la tabella esiste, altrimenti non esiste.
Qui sotto un esempio per effettuare questa ricerca.

Ho preparato due Query:
nella prima Query nell'operatore LIKE ho indicato una tabella che sicuramente esiste
Nella seconda Query ho indicato una tabella che sicuramente NON esiste

1
2
3
4
5
6
7
8
 		<?php
//    primo esempio per tabella esistente
$table1 = "sql_citta";
$Query1 = " SHOW TABLES LIKE '" . $table1 . "'";
//    secondo esempio per tabella inesistente
$table2 = "una_tabella";
$Query2 = " SHOW TABLES LIKE '" . $table2 . "'";
?>

Lo script usato è leggermente modificato rispetto a quelli standard. Siccome in questa circostanza mi interessa solo sapere se la tabella esiste, invece di fare tutti i passaggi dello script standard valorizzando la variabile $result, utile per altri usi, e controllando poi se la Query restituisce qualche riga (le due righe di uso standard sono commentate):
$result = mysql_query($Query);
if (mysql_num_rows($result)) {
eseguo il controllo comprimento in una unica riga le due istruzioni:
if (mysql_num_rows( mysql_query($Query))) {

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 		<?php
include ('res/db_login.php');
if (!$link = mysql_connect($Host, $User, $Password)) {
    echo mysql_error() . "<br />\n";
} else {
    if (!mysql_select_db($DBName)) {
        echo mysql_error() . "<br />\n";
    } else {
        mysql_set_charset("utf8");
//        $result = mysql_query($Query);
//        if (mysql_num_rows($result)) {
        if (mysql_num_rows( mysql_query($Query))) {
            echo "La tabella " . $table . " esiste!<br />\n";
        } else {
            echo "La tabella " . $table . " NON esiste!<br />\n";
        }
        $result = mysql_query($Query);
    }
}
@mysql_close ($Link); 
?>

Il risultato ovviamente è quello atteso: viene segnalato che la prima tabella esiste e che la seconda non è stata trovata. Questo controllo può evitare l'insorgere di errori o, addirittura, il blocco dello script, se viene lanciato uno script su una tabella inesistente.

La tabella sql_citta esiste!
La tabella una_tabella NON esiste!

Informazioni sulle tabelle del DB con INFORMATION_SCHEMA

INFORMATION_SCHEMA è un database virtuale che descrive la struttura di tutti gli altri database.
I valori che questa istruzione restituisce sono questi:

Proprietà Valore
TABLE_CATALOG normalmente NULL
TABLE_SCHEMA nome del DB
TABLE_NAME nome della tabella
TABLE_TYPE BASE TABLE o SYSTEM VIEW
ENGINE MySQL extension (MyISAM o MEMORY)
VERSION la versione della tabella (es: 10)
ROW_FORMAT normalmente Dynamic o Fixed
TABLE_ROWS numero di record
AVG_ROW_LENGTH MySQL extension
DATA_LENGTH dimensione in bytes dei dati
MAX_DATA_LENGTH MySQL extension
INDEX_LENGTH MySQL extension
DATA_FREE MySQL extension
AUTO_INCREMENT il valore massimo dell'indice autoincrementale
CREATE_TIME data di creazione
UPDATE_TIME ultimo aggiornamento
CHECK_TIME MySQL extension
TABLE_COLLATION codifica caratteri (latin1_swedish_ci, utf8_general_ci, ecc.)
CHECKSUM MySQL extension
CREATE_OPTIONS MySQL extension
TABLE_COMMENT MySQL extension
Le proprietà più utili
TABLE_NAME nome della tabella
ENGINE MySQL extension (MyISAM o MEMORY)
TABLE_ROWS numero di record
DATA_LENGTH dimensione in bytes dei dati
AUTO_INCREMENT il valore massimo dell'indice autoincrementale
CREATE_TIME data di creazione
UPDATE_TIME ultimo aggiornamento
TABLE_COLLATION codifica caratteri (latin1_swedish_ci, utf8_general_ci, ecc.)

Query ed esecuzione per mostrare le informazioni sulle tabelle

Qui di seguito vediamo tre differenti Query per mostrare le informazioni disponibili per la tabella o le tabelle che andremo ad esaminare.

  • prima Query: leggiamo solo alcune delle proprietà delle tabelle
  • seconda Query: leggiamo le proprietà più significative nostrate nello schema qui sopra, quindi:
    TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, TABLE_COLLATION
  • terza Query: leggiamo tutte le proprità delle tabelle.

Qui di seguito vengono dati i risultati per le sole prime due Query. Nella terza Query, invece di specificare i campi che vogliamo vedere, viene utilizzato l'asterisco ( * ) per indicare che vogliamo avere le informazioni di tutti i campi che verranno restituiti.
Perciò, per questa terza Query, che è troppo corposa, diciamo che verrà usato lo stesso script che viene usato per le prime due, anche se nell'esempio di questa pagina non viene usata.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 		<?php
$Query_1 = "SELECT TABLE_NAME, TABLE_TYPE, ENGINE
    FROM information_schema.tables
    WHERE TABLE_NAME LIKE 'sql_%'
    ORDER BY table_name;";

$Query_2 = "SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, TABLE_COLLATION
    FROM information_schema.tables
    WHERE TABLE_NAME LIKE 'sql_%'
    ORDER BY table_name;";
    
$Query_3 = "SELECT *
    FROM information_schema.tables
    WHERE TABLE_NAME LIKE 'sql_%'
    ORDER BY table_name";
?>

Per mandare in esecuzione lo script che segue le variabili $Query_1, $Query_2, $Query_3 saranno trasformate in $Query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 		<?php
include ('res/db_login.php');
if (!$link = mysql_connect($Host, $User, $Password)) {
    echo mysql_error() . "<br />\n";
} else {
    if (!mysql_select_db($DBName)) {
        echo mysql_error() . "<br />\n";
    } else {
        mysql_set_charset("utf8"); 
        $result = mysql_query($Query);
        $numfields = mysql_num_fields($result);
        $fields = array();
        for ($i=0; $i < $numfields; $i++) {
            $fields[] = mysql_field_name($result, $i);
        }
        $count = mysql_num_rows($result);
        echo "<table class=\"tabella_it\">\n";
        echo "<tr>\n";
        echo "<th colspan=\"".$numfields."\">\n";
        echo $numfields . " informazioni per " . $count . " tabelle trovate";
        echo "</th>\n";
        echo "</tr>\n";
        echo "<tr>\n";
        for ($i = 0; $i < $numfields; $i++) {
            echo "<th>" . $fields[$i] . "</th>\n";
        }
        echo "</tr>\n";
        while ($row = mysql_fetch_assoc($result)) {
            echo "<tr>\n";
            for ($i = 0; $i < $numfields; $i++) {
                echo "<td>" . $row[$fields[$i]] . "</td>\n";
            }
            echo "</tr>\n";
        }
        echo "</table>\n";
        echo "<br />\n";
    }
}
@mysql_close ($Link);
?>

Nel risultato mostrato qui di seguito vedremo solo quel che viene restituito dalle prime due Query, ma è possibile vedere anche la terza mandando in esecuzione lo stesso script presentato qui sopra.

3 informazioni per 18 tabelle trovate
TABLE_NAME TABLE_TYPE ENGINE
sql_agenda BASE TABLE MyISAM
sql_articoli BASE TABLE MyISAM
sql_citta BASE TABLE MyISAM
sql_citta_2 BASE TABLE MyISAM
sql_comuni BASE TABLE MyISAM
sql_comuni_full BASE TABLE MyISAM
sql_downloads BASE TABLE MyISAM
sql_lib_prestiti BASE TABLE MyISAM
sql_libri BASE TABLE MyISAM
sql_mag_acquisti BASE TABLE MyISAM
sql_mag_articoli BASE TABLE MyISAM
sql_mag_fornitori BASE TABLE MyISAM
sql_mag_vendita BASE TABLE MyISAM
sql_ore_lavorative BASE TABLE MyISAM
sql_province BASE TABLE MyISAM
sql_rubrica BASE TABLE MyISAM
sql_rubrica_indicizzata BASE TABLE MyISAM
sql_rubrica_nuova BASE TABLE MyISAM

8 informazioni per 18 tabelle trovate
TABLE_NAME ENGINE TABLE_ROWS DATA_LENGTH AUTO_INCREMENT CREATE_TIME UPDATE_TIME TABLE_COLLATION
sql_agenda MyISAM 103 6192 104 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_articoli MyISAM 30 8372 31 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_citta MyISAM 38 1160 163 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_citta_2 MyISAM 0 0 1 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_comuni MyISAM 14701 465512 14702 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_comuni_full MyISAM 14701 583688 14702 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_downloads MyISAM 2 92 3 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_lib_prestiti MyISAM 10 190 11 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_libri MyISAM 22 1524 23 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_mag_acquisti MyISAM 9 243 11 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_mag_articoli MyISAM 8 388 9 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_mag_fornitori MyISAM 8 296 9 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_mag_vendita MyISAM 3 100 4 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_ore_lavorative MyISAM 8 396 9 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_province MyISAM 110 2404 111 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_rubrica MyISAM 101 9188 102 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_rubrica_indicizzata MyISAM 101 9116 102 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci
sql_rubrica_nuova MyISAM 105 10448 106 2021-08-12 20:24:23 2021-08-12 20:24:23 utf8_general_ci

Informazioni sulle tabelle del DB con SHOW TABLE STATUS

Altro modo di ottenere informazioni simili è quello di usare SHOW TABLE STATUS. Le informazioni restituite sono quasi come quelle restituite da INFORMATION_SCHEMA appena visto. Eccone un elenco dettagliato.

Name Nome della tabella
Engine Il motore di memorizzazione per la tabella
Version Il Numero della versione del file .frm relativo alla tabella
Row_format Il formato di righe di archiviazione (Fisso, Dynamic, compresso, ridondante, Compact)
Rows Il numero di record
Avg_row_length La lunghezza media delle righe
Data_length La dimensione del file dei dati
Max_data_length La lunghezza massima del file di dati. Questo è il numero totale di byte di dati che possono essere memorizzati nella tabella, date le dimensioni del puntatore dati utilizzata.
Index_length La dimensione del file indice
Data_free Il numero di byte allocati ma non utilizzati
Auto_increment Il prossimo valore AUTO_INCREMENT
Create_time Data della creazione della tabella
Update_time Ultimo aggiornamento della tabella
Check_time Ultimo accesso alla tabella. Non tutti i motori di archiviazione aggiornano questo argomento, nel qual caso il valore è NULL
Collation Tipo di collation della tabella
Checksum Il valore di checksum dal vivo (se presente)
Create_options Opzioni Extra usati con CREATE TABLE. Le opzioni originali forniti quando CREATE TABLE viene chiamato vengono mantenute e le opzioni qui riportati potrebbero differire dalle impostazioni della tabella attive e opzioni
Comment I commenti usati durante la creazione della tabella

Vediamo come fare.
La Query da creare è la seguente:

1
2
3
 		<?php
$Query = "SHOW TABLE STATUS LIKE 'sql_%' ";
?>

Questa, purtroppo, produce un numero eccessivo di informazioni che è difficile da gestire se, come nell'esempio sopra indicato, volessimo ridurre. Per risolvere ho creato una matrice prendendo dal recordset prodotto solo le chiavi che mi interessano, come è visibile nell'Array qui sotto.

1
2
3
4
5
6
7
8
9
10
11
12
 		<?php
$arr_arg = array(
    "Name", 
    "Engine", 
    "Collation",
    "Rows", 
    "Auto_increment", 
    "Data_length", 
    "Create_time", 
    "Update_time"
)
?>

Fatto questo posso usare questo script che è simile a tutti gli altri usati per leggere un recordset ma che come campi non prende quelli generati dalla Query, ma quello da me preparato.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 		<?php
include ('res/db_login.php');
if (!$link = mysql_connect($Host, $User, $Password)) {
    echo mysql_error() . "<br />\n";
} else {
    if (!mysql_select_db($DBName)) {
        echo mysql_error() . "<br />\n";
    } else {
        mysql_set_charset("utf8");
        $result = mysql_query($Query);
        if (!$result) {
            echo mysql_error() . "<br />\n";
        } else {
            $numfields = count($arr_arg);
            echo "<table class=\"tabella_it\">\n";
            echo "<tr>\n";
            echo "<th colspan=\"".$numfields."\">\n";
            echo $numfields . " informazioni per " . $count . " tabelle trovate";
            echo "</th>\n";
            echo "</tr>\n";
            echo "<tr>\n";
            for ($i = 0; $i < $numfields; $i++) {
                echo "<th>" . $arr_arg[$i] . "</th>\n";
            }
            echo "</tr>\n";
            while ($row = mysql_fetch_assoc($result)) {
                echo "<tr>\n";
                for ($i = 0; $i < $numfields; $i++) {
                    echo "<td>" . $row[$arr_arg[$i]] . "</td>\n";
                }
                echo "</tr>\n";
            }
            echo "</table>\n";
        }
    }
}
mysql_close ($link);
?>

Questo è quel che vien fuori:

8 informazioni per 18 tabelle trovate
Name Engine Collation Rows Auto_increment Data_length Create_time Update_time
sql_agenda MyISAM utf8_general_ci 103 104 6192 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_articoli MyISAM utf8_general_ci 30 31 8372 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_citta MyISAM utf8_general_ci 38 163 1160 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_citta_2 MyISAM utf8_general_ci 0 1 0 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_comuni MyISAM utf8_general_ci 14701 14702 465512 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_comuni_full MyISAM utf8_general_ci 14701 14702 583688 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_downloads MyISAM utf8_general_ci 2 3 92 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_lib_prestiti MyISAM utf8_general_ci 10 11 190 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_libri MyISAM utf8_general_ci 22 23 1524 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_mag_acquisti MyISAM utf8_general_ci 9 11 243 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_mag_articoli MyISAM utf8_general_ci 8 9 388 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_mag_fornitori MyISAM utf8_general_ci 8 9 296 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_mag_vendita MyISAM utf8_general_ci 3 4 100 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_ore_lavorative MyISAM utf8_general_ci 8 9 396 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_province MyISAM utf8_general_ci 110 111 2404 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_rubrica MyISAM utf8_general_ci 101 102 9188 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_rubrica_indicizzata MyISAM utf8_general_ci 101 102 9116 2021-08-12 20:24:23 2021-08-12 20:24:23
sql_rubrica_nuova MyISAM utf8_general_ci 105 106 10448 2021-08-12 20:24:23 2021-08-12 20:24:23

Differenze tra INFORMATION_SCHEMA e SHOW TABLE STATUS

Le differenze del numero e dei tipi di proprietà tra i due metodi dono questi qui sotto elencati

Con la Query INFORMATION_SCHEMA Con la Query SHOW TABLE STATUS
TABLE_CATALOG  Name 
TABLE_SCHEMA  Engine 
TABLE_NAME  Version 
TABLE_TYPE  Row_format 
ENGINE  Rows 
VERSION  Avg_row_length 
ROW_FORMAT  Data_length 
TABLE_ROWS  Max_data_length 
AVG_ROW_LENGTH  Index_length 
DATA_LENGTH  Data_free 
MAX_DATA_LENGTH  Auto_increment 
INDEX_LENGTH  Create_time 
DATA_FREE  Update_time 
AUTO_INCREMENT  Check_time 
CREATE_TIME  Collation 
UPDATE_TIME  Checksum 
CHECK_TIME  Create_options 
TABLE_COLLATION  Comment 
CHECKSUM   
CREATE_OPTIONS   
TABLE_COMMENT   

 

 



settore tecnico il sito di lorettabweb il Forum di sostegno
il forum il forum il forum