Обновление приоритета записей в MySQL на PHP
Вход Регистрация

Обновление приоритета записей в MySQL на PHP

PHP скрипт предназначен для обновления приоритета записей в таблице базы данных MySQL. Он может выполнять запросы на обновление и удаление записей в заданной таблице, изменяя приоритет записи (идентификатор).

Лицензия GPL.

Системные требования скрипта:

PHP не младше 5.0 версии, база данных MySQL.

Исходник скрипта


/****************
This class is developed to change priority/SerialNo for a existing tables 
Variables to be used

1)$tableName		- Name of the table where you want to change the SerialNo or priority 
2)$updateOnField	- Name of the Field ON which The Priority or Serial No will execute
3)$mainFieldName	- Name of Field with which Priority will be updated e.g. DutyID,RedpnsibiltyID
	
updatePriority function
--------------
1)$primaryID		- PrimaryID FieldName with which you want to change the SerialNo or priority 
2)$newPriority		- New priority to assign
3)$oldPriority		- Old Priority to replace
4)$a_conditionIDs	- IDs on which the condition of priority will fire the format for this array will be array(FieldName1=>$fieldValue1,FieldName2=>$fieldValue2);

For deletePriority function
--------------
1)$primaryID		- PrimaryID FieldName with which you want to change the SerialNo or priority 
2)$a_deleteCondiotionIDs - IDs on which the condition of priority will fire the format for this array will be array(FieldName1=>$fieldValue1,FieldName2=>$fieldValue2);
***************/
class priority
{
	public $tableName;
	public $updateOnField;
	public $mainFieldName;
	function __construct($tableName,$updateOnField,$mainFieldName)
	{
		$this -> tableName = $tableName;
		$this -> updateOnField = $updateOnField;
		$this -> mainFieldName = $mainFieldName;
	}

	function updatePriority($primaryID,$newPriority=1,$oldPriority=0,$a_condiotionIDs=array())
	{
		$a_whereClause = array();
		$whereCluse	= " WHERE 1=1 ";

		if(!empty($a_condiotionIDs))
		{
			foreach($a_condiotionIDs as $fieldName => $fieldValue)
			{
				$a_whereClause[] = "$fieldName = $fieldValue";
			}
			$whereCluse	.= " AND ".implode(" AND ", $a_whereClause);
		}

		$diff = $oldPriority-$newPriority;
		if($diff > 0)
		{
			//die("If");
			$query = "SELECT ".$this -> mainFieldName."," . $this -> updateOnField."  FROM ".$this -> tableName. $whereCluse . " AND  " . $this -> updateOnField."  >= $newPriority ORDER BY " . $this -> updateOnField;
			//br();
			$result	=	dbQuery($query);
			if(mysql_num_rows($result))
			{

				$i = 0;
				while($row = mysql_fetch_assoc($result))
				{
					if($i < $diff)
					{
						$t_priority	= $row[$this -> updateOnField]+1;
						$tt_dutyID	= $row[$this -> mainFieldName];

						$updatQuery	= "UPDATE ".$this -> tableName." SET " . $this -> updateOnField."  = 	$t_priority WHERE ".$this -> mainFieldName."=$tt_dutyID";
						dbQuery($updatQuery);
					}
					$i++;
				}
			}
		}
		elseif($diff < 0)
		{
			//die("Else");
			$diff	=	abs($diff);

			$countTotal = @mysql_result(dbQuery("SELECT COUNT(".$this -> mainFieldName.") FROM ".$this -> tableName. $whereCluse),0); 

			$limit = $countTotal - $diff;
			//br();
			$query = "SELECT ".$this -> mainFieldName."," . $this -> updateOnField."  FROM ".$this -> tableName. $whereCluse ." AND ".$this -> mainFieldName." <> $primaryID AND " . $this -> updateOnField."  > $oldPriority ORDER BY " . $this -> updateOnField;
			//br();
			$result	= dbQuery($query);
			if(mysql_num_rows($result))
			{
				$i = 1;
				while($row = mysql_fetch_assoc($result))
				{
					if($i <= $diff)
					{
						$t_priority	= $row[$this -> updateOnField]-1;
						$tt_dutyID = $row[$this -> mainFieldName];

						$updatQuery	= "UPDATE ".$this -> tableName." SET " . $this -> updateOnField."  = $t_priority WHERE ".$this -> mainFieldName."=$tt_dutyID";
						dbQuery($updatQuery);
					}
					$i++;
				}
			}
		}
			
		$query = "UPDATE ".$this -> tableName." SET " . $this -> updateOnField." =$newPriority WHERE ".$this -> mainFieldName."=$primaryID";
		dbQuery($query);
	}

	function deletePriority($primaryID,$a_deleteCondiotionIDs=array())
	{
		$a_whereClause = array();
		$whereCluse	= " WHERE 1=1 ";
		if(!empty($a_deleteCondiotionIDs))
		{
			foreach($a_deleteCondiotionIDs as $fieldName => $fieldValue)
			{
				$a_whereClause[]= "$fieldName = $fieldValue";
			}
			$whereCluse	.=	" AND ".implode(" AND ", $a_whereClause);
		}

		$prority = @mysql_result(dbQuery("SELECT " . $this -> updateOnField."  FROM ".$this -> tableName." WHERE ".$this -> mainFieldName."=$primaryID"),0);
		//br();
		$maxPriority = @mysql_result(dbQuery("SELECT count(" . $this -> updateOnField." ) FROM ".$this -> tableName.$whereCluse),0);
		$diff = $maxPriority-$prority;
		if($diff > 0)
		{
			$query = "DELETE FROM  ".$this -> tableName. $whereCluse	." AND ".$this -> mainFieldName."=$primaryID";
			dbQuery($query);
			//br();
			$query = "SELECT ".$this -> mainFieldName."," . $this -> updateOnField."  FROM ".$this -> tableName.$whereCluse." AND  " . $this -> updateOnField."  >= $prority ORDER BY " . $this -> updateOnField." ";
			//br();
			$result	= dbQuery($query);
			if(mysql_num_rows($result))
			{

				$i = 0;
				while($row = mysql_fetch_assoc($result))
				{
					if($i < $diff)
					{
						$tt_priority = $row[$this -> updateOnField]-1;
						$tt_dutyID = $row[$this -> mainFieldName];

						$updatQuery = "UPDATE ".$this -> tableName." SET " . $this -> updateOnField."  = $tt_priority WHERE ".$this -> mainFieldName."=$tt_dutyID";
						br();
						dbQuery($updatQuery);
					}
					$i++;
				}
			}
		}
		else
		{
			$query = "DELETE FROM  ".$this -> tableName.$whereCluse." AND ".$this -> mainFieldName."=$primaryID";
			dbQuery($query);
		}
		return true;
	}
}

Скачать архивы


Комментировать

captcha

Вход

Зарегистрируйтесь, если нет учетной записи

Напомнить пароль
Регистрация
Напомнить пароль
Войти в личный кабинет