Как подключить php к mysql

Как подключить php к mysql

The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.

The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. To open a TCP/IP connection to the localhost, 127.0.0.1 must be used instead of the hostname localhost .

Example #1 Special meaning of localhost

$mysqli = new mysqli ( «localhost» , «user» , «password» , «database» );

echo $mysqli -> host_info . «\n» ;

$mysqli = new mysqli ( «127.0.0.1» , «user» , «password» , «database» , 3306 );

echo $mysqli -> host_info . «\n» ;

The above example will output:

Localhost via UNIX socket 127.0.0.1 via TCP/IP

Connection parameter defaults

Depending on the connection function used, assorted parameters can be omitted. If a parameter is not provided, then the extension attempts to use the default values that are set in the PHP configuration file.

Example #2 Setting defaults

mysqli.default_host=192.168.2.27 mysqli.default_user=root mysqli.default_pw="" mysqli.default_port=3306 mysqli.default_socket=/tmp/mysql.sock

The resulting parameter values are then passed to the client library that is used by the extension. If the client library detects empty or unset parameters, then it may default to the library built-in values.

Built-in connection library defaults

If the host value is unset or empty, then the client library will default to a Unix socket connection on localhost . If socket is unset or empty, and a Unix socket connection is requested, then a connection to the default socket on /tmp/mysql.sock is attempted.

On Windows systems, the host name . is interpreted by the client library as an attempt to open a Windows named pipe based connection. In this case the socket parameter is interpreted as the pipe name. If not given or empty, then the socket (pipe name) defaults to \\.\pipe\MySQL .

If neither a Unix domain socket based not a Windows named pipe based connection is to be established and the port parameter value is unset, the library will default to port 3306 .

The mysqlnd library and the MySQL Client Library (libmysqlclient) implement the same logic for determining defaults.

Connection options

Connection options are available to, for example, set init commands which are executed upon connect, or for requesting use of a certain charset. Connection options must be set before a network connection is established.

For setting a connection option, the connect operation has to be performed in three steps: creating a connection handle with mysqli_init() or mysqli::__construct() , setting the requested options using mysqli::options() , and establishing the network connection with mysqli::real_connect() .

Connection pooling

The mysqli extension supports persistent database connections, which are a special kind of pooled connections. By default, every database connection opened by a script is either explicitly closed by the user during runtime or released automatically at the end of the script. A persistent connection is not. Instead it is put into a pool for later reuse, if a connection to the same server using the same username, password, socket, port and default database is opened. Reuse saves connection overhead.

Every PHP process is using its own mysqli connection pool. Depending on the web server deployment model, a PHP process may serve one or multiple requests. Therefore, a pooled connection may be used by one or more scripts subsequently.

Persistent connection

If an unused persistent connection for a given combination of host, username, password, socket, port and default database cannot be found in the connection pool, then mysqli opens a new connection. The use of persistent connections can be enabled and disabled using the PHP directive mysqli.allow_persistent. The total number of connections opened by a script can be limited with mysqli.max_links. The maximum number of persistent connections per PHP process can be restricted with mysqli.max_persistent. Please note that the web server may spawn many PHP processes.

A common complain about persistent connections is that their state is not reset before reuse. For example, open and unfinished transactions are not automatically rolled back. But also, authorization changes which happened in the time between putting the connection into the pool and reusing it are not reflected. This may be seen as an unwanted side-effect. On the contrary, the name persistent may be understood as a promise that the state is persisted.

The mysqli extension supports both interpretations of a persistent connection: state persisted, and state reset before reuse. The default is reset. Before a persistent connection is reused, the mysqli extension implicitly calls mysqli::change_user() to reset the state. The persistent connection appears to the user as if it was just opened. No artifacts from previous usages are visible.

The mysqli::change_user() call is an expensive operation. For best performance, users may want to recompile the extension with the compile flag MYSQLI_NO_CHANGE_USER_ON_PCONNECT being set.

It is left to the user to choose between safe behavior and best performance. Both are valid optimization goals. For ease of use, the safe behavior has been made the default at the expense of maximum performance.

See also

