Hi folks,

Let's say i have 2 fields:
1. book titles
2. order of the book titles

I want to have them in an order according to the important of the book titles.

I can do that by adding numeric number in [sort] field and sort them by ascending

The question is that: if i have an update form to rearrange the order of the list and how do i write some code to update them?

I can update one record at the time, but i don't know how to update 10 records at the same time.

Please help.

td

You can update multiple columns by having a less restrictive HERE clause. Examples:


UPDATE tablename SET colname = colname + 1;

UPDATE tablename SET colname = colname + 1 WHERE colname > 4;


If you can figure out which row has changed, and how, then you can shift the values of the other rows accordingly.

Suppose you had 10 books, with importances of 1, 2, 3, ... 10. The user wants to move book 3 to book 7.

  First, we shift the importance of books 4, 5, 6, and 7 up one.
UPDATE tablename
   SET colname = colname - 1
 WHERE colname > 3 AND colname <= 7;

  Now we change the importance of the target book from 3 to 7.
UPDATE tablename
   SET colname = 7
 WHERE bookname = 'the book we're moving';


I strongly suggest adding a numeric primary key to this table so that you can access books by an easily sortable and searchable value, instead of using string (book titles).




Take care,

Nik
http://www.bigaction.org/

Thank you Nik, but i can't implement your advised code yet.

I guess i should clarify what i wish to do again

Here is a form that will display updating books order

************************************************** *.
Book titles | Book order
************************************************** *
Science | 2
History | 5
Art | 1
************************************************** *
[book_titles] will not be active
[book_order] will be a text box, so i can modify the order

Now i only can modify and change the order of one record at the time. This would take forever to finish if i have a long list.

So i wish to update multiple record of book order at one time when i hit the 'update' button.

I can't remember where, but i have seen the idea of updating the whole batch at once. It's like the idea of a shopping cart.

Forgive me for not understanding your advised code well.

thank you
td

Well, an update query can modify multiple rows, if the update made to ALL the rows is the same. That's why I had a query where there was a range of rows who's book order was simply incremented.

Since you're generating that form by selecting rows from your database, you should be able to give the [book_order] text field a name that uniquely identifies the book who's order is being affected.

Using the values you gave above as an example:

Science <input type="text" name="orders[Science]" value="2" />
History <input type="text" name="orders[History]" value="5" />
Art <input type="text" name="orders[Art]" value="1" />


Submitting this form will create a string-indexed array where the key (index) of the array is the book title, and the value at that array is the book's order.

You'd have to iterate over all the rows of that array and update each one in the database individually:

foreach($_POST['orders'] as $title => $order)
{
   $query = "UPDATE tbl SET order = '$order' WHERE title = '$title'";
   $result = mysql_query($query);
}


For more information on building arrays in form fields, read my old post on using arrays to organize data (including form input):
  http://p2p.wrox.com/archive/beginnin...2002-08/52.asp




Take care,

Nik
http://www.bigaction.org/

Perfect.

This is exactly what i need. And you got me. I have trouble using array. I am reading your array article now.

I'll get back and tell you when i can make it work

thank you so much

have a good weekend

td

Hi Nik,

Thank you so much,

Finally, i am working on the array again and i got it to work perfectly. Many thanks to you.

trangd :)
************************************************** *******

My pleasure! I'm glad it's working.

Take care,

Nik
http://www.bigaction.org/

Using the code you suggested I have made this page where the id and the current sequence is passed to this page, yet it won't update the db records, what am I doing wrong.
<?PHP
if ($sequence > $newsequence)
{
    $link = mysql_connect("localhost","dbuser","dbpass");
    mysql_select_db("dbname",$link);

    $query="UPDATE jackbecky SET sequence = (sequence + 1) WHERE sequence > " . $sequence . " AND sequence <= ".$newsequence;
    mysql_query($query);
    $query1="UPDATE jackbecky SET sequence = ".$sequence." WHERE id = ".$id;
    mysql_query($query1);

}
if ($sequence < $newsequence)
{
    $link = mysql_connect("localhost","dbuser","dbpass");
    mysql_select_db("dbname",$link);
    $query="UPDATE jackbecky SET sequence = (sequence - 1) WHERE sequence < " . $sequence . " AND sequence >= ".$newsequence;
    mysql_query($query);
    $query1="UPDATE jackbecky SET sequence = ".$newsequence." WHERE id = ".$id;
    mysql_query($query1);
}

header("location:index.php")
?>

Check for errors. Call mysql_query like this:

mysql_query($query) or die(mysql_error());

This should tell you the problem, which I believe is missing quotes.

HTH,

-Snib <><
http://www.snibworks.com
There are only two stupid questions: the one you don't ask, and the one you ask more than once :-)

When you say missing quotes what do you mean?

---
David Kittell