Records Reordering by the user!

babaloui · June 17th, 2004, 02:14 PM

hi,

question about (Reorder) records in end-user result page.
I have MySQl table like this:
Cat_order | Catgeory |

check this:
http://www.sqr.cc/reorder/table.jpg

and I made query by PHP and I get this result in this Order:

Cat_order | Category |Reorder|
-------------------------------------------
1 | Cars | V
2 | Computers | V ^
3 | Scanners | ^
-------------------------------------------
Check this:
http://www.sqr.cc/reorder/user.jpg

SQL:
http://www.sqr.cc/reorder/cate.sql

then I want to change this order (sort),
I want Cars to be in the bottom and Scanners on the top.

How can I Move any record up and the other down by clicking on the suitable Arrow shape.

regards.

richard.york · June 17th, 2004, 02:34 PM

SELECT foo FROM bar ORDER BY foo ASC

Sort the field in ascending order.

SELECT foo FROM bar ORDER BY foo DESC

Sort the field in descending order.

HTH!

Regards,
Rich

::::::::::::::::::::::::::::::::::::::::::
The Spicy Peanut Project
http://www.spicypeanut.net
::::::::::::::::::::::::::::::::::::::::::

babaloui · June 17th, 2004, 02:44 PM

hi,

I konw how to do ASC (a-z) and DESC (z-a)

I want the USER - End-USER to do this REORDERING throught the result page.
please Read my topic carefully and check the included images.

regrds.

richard.york · June 17th, 2004, 03:01 PM

Yes I suspected as much after I made my response.

Personally, I would do this reordering on the client-side with JavaScript. Dump all the neccessary data into JavaScript arrays, and then create event handlers. I don't have the time right now to formulate an example application, but it shouldn't be too terribly difficult.

Then have onlick events for moving an item up or down.. and a couple of functions to iterate through each array that actually do the shuffling in the array itself. Use the innerHTML or innerTEXT property to write the data back into the table.

If I have time later I'll put together an example, unless anyone else on the list has any ideas.

Regards,
Rich

::::::::::::::::::::::::::::::::::::::::::
The Spicy Peanut Project
http://www.spicypeanut.net
::::::::::::::::::::::::::::::::::::::::::

babaloui · June 17th, 2004, 03:38 PM

Well, I will wait for you JAVA SCRIPT solution, Rich

reagrds

Daniel Walker · June 18th, 2004, 07:21 AM

Depends whether this reordering is ment to persist outside of the page; in which case a session array holding the new order-sequence will be needed until sucyh changes are commited back to the database. PHP has a battery of array functions such as array push and array_pop, which will probably help in performing this reordering but I'm finding it difficult to suggest more than this without having some context about why this reordering is being done.

If the reorder _is_ only supposed to occure within this page, then Javascript probably _is_ the easier and more user-friendly way to go (users with JS disabled notwithstanding). In which case, utput your list of options into a <select> input element with the cat order as the value of each option. Javascript event triggers on button or hyperlinks could reorder the list, and a submit button could then POST the new sequence back to the server whan the user was finished. This then becomes a pure Javascript question, then, in which case I'd suggest the Javascript forums, since there are some very clever Javascript guys like Imar, who will be able to suggest something pretty swish.

babaloui · June 18th, 2004, 09:27 AM

hi,
The solution is SQL statements, but I do not know how to make it works:

Code:

<html> 
<body> 

Cars <a href="page.php?direction=down&id=ilu4h3b&cat_order=1">V</a><br /> 
Computers <a href="page.php?direction=down&id=ou4hbj3q&cat_order=2">V</a><a href="page.php?direction=up&id=2">^</a><br /> 
Scanners <a href="page.php?direction=up&id=o874huqa&cat_order=3">^</a><br /> 

</html> 
</body> 
-<? 
//MySQL Access 

$user="root"; 
$password=""; 
$database="cate"; 

//Open MySQL Connection 

mysql_connect(localhost,$user,$password); 
@mysql_select_db($database) or die( "Unable to select database"); 

$num = ($_POST['direction'] == "up")? 1 : (-1); // decides whether we should add or remove 1 from cat_order 
mysql_query("UPDATE mytable SET cat_order = cat_order + $num WHERE id = '{$_POST['id']}'"); // moves the record to the wanted direction 

// Now there are two records with the same cat_order, so we have to move the other record to the opposite direction 
mysql_query("UPDATE mytable SET cat_order = cat_order + ((-1)*$num) WHERE id != '{$_POST['id']}' AND cat_order='" .  $_POST['cat_order'] + $num . "'"); 

// Close MySQL Connection 

mysql_query($query); 
mysql_close(); 

?>