Hudzilla.org - the homepage of Paul Hudson
Contents > Databases > Using MySQL with PHP Wish List | Report Bug | About Me ]

9.4.1     Connecting to a MySQL database: mysql_connect(), mysql_select_db()

This is NOT the latest copy of this book; click here for the latest version.

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

bool mysql_select_db ( [string database_name [, resource link_identifier]])

To connect to MySQL, there are two key functions: mysql_connect() and mysql_select_db(). Mysql_connect() usually takes three arguments - the IP address of a MySQL server to connect to, the username you wish to logon as, and the password for that username, and opens a database connection for your PHP script - it lets you execute commands and read results from any database which allows you access. Do not worry about disconnecting from your database - PHP automatically disconnects you when it finishes running your script. Note that examples in this book will always use the username "phpuser" and the password "alm65z" - do not use them in your own scripts, for security reasons!

All SQL queries you run in PHP, unless you specifically say otherwise (which we shall not be doing) will be executed on the most recent connection you open in your script. Each script needs to open its own database connection through which to execute its database queries, although, by using a persistent connection, they can be made to share connections

The first parameter to mysql_connect() can either by an IP address, or a hostname. Most operating systems also allow you to use "localhost" as the local computer and have MySQL connect directly through a local socket. Alternatively, you can specify 127.0.0.1, which is also the local computer, and have MySQL connect through TCP/IP, which is a little slower. To connect to a remote server, just enter either the hostname (e.g. www.microsoft.com) or the IP address (e.g. 212.113.192.101) as the first parameter, and your data will be transparently sent over the Internet.

Author's Note: In small scenarios, having the PHP server and MySQL server on the same machine makes the most sense, and allows for very high performance. However, in order to be more scalable you will need to have your database server separate from your PHP server, with the two connected via a high-speed network.

Once you have got a connection open, it is best to call mysql_select_db() soon after - it takes just one argument, which is the name of the database you wish to use. Once you select a database, all queries you run are on tables in that database until you select another database, so it is like the USE statement in MySQL. Similarly to mysql_connect(), examples in this book will always use the database "phpdb" - again, you should change this for your own purposes for security reasons.

Like mysql_connect(), you generally only use this function once. Once both are done, you have got a connection to your database and you have got a database selected - you are all set to perform queries.

<?php
    mysql_connect("localhost", "phpuser", "alm65z");
    mysql_select_db("phpdb");
 ?>

Author's Note: Once you are connected, you can use the function mysql_ping() to check whether the server is alive - it automatically uses the most recently opened database connection so you need not pass it any parameters, and it returns true if the server was contacted or false if the connection appears to be lost.

The last two parameters aren't used all that often, but are worth knowing about. Calling mysql_connect() for the first time will open a new connection to the MySQL server, but calling it again in the same script with the same arguments as the first call will just return the previous connection. If you specify parameter four as true (or 1 as is most common), PHP will always open a new connection each time you call mysql_connect().

The last parameter allows you to specify additional connection opens, of which only really useful one is MYSQL_CLIENT_COMPRESS, which tells the server that it may use data compression to save network transfer time - a smart move if your web server and database server are on different machines.





<< 9.4 Using MySQL with PHP   9.4.2 Querying and formatting: mysql_query() and mysql_num_rows() >>
Table of Contents
 Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.



My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
Top-right shadow
 
Bottom-left shadow Bottom shadow

Comments from other readers
Randal - 06 Sep 2008
Vorapoo,

