public static function dbClient() { $stdout = ""; // ============================================ // SELECT // ============================================ if (0) { $table = "lime_survey_57939"; $table = "orcasearch"; $sqlStmt = "SELECT * " . "FROM " . $table; //$sqlStmt = "SHOW CHARACTER SET"; $result = client_safeMysqlQuery($sqlStmt, "FIX-2"); $antal_rader = @mysql_num_rows($result); $stdout .= "<p><strong>" . $antal_rader . "</strong> records found in table <strong>" . $table . "</strong></p>"; if ($antal_rader > 0) { for ($rad = 0; $rad < $antal_rader; $rad++) { $res = @mysql_fetch_assoc($result); } // end for } // end if @mysql_free_result($result); } // -------------------------------------------- if (0) { $sqlStmt = "SELECT Telekatalog_id,Titel_id FROM Telekatalog WHERE Titel_id != ''"; $result = client_safeMysqlQuery($sqlStmt, "FIX-2"); $antal_rader = @mysql_num_rows($result); if ($antal_rader > 0) { for ($rad = 0; $rad < $antal_rader; $rad++) { $res = @mysql_fetch_assoc($result); $val[] = "(" . $res["Telekatalog_id"] . "," . $res["Titel_id"] . ")<br>"; } // end for } // end if @mysql_free_result($result); } // ============================================ // DELETE // ============================================ if (0) { $sqlStmt = "DELETE FROM Telekatalog_TitelData WHERE Grupp_id='1'"; $stdout .= client_submitQuery("1", "DELETE FROM Telekatalog_TitelData", $sqlStmt); } // ============================================ // UPDATE // ============================================ if (0) { $sqlStmt = "UPDATE Telekatalog_TitelData SET Grupp_id='ADM' WHERE Grupp_id='1'"; $stdout .= client_submitQuery("1", "UPDATE Telekatalog_TitelData", $sqlStmt); } // -------------------------------------------- if (0) { $gc = array("ALB", "BIO", "DBB", "AST", "FYS", "FKM"); foreach ($gc as $idx) { $sqlStmt = "UPDATE Telekatalog_TitelData SET Status_excelkod='ƒ' WHERE Grupp_id='" . $idx . "'"; $stdout .= client_submitQuery("1", "UPDATE Telekatalog_TitelData", $sqlStmt); } } // -------------------------------------------- if (0) { $gc = array('ELPARS' => 'bub', 'ATOMFY' => 'atom', 'SYSFYS' => 'sip'); foreach ($gc as $old => $new) { $sqlStmt = "UPDATE Telekatalog_TitelData SET Status_excelkod='" . $new . "' WHERE Status_excelkod='" . $old . "'"; $stdout .= client_submitQuery("1", "UPDATE Telekatalog_TitelData", $sqlStmt); } } // ============================================ // INSERT // ============================================ if (0) { $val = array("", ""); $sqlStmt = "INSERT INTO Telekatalog_TitelData " . " (Telekatalog_id,Titel_id) " . "VALUES " . "('" . implode("','", $val) . "')"; $stdout .= client_submitQuery("1", "INSERT Telekatalog_TitelData", $sqlStmt); } // -------------------------------------------- if (0) { $sqlStmt = "INSERT INTO Telekatalog_InstitutionTyp " . " (,,) " . "VALUES " . "('','','')," . "('','','')," . "('','','')," . "('','','')"; $stdout .= client_submitQuery("1", "INSERT Telekatalog_InstitutionTyp", $sqlStmt); } // -------------------------------------------- if (0) { //lime_old_survey_57939_20120518132733 //lime_old_survey_57939_20120518135027 //lime_old_survey_57939_20120627183014 //lime_old_survey_57939_20120627184652 $table1 = "lime_survey_57939"; $table2 = "lime_old_survey_57939_20120627183014"; $sqlStmt = "INSERT INTO " . $table1 . " " . "SELECT * " . "FROM " . $table2; $stdout .= client_submitQuery("1", "INSERT INTO " . $table1, $sqlStmt); $sqlStmt = "SELECT * " . "FROM " . $table1; $result = client_safeMysqlQuery($sqlStmt, "SELECT FROM" . $table1); $antal_rader = @mysql_num_rows($result); $stdout .= "<p><strong>" . $antal_rader . "</strong> records found in table <strong>" . $table1 . "</strong></p>"; } // ============================================ // CREATE TABLE // ============================================ if (0) { $sqlStmt = "CREATE TABLE IF NOT EXISTS Telekatalog_TitelData ( " . " Telekatalog_id SMALLINT NOT NULL REFERENCES Telekatalog (Telekatalog_id), " . " Titel_id VARCHAR(30) NOT NULL REFERENCES Titel (Titel_id), " . " PRIMARY KEY (Telekatalog_id,Titel_id) " . ")"; $stdout .= client_submitQuery("1", "CREATE Telekatalog_TitelData", $sqlStmt); } // ============================================ // ALTER TABLE // ============================================ if (0) { mysql_select_db(""); $sqlStmt = "ALTER IGNORE TABLE Telekatalog " . " CHANGE COLUMN Grupp_id " . " Grupp_id CHAR(10) NULL"; $stdout .= client_submitQuery("1", "ALTER CHANGE Telekatalog", $sqlStmt); } // -------------------------------------------- if (0) { $db = "nordita_test"; $tbl = "publications"; $sqlStmt = "ALTER IGNORE TABLE " . $db . "." . $tbl . " " . " ADD COLUMN " . " publ_year varchar(200) DEFAULT NULL " . " AFTER PublIn"; echo "<br>{$sqlStmt}"; //$stdout .= client_submitQuery("1","ALTER ADD publ_year TO nordita_test",$sqlStmt); $sqlStmt = "ALTER IGNORE TABLE " . $db . "." . $tbl . " " . " ADD COLUMN " . " `label` char(1) default NULL REFERENCES pp_category (label) " . " AFTER publ_year"; echo "<br>{$sqlStmt}"; //$stdout .= client_submitQuery("2","ALTER ADD label TO nordita_test",$sqlStmt); $sqlStmt = "ALTER IGNORE TABLE " . $db . "." . $tbl . " " . " ADD COLUMN " . " `slask3` char(1) default NULL REFERENCES pp_category (label) " . " AFTER label"; //$stdout .= client_submitQuery("3","ALTER ADD slask TO nordita_test",$sqlStmt); } // -------------------------------------------- if (0) { $sqlStmt = "ALTER IGNORE TABLE kalendarium_details " . " DROP COLUMN Kolumn"; $stdout .= client_submitQuery("1", "ALTER DROP kalendarium_details", $sqlStmt); } // ============================================ // SHOW // ============================================ if (0) { $db = "nordita_test"; $sqlStmt = "SHOW TABLES " . "FROM " . $db; //$sqlStmt = "SHOW CHARACTER SET"; $result = client_safeMysqlQuery($sqlStmt, "FIX-2"); $antal_rader = @mysql_num_rows($result); $stdout .= "<p><strong>" . $antal_rader . "</strong> tables found in database <strong>" . $db . "</strong></p>"; if ($antal_rader > 0) { $stdout .= "<ul>\r\n"; for ($rad = 0; $rad < $antal_rader; $rad++) { $res = @mysql_fetch_assoc($result); $stdout .= " <li>" . $res["Tables_in_" . $db] . "</li>\r\n"; } // end for $stdout .= "</ul>\r\n"; } // end if @mysql_free_result($result); } if (0) { $db = "nordita_test"; $tbl = "publications"; $sqlStmt = "SHOW COLUMNS " . "FROM " . $db . "." . $tbl; //$sqlStmt = "SHOW CHARACTER SET"; $result = client_safeMysqlQuery($sqlStmt, "FIX-2"); $antal_rader = @mysql_num_rows($result); $stdout .= "<p><strong>" . $antal_rader . "</strong> columns found in table <strong>" . $db . "." . $tbl . "</strong></p>"; if ($antal_rader > 0) { $stdout .= "<ul>\r\n"; for ($rad = 0; $rad < $antal_rader; $rad++) { $res = @mysql_fetch_assoc($result); debug::rr($res); $stdout .= " <li>" . $res["Field"] . "</li>\r\n"; } // end for $stdout .= "</ul>\r\n"; } // end if @mysql_free_result($result); } // ============================================ @mysql_close(); // ============================================ return $stdout; }
public static function AnewseditDropDb() { $stdout = ""; functions::gpc_declare_input("doit", false, true); if ($GLOBALS["doit"]) { if (!class_exists("fromdb_client", FALSE)) { require_once PATH_CLASSES . "/fromdb/client.php"; } $dbClient = new fromdb_client(); // includes connect to "nordita" database // --------------------------------------- unset($SQL); $sqlStmt = "SHOW TABLES FROM nordita"; $result = client_safeMysqlQuery($sqlStmt, ""); if ($result) { $numrows = @mysql_num_rows($result); while ($res = @client_safeMysqlFetchRow($result)) { if (preg_match("/^" . NEWS_DB_PREFIX . "/i", $res[0])) { $SQL[] = "DROP TABLE " . $res[0]; } } } @mysql_free_result($result); // --------------------------------------- if (is_array($SQL)) { $stdout .= "<ol class='tightlist'>\r\n"; foreach ($SQL as $sqlStmt) { $stdout .= " <li>" . client_submitQuery("", "", $sqlStmt, true) . "</li>\r\n"; } $stdout .= "</ol>\r\n"; $stdout .= "<h3 class='green'>Done</h3>"; } else { $stdout .= "<h3 class='red'>Found no SQL statements to execute</h3>"; } } else { $stdout .= "<h3 class='red bottommargin'>WARNING</h3>\r\n" . "\r\n" . "<form action='" . $_SERVER["PHP_SELF"] . "' method='post'>\r\n" . " <input type='submit' name='doit' value='Drop Database'>\r\n" . "</form>\r\n" . "\r\n"; } return $stdout; }