MySql update queries, confusion

[klemme]

Hey All,

I have tried to work on a way to update all the date in a row called: "position", from a table called: "pages".

The admin have a choise on where to place his new page, in the cms. The pages/links, are being pulled from the database by position ASC;

I am posting these values from the form, the values I am using in my SQL update query further down:

$oldposition = ($_POST['oldposition']);//Posted via a hidden field, not a part of the form submit, but taken from the DB, before the updates has taken place = Before the form and, the form submit
$pid = ($_POST['pid']);
$pagetitle = ($_POST['pagetitle']);
$description = ($_POST['description']);
$keywords = ($_POST['keywords']);
$linklabel = ($_POST['linklabel']);
$position = ($_POST['position']);//The new position chosen by admin
$heading = ($_POST['heading']);
$pagebody = ($_POST['pagebody']);

Then I am trying to update the "position" row in the db, so that if admin changes a page position from position 4 down to position 2, then the other values updates accordingly, so there are no numbers in the position row, with the same value.

That part works fine with my query below.

BUT: When I want to change the position from a smaller number to a bigger number, say position 2 to position 4, then I cant seem to write a query that changes the other positions in the row: position. I end up having identical values, and therefor the placements of the links called out by "position ASC", is incorrect.

Am I making sense?

My SQL Queries looks like this, and im sure they are not correct:

// Add the updated info into the database table--------------------------------------
// if new position is a smaller number (e.g. pos2 instead of pos 12):
if (isset($_POST['position'])){
$q = mysqli_query($myConnection, "UPDATE pages SET position = position + 1 WHERE position >= $position AND position <= $oldposition");
$query = mysqli_query($myConnection, "UPDATE pages SET pagetitle='$pagetitle', linklabel='$linklabel', description='$description', keywords='$keywords', position='$position', heading='$heading', pagebody='$pagebody', lastmodified='now()' WHERE id='$pid'") or die (mysqli_error($myConnection));
} else { // Here I want to change the values if the new value a bigger than the old value
// if new position is a higher number (e.g. pos 34 instead of pos6):
$qu = mysqli_query($myConnection, "UPDATE pages SET position = position - 1 WHERE position <= $position AND position <= $position");
$query = mysqli_query($myConnection, "UPDATE pages SET pagetitle='$pagetitle', linklabel='$linklabel', description='$description', keywords='$keywords', position='$position', heading='$heading', pagebody='$pagebody', lastmodified='now()' WHERE id='$pid'") or die (mysqli_error($myConnection));
}

// The last query doesnt work, and I cant see how else to write it.

// Help will be apreciated!

Klemme

[smantscheff]

1. What does "The last query doesn't work" mean? Do you get an error message or what?
2. Isolate your problem and present exactly the query which does not work and tell us in which way it doesn't work.
3. The clause "WHERE position <= $position AND position <= $position" is tautologic.
4. You use of "row" and "column" is confusing. Use the standard connotation.
5. Learn about the extract() function and apply it to your $_POST array.