PHP: mysql_connect

User Contributed Notes
mysql_connect

arekm at pld-linux dot org
13-Mar-2005 08: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
17-Feb-2005 09: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 08: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 08: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...

20-Jan-2005 11:38


Debian (and "flavors" of Debain such as Ubuntu Linux) installations of mysqld place the socket file on a directory OTHER than /tmp/mysql.sock causing php connection problems.  A quick workaround, as described in the docs above, is to specify ":/path/to/socket in the server parameter in db_connect.  Here is an example where mysql socket is located at /var/run/mysqld/mysqld.sock:



function open_connection() {

  $host = "localhost:/var/run/mysqld/mysqld.sock";

  $user = "root";

  $passwd = NULL;

  $db_name = "ipd";

  return db_connect($host, $user, $password, $db_name);

}

dennis at inmarket dot lviv dot ua
14-Jan-2005 02:19


Just FYI:

PHP5.0.3 works perfectly using mysql extension with MySQL4.1, all that has been fixed.

notmyaddress at idrathernotshare dot edu
01-Jan-2005 06:12


Beware that most php code currently available today requires the mysql library as opposed to mysqli.  If you are running 5.x and need to connect to software like phpBB, e107, etc. then you must use an old version of mysql prior to 4.1.x in order to use passwords.



PHP developers decided to break binary compatibility by adding the mysqli insterface instead of fixing mysql to work with newer mysql 4.1.x releases.

omikorn at yahoo dot com
04-Dec-2004 05: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 03: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

webmaster at NOJUNKTHANKS dot demogracia dot com
31-Aug-2004 05:43


Manual warns: �The new_link parameter became available in PHP 4.2.0�.



Before finding workarounds, make sure your old PHP version does not really support it. The one bundled with Red Hat 7.3 (php-4.1.2-7.3.6) does accept this parameter even though it shouldn't.

chaoscontrol_hq at yahoo dot com
23-Aug-2004 04: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.

benjamin at zerotop dot com
09-Apr-2004 09:38


Note: You can only work with recource IDs when the user you are using has access to ALL databases within the chosen server! (global privileges)

php at expires0104 dot astrobox dot net
29-Dec-2003 01:22


response to calego at calego dot cc:



register_shutdown_function($this->close);



didn't work for me: Warning: Unknown(): Unable to call () - function does not exist in Unknown on line 0



but this here works:



register_shutdown_function(array(&$this, 'close'));

calego at calego dot cc
07-Dec-2003 02:04


mysql_connect() can be called through the constructor for your database class, if you have one. This is a handy little function and saves you having to place $DB->connect() on every one of your pages.



<?php

class DB {

    function DB() {

        $this->host = "mysql.host.com";

        $this->db = "myDatabase";

        $this->user = "root";

        $this->pass = "mysql";

        $this->link = mysql_connect($this->host, $this->user, $this->pass);

        mysql_select_db($this->db);

    }

}

?>



And then, on each of your pages, include the file with your database class, and then...



<?php

$DB = new DB;

?>



And you are connected and selected to your database. $DB->link now acts as the resource identifier, although you could also apply it to a simple variable like $link, since this becomes a superglobal.



Even though it's unnecessary, you could also do something like this:



<?php

class DB {

    function DB() {

        $this->host = "mysql.host.com";

        $this->db = "myDatabase";

        $this->user = "root";

        $this->pass = "mysql";

        $this->link = mysql_connect($this->host, $this->user, $this->pass);

        mysql_select_db($this->db);

        register_shutdown_function($this->close);

    }

    function close() {

        mysql_close($this->link);

    }

}

?>



That code is also useful in that it makes each class have its own specific link. So to open multiple database connections, you simply create multiple database classes.



<?php

$DB_admin = new DB;

$DB_user = new DB;

?>



And then you have two separate database connections that will close automatically and have their own resource identifier. If you don't want to remember to place the resource identifier in each query, no worries. Simply use the DB class' query() function with the proper instance of the DB class.



<?php

class DB {

    function DB() {

        $this->host = "mysql.host.com";

        $this->db = "myDatabase";

        $this->user = "root";

        $this->pass = "mysql";

        $this->link = mysql_connect($this->host, $this->user, $this->pass);

        mysql_select_db($this->db);

        register_shutdown_function($this->close);

    }

    function query($query) {

        $result = mysql_query($query, $this->link);

        return $result;

    }

    function close() {

        mysql_close($this->link);

    }

}