I am very new (and still lack the ability to figure out much of the sql/php interaction. That said, there is a part of your code that looks incorrect based on my current level of understanding.

Down on line 16, there is a missing single quote symbol in front of the word (and presumably column value) ambulance.

Okay, I get it, maybe. you are surrounding the entire variable ($sql) definition in quotes, beginning with the word 'SELECT and continuing through SERVICES'

That may work, though my study seems to indicate the double use of the same type of quotes will cause the thing to get confused. In other words, you need to use one set of double quotes and one set of single quotes IF my reading is accurate.


Randal



vorapoo at yahoo.co.in - 09 Oct 2006

--------------------------------------------------------------------------------
i have some doubt
am not able to connect to my database
could u tell me where is my mistake?
here is my code: ambulance.php

<html>
<head>
<title>Ambulance</title>
</head>
<body>
<?php

$host = "localhost";
$user = "root";
$password = "";
$link = @mysql_connect( '127.0.0.1,$user,$password);
$database = mysql_select_db("master") or die("Unable to connect to database");

$sql = 'SELECT * FROM `emergency_services` WHERE type = Ambulance Services';

$result = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error());
mysql_free_result($result);
if($result)

A PHP User - 06 Sep 2008
@vorapoo
In the line "
$link = @mysql_connect( '127.0.0.1,$user,$password);", you are missing a quote after 127.0.0.1. And since you turned off error reporting with the @, it wouldn't tell you.

vorapoo at yahoo.co.in - 06 Sep 2008
i have some doubt
am not able to connect to my database
could u tell me where is my mistake?
here is my code: ambulance.php

<html>
<head>
<title>Ambulance</title>
</head>
<body>
<?php

$host = "localhost";
$user = "root";
$password = "";
$link = @mysql_connect( '127.0.0.1,$user,$password);
$database = mysql_select_db("master") or die("Unable to connect to database");

$sql = 'SELECT * FROM `emergency_services` WHERE type = Ambulance Services';

$result = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error());
mysql_free_result($result);
if($result)
{
echo "EMERGENCY<br><br>";
echo "<table width = 90% align = centre border = 1><tr>
<td align=centre bgcolor=#00FFFF>Name<td>
<td align=centre bgcolor=#00FFFF>Street<td>
<td align=centre bgcolor=#00FFFF>Phone<td>
<td align=centre bgcolor=#00FFFF>Town<td>
<td align=centre bgcolor=#00FFFF>zip<td>
</tr>";

while($s1 = mysql_fetch_array($result))
{
$name=$result["name"];
$street=$result["street"];
$phone=$result["phone"];
$town=$result["town"];
$zip=$result["zip"];

echo"<tr>
<td>$name</td>
<td>$street</td>
<td>$phone</td>
<td>$town</td>
<td>$zip</td>
}
echo <tr>";
mysql_close();
}
}
?>
</body>
</html>


---
i get a error as
Unable to connect to database

A PHP User - 06 Sep 2008
i have some doubt
am not able to connect to my database
could u tell me where is my mistake?
here is my code: ambulance.php

<html>
<head>
<title>Ambulance</title>
</head>
<body>
<?php

$host = "localhost";
$user = "root";
$password = "";
$link = @mysql_connect( '127.0.0.1,$user,$password);
$database = mysql_select_db("master") or die("Unable to connect to database");

$sql = 'SELECT * FROM `emergency_services` WHERE type = Ambulance Services';

$result = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error());
mysql_free_result($result);
if($result)
{
echo "EMERGENCY<br><br>";
echo "<table width = 90% align = centre border = 1><tr>
<td align=centre bgcolor=#00FFFF>Name<td>
<td align=centre bgcolor=#00FFFF>Street<td>
<td align=centre bgcolor=#00FFFF>Phone<td>
<td align=centre bgcolor=#00FFFF>Town<td>
<td align=centre bgcolor=#00FFFF>zip<td>
</tr>";

while($s1 = mysql_fetch_array($result))
{
$name=$result["name"];
$street=$result["street"];
$phone=$result["phone"];
$town=$result["town"];
$zip=$result["zip"];

echo"<tr>
<td>$name</td>
<td>$street</td>
<td>$phone</td>
<td>$town</td>
<td>$zip</td>
}
echo <tr>";
mysql_close();
}
}
?>
</body>
</html>


---
i get a error as
Unable to connect to database

chivalry@mac.com - 06 Sep 2008
I have the following script in a file called php.php my home folder:

#!/usr/bin/php
<?php
mysql_connect("localhost", "root", "password");
?>

When I run this script, I get the following:

Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in /Users/chuck/php.php on line 3

Using the same parameters from the command line is successful:

mysql -u root -p

That prompts me for my password, which I then enter, and am taken to the mysql> prompt and can successfully see databases, enter "USE phpdb;" and see the tabes within that database.

I'm using MySQL 4.1.13-standard, PHP 4.3.11 on Mac OS X 10.4.2. Any help would be appreciated.

Thanks,
Chuck

cww58@msn.com - 06 Sep 2008
First, this is by far the best PHP reference I've come across. The question I have is this. You say PHP closes the database at the end of the script. What if I'm just inserting a PHP snippet in a HTML page. Will it close the database at the of the snippet or when the user breaks connection??

kevin AT ywambrussels dot be - 06 Sep 2008
Just spent several hours with a similar problem to my Indian friend. I kept getting the user denied issue. It all tied down to connecting to the database from a function and then trying to make queries from another function.

I think php throws away the connection link when you move out of scope (like you kind of expect it would). I guess keep in scope or use a global variable? Can you do this?

Great book - I'm making fast progress with php!

Thanks Paul

singpolyma AT gmail.com - 06 Sep 2008
Well, as to your query, Banerjee, I have no clue... I run all my mysql stuff off of remote servers and have never tried doing it from a local machine. Sorry.

Two quibbles I have here in the text are that it is said that you don't need to close your database connections. This is true, however it is sloppy programming style. You should end your script with a call to mysql_close().

One other thing is that you say that you don't need to specify a connection and PHP just uses the last opened connection. This is also true but it should be noted that you can (and probably should) store the connection as a resource variable and specify it manually as this will help you later on if you start working with multiple databases in one script.

learner.linux@gmail.com - 06 Sep 2008
I am new both to php and mysql. I have found this as a good tutorial but faced a problem which is as follows.

First Case :

If I use the default user (NULL) and passowrd (NULL) to communicate I can able to communicate via 127.0.0.1 (my localhost).

$link = mysql_connect('127.0.0.1','','')

I am being passed and I can access to the databases those come with the mysql package (but not to those I have created of my own).

Second Case :

If I use the username and password which I use to connect mysql server from shell, I am getting an error message.

Access denied for user: 'user_name@localhost.localdomain' (Using password: YES). In this case, I am giving the command as follows :

$link = mysql_connect('127.0.0.1','user_name','password')

I use :

Fedora Core 3 and installed the php and mysql that comes with its CDs as package. There is apache and mysqld is running on my machine.

Please help to proceed further.

Regards
Anindya Banerjee
from
India



Add comment
Please note that by posting a comment here you are committing it to the public domain. This is important so that others can make use of your code themselves, and also so that I can incorporate helpful notes directly into the main text. Comments are limited to 2000 characters in length.

If you are reporting an error in the content, please tell me directly.

Your name/email address:
Your comment:
 
Now, in order to verify that you're a real person, please answer this simple question: what is four plus eight?
The answer is:
(please write in
numbers, eg 19)


Top-right shadow
 
Bottom-left shadow Bottom shadow

This website and its content is copyright © Paul Hudson 2005, all rights reserved.