0

I wanted to get a list of all databases on a specific account on my server using PHP so I have used the following PHP/MySQL taken from this link: PHP - Get list of databases names

$db_conn = mysqli_connect('localhost', 'username', 'password');
//get a list of databases in the account
$result = mysqli_query($db_conn,"SHOW DATABASES"); 
//display results
while ($row = mysqli_fetch_array($result)) { 
    echo "database name - ".$row[0]."<br>";
}

This part works fine. But I now want to get the value of one specific row within a table in each of these databases, all databases contain exactly the same tables and therefore the same rows.

So inside the while { } I tried to put a SELECT statement:

$sql = "SELECT option_value FROM sweb_options WHERE option_name = 'siteurl'";

and then echoed $sql but this didn't do anything except output the statement. Any ideas how I can do this please?

Many thanks.

Adrian
  • 285
  • 2
  • 14

2 Answers2

1

As I said in the comments

Well first of all you need FROM {database}.sweb_options

while ($row = mysqli_fetch_array($result)) { 
    $sql = "SELECT option_value FROM `{$row[0]}`.`sweb_options` WHERE option_name = 'siteurl'";
}

Otherwise it will use whatever database you connected to with this instance of mysqli

So basically that just tells it which one you want. You can also do Joins and Unions across multiple DB in MySQL this is not true of all Databases though. Something to keep in mind more if you were using PDO instead of mysqli, but still worth mentioning.

Cheers.

ArtisticPhoenix
  • 21,464
  • 2
  • 24
  • 38
0

Something like this should work:

while ($row = mysqli_fetch_array($result)) { 
    $sql = "SELECT option_value FROM {$row[0]}.sweb_options WHERE option_name = 'siteurl'";
    $result2 = mysqli_query($db_conn, $sql);
    // check for success in case table doesn't have option_value column
    if ($result2) {
        $row2 = mysqli_fetch_assoc($result2);
        echo $row2['option_value'];
    }
}
Nick
  • 138,499
  • 22
  • 57
  • 95
  • I got message: Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in FILENAME on line 12 which is `$row2 = mysqli_fetch_assoc($result2);` but then the new values echo beautifully afterwards :-) – Adrian Mar 20 '19 at 22:35
  • @Adrian the error message is happening because you have some tables in your database which don't have the `option_value` column in them. You need to add a check for that; see my edit. – Nick Mar 20 '19 at 22:52