Update mysql table based on user input

[klemme]

Hi,

I my admin area, where the administrator can create a new page, it is also possible to decide the position of the link.

It is a menu in the left side of the screen, one link on top of the other, very simple nav.

I have a dropdown list in the admin area, where the admin can (After filling out all the form fields) decide where the link shoul be positioned.

After choosing a position from the dropdown list, the information gets sent to the database, but my problem is this:

If admin changes a link from lets say: position 4, to position 2 - Then the link that had position 2, stays with the same position. So I end up having to links positioned with the same number. I am gettting the links from the database, after position ASC.

I want to update the rest of the table so no links can have the same position.

My processing code for creating a new page looks like this:
I am only writing the mysql query, as i think it would be here i need to make some adjustments.

(I need to update it something like this: lets say I have 4 links in my vertical menu. They have a different number (position: 1,2,3,4 - from the databse), and after I change a position of a link, say link positiones as no. 4, to position no.2, then I need the links positoned with value =>2, to ++1...I just dont know how I can write this correctly.)

<?php
// Add the info into the database table

$query = mysqli_query($myConnection, "INSERT INTO pages (pagetitle, description, keywords, linklabel, position, heading, pagebody, lastmodified) 
        VALUES('$pagetitle','$description', '$keywords', '$linklabel', '$position', '$heading', '$pagebody',now())") or die (mysqli_error($myConnection));

// IS IT HERE THAT I SHOULD WRITE ANOTHER QUERY TO UPDATE THE POSITION OF THE OTHER LINKS IN THE DATABSE, AND HOW COULD THIS BE DONE...??

echo '<div align="center">Operation Completed Successfully! Head back to the admin home page, or the main website to see your changes!<br /><br /><a href="login/admin.php">Click Here For Admin home!</a> 
<br /><br /><a href="../index.php" >Or head back to view the live website!</a></div>';
exit();
?>

Help will be appreciated,

Klemme

First query
If the pos number >= new pos number for item, add one to each item.
Second query
Change pos number for selected item.

Your dropdown should give you an 'id' for the item ($id) and a 'pos' ($pos):

$q = mysql_query("UPDATE table SET field = field + 1 WHERE field >= $pos");

Then

$q = mysql_query("UPDATE table SET field = $pos WHERE id >= $id");

If you're using a js sortable widget, you may get better results from a multiple row update statement. An idea of this here:

http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/

[klemme]

My dropdown looks like this:

<?php 
		include_once "connect_to_mysql.php";
		//Placing the page, using a dropdown list
		$query="SELECT * FROM pages ORDER BY position ASC";
		while ($row = mysqli_fetch_array($query)) {
			$id = $row["id"];
			$position = $row["position"];
		}
	?>
	<p><b>Place Your Page:&nbsp;<span class="required">*</span></b></p>
	<select class="boxstyles" name="position">
	<?php
	echo "<option>Choose Position&nbsp;</option>";?>
    <?php
	$result = mysqli_query($myConnection, $query);
	$subject_count = mysqli_num_rows($result);
		// Subject_count +1 b/c adding a subject
		for($count=2; $count <= $subject_count+1; $count++) {
		echo "<option value=\"{$count}\">{$count}&nbsp;</option>";	
		} 
	?>
	</select>
	<?php

So I guess I should put the $id and $position in somewhere in the above?

I have to say, I wouldn't do it like this, but, you need to specify the id of the nav item somewhere in the form (usually with a hidden field).

Also, my previous solution will leave 'gaps' as items are re-ordered. This can be avoided with an update loop, but that's quite messy.

[klemme]

Hey :-)

Using an update loop?

Well it works perfectly! But as you write, When you re-order, og edit a page again, the position adds an extra +1 so I now have higher numbers in the position row, than actual links...

If you have the time and energy, at some point, I would gladly learn how this could be avoided using an update loop! At this point I am googling that solution too..

It is more tricky than I thought, with limited experience!

Klemme

I really don't want to go down this route. You need to know whether the id is a new page you're adding or if it already exists.

You need to be aware of the following scenarios:

Let's assume it already exists:

SCENARIO 1
if new position is higher up (e.g. pos2 instead of pos 12):
all items >=2 and < 12 need to be incremented by 1
set the pos of the item to 2
[so 2 update statements]

SCENARIO 2
if new position is lower down (e.g. pos 34 instead of pos6):
all items >6 and <= 34 decrease by 1
set the pos of the item to 34
[so 2 update statements]

BTW - check my maths - head's gone, can't figure out if it's >= > <= or <!!!!

If the id (navitem) already exists, you can place the existing pos value into a hidden field (or you can retrieve it via select from the DB).
Otherwise, if the item is new, you just increment from the position down (as suggested in my previous post).

