PHP optimizeSQLTable函数代码示例

        $insert = "INSERT INTO sms_news_categories (catName, catUserLevel, catVisible) VALUES (%s, %d, %s)";
        $query = sprintf($insert, escape_string($_POST['catName']), escape_string($_POST['catUserLevel']), escape_string($_POST['catVisible']));
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_news_categories");
        $action = "create";
    } elseif (isset($_POST['action_delete_x'])) {
        if (isset($_POST['catid']) && is_numeric($_POST['catid'])) {
            $catid = $_POST['catid'];
        } else {
            $catid = NULL;
        }
        $query = "DELETE FROM sms_news_categories WHERE catid = {$catid} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_news_categories");
        $action = "delete";
    }
    ?>

	<div class="body">
		<?php 
    $check = new QueryCheck();
    $check->checkQuery($result, $query);
    if (!empty($check->query)) {
        $check->message("news category", $action);
        $check->display();
    }
    ?>
		
		<span class="fontTitle">Create Site News Category</span><br /><br />

            break;
        default:
            $id = 0;
    }
    /* if update has been hit, continue */
    if (isset($_POST['action_update_post_x']) || isset($_POST['action_update_manage_x']) || isset($_POST['action_update_reports_x']) || isset($_POST['action_update_user_x']) || isset($_POST['action_update_other_x'])) {
        /* set the POST array */
        $accessValues = $_POST;
        $type = $_POST['type'];
        /* implode the array */
        $accessString = implode(',', $accessValues);
        /* update the database */
        $query = "UPDATE sms_accesslevels SET {$type} = '{$accessString}' WHERE id = {$id} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_accesslevels");
    }
    ?>

	<div class="body">
	
		<?php 
    if (!isset($sec)) {
        ?>
	
		<span class="fontTitle">Default User Access Levels</span><br /><br />
		From this page you can set up the default group access levels for your system.
		Levels have been set by default, but it is your choice whether to add or remove
		those items. Change group access levels will affect newly created player but will
		not change existing user levels for players. To begin, please select a group from
		the list below to set their default access levels to the system.<br /><br /><br />

        /* reset all of the positionMainPage flags */
        $updatePos = "UPDATE sms_positions SET positionMainPage = 'n'";
        $updatePosResult = mysql_query($updatePos);
        /* loop through the array and update the positions specified */
        foreach ($post as $key => $value) {
            if (!is_numeric($value)) {
                $value = NULL;
            }
            $query = "UPDATE sms_positions SET positionMainPage = 'y' WHERE positionid = {$value} LIMIT 1";
            $result = mysql_query($query);
        }
        /* optimize the table */
        optimizeSQLTable("sms_positions");
    }
    /* optimize the table */
    optimizeSQLTable("sms_globals");
    $manifest_defaults_array = explode(',', $manifest_defaults);
    if ($showInfoPositions == "n") {
        $disable = 5;
    } else {
        $disable = NULL;
    }
    ?>
	<script type="text/javascript">
		$(document).ready(function(){
			$('#container-1 > ul').tabs(<?php 
    echo $sec;
    ?>
, { disabled: [<?php 
    echo $disable;
    ?>

        if (!isset($id)) {
            $insert = "INSERT INTO sms_personallogs (logAuthor, logTitle, logContent, logPosted, logStatus) VALUES (%d, %s, %s, %d, %s)";
            $query = sprintf($insert, escape_string($sessionCrewid), escape_string($_POST['logTitle']), escape_string($_POST['logContent']), escape_string($today[0]), escape_string('saved'));
        } else {
            $update = "UPDATE sms_personallogs SET logTitle = %s, logContent = %s, logStatus = %s, logPosted = %d WHERE logid = {$id} LIMIT 1";
            $query = sprintf($update, escape_string($_POST['logTitle']), escape_string($_POST['logContent']), escape_string('saved'), escape_string($today[0]));
        }
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_personallogs");
        $action = "save";
    } elseif (isset($_POST['action_delete_x'])) {
        $query = "DELETE FROM sms_personallogs WHERE logid = {$id} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_personallogs");
        $action = "delete";
    }
    ?>
	
	<div class="body">
		<?php 
    $check = new QueryCheck();
    $check->checkQuery($result, $query);
    if (!empty($check->query)) {
        $check->message("personal log", $action);
        $check->display();
    }
    ?>
		
		<span class="fontTitle">Post Personal Log</span><br /><br />

        $query = sprintf($insert, escape_string($_POST['rankOrder']), escape_string($_POST['rankName']), escape_string($_POST['rankShortName']), escape_string($_POST['rankImage']), escape_string($_POST['rankDisplay']), escape_string($_POST['rankClass']));
        $result = mysql_query($query);
        /* optimize table */
        optimizeSQLTable("sms_ranks");
        $action = "create";
    } elseif (isset($_POST['action_delete_x'])) {
        if (isset($_POST['rankid']) && is_numeric($_POST['rankid'])) {
            $rankid = $_POST['rankid'];
        } else {
            $rankid = NULL;
        }
        /* do the delete query */
        $query = "DELETE FROM sms_ranks WHERE rankid = {$rankid} LIMIT 1";
        $result = mysql_query($query);
        /* optimize table */
        optimizeSQLTable("sms_ranks");
        $action = "delete";
    }
    ?>
<script type="text/javascript">
	$(document).ready(function() {
		$("a[rel*=facebox]").click(function() {
			var action = $(this).attr("myAction");
			
			jQuery.facebox(function() {
				jQuery.get('admin/ajax/rank_' + action + '.php', function(data) {
					jQuery.facebox(data);
				});
			});
			return false;
		});

         $arrayAwards = array();
     }
     /* get the date info from PHP */
     $now = getdate();
     /* make sure there are no semicolons in the reason */
     $reason = str_replace(";", ",", $reason);
     /* build the new award entry */
     $arrayAwards[] = $action_award . "|" . $now[0] . "|" . $reason;
     /* put the string back together */
     $joinedString = implode(";", $arrayAwards);
     /* dump the comma separated field back into the db */
     $update = "UPDATE sms_crew SET awards = %s WHERE crewid = {$action_crew} LIMIT 1";
     $query = sprintf($update, escape_string($joinedString));
     $result = mysql_query($query);
     /* optimize the table */
     optimizeSQLTable("sms_crew");
 }
 if (!isset($crew)) {
     /* active crew */
     $getActive = "SELECT crew.crewid, crew.firstName, crew.lastName, rank.rankName ";
     $getActive .= "FROM sms_crew AS crew, sms_ranks AS rank ";
     $getActive .= "WHERE crew.rankid = rank.rankid AND crew.crewType = 'active' ";
     $getActive .= "ORDER BY crew.rankid ASC";
     $getActiveResult = mysql_query($getActive);
     $activeCount = mysql_num_rows($getActiveResult);
     /* inactive crew */
     $getInactive = "SELECT crew.crewid, crew.firstName, crew.lastName, rank.rankName ";
     $getInactive .= "FROM sms_crew AS crew, sms_ranks AS rank ";
     $getInactive .= "WHERE crew.rankid = rank.rankid AND crew.crewType = 'inactive' ";
     $getInactive .= "ORDER BY crew.rankid ASC";
     $getInactiveResult = mysql_query($getInactive);

             $query = sprintf($create, escape_string($_POST['dbTitle']), escape_string($_POST['dbType']), escape_string($_POST['dbDesc']), escape_string($_POST['dbOrder']), escape_string($_POST['dbDisplay']), escape_string($_POST['dbURL']), escape_string($_POST['dbContent']), escape_string($_POST['dbDept']));
             $result = mysql_query($query);
             break;
         case 'update':
             $update = "UPDATE sms_database SET dbTitle = %s, dbOrder = %d, dbDisplay = %s, dbURL = %s, dbDesc = %s, dbContent = %s, ";
             $update .= "dbType = %s, dbDept = %d WHERE dbid = {$action_id} LIMIT 1";
             $query = sprintf($update, escape_string($_POST['dbTitle']), escape_string($_POST['dbOrder']), escape_string($_POST['dbDisplay']), escape_string($_POST['dbURL']), escape_string($_POST['dbDesc']), escape_string($_POST['dbContent']), escape_string($_POST['dbType']), escape_string($_POST['dbDept']));
             $result = mysql_query($query);
             break;
         case 'delete':
             $query = "DELETE FROM sms_database WHERE dbid = {$action_id} LIMIT 1";
             $result = mysql_query($query);
             break;
     }
     /* optimize the table */
     optimizeSQLTable("sms_database");
 }
 /* set up the database array */
 $database = array(0 => array());
 $departments = array();
 /* pull all the applicable departments */
 $depts = "SELECT * FROM sms_departments WHERE deptDatabaseUse = 'y' ORDER BY deptORDER ASC";
 $deptsR = mysql_query($depts);
 /* set up the department sections */
 while ($deptFetch = mysql_fetch_assoc($deptsR)) {
     extract($deptFetch, EXTR_OVERWRITE);
     $database[$deptid] = array();
     $departments[] = $deptid;
 }
 /* pull global entries */
 $entries = "SELECT * FROM sms_database WHERE dbDept = 0 ORDER BY dbOrder ASC";

        $query = sprintf($update, escape_string($_POST['newsCat']), escape_string($_POST['newsAuthor']), escape_string($_POST['newsTitle']), escape_string($_POST['newsContent']), escape_string($_POST['newsStatus']), escape_string($_POST['newsPrivate']));
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_news");
        $action = "update";
    } elseif (isset($_POST['action_delete_x'])) {
        $query = "DELETE FROM sms_news WHERE newsid = {$id} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_news");
        $action = "delete";
    } elseif (isset($remove)) {
        $query = "DELETE FROM sms_news WHERE newsid = {$remove} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_news");
        $action = "delete";
    }
    if (isset($id)) {
        ?>

	<div class="body">
		
		<?php 
        $check = new QueryCheck();
        $check->checkQuery($result, $query);
        if (!empty($check->query)) {
            $check->message("news item", $action);
            $check->display();
        }
        ?>

        /* do logic to figure out how to change the number of strikes */
        if ($action == "add") {
            $strikesNew = $strikeVar['0'] + 1;
        } elseif ($action == "delete") {
            $strikesNew = $strikeVar['0'] - 1;
        }
        $insert = "INSERT INTO sms_strikes (crewid, strikeDate, reason, number) VALUES (%d, %d, %s, %d)";
        $query = sprintf($insert, escape_string($crew), escape_string($today[0]), escape_string($_POST['reason']), escape_string($strikesNew));
        $result = mysql_query($query);
        /* update the user table to give the player the new number of strikes */
        $update = "UPDATE sms_crew SET strikes = %d WHERE crewid = {$crew} LIMIT 1";
        $query2 = sprintf($update, escape_string($strikesNew));
        $result2 = mysql_query($query2);
        /* optimize table */
        optimizeSQLTable("sms_crew");
        optimizeSQLTable("sms_strikes");
    }
    ?>
	
	<div class="body">
	
		<?php 
    $check = new QueryCheck();
    $check->checkQuery($result, $query);
    if (!empty($check->query)) {
        $check->message("strike", $action);
        $check->display();
    }
    ?>
		
		<span class="fontTitle">Manage Player Strikes</span><br /><br />

        $action = "send";
        $subject = "private message";
    } elseif (isset($action)) {
        $postArray = $_POST;
        foreach ($postArray as $key => $value) {
            if ($box == "inbox") {
                $boxReplace = "pmRecipientDisplay";
            } elseif ($box == "outbox") {
                $boxReplace = "pmAuthorDisplay";
            }
            $query = "UPDATE sms_privatemessages SET {$boxReplace} = 'n' ";
            $query .= "WHERE pmid = '{$value}' LIMIT 1";
            $result = mysql_query($query);
        }
        /* optimize the table */
        optimizeSQLTable("sms_privatemessages");
        $action = "remove";
        $subject = "private messages";
    }
    $getMessages = "SELECT * FROM sms_privatemessages WHERE pmRecipient = '{$sessionCrewid}' ";
    $getMessages .= "AND pmRecipientDisplay = 'y' ORDER BY pmDate DESC";
    $getMessagesResult = mysql_query($getMessages);
    $getMsgCount = "SELECT count(pmid) FROM sms_privatemessages WHERE pmRecipient = '{$sessionCrewid}' ";
    $getMsgCount .= "AND pmRecipientDisplay = 'y' AND pmStatus = 'unread'";
    $getMsgCountResult = mysql_query($getMsgCount);
    $msgCount = mysql_fetch_array($getMsgCountResult);
    $msgOut = "SELECT * FROM sms_privatemessages WHERE pmAuthor = '{$sessionCrewid}' ";
    $msgOut .= "AND pmAuthorDisplay = 'y' ORDER BY pmDate DESC";
    $msgOutResult = mysql_query($msgOut);
    ?>

    /* set the page class and vars */
    $pageClass = "admin";
    $subMenuClass = "manage";
    $query = FALSE;
    $result = FALSE;
    if (isset($_POST['action_update_x'])) {
        $update = "UPDATE sms_specs SET shipClass = %s, shipRole = %s, duration = %d, durationUnit = %s, refit = %d, refitUnit = %s, ";
        $update .= "resupply = %d, resupplyUnit = %s, length = %d, width = %d, height = %d, decks = %d, complimentEmergency = %s, ";
        $update .= "complimentOfficers = %s, complimentEnlisted = %s, complimentMarines = %s, complimentCivilians = %s, warpCruise = %s, ";
        $update .= "warpMaxCruise = %s, warpEmergency = %s, warpMaxTime = %s, warpEmergencyTime = %s, phasers = %s, torpedoLaunchers = %s, ";
        $update .= "torpedoCompliment = %s, defensive = %s, shields = %s, shuttlebays = %s, hasShuttles = %s, hasRunabouts = %s, ";
        $update .= "hasFighters = %s, shuttles = %s, runabouts = %s, fighters = %s, hasTransports = %s, transports = %s WHERE specid = 1 LIMIT 1";
        $query = sprintf($update, escape_string($_POST['shipClass']), escape_string($_POST['shipRole']), escape_string($_POST['duration']), escape_string($_POST['durationUnit']), escape_string($_POST['refit']), escape_string($_POST['refitUnit']), escape_string($_POST['resupply']), escape_string($_POST['resupplyUnit']), escape_string($_POST['length']), escape_string($_POST['width']), escape_string($_POST['height']), escape_string($_POST['decks']), escape_string($_POST['complimentEmergency']), escape_string($_POST['complimentOfficers']), escape_string($_POST['complimentEnlisted']), escape_string($_POST['complimentMarines']), escape_string($_POST['complimentCivilians']), escape_string($_POST['warpCruise']), escape_string($_POST['warpMaxCruise']), escape_string($_POST['warpEmergency']), escape_string($_POST['warpMaxTime']), escape_string($_POST['warpEmergencyTime']), escape_string($_POST['phasers']), escape_string($_POST['torpedoLaunchers']), escape_string($_POST['torpedoCompliment']), escape_string($_POST['defensive']), escape_string($_POST['shields']), escape_string($_POST['shuttlebays']), escape_string($_POST['hasShuttles']), escape_string($_POST['hasRunabouts']), escape_string($_POST['hasFighters']), escape_string($_POST['shuttles']), escape_string($_POST['runabouts']), escape_string($_POST['fighters']), escape_string($_POST['hasTransports']), escape_string($_POST['transports']));
        $result = mysql_query($query);
        /* optimize table */
        optimizeSQLTable("sms_specs");
    }
    $getSpecs = "SELECT * FROM sms_specs WHERE specid = 1";
    $getSpecsResult = mysql_query($getSpecs);
    while ($specFetch = mysql_fetch_array($getSpecsResult)) {
        extract($specFetch, EXTR_OVERWRITE);
    }
    ?>

	<div class="body">
		<?php 
    $check = new QueryCheck();
    $check->checkQuery($result, $query);
    if (!empty($check->query)) {
        $check->message("specifications", "update");
        $check->display();

        $insert = "INSERT INTO sms_awards ( awardName, awardImage, awardDesc, awardOrder, awardCat ) ";
        $insert .= "VALUES ( %s, %s, %s, %d, %s )";
        /* run the query through sprintf and the safety function to scrub for security issues */
        $query = sprintf($insert, escape_string($_POST['awardName']), escape_string($_POST['awardImage']), escape_string($_POST['awardDesc']), escape_string($_POST['awardOrder']), escape_string($_POST['awardCat']));
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_awards");
        $action = "create";
    } elseif (isset($_POST['action_delete_x'])) {
        if (isset($_POST['awardid']) && is_numeric($_POST['awardid'])) {
            $awardid = $_POST['awardid'];
        }
        $query = "DELETE FROM sms_awards WHERE awardid = {$awardid} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_awards");
        $action = "delete";
    }
    ?>
