sos Position php sql

Hello,

I have an mysql table with products. Every product has a "Position" number. When i delete a row of this table, i want update the "Position" number of the higher number products.

My thought is :

$oldPosition = 6;
$maxPosition = 8;
$gap = $maxPosition - $oldPosition;

For ($i=1;$i <= $gap;i++){
      $var[$i] = $oldNumber+ $i;
      $newPosition[$i] = $var[$i] -1;
      $query = "UPDATE table SET Position=$newPosition WHERE Position=$oldPosition";
      $result = mysql_query($query);
}

Is this possible?

I think that queries on loops is forbidden.

Thanks! ))

You can do a Trigger on the database... http://en.wikipedia.org/wiki/Database_trigger

Can you help me with syntax?

I make agoogle search for "trigger mysql update rows", but noone says something about $var -1 update.

Thank U!

An easier way goes something like this. It requires InnoDB, or a type that supports transactions for integrity.

START TRANSACTION;
  DELETE FROM someTable WHERE position = 5;
  UPDATE someTable SET position = position - 1 WHERE position > 5;
COMMIT;

Queries in loops should be avoided at all costs.

Thank u for your answer.

I make a FOR loop and i restrict under 100 loops. 

I think that 100 updates with number inputs is ok to executed for an sql, right ?

			

		$name = $_POST['selectedName'];

		$queryPosition = "SELECT Position FROM $category WHERE Name='$name'";
		$resultPosition = mysql_query($queryPosition);
		while($row = mysql_fetch_array($resultPosition)){
				$deletePosition = $row['Position'];
		}


		$position = "SELECT MAX(Counter) FROM $category";
		$maxPosition = mysql_query($position);
		while($row = mysql_fetch_array($maxPosition)){
				$maxPosition = $row['MAX(Counter)'];
		}

$gap = $maxPosition - $deletePosition;


		IF ( (isset($category)) && ($gap >= 1) && ($gap <= 100 ) && ($deletePosition >= 1) && ($maxPosition >= 1) ){
    
			For($i=1; $i<=$gap; $i++){

				$oldPosition = $deletePosition + $i;
				$newPosition = $oldPosition - 1;

				$deleteQuery = "UPDATE $category SET Position='$newPosition' WHERE Position='$oldPosition'";
				$delete = mysql_query($deleteQuery);
			}

			$hi = mysql_query("DELETE FROM $category WHERE Name='$name'");
				echo 'product deleted!';

		}Else{
		echo 'Under 100 loops!';
		}

In my opinion, no, it's not OK.

An easier way goes something like this. It requires InnoDB, or a type that supports transactions for integrity.

 

START TRANSACTION;
  DELETE FROM someTable WHERE position = 5;
  UPDATE someTable SET position = position - 1 WHERE position > 5;
COMMIT;

 

Queries in loops should be avoided at all costs.

Every mysql support "start transactio" command? Where can i execute this? In my php file or in phpmyadmin.

Thank u again

MyISAM doesn't support it. It's not NEEDED, but it helps ensure integrity. Google about SQL transactions to learn more.

The same queries will work without the transactions... all it does is automatically roll back changes if something fails

I will try your way. Thank u for your time

I'm sorry xyph.

Can i use your code to an auto increment "id" column?

When a row deleted, the "id" column auto fill up th gap. 

Thank u again!!!!!!  :D :D

i fix it in an auto increment column.

One new question is, how can i change position and the other auto change their position.

Is there any TRANSACTION?

Is this true?

START TRANSACTION;
  DELETE FROM someTable WHERE position = 2;
  UPDATE someTable SET position = position - 1 WHERE position > 2;
  INSERT INTO someTable position VALUES 6;
  UPDATE someTable SET position = position + 1 WHERE position > 6;
COMMIT;

I'm sorry xyph.

 

Can i use your code to an auto increment "id" column?

When a row deleted, the "id" column auto fill up th gap. 

 

Thank u again!!!!!!  :D :D

I really wish auto_increment did not exist. Instead it should be auto_generate which creates a unique value between 1 and 4 billion. Perhaps then people would stop worrying about having gaps in the id sequence and working out ways to reuse the gaps.