Phew! I have to lie down now. Doubtless some pro out there will have a better solution.

SORRY, forgot to include the WHERE clause:

UPDATE table SET pos = pos + 1 WHERE pos >= $newpos AND pos < $oldpos

and

UPDATE table SET pos = pos - 1 WHERE pos > $oldpos AND pos <= $newpos

check for both $oldpos > $newpos and $newpos > $oldpos since you could end up changing something back to the original before posting and then submitting anyway - you want your script to do nothing in this case ($oldpos == $newpos).

This will work if all navitems are sequential to begin with. If not, I don't know what will occur.

[klemme]

Thank so far!

I got 50 % of it working now, when I change from a bigger number to a smaller number, then everything changes accordingly. Perfect!

But I am really having a headache trying to make the right adjustments, when I change a position from a smaller number to a bigger number! Then the bigger numbers keeps their position (or value), instead of decreasing by one.

I made an if else statement, using the position from the form.

I have both pulled the $oldposition and $newposition. ($new position is just called position...)

The code is like this:

// Add the updated info into the database table--------------------------------------
// if new position is a smaller number (e.g. pos2 instead of pos 12):
if (isset($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 {
// 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 > $oldposition AND position <= $position"); //THIS IS WHERE I AM GETTING LOST...
$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));
}

Its probably just some changes on some of the >= =< <>><<<> === ........... Baaarh!

UPDATE pages SET position = position - 1 WHERE position > $oldposition AND position <= $position

Seems OK to me. COnsider the following:

imagine moving from 3rd to 9th
this means all positions from 4th to 9th shuffle up one place (i.e. increment by -1).
this means that temporarily there will be two 3rd positions, but you then update the one item in question from 3rd to 9th.

Perhaps echoing out the query may show you what's being run.

I think the first one (which you say works fine is wrong though)...

UPDATE pages SET position = position + 1 WHERE position >= $position AND position <= $oldposition

imagine moving from 11th to 4th
this means all positions from 4th to 10th [position >= $position AND position < $oldposition] shuffle down one place (i.e. increment by +1).
this means that temporarily there will be two 11th positions, but you then update the one item in question from 11th to 4th.

[klemme]

Well I get an error when I echo out the variables: $q and $qu.
But only on $qu - Which is the I want to use when a new position is a higher number (e.g. pos 34 instead of pos6):

The output of the variables looks like this:

$q = 1, 
and
$qu = Notice: Undefined variable: qu in C:\wamp\www\mycms\administrator\page_edit_parse.php on line 52

Line 52 is the line where I echo "$qu";

echo "$qu";

Wouldnt that be correct?

I declared the variable $qu like this:

// 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 > $oldposition 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));
}

So it seems not to be "accepted", right?

Still when I change from a bigger value to a smaller one, it works fine... beats me!?!

Hmm, have you tried placing the fieldname POSITION within backticks - you realise it's a reserved word in sql? ALso, you have this:

WHERE position > $oldposition AND position <= position");

It should be

WHERE position > $oldposition AND position <= $position");

[klemme]

Thats right, I changed the "position" name in the DB, to "pos", on the entire site now....

And corrected the position to $position.

Still not getting it right though, maybe its not meant to be..

I cant see why its not a defined variable, with my limited knowledge, it looks fine!?

OK, I've seen it, it's this:

if (isset($position)){

When you try the second conditional (position-1), $position isn't set, so the query makes no sense.

How about:

if($oldposition > $position){
...
}elseif($oldposition < $position){
...
}

[klemme]

Haha I did that just now, came to think that they should be compared, one at the time, run the query, do an else statement, run the other query..

I walked away from the computer cause i was seeing double....

And actually wrote it on paper...different approach I know :-) But I agree, THAT must def. be the way to do it!

Ill let you know, you have helped a lot AGAIN, I appreciate it a lot!

[klemme]

It works like this:

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

Except I get a notice, with whatever query that is NOT executed:

Notice: Undefined variable: qu in C:\wamp\www\mycms\administrator\page_edit_parse.php on line 52

This is not an error i guess?

Is it "normal" to just remove these notices. I mean does php "think" in all politeness that it should alert this, because there is a statement that is not being executed? And is the way to get rid of it, just to write the error: All notice..blah blah, Or..

Is it because it is coded wrong?

[urtrivedi]

Warning is coming because you are using $qu before initializing it.

You may write follwoing statements to ignore error in page_edit_parse.

error_reporting(0);
ini_set("display_errors", 0);

[klemme]

Yes that is working fine, thanks!

It is not an error though? I mean I dont need to change anything in the script, or can there be another way to not get that error, by rewriting something?

Which I cant see, really :-)

[urtrivedi]

in the begining you may intialize
$qu="";

Odd. Haven't seen that before.