[RESOLVED] More efficient way to change user connection

[Nightwalker83]

Hi,

What is a more efficient way to change user access to a database? At the moment I use:

//connect to server or exit
$conn = @mysql_connect("localhost", "root", "");
if (!$conn) {
die("Connection failed: " .mysql_error());
} 

and just replace "root" with whatever the new user account is called. However, this means I have a lot of connections to my databases. What would be the best way to achieve this?

[kfcSmitty]

I'm not sure I know what you mean... From what I've gotten out of this, why don't you just use a variable?

[kows]

why, exactly, do you need to have multiple users on the same database? that's incredibly inefficient if you don't have a real reason to.

and, you can re-use connections by using mysql_pconnect() to create a persistent connection for each session.

[SambaNeko]

Ditto to both prior posters - ideally don't use multiple user connections.

If what you mean is that you have mysql_connect() all over your files and suddenly you need to change the connection info for all of them, I'd suggest either...

A) Making a single function in a globally included file:

function myConnectFunction(){
  $conn = @mysql_connect("localhost", "root", "");
  if (!$conn) {die("Connection failed: " .mysql_error());}
  return $conn;
}

Now use that function instead of mysql_connect():

$conn = myConnectFunction();

And if you need to change the connection info, there's only one spot to do it in.

Or B) You could define some constants inside a globally included file:

define("MYSQL_HOST","localhost");
define("MYSQL_USER","root");
define("MYSQL_PASS","");

And use those whenever you're connecting:

$conn = @mysql_connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASS);

[Nightwalker83]

I'm not sure I know what you mean... From what I've gotten out of this, why don't you just use a variable?

why, exactly, do you need to have multiple users on the same database? that's incredibly inefficient if you don't have a real reason to.

and, you can re-use connections by using mysql_pconnect() to create a persistent connection for each session.

With two websites I created for school, the databases use multiple functions. The "root" is only required to setup the other accounts and the admin and users are accounts used by the users of the website.

Ditto to both prior posters - ideally don't use multiple user connections.

If what you mean is that you have mysql_connect() all over your files and suddenly you need to change the connection info for all of them, I'd suggest either...

A) Making a single function in a globally included file:

function myConnectFunction(){
  $conn = @mysql_connect("localhost", "root", "");
  if (!$conn) {die("Connection failed: " .mysql_error());}
  return $conn;
}

Now use that function instead of mysql_connect():

$conn = myConnectFunction();

And if you need to change the connection info, there's only one spot to do it in.

Or B) You could define some constants inside a globally included file:

define("MYSQL_HOST","localhost");
define("MYSQL_USER","root");
define("MYSQL_PASS","");

And use those whenever you're connecting:

$conn = @mysql_connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASS);

Thanks! I will try that.

Edit: What is the difference between mysql_connect() and myConnectFunction()?

[kows]

myConnectFunction is a user created function (created within his post) that calls and returns a MySQL connection. this is no different from calling mysql_connect() on its own. this would just allow you to keep the same connection details within a function that you could use in every one of your scripts. still, I would personally just have an include file that connects to the database, and then you can include it into whatever pages do need database connectivity. both would accomplish the same thing.

but -- neither really accomplish what you're trying to do. you want to connect to your database server using multiple users, and each user you connect with requires a new connection. there is no way to avoid this except to ... not do it. or close connections when you don't use them, as you are opening new ones, assuming that that's possible in your situation.

still, I would need a better understanding of what you're doing to gauge whether or not you should even be doing what you're doing. if this is a requirement in your class, that may be a little different. but it sounds silly to me. why can't your regular database user account insert into the "users" table? .. if it can do everything else, there would be no real reason to make you use your root account just to create users. and, in general, I don't think anyone really does this as far as web applications go. I could be wrong, and I'm interested in hearing what penagate might have to say about it.

[Nightwalker83]

I have moved the connection string for the database into its own files and it does what I want (on localhost at least). I doubt I will be able run my script on the server because it actually creates the admin and user accounts via code instead of manually putting them in to the database.

On localhost this required adding new fields to the users table of the mysql database. I doubt I'd be allowed to do that on their server particularly a free one.

[kows]

it just sounds like you're over complicating whatever you're doing, or something. if you'd like some help re-designing something, you can ask -- otherwise, I'm not sure how to respond from here on out.

[Nightwalker83]

it just sounds like you're over complicating whatever you're doing, or something. if you'd like some help re-designing something, you can ask -- otherwise, I'm not sure how to respond from here on out.

I was just experimenting with those two projects! I was seeing whether creating the entire database via code would be less effort rather than creating the database manually each time. This is the code I created to do the job. However, whether that script would work in real world situations is a different matter which I have now run into.

[kows]

uhh, on any production servers most likely you're never going to have a root MySQL account to create users with. you usually create your user, then your database, then associate them both, then create your tables. you don't need more than one user, and you would pretty much never want to or need to create a MySQL user via PHP.

[Nightwalker83]

Ah well, I guess I'm going to have to set the database up manually which in this case I guess I could manage but bigger databases would be a hassle.

[SambaNeko]

but bigger databases would be a hassle.

Why?

[Nightwalker83]

Why?

Never mind! I was thinking that I would have to create the fields manually.