|
|
 |
LXII. MySQL FunctionsIntroduzione
These functions allow you to access MySQL database servers.
More information about MySQL can be found at .
Documentation for MySQL can be found at .
Requisiti
In order to have these functions available, you must compile PHP with
MySQL support.
Istallazione
By using the --with-mysql configuration
option you enable PHP to access MySQL databases. If you use this option
without specifying the path to MySQL, PHP will use the built-in MySQL
client libraries. With PHP4 MySQL support is always enabled; if you don't
specify the configure option, the bundled libraries are used. Users who
run other applications that use MySQL (for example, running PHP 3 and PHP
4 as concurrent apache modules, or auth-mysql) should always specify the
path to MySQL: --with-mysql=/path/to/mysql. This will force
PHP to use the client libraries installed by MySQL, avoiding any
conflicts.
| Attenzione |
Crashes and startup problems of PHP may be encountered
when loading this extension in conjunction with the recode extension.
See the recode extension for more
information.
|
Configurazione Runtime
The behaviour of the MySQL functions is affected by settings in the
global configuration file php.ini.
Tabella 1. MySQL Configuration Options | Name | Default | Changeable |
|---|
| 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 | "-1" | PHP_INI_SYSTEM |
For further details and definition of the PHP_INI_* constants see
ini_set().
Here is a short explanation of the configuration directives.
- mysql.allow_persistent
boolean
Whether to allow
persistent connections
to MySQL.
- mysql.max_persistent
integer
The maximum number of persistent MySQL connections per
process.
- mysql.max_links
integer
The maximum number of MySQL connections per process, including
persistent connections.
- mysql.default_port
string
The default TCP port number to use when connecting to
the database server if no other port is specified. If
no default is specified, the port will be obtained
from the MYSQL_TCP_PORT environment
variable, the mysql-tcp entry in
/etc/services or the compile-time
MYSQL_PORT constant, in that order. Win32
will only use the MYSQL_PORT constant.
- mysql.default_socket
string
The default socket name to use when connecting to a local
database server if no other socket name is specified.
- mysql.default_host
string
The default server host to use when connecting to the database
server if no other host is specified. Doesn't apply in
safe mode.
- mysql.default_user
string
The default user name to use when connecting to the database
server if no other name is specified. Doesn't apply in
safe mode.
- mysql.default_password
string
The default password to use when connecting to the database
server if no other password is specified. Doesn't apply in
safe mode.
- mysql.connect_timeout
integer
Connect timeout in seconds. On Linux this timeout is also used for
waiting for the first answer from the server.
Resource Type
There are two resource types used in the MySQL module. The first one
is the link identifier for a database connection, the second a resource
which holds the result of a 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.
Since PHP 4.3.0 it is possible to specify additional client flags for the
mysql_connect() and mysql_pconnect()
functions. The following constants are defined:
Tabella 2. MySQL client constants | constant | description |
|---|
| MYSQL_CLIENT_COMPRESS | use compression protocol | | MYSQL_CLIENT_IGNORE_SPACE | Allow space after function names | | MYSQL_CLIENT_INTERACTIVE | Allow interactive_timeout seconds (instead of wait_timeout) of
inactivity before closing the connection. |
The function mysql_fetch_array() uses a constant for
the different types of result arrays. The following constants are
defined:
Tabella 3. MySQL fetch constants | constant | description |
|---|
| MYSQL_ASSOC |
Columns are returned into the array having the fieldname as the array
index.
| | MYSQL_BOTH |
Columns are returned into the array having both a numerical index
and the fieldname as the array index.
| | MYSQL_NUM |
Columns are returned into the array having a numerical index to the
fields. This index starts with 0, the first field in the result.
|
Esempi
This simple example shows how to connect, execute a query, print
resulting rows and disconnect from a MySQL database.
Esempio 1. MySQL extension overview example <?php
/* Connecting, selecting database */
$link = mysql_connect("mysql_host", "mysql_user", "mysql_password")
or die("Could not connect");
print "Connected successfully";
mysql_select_db("my_database") or die("Could not select database");
/* Performing SQL query */
$query = "SELECT * FROM my_table";
$result = mysql_query($query) or die("Query failed");
/* Printing results in HTML */
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";
/* Free resultset */
mysql_free_result($result);
/* Closing connection */
mysql_close($link);
?> |
|
User Contributed Notes
MySQL Functions |
  |
