Ecco poche righe di codice che permetto a uno script PHP di leggere un file SQL ed eseguirne le query!
$query = "";
$handle = @fopen($file_sql, "r");
if ($handle) {
mysql_query("START TRANSACTION", $dbh);
while (!feof($handle)) {
$query.= fgets($handle, 4096);
if (substr(rtrim($query), -1) == ';') {
if(!empty($query))
mysql_query($query, $dbh) or
die(mysql_error() . mysql_query("ROLLBACK"));
$query = '';
}
}
fclose($handle);
}
mysql_query("COMMIT", $dbh);
Ieri mi sono scontrato con un problema abbastanza singolare nell’estrazione di alcuni dati da una tabella MySQL.
La tabella in quetione, chiamata “comments”, è così definita (elenco solo i campi necessari… cmq si tratta della tabella comments di Wordpress):
comment_ID(PK) | comment_post_ID(FK) | comment_date
comment_ID -> chiave primaria
comment_post_ID -> chiave esterna
La mia necessità era quella di estrarre i dati degli ultimi commenti approvati di 5 post differenti.
A prima vista la soluzione sembrava semplicissima:
$query = "SELECT * FROM comments WHERE comment_approved= '1' AND comment_type = '' GROUP BY comment_post_ID ORDER BY commenta_date DESC";
Ebbene, questa prima soluzione è SBAGLIATA! Infatti in questo modo viene eseguita prima la group by e solo successivamente l’order by! Servirebbe esattamente il contrario!
Ed ecco dunque la soluzione…
$query = "select * from comments A JOIN ( select comment_ID from comments where comment_approved= '1' AND comment_type = '' ORDER BY comment_date DESC) B ON A.comment_ID = B.comment_ID GROUP BY A.comment_post_ID ORDER BY A.comment_date DESC LIMIT 5";
Ecco la sintassi per eseguire i comandi di start / stop / reload per il server mysql:
* To start mysql server:
# /etc/init.d/mysqld start
* To stop mysql server:
# /etc/init.d/mysqld stop
* To restart mysql server
# /etc/init.d/mysqld restart
Ecco un’interessante articolo su come convertire un database latin-1 in utf-8 in pochi passaggi, su gentoo-wiki.com
Il concetto base di questa metodologia sta nel fare un dump del db latin-1 e convertire il file .sql in utf-8 PRIMA di ricreare il nuovo database! Semplicemente geniale…
Ho scoperto poco fa una differenza interessante tra i tempi di azione BEFORE e AFTER dei trigger.
Prendiamo ad esempio in considerazione un trigger di INSERIMENTO.
BEFORE indica che il trigger si attiva prima che ogni riga venga inserita nella tabella.
AFTER indica che il trigger si attiva dopo l’inserimento.
N.B. Dal manuale MySQL si legge:
In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the automatically generated sequence number that will be generated when the new record actually is inserted.
Ammettiamo che il mio DB ha 2 tabelle: User [userid, nome] (con userid autoincrement) e UserStat [userid, datains]
Se volessi creare un trigger che all’inserimento di un nuovo utente mi genera una entry anche in UserStat, NON DEVO azionare il trigger BEFORE INSERT, ma necessariamente AFTER.
In caso contrario, userid della tabella UserStat verrebbe inserito con valore 0.
Parliamo di MySQL: quando si esegue una query con la clausola LIMIT, come faccio a sapere il numero totale di righe restituite dalla stessa query, ma senza limit? Questa funzionalità può essere utile ad esempio in una pagina web che deve mostrare N risultati, ma necessita anche di conoscere il numero totale di risultati!
Ebbene, in molti dichiarano che basta richiamare la query “SELECT FOUND_ROWS()” dopo una select che deve indicare la clausola SQL_CALC_FOUND_ROWS in questo modo:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
Ed ecco fatto! Nell’esempio precedente, FOUND_ROWS() ritorna esattamente lo stesso numero di una query tipo “SELECT COUNT(*) FROM tbl_name WHERE id > 100″… ma senza il “LIMIT 10″!
C’è però una considerazione da fare: una query con istruzione SQL_CALC_FOUND_ROWS è spesso più lenta di una semplice select con clausola limit. Molto spesso è quindi meglio eseguire 2 query diverse… una SELECT con clausola LIMIT e una con COUNT(*).
Un test dettagliato è visionabile a questo indirizzo su mysqlperformanceblog
Oggi mi sono imbattuto nel problema dei charset…
In pratica, in una pagina html dove vine dichiarato come content-type charset=iso-8859-1 ( <meta http-equiv=”content-type” content=”text/html; charset=iso-8859-1″ /> ) tentavo di visualizzare dei dati estratti da una tabella mysql dichiarata come UTF-8.
La pagina html presentava una serie di caratteri strani proprio dove dovevano comparire i caratteri accentati ( è ì ò … )
E così, dopo diverse ricerche, ho trovato una soluzione differente dall’utilizzare la funzione php iconv… Ovvero dichiarare i charset mysql subito dopo la connessione in questo modo:
$query = "SET character_set_results = 'latin1'";
mysql_query($query);
$query = "SET character_set_client = 'utf8'";
mysql_query($query);
Quindi, per semplificare, character_set_results = latin1 indica il fatto che la pagina è visualizzata con il content-type ISO-8859-1, mentre character_set_client = utf8 indica che le tabelle mysql che vengono prese in considerazione sono dichiarate come UTF-8
Se avessi usato semplicemente la query ” SET NAMES ‘latin1′ “, in fase di inserimento dei dati con questa connessione mysql, nelle tabelle utf-8 i caratteri accentati si sarebbero purtroppo persi!
Un’altra interessante scoperta di oggi riguarda la funzione php mysql_connect.
Questa funzione PHP accetta un quarto parametro [bool $new_link].
Nella spiegazione di questo parametro si legge…
If a second call is made to mysql_connect() with the same arguments (HOST, USER e PWD), no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.
In pratica, pur utilizzando metodi come getInstance() e variabili statiche, due chiamate a mysql_connect con identico HOST, USER e PWD riportano SEMPRE il link ad un’unica connessione!
Unico modo per forzare la nuova connessione è settare il parametro $new_link a TRUE.
Per prevenire il moltiplicarsi delle connessioni, bisogna dunque eseguire dei controlli come quelli introdotti con le variabili statiche e i metodi getInstance().
Ecco come fare se volete visualizzare le procedure/funzioni associate a un database oppure i trigger su una tabella:
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
Ricercando semplicemente SHOW PROCEDURE STATUS si ottengono informazioni su tutte le procedure del DB.
Per i Trigger invece:
SHOW TRIGGERS [FROM db_name] [LIKE expr]
Ecco un esempio di uso delle funzioni nei trigger di MySql 5:
DELIMITER //
CREATE FUNCTION max_prov_pos (param1 INT(11))
RETURNS INT(11)
RETURN (SELECT IF( MAX( posizione ) , MAX( posizione ) , 1 ) FROM info_provincia WHERE struttura = param1)//
CREATE TRIGGER pos_prov BEFORE INSERT on info_provincia
for each row
BEGIN
SET @pos = max_prov_pos(NEW.struttura);
IF NEW.posizione = 0
THEN SET NEW.posizione = @pos+1;
END IF;
END;//
DELIMITER ;
In questo caso all’inserimento di un nuovo record nella tabella viene automaticamente settato il campo “posizione” con il valore massimo
fantastiko!!!
Oggi mi sono imbattuto in un articolo interessantissimo, proprio mentre cercavo informazioni su come velocizzare e ottimizzare le query di MySQL.
In questo articolo viene spiegato come scegliere gli indici delle tabelle.
A tal proposito risulta necessario utilizzare e conoscere la sintassi del comando EXPLAIN, soprattutto per le JOIN a più tabelle!