• mysqli::__construct()
• mysqli_init()
• mysqli::options()
• mysqli::real_connect()
• mysqli::change_user()
• $mysqli::host_info
• MySQLi Configuration Options
• Persistent Database Connections

mysql_connect

This extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide. Alternatives to this function include:

Description

mysql_connect (
string $server = ini_get(«mysql.default_host») ,
string $username = ini_get(«mysql.default_user») ,
string $password = ini_get(«mysql.default_password») ,
bool $new_link = false ,
int $client_flags = 0
): resource | false

Opens or reuses a connection to a MySQL server.

Parameters

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’. In SQL safe mode, this parameter is ignored and value ‘localhost:3306’ is always used.

The username. Default value is defined by mysql.default_user. In SQL safe mode, this parameter is ignored and the name of the user that owns the server process is used.

The password. Default value is defined by mysql.default_password. In SQL safe mode, this parameter is ignored and empty password is used.

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. In SQL safe mode, this parameter is ignored.

The client_flags parameter can be a combination of the following constants: 128 (enable LOAD DATA LOCAL handling), MYSQL_CLIENT_SSL , MYSQL_CLIENT_COMPRESS , MYSQL_CLIENT_IGNORE_SPACE or MYSQL_CLIENT_INTERACTIVE . Read the section about MySQL client constants for further information. In SQL safe mode, this parameter is ignored.

Return Values

Returns a MySQL link identifier on success or false on failure.

Examples

Example #1 mysql_connect() example

$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

// 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

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

// variant 1: omit 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, the correct path should be set as mysql.default_host in php.ini and the server field left 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:

Error «Can’t create TCP/IP socket (10106)» usually means that the variables_order configure directive doesn’t contain character E . On Windows, if the environment is not copied the SYSTEMROOT environment variable won’t be available and PHP will have problems loading Winsock.

See Also

• mysql_pconnect() — Open a persistent connection to a MySQL server
• mysql_close() — Close MySQL connection

User Contributed Notes 36 notes

11 years ago

Be carefull here if you use utf8.

The file db.opt of your database should contain the following lines:
default-character-set=utf8
default-collation=utf8_general_ci

It means that your database is created to use the utf8 characterset.
One way to accomplish this is:
CREATE DATABASE my_database DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Then, after connecting to it from PHP you should use:
mysql_set_charset(«UTF8», $connection);

If you don’t do this, you will get ugly problems in case other software is reading and writing to the same database.

15 years ago

The too many connections issue can be due to several problems.

1. you are using pconnect. This can tie up many connections and is not really needed for MySQL as new connections are really fast.

2. Apache children are hanging around for too long — combine this with pconnect and you have recipe for disaster.

Suggestions: reduce the amount of time apache child processes stay connected to the client and how many connections before they are killed off. And don’t use pconnect.

7 years ago

fcgid_module modules/mod_fcgid.so
FcgidMaxRequestLen 209715200
FcgidConnectTimeout 240
FcgidIOTimeout 240
FcgidBusyScanInterval 240
FcgidBusyTimeout 240
# Esta línea instruye al servidor web para que reconozca un tipo nuevo (php)
AddHandler fcgid-script .php
# Esta línea indica al servidor web donde está instalado PHP.
FcgidInitialEnv PHPRC «c:/php»
# Esta línea indica al servidor web que debe ejecutar la aplicación
# php-cgi.exe cuando un cliente (navegador) solicite una página con
# extensión .php
FcgidWrapper «c:/php/php-cgi.exe» .php
# Con esta línea damos los permisos necesarios para que los clientes puedan
# acceder/ejecutar a los archivos .php

Options Indexes FollowSymLinks Includes ExecCGI
AllowOverride None
Allow from all

16 years ago

In case anyone else is getting «Client does not support authentication protocol requested by server; consider upgrading MySQL client» error. The problem is the new password hashing method used by MySQL >= 4.1 mentioned below.

Either update your PHP to v5 where the new password hashing is supported or use old_password() in MySQL 4.1.

UPDATE mysql.user SET password=old_password(«youroldhashpassword») WHERE user =’youruserid’ and host =’yourhost’

10 years ago

