how to update

[mukeshrasm]

Hi

I have a dynamic table of 3 cells in which fields are coming from database. I want to save the record after moving row up or down. means the last position of the data in the table to be saved. But I don't know How to do this?

Thanks!

[nathj]

Hi,

This seems quite tricky but I think I canmanage it in 3 updates.

You need to have the display order stored in the database for this to work.

When a user clicks to move a record from position 3 to poistion 4 you need to issue three update commands on your database:

1. This will change the record with display order 3 to have a display order of -1 - or some numeric value that is not going to appear. Note using minus numbers means the data structure must allow for signed values

2. This update changes display order on the record where it is 4 to 3.

3. Then update the record with the -1 display order to have a display order of 4

The final step is to refresh the page. If all of this is done with AJAX then the user won't even notice the refresh. But that's another topic.

Have a play around with this basic outline and you will probably find a more elegant solution. If you get stuck post back with your code - in code tags and I'll take another look.

Cheers
nathj

[mukeshrasm]

Hi,

This seems quite tricky but I think I canmanage it in 3 updates.

You need to have the display order stored in the database for this to work.

When a user clicks to move a record from position 3 to poistion 4 you need to issue three update commands on your database:

1. This will change the record with display order 3 to have a display order of -1 - or some numeric value that is not going to appear. Note using minus numbers means the data structure must allow for signed values

2. This update changes display order on the record where it is 4 to 3.

3. Then update the record with the -1 display order to have a display order of 4

The final step is to refresh the page. If all of this is done with AJAX then the user won't even notice the refresh. But that's another topic.

Have a play around with this basic outline and you will probably find a more elegant solution. If you get stuck post back with your code - in code tags and I'll take another look.

Cheers
nathj

Hi

I am not able to understand how to do this means issue three update commands. If you can please give some example or some code snip for that than it would be better for me to understand.

Thanks

[adamalton]

I think mukeshrasm is talking about doing something a bit like this:

Create an extra field in your database table called 'order_number'. So each record in the table also has an order number.

Then when you want to swap record number 3 with record number 4...
[PHP]
//connect to mysql database

//now select records 3 and 4 to find out what they are
$result_1 = mysql_query("SELECT primary_key FROM my_table WHERE order_number='3'");
$result_2 = mysql_query("SELECT primary_key FROM my_table WHERE order_number='4''");

$third_record = mysql_result($result_1 , 0 , 'primary_key');
$fourth_record = mysql_result($result_2 , 0 , 'primary_key');

//now set the record that currently has 'order_number' 3 to have order_number 4
mysql_query("UPDATE my_table SET order_number='4' WHERE primary_key='". $third_record ."'");

//NB: NOTE THAT THERE ARE NOW 2 RECORDS WITH order_number 4!!!

//now change the record that was number 4 to number 3
mysql_query("UPDATE my_table SET order_number='3' WHERE primary_key='". $forth_record ."'");

//done!![/PHP]

Note that you can't just do:[PHP]
mysql_query("UPDATE my_table SET order_number='3' WHERE order_number='4'");
mysql_query("UPDATE my_table SET order_number='4' WHERE order_number='3'");[/PHP]
Because once the first query has been executed you have 2 records with order_number 3, so when the second query executes you will just get 2 records with order_number 4. Not useful. Hence the other method!

[mukeshrasm]

I think mukeshrasm is talking about doing something a bit like this:

Create an extra field in your database table called 'order_number'. So each record in the table also has an order number.

Then when you want to swap record number 3 with record number 4...
[PHP]
//connect to mysql database

//now select records 3 and 4 to find out what they are
$result_1 = mysql_query("SELECT primary_key FROM my_table WHERE order_number='3'");
$result_2 = mysql_query("SELECT primary_key FROM my_table WHERE order_number='4''");

$third_record = mysql_result($result_1 , 0 , 'primary_key');
$fourth_record = mysql_result($result_2 , 0 , 'primary_key');

//now set the record that currently has 'order_number' 3 to have order_number 4
mysql_query("UPDATE my_table SET order_number='4' WHERE primary_key='". $third_record ."'");

//NB: NOTE THAT THERE ARE NOW 2 RECORDS WITH order_number 4!!!

//now change the record that was number 4 to number 3
mysql_query("UPDATE my_table SET order_number='3' WHERE primary_key='". $forth_record ."'");

//done!![/PHP]

Note that you can't just do:[PHP]
mysql_query("UPDATE my_table SET order_number='3' WHERE order_number='4'");
mysql_query("UPDATE my_table SET order_number='4' WHERE order_number='3'");[/PHP]
Because once the first query has been executed you have 2 records with order_number 3, so when the second query executes you will just get 2 records with order_number 4. Not useful. Hence the other method!

Hi
Thanks for your reply. Let me first tell you the situation actually what I am doing. I am retrieving the data(Images) from database and then displaying it in the table. From here I have given Administrator to move the selected data up or down so that it will display the modified data to the client side means to the user. Though I dont have any field in database for order no. I am swapping table row using javascript and then I am updating the modified table.

So there can be more than one swapping means I can swap row2 to row4 then I can swap row5 to row3 and row8 to row1 and so on... and then when I click the Update button it should update all the record(modified ) to the database. Or it should update the record as I swap the record one by one.

Thanks!

[mukeshrasm]

Hi

I am not able to understand how to do this means issue three update commands. If you can please give some example or some code snip for that than it would be better for me to understand.

Thanks

Hi I have not tried your queries
See Thread # 5

[nathj]

Hi
Thanks for your reply. Let me first tell you the situation actually what I am doing. I am retrieving the data(Images) from database and then displaying it in the table. From here I have given Administrator to move the selected data up or down so that it will display the modified data to the client side means to the user. Though I dont have any field in database for order no. I am swapping table row using javascript and then I am updating the modified table.

So there can be more than one swapping means I can swap row2 to row4 then I can swap row5 to row3 and row8 to row1 and so on... and then when I click the Update button it should update all the record(modified ) to the database. Or it should update the record as I swap the record one by one.

Thanks!

hi,

It sounds like you've done qite a bit of work on this already and the fact that you want to be able to move rows that are not next to each other at the start shouldn't matter.

However, without a rowOrder column in the table you are making lve unnecessarily difficult. If you do not have a row order then the table order must represent the displayed order which means moving lots of data arouind in a very complex fashion. With a rowOrder colum all you need to change is information in one column.

If you allow all the changes client side and then submit them to the server on the slick of a button then this is failry straightforward.

When the page loads in its original state you knwo the order numbers against each item, so keep these somewhere. Then as they are moved around you know the new order numbers. Simply run through a loop and change each number to the negative version of the new number. Once this loop is complete update the table once more changing all the negative numbers to absolutes.

I hope that makes sense, but I'm afraid that without the rowOrder column this problem is very tricky.

Cheers
nathj