CODEWITHSUNDEEP
phpmysqlselectecho
Adrian
Adrian

Reputation: 283

SELECT data from one table in each database on server

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.

Upvotes: 0

Views: 95

Answers (2)

Nick
Nick

Reputation: 147206

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'];
    }
}

Upvotes: 0

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21681

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.

Upvotes: 1

Related Questions