PHP  
downloads | documentation | faq | getting help | mailing lists | reporting bugs | php.net sites | links | my php.net 
search for in the  
<mysql_closemysql_create_db>
view the version of this page
Last updated: Sat, 29 Oct 2005

mysql_connect

(PHP 3, PHP 4, PHP 5)

mysql_connect -- Open a connection to a MySQL Server

Description

resource mysql_connect ( [string server [, string username [, string password [, bool new_link [, int client_flags]]]]] )

Opens or reuses a connection to a MySQL server.

Parameters

server

The MySQL server. It can also include a port number. e.g. "hostname:port" or a path to a local socket e.g. ":/path/to/socket" for the localhost.

If the PHP directive mysql.default_host is undefined (default), then the default value is 'localhost:3306'

username

The username. Default value is the name of the user that owns the server process.

password

The password. Default value is an empty password.

new_link

If a second call is made to mysql_connect() with the same arguments, 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.

client_flags

The client_flags parameter can be a combination of the following constants: MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE or MYSQL_CLIENT_INTERACTIVE. Read the section about Table 2 for further information.

Return Values

Returns a MySQL link identifier on success, or FALSE on failure.

ChangeLog

VersionDescription
4.3.0 Added the client_flags parameter.
4.2.0 Added the new_link parameter.
3.0.10 Added support for ":/path/to/socket" with server.
3.0.0 Added support for ":port" with server.

Examples

Example 1. mysql_connect() example

<?php
$link
= mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!
$link) {
   die(
'Could not connect: ' . mysql_error());
}
echo
'Connected successfully';
mysql_close($link);
?>

Example 2. mysql_connect() example using hostname:port syntax

<?php
// we connect to example.com and port 3307
$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');
if (!
$link) {
   die(
'Could not connect: ' . mysql_error());
}
echo
'Connected successfully';
mysql_close($link);

// we connect to localhost at port 3307
$link = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');
if (!
$link) {
   die(
'Could not connect: ' . mysql_error());
}
echo
'Connected successfully';
mysql_close($link);
?>

Example 3. mysql_connect() example using ":/path/to/socket" syntax

<?php
// we connect to localhost and socket e.g. /tmp/mysql.sock

//variant 1: ommit localhost
$link = mysql_connect('/tmp/mysql', 'mysql_user', 'mysql_password');
if (!
$link) {
   die(
'Could not connect: ' . mysql_error());
}
echo
'Connected successfully';
mysql_close($link);


// variant 2: with localhost
$link = mysql_connect('localhost:/tmp/mysql.sock', 'mysql_user', 'mysql_password');
if (!
$link) {
   die(
'Could not connect: ' . mysql_error());
}
echo
'Connected successfully';
mysql_close($link);
?>

Notes

Note: Whenever you specify "localhost" or "localhost:port" as server, the MySQL client library will override this and try to connect to a local socket (named pipe on Windows). If you want to use TCP/IP, use "127.0.0.1" instead of "localhost". If the MySQL client library tries to connect to the wrong local socket, you should set the correct path as mysql.default_host string in your PHP configuration and leave the server field blank.

Note: The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close().

Note: You can suppress the error message on failure by prepending a @ to the function name.



add a note add a note User Contributed Notes
mysql_connect
baldurien at club-internet dot fr
06-Oct-2005 07:13
There are some case where you will have one user, for example root, that may use different database, for example :

<?php
$x
= mysql_connect( ... );
mysql_select_db('foo', $x);
/* some queries on foo */
mysql_select_db('bar', $x );
/* some queries on bar */
?>

This is fine in that case, however if you would like to transfer one table from database "foo" to database "bar", you may do that :

INSERT INTO bar.desttable SELECT * FROM foo.srctable

(or CREATE TABLE)

But if you would like to convert data using a specific function, then you will rather use that :

<?php
$x
= mysql_connect( 'localhost', 'root', 'root' );
$y = mysql_connect( 'localhost', 'root', 'root' );
mysql_select_db('foo', $x);
mysql_select_db('bar', $y );
$q = mysql_query( 'SELECT field1, ..., fieldN
FROM srctable'
, $x );
while (
$a = mysql_fetch_row($q))
 
mysql_query( 'INSERT INTO bar VALUES(\" . foo($a[0]) . '', ...,  \'' . foo($a[0]) . '\'', $y);
?>

