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