[email protected]
07-Mar-2000 06: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);
...
}
|
|
[email protected]
22-Mar-2000 09:38 |
|
The easy way to surpress MySQL error messages is to put an "@" in
front of the mysql command. For example,
@mysql_db_query(whatever).
This also works for any other PHP
functions. (ed.)
|
|
[email protected]
21-Dec-2000 05:17 |
|
Something to Note:
MySQL uses Unix time functions and has no
problems with dates until 2069; all 2-digit years are regarded to be in
the range 1970 to 2069, which means that if you store 01 in a year column,
MySQL treats it as 2001.
|
|
|
15-Feb-2001 07:51 |
|
A good place to get beginner's info and some good pointers on using MySQL
is:
|
|
[email protected]
04-Apr-2001 09:04 |
|
Just a note, when using MySQL to output files (ie: a database backup
script) if the directory permissions are not set right, it will not create
the files AND WILL NOT return an error code. You must have the
permissions set right.
|
|
[email protected]_spam
01-May-2001 01:37 |
|
For one solution to problems with MySQL and special Nordic characters,
please see
|
|
[email protected]
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.
|
|
[email protected]
30-Jun-2001 06:24 |
|
The mysql_fetch_row function returns an array with numeric
indices.
For example, a row from the query:
SELECT
bookid,title,author FROM books
would look
like:
$record[0]=3
$record[1]="A Farewell To
Arms"
$record[2]="Ernest Hemingway"
The
mysql_fetch_assoc function returns an array with indices that are the
field names, like
such:
$record["bookid"]=3
$record["title"]="A
Farewell To Arms"
$record["author"]="Ernest
Hemingway"
The mysql_fetch_array function returns an array
with both kinds of indices, so this array looks
like:
$record[0]=3
$record["bookid"]=3
$record[1]="A
Farewell To Arms"
$record["title"]="A Farewell To
Arms"
$record[2]="Ernest
Hemingway"
$record["author"]="Ernest
Hemingway"
This is the reason for the "doubled"
field output from the example.
Note that the mysql_fetch_array
function has an optional parameter where you can specify the
indices.
So replacing the
call:
mysql_fetch_array($result)
with:
mysql_fetch_array($result,MYSQL_NUM)
would
be an identical fix to:
mysql_fetch_row($result)
Hope
this helps!
|
|
[email protected]
30-Jun-2001 06:41 |
|
It looks like the reason for the double vision is so that when you want to
use the sql results, you can call the array elements via either indices
(e.g. $line[0], $line[1], etc.) or by their database field names (e.g.
$line["uid"], $line["pw"], $line["gid"],
etc.).
You can see this by changing the example to print
$col_name as well as $col_value. Each repeated val shows up with a
different key (of course) each time.
|
|
[email protected]
13-Jul-2001 08:23 |
|
Hi,
The problem with
extract(mysql_fetch_array($result));
is that it actually fetches two arrays, a number index array
($arr[0], $arr[1], $arr[2], ...) and an associative (hash) array,
($arr["field1"], $arr["field2"],
$arr["field3"], ...), thus $arr actually contains two sets of
data (as you have observed).
If you then try to push out all the
data from $arr without explicitly naming those field ids, then you WILL
get your data appearing twice.
The full definition for
mysql_fetch_array is as follows:
mysql_fetch_array ($result_id,
[MYSQL_NUM|MYSQL_ASSOC])
Since MYSQL_NUM is equivalent to
mysql_fetch_row, you have found that switching to mysql_fetch_row works
fine.
In fact, the only real reason you may want to use
mysql_fetch_array is with the MYSQL_ASSOC option, as this allows you to
reference the fields in the associative array returned.
|
|
[email protected]
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:
*
*
*
|
|
[email protected]
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.
|
|
[email protected]
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 WHERE
bar = RAND() after 3.23
]]]
|
|
[email protected]
19-Nov-2001 04: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"];
|
|
[email protected]
18-Feb-2002 11:44 |
|
I find it the least problematic to simply use the list() construct to
assign variables from an array returned from the mysql_fetch_row() &
others.
$data = mysql_query("SELECT name, data FROM
table");
while(list($t_name, $t_data) = mysql_fetch_row($data))
{
echo "Name: $t_name Data:
$t_data\n";
}
It's not too difficult to maintain something
like this, since the list() variables mirror the fields being queried. It
gets messy when you have more than 10 fields you are working with.
|
|
[email protected]
25-Apr-2002 04:23 |
|
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.
|
|
[email protected]
31-May-2002 12: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!
|
|
[email protected]
05-Jun-2002 04:04 |
|
just a little FYI, when installing PHP from an RPM. In order to get MySql
support you need to download the php-mysql*.rpm. If you don't install the
php-mysql*.rpm you'll just get the common error: Call to undefined
function: mysql_connect().
|
|
[email protected]
09-Jun-2002 09: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);
}
}
//###################//
//###################//
|
|
|
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;
|
|
[email protected]
06-Sep-2002 02:15 |
|
is there a way to prevent php from running mysql_free_result after the page
is finished loading, as i would like to use the same result in several
pages, by passing the result id in the url??
Any suggestions?
|
|
|
|
| |
msql