This will fail.

The reason is simple : PHP will reuse the existant connection for the second mysql_connect, so when you will select a database on server $x, you will also select it for $y, and vice versa!

This is why you have to use the fourth parameter to avoid this :

<?php
$x
= mysql_connect( 'localhost', 'root', 'root' );
$y = mysql_connect( 'localhost', 'root', 'root', true );
?>

This way your script will work.

Note : doing that mysql_close on $x will result in closing the connection for $y too.

<?php
$x
= mysql_connect( 'localhost', 'root', 'root' );
$y = mysql_connect( 'localhost', 'root', 'root' );

mysql_close($x);
mysql_close($y); // server is already closed
?>
contact at sonium dot org
19-Sep-2005 04:12
I had to use php4 (without mysql.connect_timeout) but wanted to check the availability of a database (without having to wait for 30 seconds).
if you use fsockopen(with timeout) and just close the connection after a few attempts the server will block your client.

this script logs you on to mysql and gets disconnected by the server (access denied) but it doesn't block the client for future connections:
<?php

$host
= "127.0.0.1";
$port = 3306;
$timeout = 5;

   echo
"trying to connect to $host on port $port\n";
  
$testcon = @fsockopen ($host, $port, $errno, $errstr, $timeout);
   if(!
$testcon)
       die(
"database unavailable: $errno: $errstr\n");
   else
   {
       echo
"connection successful under $timeout seconds\n";
       echo
"quit connection\n";

      
$in = fread($testcon,4096);
       echo
"server says:$in\n";

      
//login string
      
$out = array(    0x26,0x00,0x00,0x01, //packet length 38 bytes (without this 4 byte header // packet no.1
                      
0x85,0xa6,0x03,0x00,
                      
0x00,0x00,0x00,0x01,
                      
0x08,
                      
0x00,0x00,0x00,0x00, 0x00,0x00,0x00,0x00, 0x00,0x00,0x00,0x00, 0x00,0x00,0x00,0x00, 0x00,0x00,0x00,0x00, 0x00,0x00,0x00,
                      
0x66,0x69,0x66,0x69,0x00, //username 'fifi'
                      
0x00);    //no password

      
foreach($out as $o)
       {
          
fputs($testcon,chr($o),1);
       }

      
$in = fread($testcon,4096);
       echo
"server says:$in\n";

      
//logout command
      
$out = array(0x01,0x00,0x00,0x00,0x01);
       foreach(
$out as $o)
          
fputs($testcon,chr($o),1);

      
fclose($testcon);
   }

?>
rob at bronco dot co dot uk
19-Aug-2005 03:49
In a follow-up to Anthony Boyd, if you have the relevant capability you can also start the MySQL server with the --old-passwords option, or put the line "old_passwords" in to my.cnf/other MySQL config file, eg:

language        = /usr/share/mysql/english
max_connections = 1000
skip-external-locking
old_passwords
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address            = 127.0.0.1
bind-address            = *.*.*.*
key_buffer = 256M

I prefer this option as it is more transparent to processes creating passwords that you do not control, eg phpMyAdmin or - in my case - debian maintenance scripts.
Anthony Boyd
27-Jul-2005 12:53
Some versions of PHP do not support the new longer passwords used by MySQL 4.1 and later.  This will cause the error message that 2raptor mentioned a few posts prior to this one:

"Client does not support authentication protocol requested by server; consider upgrading MySQL client"

However, for ease of use, let's review the two fixes right here.

First, you should upgrade to a newer PHP that supports the longer MySQL passwords.  However, I don't even know if the PHP 4.x series has been upgraded to handle this yet.  So for my installation, I took a different approach -- I downgraded MySQL's passwords to the "old" shorter encryption.  Here's how:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

As a real-world example, I used this query:

SET PASSWORD FOR 'testaccount'@'%' = OLD_PASSWORD('secret');

Suddenly, PHP could use the testaccount to access the database.  Yay.

-Tony
spam at modem-help dot com
17-Jul-2005 01:44
(Centos4, php4, mysql4.1)

If mysql uses a Unix socket to connect, and you move the DB tables from the default directory, **do not** also change the location or name of the mysql socket file within my.cnf, else your old scripts will not connect without modification.

