user1763897
Reputation: 51
how to loop through multiple MySQL databases in a PHP script
I have inherited the below code, which creates a web interface for a MySQL database named 'database_name' and defined by the variable $database.
I want to add an additional database to this script, so that the same interface can be created this second database (which is set up in exactly the same way as the first database with the same tables names, etc., but contains different data). Is there a way to make $database a string array which I can loop over for multiple database names?
<?php
class DatabaseInterface {
public $host = "localhost"; //(name or ip address)
public $userName = "aksfhah";
public $passWord = "**********";
public $database = 'database_name';
public $tableData = "measurements";
public $tableMinbins = "minbins";
public $tableHourbins = "hourbins";
public $tableDaybins = "daybins";
public $tableDescriptions = "measurementDescriptions";
public $tableSpecs = "experimentDescriptions";
public $connection;
public function __construct($databaseName = "database_name") {
$this->database = $databaseName;
$this->connect();
}
public function connect($databaseName = null) {
$dbh = mysql_connect($this->host, $this->userName, $this->passWord);
if (is_null($databaseName)) {
mysql_select_db($this->database);
} else {
mysql_select_db($databaseName);
}
$this->connection = $dbh;
return $dbh;
}
public function initializeDatabase() {
$this->createDataTable($this->tableData);
$query = "create table if not exists $this->tableDescriptions (id int auto_increment primary key,type varchar(255),
description varchar(255), experimentname varchar(255), unique Key(description,experimentname)) engine=myisam";
mysql_query($query); //create a table if it does not exist
echo mysql_error(); //report error if one occurred
}
private function createDataTable($tableName) {
$query = "CREATE TABLE IF NOT EXISTS $tableName (
id smallint(5) unsigned NOT NULL,
time datetime NOT NULL,
measurement float DEFAULT NULL,
rebinned tinyint(4) DEFAULT '0',
PRIMARY KEY (id,time),
KEY (rebinned,id)
) ENGINE=MyISAM";
mysql_query($query); //create a table if it does not exist
echo mysql_error(); //report error if one occurred
}
public function insertByDescription($time, $value, $channelDescription, $experimentDescription, $type = "other") {
$sensorID = $this->createIdFromDescription($channelDescription, $experimentDescription, $type);
return $this->insertById($time, $value, $sensorID);
}
public function insertMultipleById($timeArray, $valueArray, $sensorID,$tableName=null) {
if(is_null($tableName)){
$tableName= $this->tableData;
}
if (count($timeArray) == 0)
return 0;
$query = "insert ignore into $tableName (id,time,measurement)
VALUES ";
for ($index = 0; $index < count($timeArray); $index++) {
$timeString = $timeArray[$index]->format("Y-m-d H:i:s");
$value = $valueArray[$index];
$query = $query . "('$sensorID','$timeString','$value'),";
}
$query = substr($query, 0, strlen($query) - 1); //trim final comma
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error();
return false;
} else
return mysql_affected_rows();
}
public function insertMultipleByDescription($timeArray, $valueArray, $channelDescription, $experimentDescription, $type = "other",$tableName=null) {
$sensorID = $this->createIdFromDescription($channelDescription, $experimentDescription, $type);
return $this->insertMultipleById($timeArray, $valueArray, $sensorID,$tableName);
}
public function insertById(DateTime $time, $value, $sensorID) {
$timeString = $time->format("Y-m-d H:i:s");
$query = "insert ignore into $this->tableData (id,time,measurement)
VALUES ('$sensorID','$timeString','$value')";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error();
return false;
} else if (mysql_affected_rows() == 0) {
return false;
} else {
return true;
}
}
public function createIdFromDescription($channelDescription, $experimentDescription, $type) {
$sensorId = $this->getIdFromDescription($channelDescription, $experimentDescription);
if (!$sensorId) {
$query = "insert ignore into $this->tableDescriptions (description,experimentname,type)
VALUES ('$channelDescription','$experimentDescription','$type')";
$result = mysql_query($query);
$sensorId = mysql_insert_id();
}
return $sensorId;
}
public function getIdFromDescription($measurementDescription, $experimentDescription) {
$sensorId = false;
$query = "select id from $this->tableDescriptions where description like '$measurementDescription'
&& experimentname like '$experimentDescription'";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error(); //report error if one occurred
return false;
}
if (mysql_num_rows($result) > 0) {
$row = mysql_fetch_array($result);
$sensorId = $row['id'];
}
return $sensorId;
}
function getDataById($id, $startDate, $endDate, $interval = "") {
$data = array();
$startDateString = $startDate->format("Y-m-d H:i:s");
$endDateString = $endDate->format("Y-m-d H:i:s");
$query = "select time,measurement from $this->tableData where id='$id' && time>='$startDateString' && time <='$endDateString' order by time asc " . $interval = "" ? "" : "group by $interval";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error(); //report error if one occurred
return false;
}
while ($row = mysql_fetch_array($result)) {
//$time = new DateTime($row['time']);
$data[] = array($row['time'], $row['measurement'] * 1);
}
return $data;
}
public function getMostRecentData($id, $table) {
$query = "select date(max(time)) as time,measurement from $table where id='$id'";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error(); //report error if one occurred
return false;
} elseif (mysql_num_rows($result) > 0) {
$row = mysql_fetch_array($result);
return $row;
} else {
return false;
}
}
public function getExperimentList() {
$list = array();
$query = "select distinct experimentname from $this->tableDescriptions";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error(); //report error if one occurred
return false;
}
while ($row = mysql_fetch_array($result)) {
$list[] = $row['experimentname'];
}
return $list;
}
public function getChannelList($experimentDescription) {
$list = array();
$query = "select id,description,type from $this->tableDescriptions where experimentname='$experimentDescription'";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error(); //report error if one occurred
return false;
}
while ($row = mysql_fetch_array($result)) {
$list[$row['description']] = array("id" => $row['id'], "type" => $row['type']);
}
return $list;
}
public function getDescriptionFromId($id) {
$query = "select * from $this->tableDescriptions where id='$id'";
$result = mysql_query($query);
if (mysql_error()) {
echo mysql_error(); //report error if one occurred
return false;
}
if (mysql_num_fields($result) > 0) {
$row = mysql_fetch_array($result);
return array($row['type'], $row['description'], $row['experimentname']);
} else {
return false;
}
}
public function getDisplayName($experimentName) {
$query = "select displayName from $this->tableSpecs where experimentName='$experimentName'";
$result = mysql_query($query);
echo mysql_error();
$row = mysql_fetch_array($result);
if ($row) {
return $row['displayName'];
} else {
return false;
}
}
public function simpleQuery($query) {
$result = mysql_query($query);
echo mysql_error();
if ($result) {
$row = mysql_fetch_array($result);
if ($row) {
return $row[0];
} else {
return FALSE;
}
} else {
return FALSE;
}
}
public function rebin($tableSource, $tableTarget, $numSeconds, $sum = false) {
$this->createDataTable($tableTarget);
echo "Updating $tableSource to set rebinned from 0 to 2...";
$query = "update $tableSource set rebinned=2 where rebinned =0;";
mysql_query($query);
echo mysql_error();
echo "Done.\n";
$numRows = mysql_affected_rows();
echo "Found $numRows records in $tableSource that need rebinning...\n";
$query = "select id,from_unixtime(floor(unix_timestamp(min(time))/$numSeconds)*$numSeconds) as mintime from $tableSource where rebinned=2 group by id;";
$result = mysql_query($query);
echo mysql_error();
if ($result) {
while ($row = mysql_fetch_array($result)) {
$id = $row['id'];
$mintime = $row['mintime'];
echo $id . "...";
$query = "INSERT INTO $tableTarget (id,time,measurement,rebinned)
SELECT id,from_unixtime(floor(unix_timestamp(time)/$numSeconds)*$numSeconds)," . ($sum ? "sum" : "avg") . "(measurement) as measurement,0
FROM $tableSource WHERE id=$id && time>='$mintime'
GROUP BY id,floor(unix_timestamp(time)/$numSeconds)
ON DUPLICATE KEY UPDATE measurement=values(measurement), rebinned=0;";
mysql_query($query);
//echo $query;
echo mysql_error();
echo "Done.\n";
}
echo "Updating $tableSource to set rebinned from 2 to 1...";
$query = "update $tableSource set rebinned=1 where rebinned =2;";
mysql_query($query);
echo mysql_error();
echo "Done.\n";
}
}
public function getDCPower($experimentName, $startDate, $endDate) {
$out = array();
$query = $this->buildDCPowerQuery($experimentName, $this->tableMinbins);
if ($query) {
if ($startDate != "") {
$query = $query . " && m0.time>='$startDate' ";
}
if ($endDate != "") {
$query = $query . " && m0.time<='$endDate' ";
}
echo $query;
$result = mysql_query($query);
echo mysql_error();
while ($row = mysql_fetch_array($result)) {
// echo $row['time'].", ".$row['power']."\n";
$out[] = array($row['time'], $row['power'] + 0);
}
return $out;
} else {
return FALSE;
}
}
}
?>
Upvotes: 2
Views: 860
Answers (1)
LSerni
Reputation: 57408
Is there a way to make $database a string array which I can loop over for multiple database names?
No, it does not work that way. The script you have defines a class. You have to look in the file that uses that class, where there will be something like
$interface = new DatabaseInterface("database1");
There you'll be able to do things such as
$interface = array();
$interface[0] = new DatabaseInterface("database1");
$interface[1] = new DatabaseInterface("database2");
and use two instances of the interface against the two databases.
Upvotes: 2
Related Questions
- Use PHP Loop to Query MYSQL Database Multiple Times
- Run a query foreach databases (mysql)
- MySQL: Loop through databases and run a stored procedure on it
- Looping through multiple database tables with nested loop?
- PHP Loop to Access MySQL Databases
- Execute one MySQL query on multiple databases
- Running multiple PHP scripts at the same time (database loop issue)
- PHP loop through multiple database tables
- Looping through mysql database
- Select over multiple databases