|
|
 |
LXIV. MySQL FunctionsIntroduzione
Queste funzioni consentono l'accesso ai server di database MySQL.
Maggiori informazioni riguardo MySQL possono essere trovate su http://www.mysql.com/.
La documentazione su MySQL può essere trovata su http://dev.mysql.com/doc/.
Requisiti
Al fine di rendere queste funzioni disponibili, si deve compilare PHP con
il supporto MySQL.
Installazione
Usando l'opzione di configurazione --with-mysql[=DIR]
si abilita PHP l'accesso ai
database MySQL. Se si usa questa opzione senza specificare il percorso a MySQL,
PHP userà le librerie client MySQL interne. Con
PHP 4, il supporto a MySQL è sempre abilitato; se non si specifica l'opzione
di configurazione, vengono usate le librerie incluse. Gli utenti che eseguono altre applicazioni
che usano MySQL (ad esempio, usando PHP 3 e PHP 4 come moduli concorrenti
di Apache oppure auth-mysql) dovrebbere specificare sempre il percorso a MySQL:
--with-mysql=/percorso/a/mysql.
Questo forzerà PHP ad usare le librerie client installate
da MySQL evitando ogni conflitto.
La versione per Windows di PHP
ha già compilato il supporto per questo modulo. Non occorre caricare alcun modulo
addizionale per potere utilizzare queste funzioni. | Attenzione |
Problemi di blocco e di avvio di PHP possono essere riscontrati
quando si carica questa estensione insieme ad estensioni recode.
Vedere anche l'estensione recode per
maggiori informazioni.
|
Configurazione di Runtime
Il comportamento di queste funzioni è influenzato dalle impostazioni di php.ini.
Tabella 1. Opzioni di configurazione di MySQL | Nome | Predefinito | Modificabile in |
|---|
| mysql.allow_persistent | "On" | PHP_INI_SYSTEM | | mysql.max_persistent | "-1" | PHP_INI_SYSTEM | | mysql.max_links | "-1" | PHP_INI_SYSTEM | | mysql.default_port | NULL | PHP_INI_ALL | | mysql.default_socket | NULL | PHP_INI_ALL | | mysql.default_host | NULL | PHP_INI_ALL | | mysql.default_user | NULL | PHP_INI_ALL | | mysql.default_password | NULL | PHP_INI_ALL | | mysql.connect_timeout | "0" | PHP_INI_SYSTEM |
Per ulteriori dettagli e definizione delle costanti PHP_INI_* vedere
ini_set().
Qui c'è una breve spiegazione delle direttive di configurazione.
- mysql.allow_persistent
boolean
Determina se consentire le
connessioni persistenti
a MySQL.
- mysql.max_persistent
integer
Il numero massimo di connessioni persistenti MySQL per
processo.
- mysql.max_links
integer
Il numero massimo di connessioni MySQL per processo, incluse
le connessioni persistenti.
- mysql.default_port
string
Il numero di porta TCP predefinito da usare per connettersi ad
un server di database se nessuna altra porta viene specificata. Se
nessun valore predefinito e specificato, la porta sarà ottenuta
dalla variabile d'ambiente MYSQL_TCP_PORT,
dalla voce mysql-tcp in
/etc/services o dalla costante
MYSQL_PORT in fase di compilazione, in questo ordine. Win32
userà solo la costante MYSQL_PORT.
- mysql.default_socket
string
Il nome del socket predefinito da usare per connettersi ad un
server di database locale se nessun altro nome di socket viene specificato.
- mysql.default_host
string
L'host di default del server da usare per connettersi al server di
database se nessun altro host viene specificato. Non si applica in
safe mode.
- mysql.default_user
string
Il nome utente predefinito da usare per connettersi al server di
database se nessun altro nome viene specificato. Non si applica in
safe mode.
- mysql.default_password
string
La password predefinita da usare per connettrsi al server di
database se nessuna altra password viene specificata. Non si appplica in
safe mode.
- mysql.connect_timeout
integer
Timeout di connessione in secondi. Per Linux questo timeout è usato anche per
attendere la prima risposta dal server.
Tipi di risorse
Ci sono due tipi di risorsa usati nel modulo MySQL. Il primo
è l'identificativo di connessione per una connessione ad un database, del secondo tipo sono
le risorse che contengono i risultati di una query.
Costanti predefinite
Queste costanti sono definite da questa estensione e
sono disponibili solo se l'estensione è stata compilata
nel PHP o se è stata caricata dinamicamente a runtime.
Fin dal PHP 4.3.0 è possibile specificare flag addizionali per il client
per le funzioni mysql_connect() e mysql_pconnect().
Sono definite le seguenti costanti:
Tabella 2. Costanti client MySQL | Costante | Descrizione |
|---|
| MYSQL_CLIENT_COMPRESS | Usa la compressione del protocollo | | MYSQL_CLIENT_IGNORE_SPACE | Consente lo spazio dopo i nomi delle funzioni | | MYSQL_CLIENT_INTERACTIVE | Lascia trascorrere interactive_timeout secondi (anziché wait_timeout) di
inattività prima di chiudere la connessione |
La funzione mysql_fetch_array() usa una costante per
i diversi tipi di array risultato. Sono definite
le seguenti costanti:
Tabella 3. Costanti caricamento MySQL | Costante | Descrizione |
|---|
| MYSQL_ASSOC |
Le colonne sono restituite in un array avente il nome del campo come
indice dell'array
| | MYSQL_BOTH |
Le colonne sono restituite in un array avente sia un indice numerico
sia un indice costituito dal nome del campo
| | MYSQL_NUM |
Le colonne sono restituite in un array avente un indice numerico per
i campi. Questo indice inizia da 0, il primo campo nel risultato
|
Esempi
Questo esempio mostra come connettersi, eseguire una query, stampare
le righe risultanti e disconnettersi dal database MySQL.
Esempio 1. Esempio dell'estensione MySQL |
<?php
$connessione = mysql_connect("host_mysql", "utente_mysql", "password_mysql")
or die("Connessione non riuscita");
print "Connesso con successo";
mysql_select_db("mio_database") or die("Selezione del database non riuscita");
$query = "SELECT * FROM mia_tabella";
$risultato = mysql_query($query) or die("Query fallita");
print "<table>\n";
while ($linea = mysql_fetch_array($risultato, MYSQL_ASSOC)) {
print "\t<tr>\n";
foreach ($linea as $valore_colonna) {
print "\t\t<td>$valore_colonna</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";
mysql_free_result($risultato);
mysql_close($connessione);
?>
|
|
samcontact at myteks dot com
01-May-2004 10:32
Scott Holodak
25-Mar-2004 07:09
I was working on writing a script that would dump an INSERT query for ever row of data in every table. I was populating data on the development server and was looking for an easy way to get it onto the production server.
The task got complicated because of InnoDB referential integrity constraints. The tables needed to be backed up in a particular order for the bulk INSERTs to work properly.
The following function (ugly as it looks) reads the list of tables from a MySQL database, analyzes the CREATE TABLE statements, extrapolates the table dependencies and builds a 2-dimensional array from them. It then runs a scheduling algorithm so that the tables can be backed up. The end result is an array containing a valid ordering of the tables to be backed up (or an empty array if there are errors). Pass an empty string to schedule all tables in the current database.
I stripped this straight out of my application, but the required changes should be minimal. Maybe I'll post the backup function separately.
<?
function scheduleTables($wildcard) {
$tquery = "SHOW TABLES;";
$tables = mysql_query($tquery);
if ($tables) {
$tbldeps = array();
$tblidx = 0;
while ($row = mysql_fetch_row($tables)) {
$local_table = $row[0];
if (substr($local_table, 0, strlen($wildcard)) == $wildcard) {
$query = "SHOW CREATE TABLE $local_table";
if ($result = mysql_query($query)) {
if ($row = mysql_fetch_row($result)) {
$stmt = $row[1];
$frags = preg_split("/[,]+/", $stmt);
$ct = 0;
$deps = array();
for ($i = 0; $i < count($frags); $i++) {
if (substr(trim($frags[$i]), 0, strlen("CONSTRAINT")) == "CONSTRAINT") {
$ct++;
$cstrt = $frags[$i];
preg_match_all("/`\w+`/", $cstrt, $matches);
$local_field = substr($matches[0][1], 1, strlen($matches[0][1]) - 2);
$remote_table = substr($matches[0][2], 1, strlen($matches[0][2]) - 2);
$remote_field = substr($matches[0][3], 1, strlen($matches[0][3]) - 2);
$deps = array_merge($deps, array($ct => $remote_table));
}
}
$tbldeps = array_merge($tbldeps, array($local_table => $deps));
} else {
print "/* Unable to retrieve CREATE TABLE statement */\r\n";
return array();
}
} else {
print "/* Unable to retrieve CREATE TABLE statement */\r\n";
return array();
}
}
$tblidx++;
}
$order = array(); $kidxs = array_keys($tbldeps);
for ($i = 0; $i < count($kidxs); $i++) {
$tblkey = $kidxs[$i];
if (count($tbldeps[$tblkey]) == 0) {
array_push($order, $tblkey);
unset($tbldeps[$tblkey]);
}
}
$activity = TRUE;
while(count($tbldeps) > 0) {
$kidxs = array_keys($tbldeps);
$activity = FALSE;
for ($i = 0; $i < count($kidxs); $i++) {
$tblkey = $kidxs[$i];
if (count($tbldeps[$tblkey]) > 0 && isset($tbldeps[$tblkey])) {
$found_all = TRUE;
for ($j = 0; $j < count($tbldeps[$tblkey]); $j++) {
$found = FALSE;
for ($k = 0; $k < count($order); $k++) {
if ($tbldeps[$tblkey][$j] == $order[$k]) {
$found = TRUE;
unset($tbldeps[$tblkey][$j]);
}
}
if (!$found) {
$found_all = FALSE;
break;
}
}
if ($found_all) {
$activity = TRUE;
unset($tbldeps[$tblkey]);
array_push($order, $tblkey);
}
}
}
}
return $order;
} else {
print "/* Unable to retrieve tables */\r\n";
return array();
}
}
?>
Hope this helps (erm, someone)
Pat
22-Jan-2004 09:02
[Editor Note:
The password hashing was updated in MySQL 4.1, you must use the MySQLi extension with MySQL 4.1+ (or use the following method to allow
pre 4.1 clients to connect).]
MySQL 5.0 has a new password system, and PHP cannot connect to it because it cannot send a correct password. You must use the MySQL command OLD_PASSWORD() when adding a user to the database, or PHP cannot connect as of the library that comes with PHP 5.0Beta3
gyohng at netscape dot net
20-Jun-2003 04:16
imho at auspantheon dot com
06-Jun-2003 06:26
I wrote this function to replace mysql's <em>mysql --html</em> function.
It takes the result of a query, and creates the table.
Usage is as follows:
$result = mysql_query("SELECT col1,col2 FROM yourtable LIMIT 100")
sqlDoTable($result)
function sqlDoTable ($result) {
print "<table border=1 cellpadding=3 cellspacing=0>";
// Print Table Title
print "<tr>";
for ($i=0;$i < mysql_num_fields($result);$i++) {
print "<th>" . mysql_field_name($result, $i) . "</th>";
}
print "</tr>\n";
// Print table Content
for ($i=0;$i < mysql_num_rows($result);$i++) {
print "<tr>";
$row = mysql_fetch_row($result);
foreach ($row as $value) {
// Make sure 0's are displayed
if (is_numeric($value)) { $value = (int)$value; }
// Make sure table is displayed correctly
if (empty($value) && ($value !== 0)) { $value = " "; } else { $value = htmlspecialchars($value); }
print "<td>$value</td>";
}
print "</tr>\n";
}
print "</table>";
}
leader at k2wrpg dot org
03-May-2003 10:27
While doing lots of mysql work remotely I got tired of having to upload my files over and over again to find problems in my sql queries. I was also having a hard time checking the data in the DB itself (especially when behind a firewall, ugh.) anyway I present to you a single file query analyzer. So I made an easier way. It’s not persistent so some complex statements using temp tables and such probably won't work, but for simple Selects, Updates, Inserts and Deletes, it works just fine. I would say it handles about 95% of the queries I want to run. Anyway I found it useful so I'll share. Just save the following code into a file named sqlquery.php, and put it on your server If you name it something else you'll have to change the target of the form. Also its totally insecure so if you’re worried about eavesdroppers don't use it.
Enjoy!
rather than post the code, here is a download:
http://k2wrpg.org/~leader/sqlquery.php.zip
soren at byu dot edu
14-Mar-2003 07:23
Let's say that you want to generate a MySQL password hash from a plain text password. Normally, you would just submit the MySQL query "SELECT PASSWORD('password')", but if for some reason you can't access to MySQL database directly, then you can use the following function (translated right out of the MySQL source code):
function mysql_password($passStr) {
$nr=0x50305735;
$nr2=0x12345671;
$add=7;
$charArr = preg_split("//", $passStr);
foreach ($charArr as $char) {
if (($char == '') || ($char == ' ') || ($char == '\t')) continue;
$charVal = ord($char);
$nr ^= ((($nr & 63) + $add) * $charVal) + ($nr << 8);
$nr2 += ($nr2 << 8) ^ $nr;
$add += $charVal;
}
return sprintf("%08x%08x", ($nr & 0x7fffffff), ($nr2 & 0x7fffffff));
}
example:
<? print mysql_password("hello"); ?>
outputs:
70de51425df9d787
Which is the same result you get if you do "SELECT PASSWORD('hello')" directly in MySQL. Hopefully you'll never be in a situation where you have to use this, but if you need it (like I did), it's here.
past at sbox dot tugraz dot at
21-Feb-2003 08:17
As MySQL docs say, RAND() is not very usefull for generation of randomized result orders.
But this worked for me on Linux, however:
Somewhere before:
mt_srand((double)microtime()*1000000);
"SELECT *, " RAND(".mt_rand(0,86622340).")*10000%100 AS randomvalue ORDER BY randomvalue"
The upper value for mt_rand() has to be Quite Big to see any effect on MySQL's RAND(). The exact number shouldn't be significant. Note the multiplication and modulo; MySQL seems to count steadily upwards when generating random numbers, so we take some numbers from between.
mijnpc at xs4all dot nl
20-Nov-2002 08:33
If you have a Windows machine running a webserver with PHP you don't need to install MySQL server to locally test scripts, if you are granted to establish a Secure Telnet connection (port 22) to the remote webserver.
To do this you need a Secure Telnet client, which supports port-forwarding.
Before you establish a connection, define the port-forward.
Forward local port 3306 to [name or ip of remote server]:3306
Make sure that local ports accept connections from other hosts
Save this session
Connect to remote server with username and password
Minimize the shell and that's it...
You can use the same username (and password) as if you were working on the remote server !
E.g. : $link = mysql_connect("localhost", "root", "") or die("no way jose");
You may get a shell-timeout after xx minutes depending on your remote server, just reconnect or press enter in the shell once in a while...
An example of a superb freeware Secure Telnet client is Putty : Putty : http://www.chiark.greenend.org.uk/~sgtatham/putty/
This 'discovery' really has saved me a lot of time because I don't have to upload the scripts to the remote server time and time again, pressing [save] is enough, heh (-:
16-Jun-2002 06:38
Regarding transactions, you must use a recent MySQL version which supports InnoDB tables. you should read the mysql manual (the part about Innodb tables, section 7.5) and configure your server to use them.
Some reading about how it works:
http://php.weblogs.com/discuss/msgReader$1446?mode=topic
(Click where it says Part2, I can't put the direct URL here because it is too long)
Then in PHP you use commands like:
mysql_query("BEGIN");
mysql_query("COMMIT");
mysql_query("ROLLBACK");
You must make sure that you convert your existing tables to innodb or create new ones: CREATE TABLE (...) type=innodb;
vinod at jobsure dot com
09-Jun-2002 08:22
To protect your mysql server from long running query which hangs the database
This PHP code basically kills the long running sql process .
I kept this PHP file in cron to run every 15 minues and believe me
it had dramatic performance on my website and my mysql database NEVER HANGED
//################ //
kill_long_query.php
//################ //
set_time_limit(30000);
$result=mysql_query("show processlist");
while ($row=mysql_fetch_array($result))
{
$process_id=$row["Id"];
if (($row["Time"] > 100 ) || ($row["Command"]=="Sleep") )
{
print $row["Id"];
$sql="kill $process_id";
mysql_query($sql);
}
}
//###################//
http://www.db-forums.com/viewtopic.php?t=8
//###################//
pvenable at cs dot cmu dot edu
30-May-2002 11:43
I had some difficulties installing PHP with MySQL support on RedHat-7.1, but it works at last. :) Here are a few of the unexpected things I had to do to get it working:
After installing apache, mysql, and php4, I found a php-mysql rpm, since the php4 rpm lacks MySQL support. (I also had to find a few more rpms before this one would install.) Then, I had to make sure and export the path of the library libgcc_s.so (export LD_LIBRARY_PATH=/usr/local/lib) before running the Apache httpd. Otherwise PHP fails to load the MySQL library, which apparently depends on libgcc_s.so, but doesn't give much useful feedback except that the MySQL-specific commands you're using are unrecognized. I hope this saves someone else from going through the hours of hassle it took me to figure it out!
jeyoung at priscimon dot com
25-Apr-2002 03:23
[Ed. Note:
This may be due to the fact that subsequent calls to mysql_connect with the same parameters return the same resource id for the connection, so in reality it is using the same connection. In order to force a new link, you must specify the new_link parameter in mysql_connect.]
MySQL transactions
MySQL supports transactions on tables that are of type InnoDB. I have noticed a behaviour which is puzzling me when using transactions.
If I establish two connections within the same PHP page, start a transaction in the first connection and execute an INSERT query in the second one, and rollback the transaction in the first connection, the INSERT query in the second connection is also rolled-back.
I am assuming that a MySQL transaction is not bound by the connection within which it is set up, but rather by the PHP process that sets it up.
This is a very useful "mis-feature" (bug?) because it allows you to create something like this:
class Transaction {
var $dbh;
function Transaction($host, $username, $password) {
$this->dbh = mysql_connect($host, $username, $password);
}
function _Transaction() {
mysql_disconnect($this->dbh);
}
function begin() {
mysql_query("BEGIN", $this->dbh);
}
function rollback() {
mysql_query("ROLLBACK", $this->dbh);
}
function commit() {
mysql_query("COMMIT", $this->dbh);
}
}
which you could use to wrap around transactional statements like this:
$tx =& new Transaction("localhost", "username", "password");
$tx->begin();
$dbh = mysql_connect("localhost", "username", "password");
$result = mysql_query("INSERT ...");
if (!$result) {
$tx->rollback();
} else {
$tx->commit();
}
mysql_disconnect($dbh);
unset($tx);
The benefit of such a Transaction class is that it is generic and can wrap around any of your MySQL statements.
nospam at nospam dot nos
19-Nov-2001 03:17
ever wanted to know the date a table was last updated? use this:
$info = mysql_fetch_array(mysql_query("show table status from databasename like 'tablename'"));
echo $info["Update_time"];
skelley at diff dot nl
25-Sep-2001 08:11
Hi, here's a nice little trick to select records in random order from a table in a MySQL database prior to version 3.23
SELECT *, (ItemID/ItemID)*RAND() AS MyRandom FROM Items ORDER BY MyRandom
[[[Editors note:
And just SELECT * FROM foo WHERE bar = RAND() after 3.23
]]]
mbabcock-php at fibrespeed dot net
29-Jul-2001 01:41
Since there aren't functions to start and end/rollback transactions, you'll have to use mysql_query("BEGIN"), mysql_query("COMMIT") and mysql_query("ROLLBACK"). These will only work properly on tables that support transactions. You may also wish to roll your own mysql_begin (etc) functions that run the above queries for you.
philip at cornado dot com
23-Jul-2001 06:24
mw-php at ender dot com
22-Jun-2001 03:11
The mysql_fetch_[row|object|array] functions return data as type string. Owing to the very flexible nature of php variables, this is normally not relevent, but if you happen to retrieve two integers from a database, then try to compare with bitwise operators, you'll run into trouble, because (19 & 2) == 2, but ("19" & "2") == 0. To remedy this, if you use variables from a database with bitwise operators, use the settype() function to explicitly cast your variables as integers before comparing.
jcn at iki dot fi dot no_spam
01-May-2001 12:37
steer at projex dot hu
07-Mar-2000 05:53
A quick way to extract a mysql query result into variables, without using an array:
$result = mysql_query("select model,color,price from cars where id=$id");
extract(mysql_fetch_assoc($result));
Now you have the variables $model, $color, $price. You can ask extract() to prefix it for you, with like extract(stuff,EXTR_PREFIX_ALL,"r"), and right there you have $r_model, $r_color...
If you have to pull multiple rows from the query, e.g. using "while", you have to pull 'em to an array, but still can use extract() to extract them :)
while ($r = mysql_fetch_array($result)) {
extract($r);
...
}
| |
msql