The simplest solution is to leave the socket file in it's default location (/var/lib/mysql with Centos/Redhat). Other options are to set `mysql.default_socket' (see http://www.php.net/manual/en/ref.mysql.php#ini.mysql.default-socket), or to set the new path within the server option to mysql_connect() as above.

Extra info: Centos4 sets `skip-networking' within my.cnf by default, which causes all mysql connections to be made via Unix sockets (or named pipes on Windows). This both restricts all connections to the localhost (a security enhancement) and is faster.

The problem is that, although `mysql.default_socket' is NULL by default, the Unix socket name + location is both hard-coded into the Centos/Redhat php-mysql RPM libraries *and* PHP will ignore any my.cnf socket-settings that differ from the defaults.

Brought to you from the School of Hard Knocks.
bacchus
15-Jul-2005 06:17
If you're using PHP5 on Debian (currently only available from dotdeb.org) and you get errors connecting to MySQL despite phpinfo() reporting that PHP was compiled with MySQL support, install the php5-mysql package.
25-Jun-2005 12:18
connect to mysql via named pipe under windows :

in my.ini, add this:

[mysqld]
enable-named-pipe

then connect to the server, then connect to mysql using

mysql_connect('.')
cwenner at gmail dot com
21-Jun-2005 06:15
The previous post regarding the reg exps featuring
   <Files ~ "\.inc(.php)?$">
should of course be
   <Files ~ "\.inc(\.php)?$">
Though with a hardly noticeable difference. Note that this (as well as the previous) takes both extensions of .inc as inc.php into account. Anyone interested in learning more regarding regular expressions may find such at the following page:
http://www.php.net/manual/en/ref.pcre.php
As well as in the many resources that may be found on the net and books now adays.
On a side note, one may fuse the <Files ~ "\.inc(\.php)?$"> and <Files ~ "^\.ht"> into <Files ~ "(^\.ht|\.inc(\.php)?$)"> to account for the two at once.
arekm at pld-linux dot org
13-Mar-2005 03:29
Setting default character set when connecting to mysql 4.1 server can be done by issuing for example:
SET NAMES latin2
command after each connection which isn't so nice because it needs to change every app.

There is nicer solution that can be used on server side - simply put in [mysqld] section of mysqld.conf:
init-connect="SET NAMES latin2"

This will cause ,,set names'' command to be issued on every connection - no need to change php apps.
codeslinger at compsalot dot com
18-Feb-2005 04:15
There are two ways to connect to the local server.

1) If you want to specify a Port # you MUST use 127.0.0.1 for the Host name.  When you specify "localhost" the Port number is ignored and the default socket is used instead.

Example:  $Host = "127.0.0.1:4321";

2) If you want to specify a socket path, the host name is optional, but if used, it MUST be "localhost".

Example:  $Host = "localhost:/path/to/socket";
or            $Host = ":/path/to/socket";

========

Note:  That giving the specified user permission to login from "localhost"  is NOT the same as giving them permission to login from "127.0.0.1"  and vice versa.  If you want to be able to login from both then you must create two entries, one for each host name, in the mysql.user table.

It is a really bad idea to allow logins from any computer by specifying the host wild card.  And elsewhere on this page is some horrible advice to specify a blank password...  Don't Do It unless you want your server cracked in about 5 minutes flat.  Using old_password() as described below, works just fine for php compatibility.

Remeber after adding or changing user permissions you MUST do a FLUSH PRIVILEGES;  the change that you made does not take effect until you do...  Guess how I know this ;-)
k [DOT] gornik [AT] xds [DOT] pl
10-Feb-2005 03:10
Surely made mistake below:
should be
<?php
          