$spojeni = mysql_connect ( $server , $login , $pass ) or die ( ‘nelze se pripojit’ );
mysql_select_db ( $db , $spojeni );
mysql_query ( «Set names ‘utf8′» );
?>

16 years ago
MYSQL_CLIENT_SSL is not working, use MySQLi and mysqli->ssl_set()
14 years ago

Hopefully this saves someone some grief.

My dev computer is windows and runs wampserver. I have frequent problems with PHP being unable to connect to MySQL after periods of extreme DB activity.

Long story short, it was because I was not running mysql via named-pipes and Windows was running out of available ports to serve PHP. Apparently, on windows, you have 5000 ports to work with and once they are opened, they remain so for 120 seconds before being released. This causes problems with mysql/networking because a new port is requested for each connection.

You can read more about the problem at:
(Link too long and had to be broken up)
http://dev.mysql.com/doc/refman/5.0/en
/can-not-connect-to-server.html#can-not-connect-to-server-on-windows
?>

Since mysql is on localhost, I can just enable named-pipes (which is how you should have mysql setup if you don’t need networking) to get around the problem instead of the workaround listed on that page.

20 years ago

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.

16 years ago

The use of mysql connections can become tricky with objects. I am using mysql_connect() in a database class I wrote and the class destructor calls mysql_close. Because I have several of these database objects, mysql_connect reuses existing connections. This is fine except when the script reaches the end of execution and PHP’s garabage collection calls all the objects’ __destruct() functions. mysql_close() throws a warning that the connection is invalid, in my case for one object. This is happening with objects which use an existing connection, as the connection has already been closed. I solved the problem by forcing mysql_connect() to create a new connection each time. This is not efficient but is sufficient for my purposes for now.

I wouldn’t say this is a bug per-se, but it’s something to look out for. I imagine using mysqli is the ultimate solution.

16 years ago

Sometimes, I want that MySQL service start automatically when my app need it. This is specially true if you work in a development PC and/or in an small intranet environment.

You can do something like this: if the mysql_connect() function returns FALSE, try to force the initialization of the MySQL service!

For example, under Windows:

$link = @ mysql_connect ( $server , $user , $pass );
if (empty( $link )) @ exec ( «%SystemRoot%\\system32\\net.exe start mysql» );
sleep ( 5 );
$link = @ mysql_connect ( $servidor , $usuario , $clave );
>

?>

In Linux of course you can try «/etc/init.d/mysqld start» but you will need special permissions.

16 years ago

if between first and second call with same arguments there was another call with another argument, initial connection link is not reused, but new connection is created instead, regardless of new_link argument.

for example, here only one single link will be opened and then reused:
$link1 = mysql_connect ( «localhost» );
$link2 = mysql_connect ( «localhost» );
?>

and here _three_ separate links will be opened:
$link1 = mysql_connect ( «localhost» );
$link3 = mysql_connect ( «127.0.0.1» );
$link2 = mysql_connect ( «localhost» );
?>

so if you wanted to switch between connections just by call to mysql_connect, and rely on its internal link caching, you can be wasting your database connections.

13 years ago

Whenever you open two connections to a single database,
you are likely not to get any error when selecting not existing db.

$db1 = mysql_connect ( . );
mysql_select_db ( ‘existing_db’ , $db1 );

$db2 = mysql_connect ( . );
mysql_select_db ( ‘not_existing_db’ , $db2 );

mysql_query (. , $db2 );
//will return no errors and the query wouldn’t be executed.
?>

Pay attention and you may save few hours of debugging.

12 years ago

If you encounter speed problems using this command to a distant server, you can add the line «skip-name-resolve» in your my.cnf to fix it.

20 years ago

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

17 years ago

The addition of entries to httpd.conf to stop .inc files being served by Apache is certainly useful and to be recommended.

But it doesn’t change the fact that these files have to be readable by Apache so that the PHP processor can get at them.

As long as your don’t have multiple, possibly untrusted, users on your machine then that’s OK. But when you are running a large multi-user service with thousands of users its always possible that one of them will look at your .inc files and take a note of the passwords you have in them. They could even copy them into their own scripts and modify your databases!

Even if local users are trusted, there is always the possibility of a rogue script (PHP or some nastier language) being installed by an ignorant user. That script might then read your .inc files (whether or not they are in the web publishing tree) and expose your password.

