By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 990 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

how to update

100+
P: 254
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!
Jan 2 '08 #1
Share this Question
Share on Google+
6 Replies


nathj
Expert 100+
P: 938
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
Jan 2 '08 #2

100+
P: 254
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
Jan 5 '08 #3

P: 93
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!
Jan 6 '08 #4

100+
P: 254
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!
Jan 8 '08 #5

100+
P: 254
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
Jan 8 '08 #6

nathj
Expert 100+
P: 938
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
Jan 8 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.