if(version_compare($mysql_ver, '4.1.0', '>=')>0) {
?>
not
<?php
          
if(version_compare($mysql_ver, '4.2.0', '>=')>0) {
?>

Sorry :)
k dot gornik at xds dot pl
10-Feb-2005 03:06
To avoid something like this:

Database error: Invalid SQL: SELECT * FROM alias WHERE path='1108043672' AND fk_client_id=2
MySQL Error: 1267 (Illegal mix of collations (latin2_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '=')
Session halted.

PHP >=4.3.10
MySQL >=4.1.0

simply write:

<?php
class MySQL {

//[...]

  
function connect($host, $user, $pass) {
      
$this->Link_DB = @mysql_connect($host, $user, $pass);
       if(
$this->Link_DB) {
           if(
version_compare(phpversion(), "4.3.10", ">=")>0) {
              
$result = @mysql_query("SHOW VARIABLES LIKE 'version'", $this->Link_ID);
              
$row = mysql_fetch_row($result);
              
$mysql_ver = $row[1];
               if(
version_compare($mysql_ver, '4.2.0', '>=')>0) {
                  
$i = 1;
                   while(
defined('DB_INIT_CODE_'.$i)) {
                      
$sQuery = constant('DB_INIT_CODE_'.$i);
                      
mysql_query($sQuery, $this->Link_ID);
                      
$i++;
                   }
               }
           }
       }
       else {
           return
false;
       }
       return
true;
   }
}
?>

and

<?php

define
('DB_INIT_CODE_1', "SET NAMES latin2");
define('DB_INIT_CODE_2', "SET CHARACTER SET latin2");
define('DB_INIT_CODE_3', "SET COLLATION_CONNECTION='latin2_general_ci'");
// and so on...
?>

elsewhere (like I put it down in MySQL.config.inc.php)

The only important thing is You MUST include definitions before including object MySQL :)

Spent lot of time looking for prettier solution but there seems not to be any...
omikorn at yahoo dot com
05-Dec-2004 12:21
maybe you have seen this (using PHP 4.3.9 and MySQL client/server 4.1)

Error w/ php: mysql_connect(): Client does not support authentication protocol

Solution:
mysql -u root-p

SET PASSWORD FOR user@localhost = OLD_PASSWORD('password');

It is clearly that this is a bug in PHP's mysql module, perhaps it will be upgraded in the next version

Resource: http://forums.mysql.com/read.php?11,6400,6701#msg-6701
camitz at NOSPAM dot example dot com
27-Oct-2004 10:05
A description about the problem with the password hashing and how to adress them can be found at http://dev.mysql.com/doc/mysql/en/Password_hashing.html
chaoscontrol_hq at yahoo dot com
23-Aug-2004 11:10
In MySQL4.1 and later, the default password hashing format has changed making it incompatible with 3.x clients.
I found out mysql_connect() works on server versions >= 4.1 when your MySQL user password is blank because password authentication isn't done in that case, otherwise you need to use another connection method (e.g. mysqli).
Also if you are using old MySQL tables on a new server (i.e. the passwords are stored in the old format), then the server will use the old auth method automatically and this function should work in all cases.
Hopefully this will help someone, it had me confused for a while because some of the users on my 4.1 server could connect and some couldn't.
martinnitram at excite dot com
31-Oct-2003 11:22
to use load data local infile function from mysql (at mysql 4.0.16, php 4.3.3), set fifth parameter of mysql_connect() to CLIENT_LOCAL_FILES(128), which based on MYSQL C API ( also mysql server support load file, check by "show variables like 'local_infile' ")

Thank  'phpweb at eden2 dot com' to point this out
AlbieNoSp4m at hotmail dot com
13-Jul-2003 03:49
Just a small follow up, but a small error in someones post, regarding security of .inc files.

the ~ ".inc" is actually a regular expression, this expresion would match anything with inc in it, ex: wince.php, prince.html, etc.

The correct expression would be:
<Files ~ "\.inc(.php)?$">
  Order allow,deny
   Deny from all
   Satisfy All
</Files>

This makes it so, all your .inc or .inc.php files are not readable.

QUOTE:
So, just add the following chunk of text to your httpd.conf file:

<Files ~ ".inc">
  Order allow,deny
   Deny from all
   Satisfy All
</Files>

--
Andy Calderbank
phpweb at eden2 dot com
28-Jun-2003 08:55
client_flags can be things other than MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE and MYSQL_CLIENT_INTERACTIVE.

I presume that mysql_connect() just passes through to the C MySQL API, which provides these constants:

#define CLIENT_LONG_PASSWORD    1    /* new more secure passwords */
#define CLIENT_FOUND_ROWS    2    /* Found instead of affected rows */
#define CLIENT_LONG_FLAG    4    /* Get all column flags */
#define CLIENT_CONNECT_WITH_DB    8    /* One can specify db on connect */
#define CLIENT_NO_SCHEMA    16    /* Don't allow database.table.column */
#define CLIENT_COMPRESS        32    /* Can use compression protocol */
#define CLIENT_ODBC        64    /* Odbc client */
#define CLIENT_LOCAL_FILES    128    /* Can use LOAD DATA LOCAL */
#define CLIENT_IGNORE_SPACE    256    /* Ignore spaces before '(' */
#define CLIENT_CHANGE_USER    512    /* Support the mysql_change_user() */
#define CLIENT_INTERACTIVE    1024    /* This is an interactive client */
#define CLIENT_SSL              2048    /* Switch to SSL after handshake */
#define CLIENT_IGNORE_SIGPIPE  4096    /* IGNORE sigpipes */
#define CLIENT_TRANSACTIONS    8192    /* Client knows about transactions */

Not all of these may work or be meaningful, but CLIENT_FOUND_ROWS does, at least.
Graham_Rule at ed dot ac dot uk
14-May-2003 05:43
Another solution to the security problems of putting usernames and passwords into scripts. I haven't found this documented anywhere else so thought I'd suggest it for the online documentation. ........

Don't put passwords for mysql into scripts which may be read by any user on the machine.  Instead put them into an Apache configuration file and make sure that it is not world-readable. (Apache reads its main config files as root.)

For example, add this to your httpd.conf (and chmod it to 600 or 660) then tell your apache to reload itself (apachectl graceful).

<Directory /var/www/html/mydatabase>
   php_value mysql.default_user fred
   php_value mysql.default_password secret
   php_value mysql.default_host server.example.com
</Directory>

Then all you need in your PHP code is

$handle = mysql_connect() or die(mysql_error());

The passwords etc will only be picked up by scripts running in the named directory (or a sub-directory).  The same may be done for virtualhosts etc.

If you don't want to keep reloading your Apache server then you ay test things putting the php_value directives into a (world readable) .htaccess file. (Clearly not for production use.)

If you need to debug the values that are being supplied (or not) then use this snippet:

@syslog(LOG_DEBUG, "Using user=".ini_get("mysql.default_user").
           " pass=".ini_get("mysql.default_password").
           " host=".ini_get("mysql.default_host"));

(This assumes that you are not running in 'safe_mode' and that you are on a unix of some sort.)
amn -at- frognet.net
12-Mar-2003 07:40
Just in case you didn't know. You can use mysql_connect in a function to connect to a database and the connection is a super-global... meaning you can use mysql_query in other functions or in no function at all and PHP will use the connection that you opened. This is a handy bit of knowledge that helps if you have a large site with lots of scripts. If you create one function to connect to a db, and call that function in all your scripts, it makes for easier code maintenance since you only have to update one line of code to change your mysql connection instead of updating all your scripts individually.
rec0rder at lycos dot com
09-Apr-2002 08:54
The method I use to "protect" mySQL connect is to place dbConnect.php outside the web directory.

I will create a directory:
/var/include/

Put "dbConnect.php" into
/var/include/

Edit your php.ini file to read "/var/include/" an include directory.

In your PHP now, you just have to do:
require("dbConnect.php");
nospam at code24 dot com
27-Mar-2002 12:37
There should already be a post in here about this, but I would like to follow up on the idea that anyone can read your .inc files, which might contain username/password combos for mysql access.

There is a very simple way to block this.

If you are using Apache, just edit your httpd.conf file, and look for the following lines:

<Files ~ "^\.ht">
   Order allow,deny
   Deny from all
   Satisfy All
</Files>

Okay... that little chunk of text is saying that you don't want files that begin with .ht to be readable through apache. We also don't want people to see any files that end with .inc.

So, just add the following chunk of text to your httpd.conf file:

<Files ~ ".inc">
   Order allow,deny
   Deny from all
   Satisfy All
</Files>

This will block anyone from seeing your .inc files over the web.  It is much smarter than naming include files, "*.php".  Use the .php extension for your code, and save .inc for actual include data, and don't worry about people reading your .inc's anymore.

Hope this helps somebody.  Oh yeah... one other thing... obviously, anytime you make a change to httpd.conf (or whatever you have named your Apache config file), you must restart apache for the changes to take effect.

<mysql_closemysql_create_db>
 Last updated: Sat, 29 Oct 2005
show source | credits | sitemap | contact | advertising | mirror sites 
Copyright © 2001-2005 The PHP Group
All rights reserved.
This mirror generously provided by: Speedbone
Last updated: Wed Nov 16 23:18:20 2005 CET