Php-mysql connection

[Network_ninja]

hi everyone I have a question... does creating a connection to mysql takes time depeding on the database size?

Lets say that in my entire project I will be connection to 4 databases so I created a config.php which looks like this:

 

$hostname = "localhost";
$username = "root";
$pword = "";

$con1 = @mysql_connect($hostname,$username,$pword,true);
@mysql_selectdb("databasename1",$con1);

$con2 = @mysql_connect($hostname,$username,$pword,true);
@mysql_selectdb("databasename2",$con2);

$con3 = @mysql_connect($hostname,$username,$pword,true);
@mysql_selectdb("databasename3",$con3);

$con4 = @mysql_connect($hostname,$username,$pword,true);
@mysql_selectdb("databasename4",$con4);

 

so, this file is included in every pages, I put this on the top. Basically every page request it will open 4 connection and the script will only use 2 connection and another page will only 1 and so on.....

 

this style is very convenient as for I am not creating a connection in every page.. But my concern is will it effect the performance of my system?

 

Tnx in advance.....

[Adam]

You only need a single connection to the same server. You can switch ('use') different databases multple times, and also if you have the required permisssions query another database from another.

[Network_ninja]

Pardon me... Is it ok for you if you can give me some example on how you do it? tnx

[WebStyles]

why not create a function to handle the connections?

function conn($database){
$server = '127.0.0.1';
$u = 'root';
$p = '';
$c = @mysql_connect($server,$u,$p);
@mysql_select_db($database,$c);
return $c;
}

 

call it with:

$conn = conn('databasename');

use it as resource:

$query = mysql_query("select * from whatever", $conn);

close connection:

@mysql_close($conn);

 

keeps things nice and tidy.

[Network_ninja]

So in your example say that I will be needing to connect to 3 database in a script i will have to write something like this?

 

$conn = conn('databasename1');
$conn2 = conn('databasename2');
$conn3 = conn('databasename3');

@mysql_close($conn);
@mysql_close($conn2);
@mysql_close($conn3);

[WebStyles]

So in your example say that I will be needing to connect to 3 database in a script i will have to write something like this?

 

yep.

[Network_ninja]

tnx a lot bro....

[Adam]

As I said, that's completely unnecessary. You're wasting resources by opening more than one connection. You can query one database while using another like this:

 

SELECT * FROM database_name.table_name

 

That's assuming you have the required permissions to talk to another database, and if you're on a shared host that might not be the case.  If not you can always just call mysql_select_db multiple times for the same connection. If you are on a shared host by the way, it's very likely you have a low limit on the number of simultaneous connections. Opening 3 is going to third the number of users you can have at any one time.

[Network_ninja]

Anyway, you have a good point there...... And I just realize that you can always add alias to your queries to make it short, like this:

 

SELECT * FROM database_name.table_name AS a

 

what do you mean calling the mysql_select_db() multiple times?

 

Do you mean this?

$hostname = "localhost";
$username = "root";
$pword = "";

$conn = @mysql_connect($hostname,$username,$pword,true);
@mysql_selectdb("databasename1",$conn);

$query = @mysql_query("SELECT * FROM tablename ",$conn); 

@mysql_selectdb("databasename2",$conn);
$query = @mysql_query("SELECT * FROM tablename ",$conn); 

 

[jcbones]

Yes, but PLEASE remove the error suppression.  (just a pet peeve of mine).

[Adam]

Yes. You can also use mysql_db_query. There's a number of different ways of doing it, just don't create multiple connections.

 

jcbones makes a good point also. Supressing the errors is like turning a blind eye to a leaky pipe. Learn about error reporting and displaying errors. On a production system you should still allow more serious errors to be reported (added to the log), but not displayed in the browser for users to see.

[Network_ninja]

tnx for all your help guys...

[Network_ninja]

by the way guys... there are a lots of error_reporting.. what do you usually use?

[Adam]

Development:

 

error_reporting(-1);
ini_set('display_errors', 1);

Production:

 

error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('display_errors', 0);

[skwap]

Development:

 

error_reporting(-1);
ini_set('display_errors', 1);

Production:

 

error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set('display_errors', 0);

 

can you just explain each error reporting for a knowledge purpose ?

[Adam]

Sure.

 

error_reporting(-1);

 

This is basically setting the type of errors PHP will complain about; FATAL, WARNING, NOTICE, etc. The type is included in every message you receive. -1 sets it to all, which unlike E_ALL includes STRICT types. You can read a short description of what each type means in the manual.

 

error_reporting(E_ERROR | E_WARNING | E_PARSE);

 

As this is production, we don't want to fill the logs with uneccesary information about undefined indexes and such, that *should* have all been solved during development; so we only set the three more substantial type of errors. The pipe characters between each constant are bitwise operators - these are fairly complex to explain within a forum post, so if you want to learn more have a read in the manual.

 

ini_set('display_errors', 1);

ini_set('display_errors', 0);

 

ini_set overrides a setting within your php.ini file for the current request. "display_errors" decides whether to display errors within the output. If 0, they're obviously not, but (by default) they will still be logged within the error log. That means within a production environment you can hide errors from users, but still have a historical log of errors to detemine what happened.