Cool Websites. Affordable Prices.

Making DataAssist Applications Sort-Able

2010

- Jan -

17

COMMENTS
1

PUBLISHED IN:
Web Design

ISSUE:

#12

Tutorial for Sort-able Rows with DataAssist

1. Get the needed tools.

The first thing to do is download the latest version of JQuery: http://jquery.com/

Next, download the Table Drag n Drop plugin found here:

http://www.isocra.com/articles/jquery.tablednd_0_5.js.zip

For more information on this plug-in (which you won’t need for this tutorial, check out the site: http://www.isocra.com/2008/02/table-drag-and-drop-jquery-plugin/ (While not needed for this tutorial, it has some great example that may help in your other endeavours).

2. Add a column called “Order_Weight” to your table that you wish to make sort-able.

This is pretty simple. Add your column called “Order_Weight” (you could use a different name as this is arbitrary but the tutorial will assume that yours is called “Order_Weight” as well). Set the column type to integer.

3. When running the DataAssist wizard, be sure to specify your new Order_Weight column as a hidden value in your update and insert forms.

This will add the Order_Weight value which will be ready for insert (with a few tricks in the next step) and the update page.

4. On the insert page, supply the code to add the correct value to the order column.

We need to make sure that we add a value for the order weight column that is unique. By default, all new items will be last in order… of course you will be able to sort them any way you wish when this is done.

In order to be sure that we supply a unique order weight integer, the first thing to do is to run a query before the page loads to find the highest number in the order weight column.

For my example, you will see my example query from a table of staff members that I wanted to make sort-able:

mysql_select_db($database_mydb, $mydb);
$query_ow = "SELECT Order_Weight FROM staff ORDER BY Order_Weight DESC LIMIT 1";
$ow = mysql_query($query_ow, $mydb) or die(mysql_error());
$row_ow = mysql_fetch_assoc($ow);
$totalRows_ow = mysql_num_rows($ow);

Notice, I called the query “ow”… yes, short for Order Weight. I also ordered by the Order Weight column and limited my results to 1. This will give me the highest number in the Order column so far. 

So, I do two things with this data. 

First set a variable that will be used later in the hidden value for the Order Weight input in the form. Second, I then increase the variable by one increment to ensure a unique number in the Order Weight column. 

$oweight = $row_ow['Order_Weight'];
++$oweight; 

The next thing to do is to set the Order Weight hidden input by binding the variable: 

<input type="hidden" name="Order_Weight" id="Order_Weight" value="<?php echo $oweight; ?>" />

Pretty easy so far, huh? For the Update Page, there is no code to add, as the hidden value for the Order Weight will be automatically populated based on which number is already in the database.

5. Now, modify the Results Page allowing you to sort the rows and save their new order.

Be sure when running the DataAssist wizard that you sort by Order_Weight Ascending. 

On your results page, based on what you have selected to be displayed, you will see a table like this (please note, the fields shown here are the fields in my company staff table that I am using for purpose of this tutorial):

<table class="WADAResultsTable" border="0" cellpadding="0" cellspacing="0">

                        <tr>

                          <th class="WADAResultsTableHeader">ID:</th>

                          <th class="WADAResultsTableHeader">Name:</th>

                          <th class="WADAResultsTableHeader">Position:</th>

                        </tr>

                        <?php do { ?>

                          <tr class="<?php echo $WARRT_AltClass1->getClass(true); ?>">

                            <td class="WADAResultsTableCell"><?php echo($row_WADAstaff['ID']); ?></td>

                            <td class="WADAResultsTableCell"><?php echo($row_WADAstaff['Name']); ?></td>

                            <td class="WADAResultsTableCell"><?php echo($row_WADAstaff['Position']); ?></td>

                        <td class="WADAResultsEditButtons" nowrap="nowrap"><table class="WADAEditButton_Table">

                                <tr>

                                  <td><a href="staff_Detail.php?ID=<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" title="View"><img border="0" name="View<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" id="View<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" alt="View" src="../WA_DataAssist/images/Pacifica/Modular_zoom.gif" /></a></td>

                                  <td><a href="staff_Update.php?ID=<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" title="Update"><img border="0" name="Update<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" id="Update<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" alt="Update" src="../WA_DataAssist/images/Pacifica/Modular_edit.gif" /></a></td>

                                  <td><a href="staff_Delete.php?ID=<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" title="Delete"><img border="0" name="Delete<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" id="Delete<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" alt="Delete" src="../WA_DataAssist/images/Pacifica/Modular_trash.gif" /></a></td>

                                </tr>

                              </table></td>

                          </tr>

                        <?php } while ($row_WADAstaff = mysql_fetch_assoc($WADAstaff)); ?>

                      </table>

Now, we actually want to SPLIT this table into two because we don’t want the header row to become sort-able. So add a closing table tag after your header row and a new opening table tag after it to make the rows with the repeat region to be in a unique table like this:

<table class="WADAResultsTable" border="0" cellpadding="0" cellspacing="0">

                        <tr>

                          <th class="WADAResultsTableHeader">ID:</th>

                          <th class="WADAResultsTableHeader" style="padding-right: 50px;">Name:</th>

                          <th class="WADAResultsTableHeader">Position:</th>

                          <th>&nbsp;</th>

                          <th>&nbsp;</th>

                        </tr>

                      </table>

Now for the additional changes… we want to wrap the new table in a form that will post to a new page where the order re-sort will be saved. You will want to make sure this processes on a different page so that the query results on the existing results page do not interfere with the multiple array variable. Notice the key changes in bold.

                      <form name="form" action="updatestafforder.php" method="POST"> 

Here, I have added a form that will process on a page called updatestafforder.php. This comes before my new opening table tag.

                        <table class="WADAResultsTable" border="0" cellpadding="0" cellspacing="0" id="table-1">

Notice on the table tag, I have added the ID “table-1”. This is very important as to tell the Jquery function what table has rows that are sort-able. While this is an arbitrary name, make note of whatever name you use as you will need it later.

                          <?php do { ?>

                            <tr class="<?php echo $WARRT_AltClass1->getClass(true); ?>">

                              <td class="WADAResultsTableCell"><input type="hidden" name="idorder[]" readonly="readonly" value="<?php echo($row_WADAstaff['ID']); ?>" />

Here, I have added a hidden input with the name “idorder[]” that gets repeated and will provide the array of ID numbers that we will need when saving the new order.

                                <?php echo($row_WADAstaff['ID']); ?></td>

                              <td class="WADAResultsTableCell"><?php echo($row_WADAstaff['Name']); ?></td>

                              <td class="WADAResultsTableCell"><?php echo($row_WADAstaff['Position']); ?></td>

                                                            <td class="WADAResultsEditButtons" nowrap="nowrap"><table class="WADAEditButton_Table">

                                <tr>

                                  <td><a href="staff_Detail.php?ID=<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" title="View"><img border="0" name="View<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" id="View<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" alt="View" src="../WA_DataAssist/images/Pacifica/Modular_zoom.gif" /></a></td>

                                  <td><a href="staff_Update.php?ID=<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" title="Update"><img border="0" name="Update<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" id="Update<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" alt="Update" src="../WA_DataAssist/images/Pacifica/Modular_edit.gif" /></a></td>

                                  <td><a href="staff_Delete.php?ID=<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" title="Delete"><img border="0" name="Delete<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" id="Delete<?php echo(rawurlencode($row_WADAstaff['ID'])); ?>" alt="Delete" src="../WA_DataAssist/images/Pacifica/Modular_trash.gif" /></a></td>

                                </tr>

                              </table></td>

                            </tr>

                            <?php } while ($row_WADAstaff = mysql_fetch_assoc($WADAstaff)); ?>

                        </table>

                        <input name="submit" type="submit" value="Update Order" style="float:right; margin-right: 500px; margin-top: 20px;" />

Here, I have added my submit button… I also applied an inline style to make it look better aligned with my form. You may need to style it differently based on your layout. You could get real fancy an add an image that looked as coherent with the rest of the theme you used.

                       <input name="MM_update" type="hidden" id="MM_update" />

I also decided to add an update flag to ensure that the process page wouldn’t do anything unless specifically triggered.

                      </form>

6. Now, add the JQuery and TableDnD code to your results page head code. I added mine just above the closing </head> tag.

<script type="text/javascript" src="../jquery/jquery-1.3.2.js"></script>
<script type="text/javascript" src="../jquery/jquery.tablednd_0_5.js"></script>
<script type="text/javascript">
$(document).ready(function() {
    // Initialise the table
    $("#table-1").tableDnD();
});
</script>

Make sure you specify your paths correctly to your Jquery library and the TableDnD js file. Then add the little bit of java code that makes your table sort-able. This is where that table ID I spoke earlier, comes into play.

7. Now, create the process page.

If you remember, I made the form go to updatestafforder.php. Be sure to direct your form to whatever name you choose to use for your process page.

On the process page, it looks like this:

<?php

if (isset($_POST["MM_update"])) {

            $ordernumber = 1;

            $neworder = $_POST['idorder'];

            foreach ($neworder as $item){

                        $updateSQL = sprintf("UPDATE staff SET Order_Weight='$ordernumber' WHERE ID='$item'");

                        mysql_select_db($database_mydb, $mydb);

                         $Result = mysql_query($updateSQL, $mydb) or die(mysql_error());

                        ++$ordernumber;

            } 

 header('Location: staff_Results.php');

 exit;

}

?>

It’s fairly simple… it first checks for the update flag and then stores the array created from the sorted table into a variable called $neworder. It then uses a “for each” loop to go through each iteration that was stored in the array to rewrite the order weight column starting with 1 and adding an increment to each item based on the order they were stored in from the array which was created by us sorting the table.

Lastly, it redirects back to the results page where our new order is displayed and can be resorted.