function get_db_info($db_name)
{
// Carl grabbed this from the mysql.com boards http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html
$result = _mysql_query("SHOW TABLE STATUS FROM {$db_name}");
// SQL output
// mysql> show table status from [table_name];
// | Name | Engine | Version | Row_format | Rows
// | Avg_row_length | Data_length | Max_data_length
// | Index_length | Data_free | Auto_increment | Create_time
// | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
//
$gdata = 0;
$gindex = 0;
$gtotal = 0;
$grows = 0;
$i = 0;
$db_rec = array();
while ($myarr = _mysql_fetch_assoc($result)) {
// sum grand totals
$total = $myarr["Data_length"] + $myarr["Index_length"];
$gindex += $myarr["Index_length"];
$gdata += $myarr["Data_length"];
$grows += $myarr["Rows"];
$gtotal += $total;
$db_rec[$i] = new DB_REC($myarr["Name"], $myarr["Data_length"], $myarr["Index_length"], $total, $myarr["Rows"], $myarr["Avg_row_length"]);
$i++;
}
$db_rec[$i] = new DB_REC("Total", $gdata, $gindex, $gtotal, $grows, "");
return $db_rec;
}
<?php
include 'includes/corefuncs.inc.php';
include '../includes/sqlfunctions.inc.php';
include 'includes/adminconnection.inc.php';
if (function_exists('nukeMagicQuotes')) {
nukeMagicQuotes();
}
$conn = dbConnect();
$sql = 'SELECT * FROM announcements WHERE active = "1" ORDER BY displayorder, enddate';
$result = _mysql_query($conn, $sql);
$strReturn = '';
while ($row = _mysql_fetch_assoc($conn, $result)) {
$strReturn .= '<tr><td><div class="plaintext">' . $row['description'] . '</div></td><td style="text-align: right; padding-right: 50px;">' . $row['displayorder'] . '</td><td>' . $row['startdate'] . '</td><td>' . $row['enddate'] . '</td><td style="text-align: right;"><a class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only" href="#" role="button" disabled="false" id="edit' . $row['id'] . '" onclick="javascript:msgedit(\'' . $row['id'] . '\'); return false;"><span class="ui-button-text">Edit</span></a> <a class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only" href="#" role="button" disabled="false" id="del' . $row['id'] . '" onclick="javascript:msgdelete(\'' . $row['id'] . '\'); return false;"><span class="ui-button-text">Delete</span></a></td></tr>';
// $strReturn .= '<tr><td><div class="plaintext">'.$row['description'].'</div></td><td style="text-align: right; padding-right: 50px;">'.$row['displayorder'].'</td><td>'.$row['startdate'].'</td><td>'.$row['enddate'].'</td><td style="text-align: right;"><button class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only" id="edit'.$row['id'].'" ng-click="msgedit(\''.$row['id'].'\');"><span class="ui-button-text">Edit</span></button> <button class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only" id="del'.$row['id'].'" ng-click="msgdelete(\''.$row['id'].'\');"><span class="ui-button-text">Delete</span></button></td></tr>';
}
print $strReturn;
//<!-- <div class="row memberlist"> -->
//run the query to get the current membership
//get the tag to see if we are sorting by member name or
//call sign
if (!empty($_GET["sort"])) {
$findrecords = "SELECT *\n , SUBSTRING(fcccall\n , LEAST(\n if (Locate('0',fcccall) >0,Locate('0',fcccall),99),\n if (Locate('1',fcccall) >0,Locate('1',fcccall),99),\n if (Locate('2',fcccall) >0,Locate('2',fcccall),99),\n if (Locate('3',fcccall) >0,Locate('3',fcccall),99),\n if (Locate('4',fcccall) >0,Locate('4',fcccall),99),\n if (Locate('5',fcccall) >0,Locate('5',fcccall),99),\n if (Locate('6',fcccall) >0,Locate('6',fcccall),99),\n if (Locate('7',fcccall) >0,Locate('7',fcccall),99),\n if (Locate('8',fcccall) >0,Locate('8',fcccall),99),\n if (Locate('9',fcccall) >0,Locate('9',fcccall),99)\n )+1,3\n ) AS fccsuffix " . "FROM members, paid " . "Where (`members`.`member_id` = `paid`.`member_id`) AND " . "(year > '{$yearfull}') " . "ORDER BY (CASE WHEN fcccall IS NULL THEN 1 ELSE 0 END) ASC" . ",(CASE WHEN SUBSTRING(fcccall,1,1) = ' ' THEN 1 ELSE 0 END) ASC," . " fccsuffix, lname, fname;";
//echo $findrecords . '<br>';
} else {
$findrecords = "select * from members, paid " . "Where (`members`.`member_id` = `paid`.`member_id`) AND " . "(year > '{$yearfull}') " . "ORDER BY lname, fname, fcccall";
}
$result = _mysql_query($connection1, $findrecords);
//if query is successful display the member list
//use bold type for current paid memmbers based on the expiration
//date in the database
if ($result) {
while ($row = _mysql_fetch_assoc($connection1, $result)) {
//$datenow = time();
$current = 'current';
echo '<div class="row memberlist">';
echo '<div class="col-md-3 col-md-offset-3 col-xs-4 col-xs-offset-0 string ' . $current . '">';
displayFullName($row);
echo '</div><div class="col-md-2 col-xs-3 ' . $current . '">';
displayMemberIcon($row);
echo '</div><div class="col-md-2 col-xs-3 col-xs-offset-1 string ' . $current . '">';
displayCallAndQRZLink($row);
echo '</div>';
echo '</div>';
}
//end while
}
//end if
function UploadSyncClick($sender, $params)
{
//upload the file
//test if the working directory exists
$dir = 'dbsync';
if (!is_dir($dir)) {
//create the directory
$this->UploadStatus->Caption = 'Directory ' . $dir . ' does not exist. Creating.';
mkdir($dir);
}
//upload the file
$uploaddoc = $dir . '/' . $this->Upload1->FileName;
if (@move_uploaded_file($this->Upload1->FileTmpName, $uploaddoc)) {
//show success to the user
$this->UploadStatus->Font->Color = Green;
$this->UploadStatus->Caption = 'Upload successful.';
} else {
//exit on failure
$this->UploadStatus->Font->Color = Red;
$this->UploadStatus->Caption = 'Upload failure.';
return;
}
//stage 1
//parse the file
//create the working file on the server
//add the database to be using
//show progress to the user as we go along
try {
$this->UploadStatus->Caption = 'Parsing file.';
//create the work file
$sqlworkingfile = fopen($dir . '/uploadsync.sql', "w");
$sqluploadedfile = fopen($uploaddoc, "r");
//get the number of lines in the file
$numorginallines = count(file($uploaddoc));
//set the progress bar to the max count of the lines
$this->SyncProgress->Max = $numorginallines;
//loop through the lines from the uploaded file
for ($x = 0; $x <= $numorginallines; $x++) {
$line = fgets($sqluploadedfile);
//remove the comment lines
//test for the following
//comment lines beginning with /* AND
//comment lines beginning with --
$findcomment1 = strpos($line, '/*');
$findcomment2 = strpos($line, '--');
if ($findcomment1 === false && $findcomment2 === false) {
//if this line doesn't have these comments write to the
//sql command temp file
fwrite($sqlworkingfile, $line);
}
$this->SyncProgress->Position = $x;
}
//close the original file and the working file since inside a try block
fclose($sqluploadedfile);
fclose($sqlworkingfile);
} catch (Exception $e) {
$this->UploadStatus->Font->Color = Red;
$this->UploadStatus->Caption = 'Trouble in stage 1.' . $e;
return;
}
//stage 2
//connect to the database
$dbconnection = dbConnect();
try {
//for testing login previously required will handle this
//copy the entire file into a query
$sqlstatement = file_get_contents($dir . '/uploadsync.sql');
//start a transaction (all or nothing change)
$result = _mysql_begin_transaction($dbconnection, $param);
_mysql_query($dbconnection, 'use w3oiworkinprog;');
$this->SyncProgress->Max = strlen($sqlstatement);
while (strlen($sqlstatement) > 0) {
//update the progress bar position
$positionnow = $this->SyncProgress->Max - strlen($sqlstatement);
$this->SyncProgress->Position = $positionnow;
//break apart the sql commands
$position = strpos($sqlstatement, ";\n");
if ($position === false) {
break;
}
$sqlcommand = substr($sqlstatement, 0, $position + 1);
//run the query
$result = _mysql_query($dbconnection, $sqlcommand);
//now strip this done command from the the sql statement
$sqlstatement = substr($sqlstatement, $position + 1);
}
$this->UploadStatus->Caption = 'SQL statement update successful.';
//end the transaction
$result = _mysql_commit($dbconnection);
} catch (Exception $e) {
$this->UploadStatus->Font->Color = Red;
$this->UploadStatus->Caption = 'Trouble in stage 2. ' . $e;
return;
}
//if the database has been successfully updated syncronize the data
try {
//delete the member table
$sqlstatement = 'DROP TABLE IF EXISTS members;';
$result = _mysql_query($dbconnection, $sqlstatement);
//copy from the old style table
$sqlstatement = 'CREATE TABLE members LIKE w3oi_mbr;';
$result = _mysql_query($dbconnection, $sqlstatement);
//insert members into the new table
$sqlstatement = 'INSERT members SELECT * FROM w3oi_mbr;';
$result = _mysql_query($dbconnection, $sqlstatement);
//drop the unwanted columns
$sqlstatement = 'ALTER TABLE `members`' . 'CHANGE `id` `member_id` INT,' . 'DROP `pd00`,' . 'DROP `pd01`,' . 'DROP `pd02`,' . 'DROP `pd03`,' . 'DROP `pd04`,' . 'DROP `pd05`,' . 'DROP `pd06`,' . 'DROP `pd07`,' . 'DROP `pd08`,' . 'DROP `pd09`,' . 'DROP `pd10`,' . 'DROP `pd11`,' . 'DROP `pd12`,' . 'DROP `pd13`,' . 'DROP `pd14`,' . 'DROP `pd15`,' . 'DROP `pd16`,' . 'DROP `pd17`,' . 'DROP `pd18`,' . 'DROP `pd19`,' . 'DROP `bogpos`,' . 'DROP `acode`,' . 'DROP `xchg`,' . 'DROP `fone`,' . 'DROP `csz`,' . 'DROP `cty_st`,' . 'DROP `fullname`,' . 'DROP `pid`,' . 'DROP `expires`;';
$result = _mysql_query($dbconnection, $sqlstatement);
$sqlstatement = 'DELETE FROM `paid`;';
$result = _mysql_query($dbconnection, $sqlstatement);
//loop through each year
$sqlstatementarray = array();
for ($year = 0; $year <= 16; $year++) {
$yearastwodec = sprintf("%1\$02d", $year);
$sqlstatement = 'SELECT `id` AS `member_id`, `pd' . $yearastwodec . '` FROM `w3oi_mbr`' . ' WHERE `pd' . $yearastwodec . "` <> ''";
//echo '<p>' . $sqlstatement . '</p>';
$result = _mysql_query($dbconnection, $sqlstatement);
while ($row = _mysql_fetch_assoc($dbconnection, $result)) {
//get the member id
$memberid = $row['member_id'];
//get the paid status
$paidtype = $row["pd{$yearastwodec}"];
//convert to a 4 decimal year
$yearpull = '20' . $yearastwodec . '-12-31';
//now push the record
//echo $memberid . ' ' . $yearpull . '<br>';
$sqlupdate = 'insert into `paid` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$paidtype}');";
//echo $sqlupdate . '<br>';
array_push($sqlstatementarray, $sqlupdate);
}
}
foreach ($sqlstatementarray as $sqlstatement) {
//now loop through the array and post the updates
$result = _mysql_query($dbconnection, $sqlstatement);
}
$sqlstatement = 'DELETE FROM `officersboard`;';
$result = _mysql_query($dbconnection, $sqlstatement);
//check the board records
$sqlstatement = 'SELECT * FROM boghistory;';
//echo '<p>' . $sqlstatement . '</p>';
$result = _mysql_query($dbconnection, $sqlstatement);
$sqlstatementboardarray = array();
//loop through each year
while ($row = _mysql_fetch_assoc($dbconnection, $result)) {
//get the year, common to all the data
$year = $row['year'];
$yearpull = $year . '-12-31';
$memberid = $row['pid'];
$type = 'P';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['vid'];
$type = 'V';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['sid'];
$type = 'S';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['tid'];
$type = 'T';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['m1id'];
$type = 'B';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['m2id'];
$type = 'B';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['m3id'];
$type = 'B';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['m4id'];
$type = 'B';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
$memberid = $row['m5id'];
$type = 'B';
$sqlupdate = 'insert into `officersboard` ' . '(`year`, `member_id`, `type`) ' . "VALUES ('{$yearpull}', '{$memberid}', '{$type}');";
array_push($sqlstatementboardarray, $sqlupdate);
}
foreach ($sqlstatementboardarray as $sqlstatement) {
//now loop through the array and post the updates
$result = _mysql_query($dbconnection, $sqlstatement);
}
$this->UploadStatus->Caption = 'SQL transfer successful.';
} catch (Exception $e) {
echo $sqlupdate . '<br>';
$this->UploadStatus->Font->Color = Red;
$this->UploadStatus->Caption = 'Trouble in stage 2. ' . $e;
return;
}
}
