|
 |
LXXIX. Функции СУБД MySQL
Чтобы работать с функциями, вы должны скомпилировать PHP с поддержкой
MySQL.
Используя директиву --with-mysql[=DIR]
вы можете включить в PHP поддержку СУБД MySQL.
В PHP 4, директива --with-mysql
включена по умолчанию. Чтобы отключить её, используйте директиву
конфигурации --without-mysql.
Кроме того, в PHP 4, при включении директивы без указания пути к папке установки
MySQL, PHP будет использовать встроенную библиотеку
MySQL-клиента. В Windows специальные DLL отсутствуют, клиент всегда встроен
в PHP4.
При использовании приложений использующих MySQL (к примеру, auth-mysql) не
стоит использовать встроенную библиотеку -- укажите путь к папке установки
MySQL, что будет выглядеть примерно так: --with-mysql=/path/to/mysql.
Это заставит PHP использовать библиотеку, установленную MySQL,
что позволит избежать любых конфликтов.
В PHP 5 поддержка MySQL больше не включена по умолчанию, кроме того в нём
отсутствует встроенная библиотека-клиент. Подробнее о причине можно
прочитать в
FAQ.
Расширение работает с MySQL версий >= 4.1.0, но не эксплуатирует
всю возможную функциональность этих версий.
Для этого используйте MySQLi.
| Внимание |
Сбои в работе PHP могут иметь место при загрузке
совместно этого расширения и расширения для работы с библиотекой GNU Recode.
За дополнительной информацией обращайтесь к разделу о расширении для recode.
|
Замечание:
Если вам требуется поддержка кодировок (отличных от latin, установленной по умолчанию),
вам придётся установить внешнюю библиотеку, скомпилированную с их
поддержкой.
Поведение этих функций зависит от установок в php.ini.
Таблица 1. Директивы конфигурации MySQL | Имя | Значение по умолчанию | Область изменения |
|---|
| 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 |
За дальнейшей информацией и значениями PHP_INI_* констант обращайтесь к
ini_set().
Краткое разъяснение конфигурационных
директив.
- mysql.allow_persistent
логическое
Позволять ли
постоянные соединения
с MySQL.
- mysql.max_persistent
целое
Максимальное количество постоянных соединений на один процесс.
- mysql.max_links
целое
Максимальное количество соединений с MySQL на один процесс, включая
постоянные соединения.
- mysql.default_port
строка
TCP-порт, используемый для соединения с базой данных по умолчанию (если
не был указан другой). Если эта директива опущена, порт будет взят из
переменной среды MYSQL_TCP_PORT, значения
mysql-tcp в /etc/services или константы
MYSQL_PORT, указанной при компиляции, в указанном порядке.
Win32 использует только константу MYSQL_PORT.
- mysql.default_socket
строка
Тип сокета, используемого для соединения с локальной базой данных, если не
был указан другой.
- mysql.default_host
строка
Адрес сервера, используемый для соединения с базой данных, если не указан
другой. Не работает в
безопасном режиме.
- mysql.default_user
строка
Имя пользователя, используемое для соединения с базой данных, если не
указано другое. Не работает в
безопасном режиме.
- mysql.default_password
строка
Пароль, используемый для соединения с базой данных, если не указан
другой. Не работает в
безопасном режиме.
- mysql.connect_timeout
целое
Время ожидания овета до разрыва соединения в секундах. Linux также использует это значение
при ожидании первого ответа от сервера.
Модуль MySQL исползует два дополнительных типа указателей. Первый
является указателем на соединение с базой данных, второй указывает на
ресурс, содержащий результат запроса.
Перечисленные ниже константы определены данным расширением и могут быть
доступны только в том случае, если PHP был собран с
поддержкой этого расширения или же в том случае, если
данное расширение подгружается во время выполнения.
Начиная с PHP 4.3.0 можно указать дополнительные флаги для функций
mysql_connect() и mysql_pconnect()
functions. Предопределены следующие константы:
Таблица 2. MySQL константы | константа | описание |
|---|
| MYSQL_CLIENT_COMPRESS | использовать протокол сжатия | | MYSQL_CLIENT_IGNORE_SPACE | Позволяет вставлять пробелы после имён функций | | MYSQL_CLIENT_INTERACTIVE | Ждать interactive_timeout секунд (виесто wait_timeout)
бездействия, до закрытия соединения. |
Функция mysql_fetch_array() использует константы для
определения типа возвращаемого массива. Предопределены следующие константы:
Таблица 3. Константы выборки MySQL | константа | описание |
|---|
| MYSQL_ASSOC |
Результат возвращается в ассоциативном массиве с индексами под именами
колонок.
| | MYSQL_BOTH |
Результат возвращается в массиве, содержащем как численные индексы, так
и индексы под именами колонок.
| | MYSQL_NUM |
Результат возвращается в массиве, содержащем численные индексы. Индексы
стартуют с 0 (0 содержит первую колонку).
|
Этот пример показывает, как соединиться с базой данных, выполнить запрос,
распечатать результат и отсоединиться.
Пример 1. Пример работы с MySQL |
<?php
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die("Could not connect : " . mysql_error());
print "Connected successfully";
mysql_select_db("my_database") or die("Could not select database");
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Query failed : " . 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);
?>
|
|
 add a note
