PHP/SQL - Multiple Connections?

Question

Using PHP to connect to MySQL database.

I was wondering:
If I want to execute 2 or more separate SQL Select queries (not concurrently, but one after another), can I use the same mysqli_connect "object(?)" or should I create multiple connections?

e.g.:

$con = mysqli_connect(blahblahblah);
$con2 = mysqli_connect(blahblahblah2);

Not sure if it matters or not, but I would most likely be connecting to the same schema. If I was not, I assume I could just use databaseschemaname.tablename in my SQL?

Questions

Can I use the same $con = mysqli_connect or should I create multiple connections?
When is it proper to use multiple $con = mysqli_connect?
What are the consequences of not "free"ing up the query before executing the next SQL?

Mike Brant · Accepted Answer · 2019-02-24 02:40:50Z

3

As long as you don't need different characteristics on the second connection (i.e. login, connection encoding, etc.) you would likely want to reuse the same connection handle.

edited Feb 24, 2019 at 2:40

answered Jun 6, 2014 at 22:56

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

agent provocateur Over a year ago

do i need to free the result set before doing my next query?

Community · Accepted Answer · 2023-11-17 20:42:18Z

Can I use the same $con = mysqli_connect or should I create multiple connections?

Yes, usually you would just reuse it if it's to the same DB, same schema, same connection parameters.

When is it proper to use multiple $con = mysqli_connect?

When you have to connect to different databases, or when you need to connect using different parameters. Other than that, practically never. In the event that you need to run multiple queries, you could actually do this with one connection and mysqli::multi_query. See http://www.php.net/manual/en/mysqli.multi-query.php

What are the consequences of not "free"ing up the query before executing the next SQL?

It's a best practice to close your result as soon as you no longer need it. That said, I know people don't close results or connections explicitly, but rather they let the connection get reaped when the PHP page finishes processing automatically. This can burn you pretty bad in Java when pooling connections. Not so bad in PHP, especially when not pooling. https://www.php.net//manual/en/mysqli.query.php

How about do an experiment. Don't do this on a production database, but find a Dev/sandbox database and write some PHP page that opens 200+ connections without closing them in between. Watch what happens on your PHP (i.e. Apache) server, and also watch what happens on your DB server when this happens. Repeat with explicit closing between each connection opening.

If I was not, I assume I could just use databaseschemaname.tablename in my SQL?

Yes, as long as the db user you are connecting with has privileges on the other database or table, you can do this.

Collectives™ on Stack Overflow

PHP/SQL - Multiple Connections?

Questions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Questions

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related