|
|
 |
LXVII. MySQL funkce
Tyto funkce zprost�edkov�vaj� p��stup na MySQL datab�zov� server.
V�ce informac� o MySQL lze nal�zt na .
Dokumentace k MySQL je dostupn� na .
Aby byly tyto funkce dostupn�, mus� b�t PHP zkompilov�no s
podporou MySQL.
Pou�it�m konfigura�n� volby --with-mysql[=DIR]
povol�te PHP p�istupovat k MySQL
datab�z�m.
V PHP 4 je volba --with-mysql ve
v�choz�m nastaven� povolena. Pro zak�z�n� MySQL m��ete pou��t volbu
--without-mysql.
Pokud v PHP 4 povol�te MySQL bez zad�n� cesty k
MySQL, PHP pou�ije p�ibalen� klientsk� knihovny
MySQL. Pod Windows nen� pot�eba ��dn� DLL, je p��mo obsa�eno v PHP 4.
U�ivatel�, kte�� pou��vaj� jin� aplikace, kter� pou��vaj� MySQL (nap��klad
auth-mysql) by nem�li pou��t p�ibalen� knihovny, ale m�li by rad�ji ur�it
cestu k instala�n�mu adres��i MySQL, nap�.:
--with-mysql=/path/to/mysql.
To p�inut� PHP pou��vat klientsk� knihovny
instalovan� MySQL, ��m� se vyhnete p��padn�m konflikt�m.
V PHP 5 nen� ve v�choz�m nastaven� MySQL nad�le zapnuto ani nejsou k dispozici p�ibalen�
knihovny MySQL. P�e�t�te si tuto
FAQ s vysv�tlen�m d�vod�.
Toto MySQL roz���en� nebude pracovat s MySQL verzemi v�t��mi ne�
4.1.0. Pro tyto verze pou�ijte MySQLi.
| Varov�n� |
Pad�n� a probl�my p�i startu PHP mohou nastat,
pokud nahr�v�te toto roz���en� spolu s roz���en�m recode.
Viz roz���en� recode pro bli���
informace.
|
Pozn�mka:
Pokud pot�ebujete k�dov� str�nky jin� ne� latin (v�choz�),
mus�te nainstalovat extern� (ne p�ibalenou) knihovnu se zakompilovanou podporou k�dov�ch
str�nek.
Chov�n� t�chto funkc� je ovlivn�no nastaven�m parametr� v php.ini.
Tabulka 1. Mo�nosti nastaven� MySQL | Jm�no | V�choz� | Zm�niteln� |
|---|
| 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 |
Podrobn� popis a definice konstant PHP_INI_* naleznete v
ini_set().
Zde je stru�n� vysv�tlen�
konfigura�n�ch direktiv.
- mysql.allow_persistent
boolean
M�-li b�t povoleno
persistentn� (trval�)
spojen� s MySQL.
- mysql.max_persistent
integer
Maxim�ln� po�et persistentn�ch spojen� na jeden proces.
- mysql.max_links
integer
Maxim�ln� po�et spojen� s MySQL na jeden proces v�etn� persistentn�ch
spojen�.
- mysql.default_port
string
��slo v�choz�ho TCP portu pro spojen� s datab�zov�m serverem, pokud nen�
port zad�n. Nen�-li v�choz� port zad�n, pou�ije se port uveden� v prom�nn�
prost�ed� MYSQL_TCP_PORT, z�znam
mysql-tcp v /etc/services
nebo "compile-time" konstanta MYSQL_PORT, v tomto
po�ad�. Win32 pou��v� pouze konstantu MYSQL_PORT.
- mysql.default_socket
string
V�choz� jm�no socketu pro p�ipojen� k lok�ln�mu datab�zov�mu serveru,
nen�-li jin� socket specifikov�n.
- mysql.default_host
string
V�choz� server pro spojen� s datab�zov�m serverem, nen�-li uveden jin�.
Nelze pou��t p�i bezpe�n�m re�imu (safe mode).
- mysql.default_user
string
V�choz� u�ivatel pro spojen� s datab�zov�m serverem, nen�-li uveden jin�
u�ivatel. Nelze pou��t p�i bezpe�n�m re�imu (safe
mode).
- mysql.default_password
string
V�choz� heslo pro spojen� s datab�zov�m serverem, nen�-li uvedeno jin�
heslo. Nelze pou��t p�i bezpe�n�m re�imu (safe
mode).
- mysql.connect_timeout
integer
Timeout p�ipojen� v sekund�ch. Na Linuxu je tento timeout pou�it tak� pro
�ek�n� na prvn� odpov�� serveru.
V MySQL modulu jsou pou�ity dva typy zdroj�. Prvn� je identifik�tor
spojen� pro p�ipojen� k datab�zi a druh� uchov�v� v�sledek dotazu.
Tyto konstanty jsou definov�ny t�mto roz���en�m a budou k dispozici pouze
tehdy, bylo-li roz���en� zkompilov�no spole�n� s PHP nebo dynamicky zavedeno
za b�hu.
Od PHP 4.3.0 je mo�n� nastavit klienta dopl�uj�c�mi parametry pro
funkce mysql_connect() a mysql_pconnect()
Jsou definov�ny n�sleduj�c� konstanty:
Tabulka 2. MySQL klientsk� konstanty | Konstanta | Popis |
|---|
| MYSQL_CLIENT_COMPRESS | Pou�ije kompresn� protokol | | MYSQL_CLIENT_IGNORE_SPACE | Povol� mezeru za n�zvy funkc� | | MYSQL_CLIENT_INTERACTIVE | Povol� interactive_timeout sekundy (nam�sto wait_timeout)
neaktivity p�ed uzav�en�m spojen�. |
Funkce mysql_fetch_array() pou��v� konstanty pro
r�zn� typy v�sledkov�ch pol�. Jsou definov�ny n�sleduj�c� konstanty:
Tabulka 3. MySQL fetch konstanty | Konstanta | Popis |
|---|
| MYSQL_ASSOC |
Sloupce jsou vraceny do pole jeho� kl��emi jsou n�zvy sloupc�.
| | MYSQL_BOTH |
Sloupce jsou vr�ceny do pole maj�c�ho ��slen� i textov�
kl��e, ur�uj�c� po�ad� sloupce v tabulce, respektive jeho jm�no. | | MYSQL_NUM |
Vrac� sloupec do pole s ��seln�mi kl��i reprezentuj�c�mi po�ad� sloupce
v tabulce. Prvn� sloupec tabulky za��n� kl��em 0. |
Tento jednoduch� p��klad ukazuje jak se p�ipojit, prov�st dotaz,
zobrazit v�sledn� ��dky a odpojit se z MySQL datab�ze.
P��klad 1. Uk�zkov� p��klad pou�it� MySQL |
<?php
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die("Nelze se p�ipojit: " . mysql_error());
print "P�ipojeno �sp�n�";
mysql_select_db("my_database") or die("Nelze vybrat datab�zi");
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Dotaz nelze prov�st: " . mysql_error());
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "\t<tr>\n";
foreach ($line as $col_value) {
print "\t\t<td>$col_value</td>\n";
}
print "\t</tr>\n";
}
print "</table>\n";
mysql_free_result($result);
mysql_close($link);
?>
|
|
22-Oct-2004 02:04
Having trouble loading extensions under windows? Seems as though php.ini is not being read at all?
Maybe the php5 installer has written a PHPIniDir directive in your httpd.conf telling php to look for php.ini in c:\php\
nleippe at integr8ted dot com
12-Oct-2004 10:22
trace_mode breaks SQL_CALC_FOUND_ROWS.
This is because it emits an EXPLAIN <query> before sending the <query> by itself, thus the subsequent SELECT FOUND_ROWS() is no longer the next consecutive query, and the result is zero.
This was true for me for at least MySQL 4.0.21 and 4.1.5gamma.
(PHP 4.3.9)
Melvin Nava: e-4(at)venezolano.web.ve
13-Sep-2004 07:02
To count page hits, just put next code in a text file and include it in every one of your pages. It will log even different querystrings as different pages. (a MySQL database and table is needed first)
This can be a pretty good example of what you can do with PHP and MySQL. I made this script to log and show all hits in:
<?php
function page_count($page) {
$c_link = mysql_connect('localhost', 'username', 'password')
or die('counter CONNECT error: '.mysql_errno().', '.mysql_error());
mysql_select_db('database_name');
$c_query = "SELECT * FROM stats_pagecounter
WHERE (page_name = '$page')";
$c_result = mysql_query($c_query, $c_link)
or die('counter SELECT error: '.mysql_errno().', '.mysql_error());
if (mysql_num_rows($c_result)) {
$row=mysql_fetch_array($c_result,MYSQL_ASSOC);
$pcounter = $row['page_hits']+1;
$c_update = "UPDATE stats_pagecounter
SET page_hits = '$pcounter' WHERE page_name = '$page'";
$c_hit = mysql_query($c_update, $c_link)
or die('counter UPDATE error: '.mysql_errno().', '.mysql_error());
} else {
$c_insert = "INSERT INTO stats_pagecounter
VALUES ( '0', '$page', '1')";
$c_page = mysql_query($c_insert, $c_link)
or die('counter INSERT error: '.mysql_errno().', '.mysql_error());
$pcounter = 1;
}
mysql_free_result($c_result);
mysql_close($c_link);
return $pcounter;
}
$phpself_url = $_SERVER['SERVER_NAME'].
$_SERVER['PHP_SELF'].'?'.
$_SERVER['QUERY_STRING'];
$page_hits = page_count($phpself_url);
echo $page_hits;
?>
aidan at php dot net
15-Aug-2004 12:59
If you want to replicate the output of `mysql --html`, printing your results in a HTML table, see this function:
irn-bru at gmx dot de
27-May-2004 12:27
Note, that the sql.safe_mode configuration setting does effect all mysql_* functions. This has nothing to to with the php safe mode, check the [SQL] section in php.ini.
I found out, that if you set sql.safe_mode = On, mysql_connect will ignore provided username and passwort and makes use of the script owner instead (checked on debian).
Brian
Pat
22-Jan-2004 11: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 05:16
The following page contains a complete easy to read tutorial of MySQL programming with PHP.
soren at byu dot edu
14-Mar-2003 09: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):
<?php
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 10: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 10: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 :
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 07: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:
(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;
jeyoung at priscimon dot com
25-Apr-2002 04: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 05: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 09: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 ORDER BY RAND()" after 3.23]
mbabcock-php at fibrespeed dot net
29-Jul-2001 02: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 07:24
If you're new to this, you really should learn basic SQL before moving on. PHP != SQL. Here's are a few good basic SQL tutorials:
*
*
*
mw-php at ender dot com
22-Jun-2001 04: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.
| |
msql