20 years ago

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).

php_value mysql.default_user fred
php_value mysql.default_password secret
php_value mysql.default_host server.example.com

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.)

12 years ago

My situation: «I could connect to MySQL from the PHP via Apache and MySQL via the MySQL console, and could not connect via the PHP»

But, PHP only can connect to MySQL using mysql_connect(«localhost», «root», «password»);

«Its selinux not allowing apache to make remote connections»

Solution:
setsebool -P httpd_can_network_connect=1

15 years ago

Recently, I saw an obscure problem where I could connect to MySQL from the PHP via Apache and MySQL via the MySQL console, and could not connect via the PHP-CLI. This was in Windows (XP). I usually use MySQLi extension, but also tried MySQL, and both refused to work.

I restarted the service multiple times, and the PHP-CLI still would not connect.

This eventually cleared up.

I made sure to stop the service. Then, I downloaded a zipped binary-package from dev.mysql.com and started the server a few times from the commandline (mysqld/mysqld-nt, where mysqld-nt is tuned specifically for Windows) and stopped it («mysqladmin shutdown»). I was then able to successfully connect from the PHP-CLI («php -r «mysql_connect(‘localhost’, ‘root’, »); «).

Making sure it was stopped, I started the regular server from the commandline, and that was then successful. I then stopped it and started it via the Services panel, and everything still worked.

I’m assuming that when the service was restarted initially, there was a component that had died and refused to be shutdown even though the service appeared to be stopped, but shutting it down via mysqladmin killed everything entirely.

13 years ago

Note that named pipe on Windows is unusable since PHP 5.3, and TCP connection shall be used even in localhost.

16 years ago

The post from ‘Graham_Rule at ed dot ac dot uk’ should include the following WARNING:

WARING: THE VALUES OF THESE DIRECTIVES WILL BE EXPOSED IF ANY OF THE CODE INCLUDES THE phpinfo() FUNCTION.

The phpinfo() function will print these values clear as day. I highly suggest against this method of storing MySQL authentication information.

I recommend creating connect and cleanup functions in a separate include file. If security is a concern, locate the include file outside of your web root folder.

function GetMyConnection ()
global $g_link ;
if( $g_link )
return $g_link ;
$g_link = mysql_connect ( ‘host.name’ , ‘user’ , ‘password’ ) or die( ‘Could not connect to server.’ );
mysql_select_db ( ‘database_name’ , $g_link ) or die( ‘Could not select database.’ );
return $g_link ;
>

function CleanUpDB ()
global $g_link ;
if( $g_link != false )
mysql_close ( $g_link );
$g_link = false ;
>

?>

Simply include your connnection.php file in your script and anywhere you use the mysql_query() function include a call to the GetMyConnection() function.

$res = mysql_query ( «SELECT . » , GetMyConnection () );
?>

15 years ago

If you are trying to open multiple, separate MySQL connections with the same MySQL user, password, and hostname, you must set $new_link = TRUE to prevent mysql_connect from using an existing connection.

For example, you are opening two separate connections to two different databases (but on the same host, and with the same user and password):

$db1 = mysql_connect($dbhost, $dbuser, $dbpass);
$rv = mysql_select_db($dbname1, $db1);
$db2 = mysql_connect($dbhost, $dbuser, $dbpass);
$rv = mysql_select_db($dbname2, $db2);

At this point, both $db1 and $db2 will have selected the database named by $dbname2.

The workaround is to require that the second MySQL connection is new:

$db1 = mysql_connect($dbhost, $dbuser, $dbpass);
$rv = mysql_select_db($dbname1, $db1);
$db2 = mysql_connect($dbhost, $dbuser, $dbpass, TRUE);
$rv = mysql_select_db($dbname2, $db2);

Now, $db1 should have selected $dbname1, and $db2 should have selected $dbname2.

This has been documented on the mysql_select_db page as well.

Note: This occurs only when the server, username, and password parameters are identical for each mysql_connect statement.

12 years ago

Portable connections: If you migrate your source code from one to different servers and you would like to avoid re-configuration on a new serve, user the $_SERVER[‘SERVER_NAME’] flag as:

