how to make a query with database as a variable

Question

I have two databases - lorem and nts.lorem - and need to operate with both of them

$user = 'root';
$pass = '';
$db1 = new PDO('mysql:host=localhost; dbname=nts.lorem', $user, $pass);
$db2 = new PDO('mysql:host=localhost; dbname=lorem', $user, $pass);

everything works fine until db is a variable in an ajax request - for example:

js

var db;
if(something is true){db = 'db1';};
else{db = 'db2';}
//... ajax post code

php

function something($db){
    global $db1, $db2;
    // how to say the next line  
    $sq = "select id from " . $db . ".tableName order by title asc";
    // error - table db1.tableName doesn't exist  
}

any help?

can you show the ajax code also, db is avariable and you can tranfer it like any other, so i don't understand where your problem is — nbk, Oct 05 '20 at 20:30
@nbk, ajax code is ok - echoing `db` on server side is ok. problem is given error. In both databases the table does exist — qadenza, Oct 05 '20 at 20:34
I do not unerstand this question either. Databasename.tablename syntax works in mysql. Perhaps you are passing a wrong database name there. You do not even need two database connections. — Shadow, Oct 05 '20 at 20:36
@Shadow, maybe because dbname is `nts.lorem` so in query it becomes `select from nts.lorem.tableName` - and it is not clear what is database and what is table — qadenza, Oct 05 '20 at 20:41
In that case you just need to add `` around the databse name — Shadow, Oct 05 '20 at 20:42
Like `"select id from \`" . $db . "\`.tableName order by title asc"` — Shadow, Oct 05 '20 at 20:46
it is claerly not in order when you have the false connction, i stuill don't get what you send via ajax — nbk, Oct 05 '20 at 20:46
Are we talking about https://stackoverflow.com/q/11321491/2943403 — mickmackusa, Oct 05 '20 at 20:53
@qadenza can you explain why you need to make 2 connections versus making just one connection and setting the targeted database conditionally? My code works until it doesn't = "Needs Debugging Details". — mickmackusa, Oct 05 '20 at 20:59
everything is downvoted here - the question and answers - multiple times - within a pair of seconds. — qadenza, Oct 05 '20 at 21:01
@qadenza your question was probably downvoted (that was not me) because it is unclear and you are not providing much extra information. I downvoted both answers because I don't think those are useful and even provided explanation as to why I did so. The solution the answers provided may work, but unless you do plan to deploy the two databases on two different servers / use different mysql users **and** you need to connect to both databases at the same time from the same script, there is no reason to use 2 db connections. That's just a waste of resources. But you could clarify this. — Shadow, Oct 05 '20 at 21:11
@Shadow, thanks for your efforts, I'm aware that it is a strange situation and possible waste of resources, but it's a long story why I have this scenario now. In short - I'm trying to follow my client's requirements to change a table on a main domain from a subdomain - each of them already have a separate database. — qadenza, Oct 05 '20 at 21:54
@qadenza This still does not explain why you have two database connections. See my comment above for those limited use cases when you truly need two connections. Otherwise enclosing the db name by `` will suffice or even configuring the database connection at the beginning of the script to the right database will do. — Shadow, Oct 05 '20 at 22:20

u_mulder · Accepted Answer · 2020-10-05T20:40:12.720

0

Choose connection according to $db value:

function something($db){
    global $db1, $db2;
    $sq = "select id from tableName order by title asc";
    if ($db === 'db1') {
        $db1->execute($sq);
    } else {
        $db2->execute($sq);
    }    
    // rest of the code  
}

edited Oct 05 '20 at 20:40

answered Oct 05 '20 at 20:36

u_mulder

54,101
5
48
64

Database.tablename syntax works in mysql and there is no need to have two database connections, unless you connect to different mysql instances or want to use different mysql users. None of these conditions are true here. – Shadow Oct 05 '20 at 20:40
If database names are `lorem` and something else then definitely OP should not use `db1/db2` values in js. – u_mulder Oct 05 '20 at 20:41
@TravelingTechGuy yes, I downvoted both answers because both repeat the same mistake: using two database connections when one would suffice. – Shadow Oct 05 '20 at 20:48
And @TravelingTechGuy explained why two connections can be used. – u_mulder Oct 05 '20 at 20:49
And I explained why one would suffice. – Shadow Oct 05 '20 at 20:50

score 0 · Answer 2 · answered Oct 05 '20 at 20:37

0

Add the line that executes the query to your code sample. Without it, it's hard to be sure what's wrong, but I can guess: you don't need the name of the database in the query text, you need to execute the query with the proper database connection, based on the parmeter received from the client.

Something like:

function something($db){
    global $db1, $db2;
    $sq = "select id from tableName order by title asc";
    $stmt = $db === 'db1' ? $db1->query($sq) : $db2->query($sq);
    $result = $stmt->fetch();
}

Comment: this assumes you have a table called tableName in both databases.

answered Oct 05 '20 at 20:37

Traveling Tech Guy

27,194
23
111
159

Database.tablename syntax works in mysql and there is no need to have two database connections, unless you connect to different mysql instances or want to use different mysql users. None of these conditions are true here. – Shadow Oct 05 '20 at 20:40
I don't know that I support the use of the variable `$stmt` when you are not using a prepared statement. This is likely to misinform researchers. – mickmackusa Oct 05 '20 at 20:42
@mickmackusa literally took it from PDO documentation: https://phpdelusions.net/pdo_examples/select – Traveling Tech Guy Oct 05 '20 at 20:43
The two databases are located on the same mysql instance and connected by using the same mysql username and password. There is no logical reason for using two database connections for it, it is just a waste of resources. – Shadow Oct 05 '20 at 20:44
That is not the PHP manual, that is @YourCommonSense's website. https://www.php.net/manual/en/pdo.query.php#:~:text=A%20nice,statement – mickmackusa Oct 05 '20 at 20:45
1

OP went the length of defining 2 DB connections. They may currently be located on the same server, but may be deployed differently later. Regardless, he asked for a solution using both connection objects he defined. These answer his question. You can definitely explain to him that he can use the same connection object for 2 dbs installed on the same server - but that's besides the point. – Traveling Tech Guy Oct 05 '20 at 20:46
I don't see any part in the question asking for a solution with two db connections. The fact that the OP uses two connections is more likely a result of the OP could not make the single connection code work. – Shadow Oct 05 '20 at 20:55

how to make a query with database as a variable

2 Answers2