User Contributed Notes
Функции СУБД MySQL
medic at setiherbipolis dot de
15-May-2005 11:42
Client does not support authentication protocol requested by server; consider upgrading MySQL client
means that you're using an old version of MySQL Client ( possibly not mysqli)
Authentication protocol for MySQL has changed with version 4.1.
To get a hint at which mysql-client version you're using try phpinfo();
05-May-2005 03:24
I install php 5 and mysql 4.1. when I try to connect to mysql, I get this:
Client does not support authentication protocol requested by server; consider upgrading MySQL client
any help is appreciated.
04-May-2005 09:12
1) Windows users will need to enable php_mysql.dll inside of php.ini
2) make libmysql.dll available to the PATH
3) change this in your php.ini file:
extension_dir = "./" to something like "c:\php\ext"
jonathan at belial dot co dot uk
14-Apr-2005 03:36
I spent the last age and a day trying to get mysql 4.1.1. to tie into php 5 with no avail... be sure to set:
PHPIniDir "C:/php"
in your httpd.conf file. If you do not then phpinfo() will report that your extension directory is 2c:/php5" and thereby ignore any extensions you attempt to include in your php.ini.
Good Luck.
06-Mar-2005 02:01
If you are installing PHP5 on Windows 2003 server (AKA Win 2k3) and need MySQL to work using the either the php_mysql.dll or php_mysqli.dll or both of them at the same time, and MySQl isn't showing up in phpinfo, then your php.ini is probably not loading. In the direction in the PHP 5 zip file, they will tell you to add your PHP install directory to your windows path. This should tell php where to load its php.ini from but it doesn't. If you want to get this to work, you don't have to copy any DLL's anywhere like everyone suggests. All you have to do is add the folling regsitry key to windows:
[HKEY_LOCAL_MACHINE\SOFTWARE\PHP]
"IniFilePath"="C:\\PHP"
simply copy the above 2 lines of code into a text file and save the file as php_ini_path.reg
After you save the file it will look like a registry file. Simply double click on it.
It will make it so PHP will look for your php.ini in C:\PHP. I would assume you can edit this if you install php into a different location, but I haven't tried that.
After running the reg file, make sure your php.ini is in your PHP dir and make sure all the appropriate things are set. This should get you up and running. Make sure you also follow all the steps on how to make it work in IIS. This is just an addition to the direction.
Protik Mukherjee
03-Mar-2005 12:34
Fedora mysql problems!!
In Fedora 3 the php mysql module does not come with the default installation. To install it use $>yum install php_mysql
If u dont do this you will get errors with mysql functions like mysql_connect()
Hope this helps!
j at jonathany.com
01-Feb-2005 03:09
Users attempting to install MySQL under PHP5 on Windows may have trouble if they use the MSI installer of PHP, which does not include the DLL php_mysql.dll .
In order to succesfully install MySQL on PHP5, download the ZIP version of PHP, which includes the php_mysql.dll.
tumaine no at spam verizon net
23-Dec-2004 11:21
I had a hard time with upgrading to php version 5.2.0 in Windows XP Pro since mySQL queries all of a sudden stopped working and led to blank pages on my site. I spent a good half day searching google trying to figure out this problem, and didn't quite know how compiling PHP would help me. It is not necessary. Set up PHP manually with the ZIP folder download.
This is a good link to read and wish I found it earlier:
http://www.zend.com/manual/install.windows.extensions.php
If you are getting an error popup about not being able to load some mysql.dll when starting apache, you need to change this in your php.ini file:
extension_dir = "./" to something like "c:\php\ext"
Also what I was doing wrong was that I forgot to uncomment the following line in my php.ini file:
extension=php_mysql.dll
Restart apache, and everything should work.
Thought that I could save someone time and frustration when upgrading, since versions 5+ do not include mySQL support by default as earlier versions apparently do.
jon at mysql dot com
11-Dec-2004 05:32
Re Pat's note: You can add the --old-passwords option in the [mysqld] section of your MySQL my.cnf or my.ini configuration file. This option will force the MySQL server to use the old-style password hashing for all connections. This is not really recommended, as it's less secure, but will allow you to use existing accounts without resetting the passwords.
Of course, as already mentioned, you can use the MySQL OLD_PASSWORD() function instead to handle this issue on an account-by-account basis.
The optimal solution when migrating to MySQL 4.1+ from a previous version is to upgrade to PHP 5 (if you're not using it already) and rewrite any code accessing MySQL using the mysqli extension, which is more secure and provides a much better API.
For more information, see the MySQL Manual: http://dev.mysql.com/doc/mysql/en/Application_password_use.html
lkujala at uniserve dot com
18-Nov-2004 04:43
PROBLEM:
Error Message: the specified module could not be found.
When trying to load a php_mysql.dll / php_mysqli.dll / php_mssql.dll extension on a Windows platform.
CAUSE:
The standard windows installer package is rather incomplete; it does not include any of the DLL's needed for the optional extensions. In order to use any extension you need to install the FULL zip distribution (unless you like fooling around with dll hell), not just the php_*.dll extensions. You might as well include ALL of the DLL's since the dependencies as documented are wrong (i.e. you need more than libmysql.dll for the php_mysql.dll to load).
I did find the standard windows installer useful for the inital setup though.
22-Oct-2004 10: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 06: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 03: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: http://www.venezolano.web.ve/
<?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 08:59
irn-bru at gmx dot de
27-May-2004 08: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 06: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 01:16
soren at byu dot edu
14-Mar-2003 04: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 05: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 05: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 03: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;
jeyoung at priscimon dot com
25-Apr-2002 12: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 12: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 05: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
28-Jul-2001 10: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 03:24
mw-php at ender dot com
22-Jun-2001 12: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.
| |