You are right.! A gap between id's (auto_increment) is not a problem. My problem is position 

If i have 2 columns (position,product), and i have values in there order by position, can i change the position of one product and the others auto change their position?

Thank you in advance! 

"auto" in this case would mean you run an update query to increment (or decrement) the positions of those between the old and new position of the product

yes sir 

You want to update the tables before you insert.

If there's already a 6, you'll have multiple 6s. To avoid this, make the column a unique key, and use something like this

START TRANSACTION;
  UPDATE someTable SET position = position + 1 WHERE position >= 6;
  INSERT INTO someTable (position) VALUES (6);
COMMIT;

To move G (pos 7) to pos 2 would be a three step approach to avoid duplication

item  pos   

  A    1 

  B    2  <-+

  C    3    |

  D    4    |

  E    5    |

  F    6    |

  G    7  --+

  H    8

STEP 1 - set G pos to null

item  pos             

  A    1 

  B    2 

  C    3 

  D    4 

  E    5 

  F    6 

            G  null 

  H    8

STEP 2 - increment current position2 2 - 6  to 3 - 7

item  pos

  A    1

  B    3

  C    4

  D    5

  E    6 

  F    7  G  null

  H    8

STEP 3 - set G pos to 2

item  pos

  A    1

  G    2         

  B    3

  C    4

  D    5

  E    6 

  F    7 

  H    8

Barand, i use your recommendations and make this. Everything goes right! 

Thanks again!

		$newPosition = $_POST['newPosition'];
	$Counter = $_SESSION['Counter'];

	If((isset($newPosition)) && ($newPosition > 0)){
		include("connector.php");
		$Position = $_SESSION['Position'];

		$position = "SELECT MAX(Position) FROM $TableName";
		$maxPosition = mysql_query($position);
		while($row = mysql_fetch_array($maxPosition)){
				$maxPosition = $row['MAX(Position)'];
		}

		If(($Position<$newPosition) && ($newPosition<$maxPosition)){
				$k = $Position +1;
				For($i=$k;$i<=$newPosition;$i++){//products<= $k, - 1

					$newSmallerPosition = $i-1;
					$updateSmallers = "UPDATE $TableName SET Position='$newSmallerPosition' WHERE Position='$i'";
					$queryUpdateSmallers = mysql_query($updateSmallers);					

				}//products>$k let same

				$newPos = "UPDATE $TableName SET Position='$newPosition' WHERE Counter='$Counter' ";
				$queryNewPosition = mysql_query($newPos);

		}ElseIf(($Position>$newPosition) && ($newPosition<=$maxPosition)){ 
				$j=$Position-1;

				For($i=$j;$i>=$newPosition;$i=$i-1){//Products >= newPosition, - 1 

					$newLargerPosition = $i+1;
					$updateLargers = "UPDATE $TableName SET Position='$newLargerPosition' WHERE Position='$i'";
					$queryUpdateLargers = mysql_query($updateLargers);

				}				

					$newPos = "UPDATE $TableName SET Position='$newPosition' WHERE Counter='$Counter'";
					$queryNewPosition = mysql_query($newPos);
		}Else{
			echo 'error';
		}

You shouldn't be running queries inside a  for loop, it's inefficient. Basically all you need is

<?php

$oldpos = 2;
$newpos = 7;

if ($newpos < $oldpos) {
    mysql_query ("UPDATE $tablename SET position = null WHERE position = $oldpos");
    mysql_query ("UPDATE $tablename SET position=position+1 WHERE position BETWEEN $newpos AND $oldpos-1");
    mysql_query ("UPDATE $tablename SET position = $newpos WHERE position IS NULL");
}
elseif ($newpos > $oldpos) {
    mysql_query ("UPDATE $tablename SET position = null WHERE position = $oldpos");
    mysql_query ("UPDATE $tablename SET position=position-1 WHERE position BETWEEN $oldpos+1 AND $newpos");
    mysql_query ("UPDATE $tablename SET position = $newpos WHERE position IS NULL");
}   
?>