<script type="text/javascript">
	$(document).ready(function() {
		$("a[rel*=facebox]").click(function() {
			var action = $(this).attr("myAction");
			
			jQuery.facebox(function() {
				jQuery.get('admin/ajax/award_' + action + '.php', function(data) {
					jQuery.facebox(data);
				});
			});
			return false;
		});

 $pageClass = "admin";
 $subMenuClass = "user";
 $result = false;
 $query = false;
 if (isset($_GET['t']) && is_numeric($_GET['t'])) {
     $tab = $_GET['t'];
 } else {
     $tab = 1;
 }
 if (isset($_POST['action_x'])) {
     $insert = "INSERT INTO sms_awards_queue ( crew, nominated, award, reason ) VALUES ( %d, %d, %d, %s )";
     /* run the query through sprintf and the safety function to scrub for security issues */
     $query = sprintf($insert, escape_string($_POST['nominator']), escape_string($_POST['crew']), escape_string($_POST['award']), escape_string($_POST['reason']));
     /* run the query */
     $result = mysql_query($query);
     optimizeSQLTable("sms_awards_queue");
     /* set the email author */
     $userFetch = "SELECT crew.crewid, crew.firstName, crew.lastName, crew.email, rank.rankShortName ";
     $userFetch .= "FROM sms_crew AS crew, sms_ranks AS rank ";
     $userFetch .= "WHERE crew.crewid = '{$sessionCrewid}' AND crew.rankid = rank.rankid LIMIT 1";
     $userFetchResult = mysql_query($userFetch);
     while ($userFetchArray = mysql_fetch_array($userFetchResult)) {
         extract($userFetchArray, EXTR_OVERWRITE);
     }
     $firstName = str_replace("'", "", $firstName);
     $lastName = str_replace("'", "", $lastName);
     $from = $rankShortName . " " . $firstName . " " . $lastName . " < " . $email . " >";
     /* set the TO email addresses */
     $emFetch = "SELECT crewid, email FROM sms_crew WHERE (accessManage LIKE 'm_giveaward,%' OR accessManage LIKE '%,m_giveaward' ";
     $emFetch .= "OR accessManage LIKE '%,m_giveaward,%')";
     $emFetchR = mysql_query($emFetch);

        /* optimize the table */
        optimizeSQLTable("sms_departments");
        $object = "departmental database access";
        $action = "update";
    } elseif (isset($_POST['action_delete_x'])) {
        if (isset($_POST['deptid']) && is_numeric($_POST['deptid'])) {
            $deptid = $_POST['deptid'];
        } else {
            $deptid = FALSE;
            exit;
        }
        /* do the delete query */
        $query = "DELETE FROM sms_departments WHERE deptid = {$deptid} LIMIT 1";
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_departments");
        $object = "department";
        $action = "delete";
    }
    ?>
<script type="text/javascript">
	$(document).ready(function() {
		$("a[rel*=facebox]").click(function() {
			var action = $(this).attr("myAction");
			
			jQuery.facebox(function() {
				jQuery.get('admin/ajax/department_' + action + '.php', function(data) {
					jQuery.facebox(data);
				});
			});
			return false;

/* access check */
if (in_array("m_messages", $sessionAccess)) {
    /* set the page class and vars */
    $pageClass = "admin";
    $subMenuClass = "manage";
    $query = FALSE;
    $result = FALSE;
    /* if the POST action is update */
    if (isset($_POST['action_update_x'])) {
        $update = "UPDATE sms_messages SET welcomeMessage = %s, shipMessage = %s, simmMessage = %s, shipHistory = %s, ";
        $update .= "cpMessage = %s, joinDisclaimer = %s, samplePostQuestion = %s, rules = %s, acceptMessage = %s, ";
        $update .= "rejectMessage = %s, siteCredits = %s WHERE messageid = 1 LIMIT 1";
        $query = sprintf($update, escape_string($_POST['welcomeMessage']), escape_string($_POST['shipMessage']), escape_string($_POST['simmMessage']), escape_string($_POST['shipHistory']), escape_string($_POST['cpMessage']), escape_string($_POST['joinDisclaimer']), escape_string($_POST['samplePostQuestion']), escape_string($_POST['rules']), escape_string($_POST['acceptMessage']), escape_string($_POST['rejectMessage']), escape_string($_POST['siteCredits']));
        $result = mysql_query($query);
        /* optimize the table */
        optimizeSQLTable("sms_messages");
        /* this makes sure that once they hit update, the update is immediately seen */
        foreach ($_POST as $k => $v) {
            ${$k} = stripslashes($v);
        }
    }
    /* strip the slashes from the vars */
    $welcomeMessage = stripslashes($welcomeMessage);
    $shipMessage = stripslashes($shipMessage);
    $simmMessage = stripslashes($simmMessage);
    $shipHistory = stripslashes($shipHistory);
    $cpMessage = stripslashes($cpMessage);
    $joinDisclaimer = stripslashes($joinDisclaimer);
    $samplePostQuestion = stripslashes($samplePostQuestion);
    $rules = stripslashes($rules);
    $acceptMessage = stripslashes($acceptMessage);