switch( $_SERVER [ ‘SERVER_NAME’ ])
<
case ‘server1.example.com’ :
mysqlconnect ( ‘host1’ , ‘user1’ , ‘password1’ );
mysql_select_db ( ‘db1’ );
break;
case ‘server2.example.com’ :
mysqlconnect ( ‘host2’ , ‘user2’ , ‘password2’ );
mysql_select_db ( ‘db2’ );
break;
>
?>

This makes a conditional connection to mysql database. It automatically chooses the correct server according to the server name from where your script runs.

Hopefully, you like this portable configuration.

17 years ago

If you prefer to use a hostname instead of an ip on your connection string in a script (to be able to change the ip at will), but don’t want the overhead of dns lookups, just add it to your /etc/hosts file (in windows: %WINDIR%/system32/drivers/etc/hosts).

For example, add the following to your hosts file (changing the bogus ip to your server’s real ip):

Note: On linux, make sure you have «order: hosts,bind» on your /etc/host.conf file.

On a script, make the mysql connection like so:

$sid = mysql_connect («mysqlserver1», «user», «pass»);
?>

Note: this sample is in php, but it can be any other programming language (just type «ping mysqlserver1» on a prompt, on your server)

And there you have it! If your server ever gets assigned a different ip, just update the hosts file with the new one (every script will work as-is, even if under different hostnames).

11 years ago

A little note if your scripts sleep a lot, you want to run exactly the same SQL statement 2+ times and you have the «MySQL has gone away» error a lot.

Try setting the 4th parameter to TRUE as it seems sometimes PHP doesn’t spot that resource ID x which it used for the last identical lookup is now dud and so tries to use it, thus bypassing tests such as is_resource() and causing a failure.

This is for when mysql_ping() doesn’t work for your situation of course.

19 years ago

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.

17 years ago

Ever wonder what «default username» is?
$link = mysql_connect () or die( mysql_error ());
$result = mysql_query ( «SELECT SESSION_USER(), CURRENT_USER();» );
$row = mysql_fetch_row ( $result );
echo «SESSION USER: » , $row [ 0 ], «
\n» ;
echo «CURRENT USER: » , $row [ 1 ], «
\n» ;
?>
Both are ODBC@localhost in my win2k install, so my advice for windows is:
— create a MySQL user named ODBC with no password
— add localhost to ODBC user [right-click ODBC]
— set schema previleges to ODBC@localhost
— use mysql_connect() with no parms, or do not use 😉
This turns to work also with odbc_connect:
odbc_connect(«myDSN», «», «»)

10 years ago

hello,every one.
do use «127.0.0.1» instead of «localhost» for php5.4-win7 if you feel the speed is slow.because win7 use ipv6 default.
«localhost» is for ipv4.

15 years ago

I just wanted to share a common wrapper that I use for executing one line SQL statements. Its an easy wrapper to use that takes care of the connection open/close. Optionally, the mysql_connect can be replaced with mysql_pconnect for persistent connections.

function executeQuery( $query, $db, $nocon )
if( $nocon != «nocon» )
if( $db != «» ) connect( $db );
else connect( «pascal_crm» );

$result= mysql_query( $query );
$err = mysql_error();
if( $err != «» ) echo «error=$err «;

if( $nocon != «nocon» )
mysql_close();
return $result;
>

18 years ago

connect to mysql via named pipe under windows :

in my.ini, add this:

then connect to the server, then connect to mysql using

15 years ago

When you connect and expect to use a stored procedure,you must pass a special flag to MySQL via the connect command, otherwise you will not get the results returned, and it will result in this error:
PROCEDURE AlexGrim.GetStats_ForumCategories can’t return a result set in the given context

To fix this, change you connection string, adding «,false,65536» as the last 2 fields:
$this->con = mysql_connect($this->h,$this->u,$this->p,false,65536);

13 years ago

If you are getting an error «Can’t assign requested address» you may have a problem with the mysql port. I had just moved my server to Mac OS X 10.6 and mysql_connect was giving this error. Going into the /etc/php.ini file and setting the default port number to 3306 fixed the problem.

