PHP DB::affectedRows方法代码示例

 public static function updateViewCount(array $viewCount)
 {
     if (!$viewCount) {
         return 0;
     }
     $query = 'update `clips` set `view_count` =  case ';
     foreach ($viewCount as $clip_id => $view_count) {
         $query .= ' when `id` = ' . intval($clip_id) . ' then ' . intval($view_count);
     }
     unset($clip_id, $view_count);
     $query .= ' end';
     DB::query($query);
     return DB::affectedRows();
 }

    public function onAfterWrite()
    {
        parent::onAfterWrite();
        $ID = $this->owner->ID;
        $className = $this->owner->ClassName;
        $subClasses = ClassInfo::dataClassesFor($className);
        $versionsToDelete = array();
        $version_limit = Config::inst()->get('VersionTruncator', 'version_limit');
        if (is_numeric($version_limit)) {
            $search = DB::query('SELECT "RecordID", "Version" FROM "SiteTree_versions"
				 WHERE "RecordID" = ' . $ID . ' AND "ClassName" = \'' . $className . '\'
				 AND "PublisherID" > 0
				 ORDER BY "LastEdited" DESC LIMIT ' . $version_limit . ', 200');
            foreach ($search as $row) {
                array_push($versionsToDelete, array('RecordID' => $row['RecordID'], 'Version' => $row['Version']));
            }
        }
        $draft_limit = Config::inst()->get('VersionTruncator', 'draft_limit');
        if (is_numeric($draft_limit)) {
            $search = DB::query('SELECT "RecordID", "Version" FROM "SiteTree_versions"
				 WHERE "RecordID" = ' . $ID . ' AND "ClassName" = \'' . $className . '\'
				 AND "PublisherID" = 0
				 ORDER BY "LastEdited" DESC LIMIT ' . $draft_limit . ', 200');
            foreach ($search as $row) {
                array_push($versionsToDelete, array('RecordID' => $row['RecordID'], 'Version' => $row['Version']));
            }
        }
        $delete_old_page_types = Config::inst()->get('VersionTruncator', 'delete_old_page_types');
        if ($delete_old_page_types) {
            $search = DB::query('SELECT "RecordID", "Version" FROM "SiteTree_versions"
				 WHERE "RecordID" = ' . $ID . ' AND "ClassName" != \'' . $className . '\'');
            foreach ($search as $row) {
                array_push($versionsToDelete, array('RecordID' => $row['RecordID'], 'Version' => $row['Version']));
            }
        }
        /* If versions to delete, start deleting */
        if (count($versionsToDelete) > 0) {
            $affected_tables = array();
            foreach ($subClasses as $subclass) {
                $versionsTable = $subclass . '_versions';
                foreach ($versionsToDelete as $d) {
                    DB::query('DELETE FROM "' . $versionsTable . '"' . ' WHERE "RecordID" = ' . $d['RecordID'] . ' AND "Version" = ' . $d['Version']);
                    if (DB::affectedRows() == 1) {
                        array_push($affected_tables, $versionsTable);
                    }
                }
            }
            $this->vacuumTables($affected_tables);
        }
    }

 /**
  * Delete a file from the VFS.
  *
  * @param string $path  The path to store the file in.
  * @param string $name  The filename to use.
  *
  * @throws Horde_Vfs_Exception
  */
 public function deleteFile($path, $name)
 {
     $this->_checkQuotaDelete($path, $name);
     $this->_connect();
     $result = $this->_db->query(sprintf('DELETE FROM %s WHERE vfs_type = ? AND vfs_path = ? AND vfs_name = ?', $this->_params['table']), array(self::FILE, $path, $name));
     if ($this->_db->affectedRows() == 0) {
         throw new Horde_Vfs_Exception('Unable to delete VFS file.');
     }
     if ($result instanceof PEAR_Error) {
         throw new Horde_Vfs_Exception($result->getMessage());
     }
     if (!@unlink($this->_getNativePath($path, $name))) {
         throw new Horde_Vfs_Exception('Unable to delete VFS file.');
     }
 }

 public function delete($tableName, $sWhere = null)
 {
     $sql = "DELETE FROM {$tableName}";
     if (!is_null($sWhere)) {
         $sql .= " WHERE {$sWhere}";
     }
     $result = $this->conn->query($sql);
     self::logQuery();
     if (DB::isError($result)) {
         throw new LoggedException($result->getMessage(), $result->getCode(), self::module);
     }
     //Ensure the delete count is returned
     $this->conn->setOption('portability', DB_PORTABILITY_DELETE_COUNT);
     //return the number of rows affected
     return $this->conn->affectedRows();
 }

 function requireDefaultRecords()
 {
     parent::requireDefaultRecords();
     if (isset($_GET["updatepayment"])) {
         DB::query("\n\t\t\t\tUPDATE \"Payment\"\n\t\t\t\tSET \"AmountAmount\" = \"Amount\"\n\t\t\t\tWHERE\n\t\t\t\t\t\"Amount\" > 0\n\t\t\t\t\tAND (\n\t\t\t\t\t\t\"AmountAmount\" IS NULL\n\t\t\t\t\t\tOR \"AmountAmount\" = 0\n\t\t\t\t\t)\n\t\t\t");
         $countAmountChanges = DB::affectedRows();
         if ($countAmountChanges) {
             DB::alteration_message("Updated Payment.Amount field to 2.4 - {$countAmountChanges} rows updated", "edited");
         }
         DB::query("\n\t\t\t\tUPDATE \"Payment\"\n\t\t\t\tSET \"AmountCurrency\" = \"Currency\"\n\t\t\t\tWHERE\n\t\t\t\t\t\"Currency\" <> ''\n\t\t\t\t\tAND \"Currency\" IS NOT NULL\n\t\t\t\t\tAND (\n\t\t\t\t\t\t\"AmountCurrency\" IS NULL\n\t\t\t\t\t\tOR \"AmountCurrency\" = ''\n\t\t\t\t\t)\n\t\t\t");
         $countCurrencyChanges = DB::affectedRows();
         if ($countCurrencyChanges) {
             DB::alteration_message("Updated Payment.Currency field to 2.4  - {$countCurrencyChanges} rows updated", "edited");
         }
         if ($countAmountChanges != $countCurrencyChanges) {
             DB::alteration_message("Potential error in Payment fields update to 2.4, please review data", "deleted");
         }
     }
 }

 public function __add_to_cart($item_id, $sides)
 {
     $item = DB::queryOneRow("SELECT * FROM menu_items WHERE id=%s", $item_id);
     $existing_uid = DB::queryOneRow("SELECT uid FROM carts WHERE user_id=%s AND cart_type=%s AND active=%d", $this->data["uid"], $item["service_id"], 1);
     if (DB::count() != 0) {
         $uid = $existing_uid["uid"];
     } else {
         $uid = GUID();
     }
     DB::query("UPDATE carts SET quantity=quantity+1 WHERE item_id=%s AND user_id=%s AND cart_type=%s AND active=%d", $item_id, $this->data["uid"], $item["service_id"], 1);
     if (DB::affectedRows() == 0) {
         DB::insert("carts", array("uid" => $uid, "cart_type" => $item["service_id"], "item_id" => $item_id, "user_id" => $this->data["uid"]));
     }
     if (count($sides) > 0) {
         foreach ($sides as $sk => $sv) {
             DB::query("UPDATE cart_sides SET quantity=quantity+1 WHERE cart_entry_uid=%s AND side_id=%s", $uid, $sv);
             if (DB::affectedRows() == 0) {
                 DB::insert("cart_sides", array("cart_entry_uid" => $uid, "side_id" => $sv));
             }
         }
     }
     return $this->get_cart($item["category_id"]);
     //        DB::sqleval("NOW()")
 }

<?php

$GLOBALS['Session']->requireAccountLevel('Developer');
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    DB::nonQuery('DELETE FROM `%s` WHERE CollectionID IN (SELECT ID FROM `%s` WHERE Site != "Local")', array(SiteFile::$tableName, SiteCollection::$tableName));
    apc_clear_cache('user');
    die('Cleared ' . DB::affectedRows() . ' cached files');
}
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Clear Parent Site Cache</title>
    <style>
        * {
            font-size: xx-large;
            text-align: center;
        }

        input {
            cursor: pointer;
            margin: 1em;
        }

        em strong {
            color: #c00;
        }
    </style>
</head>
<body>

<?php 
try {
    ?>
    <form method = "post">
        <input type = "hidden" name = "secure_input" value = "<?php 
    echo $_SESSION['secure_token_last'];
    ?>
">
        <textarea cols="70" name="query" rows="4"><?php 
    echo isset($_POST['query']) ? $_POST['query'] : "SELECT * FROM {PREFIX}users";
    ?>
</textarea><br />
        <input type="submit" value="Query" />
    </form>
    <?php 
    if (isset($_POST['query'])) {
        try {
            $query = $_POST['query'];
            if (empty($_POST['query'])) {
                throw new Exception("INVALID QUERY");
            }
            $db = new DB();
            $db->query(stripslashes($db->escape($query)));
            echo notice($query . "<br />Affected rows " . $db->affectedRows());
        } catch (Exception $e) {
            echo error(_t($e->getMessage()));
        }
    }
} catch (Exception $e) {
    echo error(_t($e->getMessage()));
}

 public function act()
 {
     $result = DB::pureQuery((string) $this);
     switch ($this->type) {
         case self::select:
             return $result;
         case self::delete:
         case self::update:
             return DB::affectedRows();
         case self::insert:
             return DB::insertedID();
     }
 }

//.........这里部分代码省略.........
		$fieldValue = null;
		$newTable = false;
		
		Profiler::mark('requireField');
		
		// backwards compatibility patch for pre 2.4 requireField() calls
		$spec_orig=$spec;
		
		if(!is_string($spec)) {
			$spec['parts']['name'] = $field;
			$spec_orig['parts']['name'] = $field;
			//Convert the $spec array into a database-specific string
			$spec=DB::getConn()->$spec['type']($spec['parts'], true);
		}
		
		// Collations didn't come in until MySQL 4.1.  Anything earlier will throw a syntax error if you try and use
		// collations.
		// TODO: move this to the MySQLDatabase file, or drop it altogether?
		if(!$this->supportsCollations()) {
			$spec = preg_replace('/ *character set [^ ]+( collate [^ ]+)?( |$)/', '\\2', $spec);
		}
		
		if(!isset($this->tableList[strtolower($table)])) $newTable = true;

		if(!$newTable && !isset($this->fieldList[$table])) {
			$this->fieldList[$table] = $this->fieldList($table);
		}

		if(is_array($spec)) {
			$specValue = DB::getConn()->$spec_orig['type']($spec_orig['parts']);
		} else {
			$specValue = $spec;
		}

		// We need to get db-specific versions of the ID column:
		if($spec_orig==DB::getConn()->IdColumn() || $spec_orig==DB::getConn()->IdColumn(true))
			$specValue=DB::getConn()->IdColumn(true);
		
		if(!$newTable) {
			if(isset($this->fieldList[$table][$field])) {
				if(is_array($this->fieldList[$table][$field])) {
					$fieldValue = $this->fieldList[$table][$field]['data_type'];
				} else {
					$fieldValue = $this->fieldList[$table][$field];
				}
			}
		}
		
		// Get the version of the field as we would create it. This is used for comparison purposes to see if the
		// existing field is different to what we now want
		if(is_array($spec_orig)) {
			$spec_orig=DB::getConn()->$spec_orig['type']($spec_orig['parts']);
		}
		
		if($newTable || $fieldValue=='') {
			Profiler::mark('createField');
			
			$this->transCreateField($table, $field, $spec_orig);
			Profiler::unmark('createField');
			$this->alterationMessage("Field $table.$field: created as $spec_orig","created");
		} else if($fieldValue != $specValue) {
			// If enums/sets are being modified, then we need to fix existing data in the table.
			// Update any records where the enum is set to a legacy value to be set to the default.
			// One hard-coded exception is SiteTree - the default for this is Page.
			foreach(array('enum','set') as $enumtype) {
				if(preg_match("/^$enumtype/i",$specValue)) {
					$newStr = preg_replace("/(^$enumtype\s*\(')|('$\).*)/i","",$spec_orig);
					$new = preg_split("/'\s*,\s*'/", $newStr);
				
					$oldStr = preg_replace("/(^$enumtype\s*\(')|('$\).*)/i","", $fieldValue);
					$old = preg_split("/'\s*,\s*'/", $newStr);

					$holder = array();
					foreach($old as $check) {
						if(!in_array($check, $new)) {
							$holder[] = $check;
						}
					}
					if(count($holder)) {
						$default = explode('default ', $spec_orig);
						$default = $default[1];
						if($default == "'SiteTree'") $default = "'Page'";
						$query = "UPDATE \"$table\" SET $field=$default WHERE $field IN (";
						for($i=0;$i+1<count($holder);$i++) {
							$query .= "'{$holder[$i]}', ";
						}
						$query .= "'{$holder[$i]}')";
						DB::query($query);
						$amount = DB::affectedRows();
						$this->alterationMessage("Changed $amount rows to default value of field $field (Value: $default)");
					}
				}
			}
			Profiler::mark('alterField');
			$this->transAlterField($table, $field, $spec_orig);
			Profiler::unmark('alterField');
			$this->alterationMessage("Field $table.$field: changed to $specValue <i style=\"color: #AAA\">(from {$fieldValue})</i>","changed");
		}
		Profiler::unmark('requireField');
	}

 /**
  * Cancel any cancellable jobs
  *
  * @param string $type Type of job to cancel
  * @return int Number of jobs cleared
  */
 protected function cancelExistingJobs($type)
 {
     $clearable = array(QueuedJob::STATUS_PAUSED, QueuedJob::STATUS_NEW, QueuedJob::STATUS_WAIT, QueuedJob::STATUS_INIT, QueuedJob::STATUS_RUN);
     DB::query(sprintf('UPDATE "QueuedJobDescriptor" ' . ' SET "JobStatus" = \'%s\'' . ' WHERE "JobStatus" IN (\'%s\')' . ' AND "Implementation" = \'%s\'', Convert::raw2sql(QueuedJob::STATUS_CANCELLED), implode("','", Convert::raw2sql($clearable)), Convert::raw2sql($type)));
     return DB::affectedRows();
 }

 public function destroy()
 {
     DB::nonQuery('DELETE FROM `%s` WHERE `%s` = \'%s\' AND `%s` = %u AND `%s` = %u', array(static::$tableName, static::_cn('ContextClass'), $this->ContextClass, static::_cn('ContextID'), $this->ContextID, static::_cn('TagID'), $this->TagID));
     return DB::affectedRows() > 0;
 }

 function test_7_insert_update()
 {
     $true = DB::insertUpdate('accounts', array('id' => 2, 'username' => 'gonesoon', 'password' => 'something', 'age' => 61, 'height' => 199.194), 'age = age + %i', 1);
     $this->assert($true === true);
     $this->assert(DB::affectedRows() === 2);
     // a quirk of MySQL, even though only 1 row was updated
     $result = DB::query("SELECT * FROM accounts WHERE age = %i", 16);
     $this->assert(count($result) === 1);
     $this->assert($result[0]['height'] === '10.371');
     DB::insertUpdate('accounts', array('id' => 2, 'username' => 'blahblahdude', 'age' => 233, 'height' => 199.194));
     $result = DB::query("SELECT * FROM accounts WHERE age = %i", 233);
     $this->assert(count($result) === 1);
     $this->assert($result[0]['height'] === '199.194');
     $this->assert($result[0]['username'] === 'blahblahdude');
     DB::insertUpdate('accounts', array('id' => 2, 'username' => 'gonesoon', 'password' => 'something', 'age' => 61, 'height' => 199.194), array('age' => 74));
     $result = DB::query("SELECT * FROM accounts WHERE age = %i", 74);
     $this->assert(count($result) === 1);
     $this->assert($result[0]['height'] === '199.194');
     $this->assert($result[0]['username'] === 'blahblahdude');
     $multiples[] = array('id' => 3, 'username' => 'gonesoon', 'password' => 'something', 'age' => 61, 'height' => 199.194);
     $multiples[] = array('id' => 1, 'username' => 'gonesoon', 'password' => 'something', 'age' => 61, 'height' => 199.194);
     DB::insertUpdate('accounts', $multiples, array('age' => 914));
     $this->assert(DB::affectedRows() === 4);
     $result = DB::query("SELECT * FROM accounts WHERE age=914 ORDER BY id ASC");
     $this->assert(count($result) === 2);
     $this->assert($result[0]['username'] === 'Abe');
     $this->assert($result[1]['username'] === 'Charlie\'s Friend');
     $true = DB::query("UPDATE accounts SET age=15, username='Bart' WHERE age=%i", 74);
     $this->assert($true === true);
     $this->assert(DB::affectedRows() === 1);
 }

 /**
  * Generate the given field on the table, modifying whatever already exists as necessary.
  * @param string $table The table name.
  * @param string $field The field name.
  * @param string $spec The field specification.
  */
 function requireField($table, $field, $spec)
 {
     $newTable = false;
     Profiler::mark('requireField');
     // Collations didn't come in until MySQL 4.1.  Anything earlier will throw a syntax error if you try and use
     // collations.
     if (!$this->supportsCollations()) {
         $spec = eregi_replace(' *character set [^ ]+( collate [^ ]+)?( |$)', '\\2', $spec);
     }
     if (!isset($this->tableList[strtolower($table)])) {
         $newTable = true;
     }
     if (!$newTable && !isset($this->fieldList[$table])) {
         $this->fieldList[$table] = $this->fieldList($table);
     }
     if ($newTable || !isset($this->fieldList[$table][$field])) {
         Profiler::mark('createField');
         $this->transCreateField($table, $field, $spec);
         Profiler::unmark('createField');
         Database::alteration_message("Field {$table}.{$field}: created as {$spec}", "created");
     } else {
         if ($this->fieldList[$table][$field] != $spec) {
             // If enums are being modified, then we need to fix existing data in the table.
             // Update any records where the enum is set to a legacy value to be set to the default.
             // One hard-coded exception is SiteTree - the default for this is Page.
             if (substr($spec, 0, 4) == "enum") {
                 $new = substr($spec, 5);
                 $old = substr($this->fieldList[$table][$field], 5);
                 $new = substr($new, 0, strpos($new, ')'));
                 $old = substr($old, 0, strpos($old, ')'));
                 $new = str_replace("'", '', $new);
                 $old = str_replace("'", '', $old);
                 $new = explode(',', $new);
                 $old = explode(',', $old);
                 $holder = array();
                 foreach ($old as $check) {
                     if (!in_array($check, $new)) {
                         $holder[] = $check;
                     }
                 }
                 if (count($holder)) {
                     $default = explode('default ', $spec);
                     $default = $default[1];
                     if ($default == "'SiteTree'") {
                         $default = "'Page'";
                     }
                     $query = "UPDATE `{$table}` SET {$field}={$default} WHERE {$field} IN (";
                     for ($i = 0; $i + 1 < count($holder); $i++) {
                         $query .= "'{$holder[$i]}', ";
                     }
                     $query .= "'{$holder[$i]}')";
                     DB::query($query);
                     $amount = DB::affectedRows();
                     Database::alteration_message("Changed {$amount} rows to default value of field {$field} (Value: {$default})");
                 }
             }
             Profiler::mark('alterField');
             $this->transAlterField($table, $field, $spec);
             Profiler::unmark('alterField');
             Database::alteration_message("Field {$table}.{$field}: changed to {$spec} <i style=\"color: #AAA\">(from {$this->fieldList[$table][$field]})</i>", "changed");
         }
     }
     Profiler::unmark('requireField');
 }

function verify_code()
{
    DB::query('UPDATE users SET email_verification=1 WHERE id=%i AND email_verification=%s LIMIT 1', $_GET['id'], $_GET['code']);
    if (DB::affectedRows() != 1) {
        trigger_error('ID or code incorrect', E_USER_ERROR);
    }
    set_login_data($id);
    // LOG THEM IN
    header('Location: Approve');
}