PHP/MySQL Updateing order

Question

I have an sql table with articles in it. I stored the order of the articles in an INT field in sql.
With an ajax post from the (drag and drop) UI, i got an array with integers (new order).
example:
Old order: 2,1,3,4
New order should be: 2,1,4,3
php:

$i = 1;    
foreach ($inputarray as $var) :
        if ($i != $var) :
            $this->_sqli->query("UPDATE articles 
    SET artorder=$i WHERE artorder = $var and pagesid = $pagesid");
        endif;

    $i++;
endforeach;

The problem: when i try to swap elements, they are gonna be the same. So the previous example's output will be: 2,1,4,4 How should i update my sql?

Charles R · Accepted Answer · 2012-03-02 18:50:33Z

1

This will not work as long as "artorder" is the unique key that you use when updating a row. This is because you will run into situations where the artorder is the same in two rows; thus, you no longer have a unique key. Just add a primary key to the table and use that on your UPDATE statements:

ALTER TABLE articles ADD articleId INT(5) NOT NULL,
ADD PRIMARY KEY (articleId)

answered Mar 2, 2012 at 18:50

Charles R

4413 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

msgmash.com · Accepted Answer · 2012-03-02 14:37:43Z

1

You need to pass the old order as well as the new order. Then you could write

$sz = count ($inputarray);
for ($i = 0; $i < $sz; ++$i):
    // $oldarray holds the old order
   if ($oldarray[$i] != $inputarray[$i]) :
      $this->_sqli->query("UPDATE articles 
         SET artorder=" . $inputarray[$i] . " WHERE artorder = " . $oldarray[$i] . " and pagesid = $pagesid");
      endif;
endfor;

answered Mar 2, 2012 at 14:37

msgmash.com

1,0356 silver badges10 bronze badges

2 Comments

szab.kel Over a year ago

I got the same result with this. :(

msgmash.com Over a year ago

Yes, I just realised what the problem is. Using your example, on the 3rd iteration it changes artorder to 4 from 3 - so there are two rows wth artorder = 4. On the 4th iteration, it changes all artorder values of 4 to 3 :( Doh! Is there another way to uniquely refer to rows other than artorder? I think you will need this to avoid the problem of updating the same column that you are using to select on.

Collectives™ on Stack Overflow

PHP/MySQL Updateing order

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related