The php.ini file suggests that PHP will select the port by using the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services, but in this case it is not so. /etc/services on my machine has 3306 listed, but it didn’t get picked up.

This is sort of a bug report, in that the documented feature isn’t working. Please don’t delete this until the community decides how to address the problem. This is one of those hair pulling exercises to get fixed.

14 years ago

If you are getting MySQL Errors like #2006: MySQL server has gone away, and you are using mysql_connect() and pcntl_fork() then make shure that you are reconnecting to the mysql server with each created child which you fork()ed.

I pulled my hair out for many days because i was using the same mysql connection for each child and was getting that «MySQL server has gone away» errors.

Here is a simple example:
$link = mysql_connect ( $db_server , $db_user , $db_pass );
mysql_select_db ( $db_database , $link ));

$pid = pcntl_fork ();
if ( $pid == — 1 )
// Error forking child
elseif ( $pid ) // Parent will be here
> else // The child has to esablish a *new* mysql connection.
// if you use mysql_connect without the 4th parameter
// then it will use the connection from the parent. But
// if the child dies, the connection will be unaviable in
// the parent too.
// So, note the «true» as 4th parameter.
$newlink = mysql_connect ( $db_server , $db_user , $db_pass , true );
mysql_select_db ( $db_database , $newlink ));
// .
>
?>

15 years ago

[EDIT by danbrown AT php DOT net: The issue this user is illustrating is a common problem when dealing with multiple databases from PHP. Note his comments at the end of the code for an explanation.]

Looks like I learned this the hard way:

//establish connection to master db server
mysql_connect ( DB_HOST , DB_USER , DB_PASSWORD );
mysql_select_db ( DB_NAME );

//establish connection to read-only slave cluster
$objMySQL_Read = mysql_connect ( SLAVE_DB_HOST , SLAVE_DB_USER , SLAVE_DB_PASSWORD );
mysql_select_db ( DB_NAME , $objMySQL_Read );

$strSQL = «SELECT col1,col2 FROM » . DB_NAME . «.» . «tbl1 WHERE 1=1» ;

$objRS = mysql_query ( $strSQL , $objMySQL_Read ); //returns data from slaves

$strSQL = «INSERT INTO » . DB_NAME . «.» . «tbl1 (col1,col2) VALUES (val1,val2)» ;

//expected behavior, to insert the last statement into the master db, since it doesn’t reference the read-only resource explicitly. instead, it inserts the record into the last connection, even though it shouldn’t, since the last connection is not a global/anonymous connection like the first one, it’s $objMySQL_Read.

//you’ll get out of sync db’s across your cluster unless you explicitly define all connection resources

11 years ago

After upgrading mysql sever to 5.5.21 from 5.0.9 on centos, my WinXP stopped being able to connect with host with the error message
‘Bad Handshake’.

Eventual solution to problem was to remove MYSQL_CLIENT_SSL from the call to mysql_connect().

I will also report this as a bug.

15 years ago

If you trying to connect to a remote server, here are a few things that can go wrong. Perhaps this list will save someone some time:

1. You may need to get in touch with the remote server’s tech support:

a. to ensure that you can get through its firewall. It is not necessarily enough to have your server number listed in the recipient site’s cpanel remote access host list. It depends on how the server company has things set up;

b. to find out what port number they are using for database connections, which may not be the default used by mysql_connect;

c. If you are using ODBC, the host to which you are trying to connect may or may not have any ODBC drivers installed; and

d. If you are working from a dynamic IP, they may be set up to accommodate it, or you may have to use a proxy. See http://forge.mysql.com/wiki/MySQL_Proxy .

2. If you are working from a shared server yourself, the server number you were sent in the sign-up letter is probably NOT the server number you should be using to connect to a remote database. You need the server number of the machine on which your site is sitting, not your virtual account server number on that machine. You can get this from your own tech support.

I am grateful to Jonathan Jones at Bluehost for this analysis.

PHP-подключение к БД MySQL (Два Способа с Примерами)

Если вы новичок в разработке сайтов, вам может пригодиться это руководство, объясняющее, как настроить подключение к БД MySQL посредством PHP-скрипта. С помощью этого навыка вы сможете изменять, просматривать и управлять таблицами, созданными в базе данных MySQL. И сейчас мы покажем вам самые простые способы это сделать. Давайте начнём!

