Member Avatar for anthonyjpv

Im trying to swap the values if taken already but its not working

heres the code:

<?php 

ob_start();

require_once("../session.php");
require_once("../functions.php");
require_once("../../includes/connection.php");
include_once("../../includes/header.php");

confirm_logged_in();

if(isset($_GET['cat_id']))
{
	$category_id = $_GET['cat_id'];

	$result = get_specific_category($category_id);

	while($row = mysql_fetch_array($result))
	{
		$category_name = $row['category_name'];
		$position = $row['position'];
		$visible = $row['visible'];
	}
}

if(isset($_POST['Submit']))
{
	$errors = verify_null_values(array('Name', 'Position'));
	
	if(empty($errors))
	{
		$category_id = $_POST['cat_id'];
		$category_name = mysql_prep($_POST['Name']);
		$position = $_POST['Position'];
		$visible = $_POST['Visible'];
		
		
	$want_position = $_POST['Position']; //1
	
	$query = "SELECT *
					FROM categories
					WHERE position='{$category_id}'";
	
	$result = mysql_query($query);
	
	while($row = mysql_fetch_array($result))
	{
		$category_name = $row['category_name'];
		$orig_position = $row['position'];
		$visible = $row['visible'];
	}
	
	// as init value
	$new_position = $orig_position; //4
	
	
	//error
	$move_position_of_this = ($new_position - 1);
	
	echo $new_position;
	if($orig_position > $want_position)
	{
		// data has been preserved above
		// as in temp = data
		//$move_position_of_this = ($orig_position - 1); //2
		
		//move existing categories position down
		while($move_position_of_this >= $want_position) 
		{
			//take the new position
			$query_move_positions = "UPDATE categories
												SET position='{$new_position}'
												WHERE position='{$move_position_of_this}'";
			$result = mysql_query($query_move_positions);
			
			$new_position--; //2
			$move_position_of_this--; //1
		}
	}
		//correct
		$query = "UPDATE categories 
					SET 
					category_name='{$category_name}', 
					position='{$position}', 
					visible='{$visible}' 
					WHERE 
					category_id='{$category_id}'";
					
		$result = mysql_query($query);
		
		
		//redirect_to("category_view.php");
	}
	
	else
	{
		output_errors($errors);
	}
}
?>

<h1>Edit Category</h1>
	<form action="category_edit.php" method="post">
		Category Name:&nbsp;<input name="Name" type="text" value="<?php echo $category_name ?>" /> <br />
		Position:&nbsp;<select name="Position">
		<?php
				
			$result_category = get_categories();
			
			$num_rows = mysql_num_rows($result_category);
			
			for ($count = 1; $count <= $num_rows; $count++)
			{
				echo "<option value=\"{$count}\"";
				if($position == $count)
				{
					echo " selected";
				}
				echo ">{$count}</options>";
			}
		?>
		</select>
		<br />
		Visible:&nbsp;<input name="Visible" type="radio" value="1"	
		<?php if($visible == 1){echo " checked";}?>	/> Yes
		&nbsp;<input name="Visible" type="radio" value="0" 
		<?php if($visible == 0){echo " checked";}?>/> No<br />
		<input name="cat_id" type="hidden" value="<?php echo $category_id ?>" />
		<input name="Reset" type="reset" value="Reset" />
		<input name="Submit" type="submit" value="Update" />
	</form>
	
<?php 

include_once("../../includes/footer.php"); 
ob_flush();
mysql_close();

?>

any reason why its not working so far?

thanks!

  • 2 Contributors
  • 2 Replies
  • 240 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by anthonyjpv

Dani AI

Generated

Short summary and a clean fix for the “move category” problem (as clarified by and questioned by ).

The root causes seen in the posted code are: (1) the query that is supposed to read the edited category’s current position is selecting the wrong row (it compares the position column to the category id), and (2) the loop-based shifting approach is fragile and easy to produce duplicates or wrong mappings. A simpler, safer approach is to do a single range UPDATE for the set of affected rows, then set the edited category to the target position — all inside a transaction.

Recommended SQL pattern (use prepared statements / integer casts for inputs):

-- move up (orig > want): shift existing rows down by +1
UPDATE categories
SET position = position + 1
WHERE position >= :want AND position < :orig;

-- move down (orig < want): shift existing rows up by -1
UPDATE categories
SET position = position - 1
WHERE position <= :want AND position > :orig;

-- finally place the edited category at the wanted position
UPDATE categories
SET position = :want
WHERE category_id = :id;

Wrap those statements in a transaction (START TRANSACTION / COMMIT) so the list never becomes inconsistent. Important implementation notes: cast input values with intval, validate that :want is within 1..(count of categories), and fetch the original position by selecting WHERE category_id = :id. Remove any debug echoes left in production. If the position column has a UNIQUE constraint and the DB engine enforces it strictly, either use a single-range UPDATE (as above) or use a temporary out‑of‑range placeholder in a transaction to avoid intermediate conflicts.

Also consider switching from deprecated mysql_* calls to mysqli or PDO with prepared statements, avoid reusing variable names (don’t overwrite the edited category’s name when looking up other rows), and verify the form’s option markup is emitting valid <option> tags so posted values match expected integers.

Recommended Answers

All 2 Replies

Member Avatar for Member #46692

Not working?

What is not working... You need to be more specific.

Member Avatar for anthonyjpv

I noticed that if condition is not being utilized if say:

current position is 4

and i want to move that into position 1

i want to update set the data

from 3 to 4

from 2 to 3

from 1 to 2

and then update the data that i want to move to the position 1

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.