Bruce Bruce - 2 months ago 9
MySQL Question

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

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.

Answer

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());
Comments