mysql UNION across 2 databases where a table name is dynamic?

Question

I have a series of MySQL unions that look like the following:

$stmt=$db->prepare('SELECT * FROM social_posts WHERE username IN (
                    SELECT friend2 as username FROM list_friends 
                    WHERE (friend1 = :username AND friend2 <> :username) 
                        UNION
                    SELECT friend1 as username FROM list_friends
                    WHERE (friend2 = :username AND friend1 <> :username)
                   )');
$stmt->bindParam(':username', $username);
$stmt->execute();
$row = $stmt->fetchAll();

I need to apply a similar union to a dynamic table in a second database. The current SELECT statement I have for that database/table looks like the following.

if ($db2->query("SHOW TABLES LIKE 'elfinder_file_".strtolower(:username)."'"
           )->rowCount() > 0 ){
    $stmt=$db2->prepare("SELECT * FROM elfinder_file_".strtolower(:username)." 
                         WHERE mime <> 'directory' GROUP BY time");
    $stmt->execute();
    $row4 = $stmt->fetchAll();
}

The problem I am having is that the UNION in the first part, is selecting data based on the friends of $username and I have no idea how I can apply such a thing to the second part of the code.

How would I pass the friend of $username to the dynamic table strtolower(:username) for both the SHOW TABLES statement and the SELECT statement?

Notes: I cannot use a foreach loop to get the friends and then loop them through the second table. The reason for this is then the results of the other tables will not match the results of this table. I also cannot use one giant loop that gets the friend and loops them through each table, as then each instance of the loop, over-writes the last instance of the loop and I am echoing the results of the loop as an array outside the loop. The array would be different with each instance of the loop.

any logical reason for that belief? Its not like table names are something you need to keep private. If you did, most opensource software would be at a large disadvantage — Bruce, Sep 14 '16 at 22:28
I don't see where you're accessing a different database name. The syntax for accesing a table in a database is `databasename.tablename`, but you don't have a `.` delimiter. — Barmar, Sep 14 '16 at 22:31
@Bruce It's not a matter of privacy, it's because dynamic data should be in table cells, not table and column names. If you just had a single table with all the data, you could just write a simple `JOIN` to match the usernames. — Barmar, Sep 14 '16 at 22:35
i dont know what your doing exactly but if you want to run a query on all or multiple users, doing that on 1 table vs n * users tables, is n * users tables easier — , Sep 14 '16 at 22:35
@Barmar, logically - $stmt=$db is one database and if $db2->query is a second - $db and $db2 being the different dbases. — Bruce, Sep 14 '16 at 22:36
Ironically, the coder of the elfinder asked me to add my howto make elfinder work multi user to their wiki pages. https://github.com/Studio-42/elFinder/issues/1648 — Bruce, Sep 14 '16 at 22:38
@Bruce I thought you meant databases as in `CREATE DATABASE` in MySQL. — Barmar, Sep 14 '16 at 22:38
@Barmar Ah I could see the confusion, but no I have the dbases connected, just don't know how to do a union across databases, or how to otherwise achieve desired outcome. — Bruce, Sep 14 '16 at 22:40

score 1 · Accepted Answer · answered Sep 14 '16 at 22:47

1

You should be able to do it with a loop. I put all the results in a 2-dimensional array; the first dimension is the friend name, its value is the rows from the corresponding table.

$stmt = $db->prepare("SELECT friend2 as username FROM list_friends 
                WHERE (friend1 = :username AND friend2 <> :username) 
                    UNION
                SELECT friend1 as username FROM list_friends
                WHERE (friend2 = :username AND friend1 <> :username)";
$stmt->bindParam(':username', $username);
$stmt->execute();
$friend_rows = $stmt->fetchAll();
$all_results = array();
foreach ($friend_rows as $row) {
    $friend = strtolower($row['username']);
    if ($db2->query("SHOW TABLES LIKE 'elfinder_file_$friend'"
               )->rowCount() > 0 ){
        $stmt=$db2->prepare("SELECT * FROM elfinder_file_$friend 
                             WHERE mime <> 'directory' GROUP BY time");
        $stmt->execute();
        $all_results[$friend] = $stmt->fetchAll();
    }
}

BTW, using GROUP BY time with no aggregation functions is likely to produce unpredictable results. Each column in the results could be from a different row in the group.

Instead of a 2-dimensional array, you could concatenate all the results into a single array, which is the equivalent of doing a UNION in the database.

$all_results = array_merge($all_results, $stmt->fetchAll());

answered Sep 14 '16 at 22:47

Barmar

741,623
53
500
612

GROUP BY time DOES profuce unpredictable results, but when someone does a mass upload of 100 images, its better to have a group all, and display 1 image, than to display a loop of 100 images. :) – Bruce Sep 14 '16 at 22:51
This "$friend_rows = $stmt->fetchAll(); $all_results = array();" right here is the solution! THANK YOU SO MUCH. – Bruce Sep 14 '16 at 22:53
But what about all the other columns? They won't necessarily come from the same row as the image you select. – Barmar Sep 14 '16 at 22:55
I assumed you cared about other columns, since you used `SELECT *` instead of `SELECT image`. – Barmar Sep 14 '16 at 22:56
I generally create everything with *, then go back through and name each thing specifically before going live. * makes it easier to add and remove on the fly, but naming columns makes for better performance.I do admit grouping by time is not the best way to do things, frequently images are matched with wrong titles, multiple users having done things at the same EXACT time (I am not using microsecond times) gets confused.... I am just OBVIOUSLY new to coding - only been doing it ~1 year, so there is still TONS I don't know how to do. I make things work and hope to learn better practice as I go. – Bruce Sep 14 '16 at 23:02
@bruce please see [Why is SELECT * considered harmful?](http://stackoverflow.com/q/3639861) – Drew Sep 28 '16 at 23:40

score 0 · Answer 2 · answered Sep 14 '16 at 22:00

This is outright wrong:

$stmt=$db2->prepare("SELECT * FROM elfinder_file_".strtolower(:username)." 
                                                  ^^^^^^^^^^^^^^^^^^^^^^^

PHP doesn't know (or care) what an SQL placeholder is. That's a flat-out syntax error.

You'd have to use

$stmt = blahblah FROM elfinder_file>" . strtolower($var_with_username) . " etc...

As far as a dynamic table name, the DB has no idea where/how you generated a query string. It only sees the query string AFTER it's generated. e.g.

$var = 'sometable';
$sql = "SELECT foo FROM bar UNION ALL SELECT baz FROM $var";

will generate the exact same

SELECT foo FROM bar UNION ALL SELECT baz FROM sometable

that the DB receives, as if you'd typed in that query by hand.

And of course, on a meta level, note that placeholders can only represent VALUES. You cannot use placeholders for identifiers/keywords.

This problem is so incredibly hard to explain. How would I set $var? That is the problem. In the first statement, I am going to have the data from every friend of $username returned. I need the same in the second statement, every table belonging to friend of username returned in one single statement, not a loop that delivers each statement one at a time. — Bruce, Sep 14 '16 at 22:14

mysql UNION across 2 databases where a table name is dynamic?

2 Answers2