MySQLi - Set DB connection based on Session

[will35010]

I am working on an application and I wanted it to be a single app with a database for each client. What is the best way to do this? I've tried setting the DB connection using a variable, but it doesn't work.

 

Example:

<?php
session_start();

$_SESSION['clientid'] = "clientA";

$db = $_SESSION['clientid'];

$clientA = @mysqli_connect('localhost', 'fake_user', 'my_password', 'db_clientA');

$clientB = @mysqli_connect('localhost', 'fake_user', 'my_password', 'db_clientB');

$sql = mysqli_query($db, "SELECT * FROM test;";
?>

[HDFilmMaker2112]

I am working on an application and I wanted it to be a single app with a database for each client. What is the best way to do this? I've tried setting the DB connection using a variable, but it doesn't work.

 

Example:

<?php
session_start();

$_SESSION['clientid'] = "clientA";

$db = $_SESSION['clientid'];

$clientA = @mysqli_connect('localhost', 'fake_user', 'my_password', 'db_clientA');

$clientB = @mysqli_connect('localhost', 'fake_user', 'my_password', 'db_clientB');

$sql = mysqli_query($db, "SELECT * FROM test;";
?>

 

 

 

<?php
session_start();

$_SESSION['clientid'] = "clientA";

$clientid = $_SESSION['clientid'];

$db_name="db_".$clientid; //Just my coding style, you could also do $db_name="db_$clientid"; If you're okay with a hybrid variable and string inside the same quotes. 

$db = mysqli_connect('localhost', 'fake_user', 'my_password', $db_name);

$sql = mysqli_query($db, "SELECT * FROM test");
?>

 

If I'm not mistaken, they way you had it you would open two MySQL connections, but you'd only ever be using one of them. Major waste of server resources; and if you add more clients, it would just be a nightmare, and possibly a MySQL server crash, due to overload.

[will35010]

I really don't want to put the connect function on each page so I usually create a db.php and just include the file. It there a way to do it with the connection variable on mysqli instead.

 

For example, client A:

include('db.php');

mysqli_query($clientA, "SELECT * FROM test;");

ClientB

include('db.php');

mysqli_query($clientB, "SELECT * FROM test;");

 

Is there a way to set the $conn name based on session data?

[will35010]

I am working on an application and I wanted it to be a single app with a database for each client. What is the best way to do this? I've tried setting the DB connection using a variable, but it doesn't work.

 

Example:

<?php
session_start();

$_SESSION['clientid'] = "clientA";

$db = $_SESSION['clientid'];

$clientA = @mysqli_connect('localhost', 'fake_user', 'my_password', 'db_clientA');

$clientB = @mysqli_connect('localhost', 'fake_user', 'my_password', 'db_clientB');

$sql = mysqli_query($db, "SELECT * FROM test;";
?>

 

 

 

<?php
session_start();

$_SESSION['clientid'] = "clientA";

$clientid = $_SESSION['clientid'];

$db_name="db_".$clientid; //Just my coding style, you could also do $db_name="db_$clientid"; If you're okay with a hybrid variable and string inside the same quotes. 

$db = mysqli_connect('localhost', 'fake_user', 'my_password', $db_name);

$sql = mysqli_query($db, "SELECT * FROM test");
?>

 

If I'm not mistaken, they way you had it you would open two MySQL connections, but you'd only ever be using one of them. Major waste of server resources; and if you add more clients, it would just be a nightmare, and possibly a MySQL server crash, due to overload.

 

Thank you for your reply. I always put my db info in a file and include it where needed and close the connection after use. I don't see how to make your solution work in the way I need. The site will be too big to have the db connect info on each page and would be a nightmare to change when the db needs to be moved.

[HDFilmMaker2112]

I really don't want to put the connect function on each page so I usually create a db.php and just include the file. It there a way to do it with the connection variable on mysqli instead.

 

For example, client A:

include('db.php');

mysqli_query($clientA, "SELECT * FROM test;");

ClientB

include('db.php');

mysqli_query($clientB, "SELECT * FROM test;");

 

Is there a way to set the $conn name based on session data?

 

??

 

 

You can just do this:

 

db.php

<?php
session_start();

$clientid = $_SESSION['clientid']; //Get set session with index of "clientid". Ever where this is included it will run and look for the value of the session "clientid".

$db_name="db_".$clientid; //Generate db name from current client, via the clientid variable set from the clientid session

$db = mysqli_connect('localhost', 'fake_user', 'my_password', $db_name); // connect to DB using generated db name.
?>

 

<?php
include 'db.php';
$sql = mysqli_query($db, "SELECT * FROM test");
?>

 

When you include a file you're essentially including the raw contents of it, in the exact position where it's called.

 

If you were able to see the pure code of the combined file:

 

This:

<?php
include 'db.php';
$sql = mysqli_query($db, "SELECT * FROM test");
?>

 

Would actually look like this:

 

<?php
session_start();

$clientid = $_SESSION['clientid']; //Get set session with index of "clientid". Ever where this is included it will run and look for the value of the session "clientid".

$db_name="db_".$clientid; //Generate db name from current client, via the clientid variable set from the clientid session

$db = mysqli_connect('localhost', 'fake_user', 'my_password', $db_name); // connect to DB using generated db name.
?>

<?php
$sql = mysqli_query($db, "SELECT * FROM test;";
?>

[xyph]

Is there any reason you need a single database for each user? Unless you're dealing with huge amounts of data, it will be 'faster' and much less complicated to use one.

 

You can always query databases other than the one selected, assuming the MySQL user has privs to do it.

 

SELECT * FROM db.table

[will35010]

This really isn't db problem but I see you moved the thread. This problems relates to setting the db name using a session variable.

 

I'll just use one db and use clientID's to separate the data since this doesn't seem to be easily accomplished. I know I can set a clientID for a query since I've done that in the past.

 

Thanks for your help.

[xyph]

Yeah, but overall, the MySQL guys will probably have something to say about the design. They will know any downsides and potential problems you might run into in the future by using a separate DB for each client. They also might be able to provide details about using a different user for each client's database, and the added security that might bring (in the event of a successful injection attack).

 

You've already got the solution for your problem - using a variable for your database name. I'm not sure why it wasn't initially working for you, and neither are you because you've suppressed any errors that mysqli_connect() might try to throw. The issue is in your programming though, as setting the database using a variable does work.