?>

martinnitram at excite dot com
31-Oct-2003 04: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

razi at qau dot edu dot pk
18-Oct-2003 05:34


mysql_connect() opens a connection that allows only a limited no. of resource ids. Which means, say, in a certain connection you can have only a hundred or so (I don't know exactly how many) select statements running properly.

After these Resource ids are exhausted mysql queries won't return any information.

For large scripts therefore mysql_close() becomes necessary.

AlbieNoSp4m at hotmail dot com
13-Jul-2003 08: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 01: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 10: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.)

joc at presence-pc dot com
10-May-2003 11:27


A new URL for the documentation on the effects of the client_flags :



http://www.mysql.com/doc/en/mysql_real_connect.html

apmuthu at usa dot net
16-Mar-2003 04:59


If the server, port and socket are given, then for example the server can be replaced with:-



localhost:3424:/tmp/mysock.sock



Have tested it out with PHP v4.1.2+ and MySQL v3.23.49+

amn -at- frognet.net
12-Mar-2003 12: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.

19-Feb-2003 02:07


ideservefreesoftware's method of forcing a new connection to be made was not practical for my customers, as setting up a new MySQL user is not an easy task for them.



Instead, I was able to mimic the behaviour of the new_link parameter by tacking ":3306" to the end of the server hostname. This is fairly safe since the port of the MySQL server is almost always 3306, however I recommend only doing this if you really have to.



if (!function_exists('version_compare') or version_compare(phpversion(), '4.2.0') < 0) {

$config['server'] .= ':3306';

// call mysql_(p)connect() without the new_link parameter

} else {

// call mysql_(p)connect() with new_link set to true

}

ideservefreesoftware at gmx dot de
28-Jun-2002 10:01


If you use mysql_connect with identical parameters twice (e.g. a second connection to log all sql queries without an impact on the first one), the resource variables will be different (echo $dbl says Resource id #1, echo $dbl_log says Resource id #2), but in some way the same (mysql_insert_id($dbl) and mysql_insert_id($dbl_log) are always identical).



Workaround for those using PHP < 4.2.0 (4.2.0 provides the new_link parameter to solve this problem):



use a separate db-user for the second connection



I encountered this problem with PHP Version 4.0.6.



Example:



$dbl = @mysql_connect($dbms_hn, $dbms_un, $dbms_pw);

$dbl_log = @mysql_connect($dbms_hn, dbms_un, $dbms_pw);

mysql_select_db($dbms_db, $dbl); 

mysql_select_db($dbms_db, $dbl_log); 

$sql = ... insert into table-1-with-autoincrement-column statement;

$rs = mysql_query($sql, $dbl);

echo "dbl: " . $dbl . " insert_id: " . mysql_insert_id($dbl) . '<br/>';

echo "dbl_log: " . $dbl_log . " insert_id: " . mysql_insert_id($dbl_log) . '<br/>';

$sql = ... insert into table-2-with-autoincrement-column statement;

$rs = mysql_query($sql, $dbl_log);

echo "dbl: " . $dbl . " insert_id: " . mysql_insert_id($dbl) . '<br/>';

echo "dbl_log: " . $dbl_log . " insert_id: " . mysql_insert_id($dbl_log) . '<br/>';



Result: (table1 initially contained 3 rows)



dbl: Resource id #1 mysql_insert_id: 4

dbl_log: Resource id #2 mysql_insert_id: 4

dbl: Resource id #1 mysql_insert_id: 1

dbl_log: Resource id #2 mysql_insert_id: 1



This should be the result:



dbl: Resource id #1 mysql_insert_id: 4

dbl_log: Resource id #2 mysql_insert_id: 0

dbl: Resource id #1 mysql_insert_id: 4

dbl_log: Resource id #2 mysql_insert_id: 1



---

rec0rder at lycos dot com
09-Apr-2002 01: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
26-Mar-2002 05: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.

flemming at webduo dot dk
29-Jan-2002 10:25


If using an mysql server on a different port like 3307 instead of 3306, or running multiple mysql serveres on one machine. Be advised that using localhost as host in mysql_connect for instance localhost:3307 will override the port and use the default port 3306. Use the computer host name or domain instead. Even IP is better than localhost. But remember to add the user with the correct host in your mysql database. It's not a bug in the mysql module, but a bug in the mysql client. Mysql unix client have the same flaw.

mysql_connect

Description

Parameters

Return Values

ChangeLog

Examples

Notes

See Also