Получите скидку до 85% на наши тарифы хостинга с поддержкой PHP.

Создание Базы Данных MySQL (При необходимости)

Этот шаг можно пропустить, если у вас уже есть база данных MySQL. Если вы являетесь пользователем Hostinger, вы можете легко создать новую БД через hPanel — панель управления хостингом Hostinger, следуя простой инструкции:

1. Найдите меню «Базы данных MySQL» в разделе «Базы данных».
2. Заполните все необходимые поля и нажмите «Создать».

Инструкции по созданию базы данных MySQL с помощью cPanel см. в этом руководстве (англ.). Однако имейте в виду, что это пустые базы данных. Вам нужно будет заполнить их данными, прежде чем вы сможете ими управлять.

Запишите учётные данные только что созданной базы данных MySQL для следующего шага. А также не забудьте имя пользователя и пароль к БД!

Два способа PHP-подключения к БД MySQL

Есть два метода подключения к базе данных MySQL с помощью PHP: MySQLi и PDO.

MySQLi расшифровывается как MySQL Improved. Это эксклюзивное расширение MySQL, которое добавляет новые функции в интерфейс базы данных. Функции MySQLi являются как процедурными, так и объектно-ориентированными, причём первую парадигму расширение унаследовало от более ранней версии MySQL.

Сама MySQL разбивает задачу на линейные, пошаговые процедуры, что затрудняет внесение изменений, поскольку вам приходится редактировать код сверху. Между тем MySQLi рассматривает данные как набор взаимозаменяемых объектов с функциями, позволяя пользователям легко добавлять или удалять данные.

PDO расшифровывается как PHP Data Object, или объект данных PHP. В отличие от MySQLi, PDO является только объектно-ориентированным методом. Он поддерживает ряд различных типов баз данных, использующих PHP, таких как MySQL, MSSQL, Informix и PostgreSQL.

Исходные функции mysql_ устарели. Их лучше не использовать, поскольку они небезопасны и больше не поддерживаются.

Одна из наиболее важных функций, которую поддерживают оба метода — это подготовленные выражения (prepared statements). Она сокращает время, необходимое MySQL для выполнения повторяемого запроса. Эта функция также используется для предотвращения SQL-инъекций при внесении изменений в базу данных.

Какой бы метод вы ни использовали, вам понадобится правильная информация для подключения к созданной вами базе данных MySQL. Здесь вам пригодятся ранее сохранённые данные БД.

Вам также потребуется правильное имя сервера, или имя хоста для конфигурации. Hostinger использует “localhost” в качестве имени хоста своего сервера MySQL. Это имя, которое вы будете использовать, если загрузите свой PHP-скрипт на тот же сервер, что и база данных.

С другой стороны, если вы подключаетесь к базе данных из удалённого места (например, со своего компьютера), вам придётся использовать IP-адрес MySQL-сервера. Чтобы получить дополнительную информацию, обратитесь к своему хостинг-провайдеру. Он предоставит вам актуальную информацию о том, какое имя использовать в качестве имени хоста.

PHP-подключение к БД MySQL с MySQLi

Выполните следующие действия, чтобы подключить PHP-скрипт к MySQL посредством MySQLi:

1. Перейдите в Файловый менеджер ->public_html.
2. Создайте новый файл, щёлкнув на соответствующую иконку в верхнем меню.
3. Сохраните его как databaseconnect.php. Вы можете заменить имя на любое другое, просто убедитесь, что в качестве расширения используется php.
4. Дважды щёлкните по файлу, чтобы открыть его. Скопируйте и вставьте в него следующие строки кода. Замените первые четыре значения после учётными данными, которые вы указали ранее.

 echo "Connected successfully"; mysqli_close($conn); ?>

 catch (PDOException $pe) < die("Could not connect to the database $dbname :" . $pe->getMessage()); >

Warning: mysqli::__construct(): (HY000/2002): Connection timed out in /storage/ssd1/482/8221482/public_html/index.php on line 12 Ошибка: Connection timed out 

telnet 94.130.120.65 3306