Webassist Manage relational tables and setting the order.

I recently had a project i was working on where i needed to use the WebAssist Manage Relational Tables behaviour.

When adding the relational records it does not however provide an easy way to manage the order of the records it enters into the database, in fact a big drawback with the DataAssist Suite from WebAssist is that it does not provide any facilities for record ordering.

However if you visit this post on the WebAssist forums
A guy called Brian (sorry dont know his second name and at the time of writing this the link on his post is not working) found a way to add recordset ordering.

Cue This Guy -> Stephen has worked on the code originally created by Brian, and has a great easy to implement soloution for re-ordering of records.

I found though it only worked with standard forms created with dataAssist and was not a lot of help when adding to relational tables.

My Turn to write some code for this.

I’ll assume you have a page creaed by DataAssist for inserting a record, and have the Manage Relational Table Applied to the page also.
The manage Relational Table when inserting a record needs to also update your order colulmn with a value of 0.
or alternatively you can just have your colulmn set up so its default value is 0. (thats probably the best method in fact).

The code can be used on the insert update and delete pages for the manage relational table.

When applied to the insert page it will order the records in the order the checkboxes appear in the manage relational table section.

The code block i have written is simply placed after the manage relational table code block.
The full code block is at the end of this post I will go through what each bit does and what you should put in for the settings:

The first part is for the trigger on an insert page this should be set to “Insert_x”, Update page “Update_x” and for delete “Delete_X”

// WA DataAssist extension set order for relational data
//Trigger for insert Record
if (isset($_POST["Update_x"])) // Trigger

Time for the settings (you will need to modify these to suit your particular data):
The first is the redirect URL, it is important that you remove any existing redirects from the insert/update/delete functions and the Manage Relational Table Function, if you dont this code will not get executed.

   $WA_redirectURL = "Results.php";
   $WA_redirectURL = $WA_redirectURL?rel2abs($WA_redirectURL,dirname(__FILE__)):"";
   $WA_keepQueryString = false;

Next you need to put in the information for your relational table:

  $WA_relationalTable = "pageimages_pim"; // name of your relational table
  $WA_primaryKey = "id_pim"; // The primary key of your relational table
  $WA_orderField = "order_pim"; // he order field of your relational table

The next two values are the Masterkey colulmn and its value, set these to the same as what you used when adding the manage relational table behaviour.

  $WA_masterKey = "idsec_pim"; // The Master Key field of your relational table
  $WA_masterKeyValue = "".$row_WADAsections_sec['id_sec'].""; //The value of the the Master Key

Those are the only things you need to change in the code block for this to work.
You also neexd a hiiden field somwhere on your update and delete form wih an id of “keepOrder” and its value set to “0″

I will continue with the breakdown of the code for those of you that are intrested.

This part of the code is only really applicable when applied to a delete or update page.
You will need a hidden field on your form called keepOrder and have it set to “0″
If you dont when records are removed from the list, there will be gaps in the ordering, because the retrieved records in the code block are ordered by the current order, the existing order set will be preserved.

$WA_keepOrder = 0;
	  if($_POST['keepOrder'] == 1){
	  	$WA_keepOrder = $_POST['keepOrder'];

This part of the code gets the order value that should be assigned to the next record.
it is taken from Stephens original post.

  //Get the starting order number
  mysql_select_db($database_conn_falken, $conn_falken);
  $query_sortvalue = ("SELECT (MAX(".$WA_orderField.")+1) AS ".$WA_orderField."  FROM ".$WA_relationalTable." WHERE ".$WA_masterKey." = ".$WA_masterKeyValue."");
  $sortvalue = mysql_query($query_sortvalue, $conn_falken) or die(mysql_error());
  $row_sortvalue = mysql_fetch_assoc($sortvalue);
  $totalRows_sortvalue = mysql_num_rows($sortvalue);

Next we get a list of the records we want to apply the filtering too, its ordered by order colulmn in the relational table.
that way if ordering has been applied previously and new records are added or deleted, the order of the existing items will not change, any new records will be given an order number that places them at the end of the current list.

  mysql_select_db($database_conn_falken, $conn_falken);
  $query_WAsortitemlist = ("SELECT * FROM ".$WA_relationalTable." WHERE ".$WA_masterKey." = ".$WA_masterKeyValue." ORDER BY ".$WA_orderField." ASC");
  //echo("WA_sortitemlist query: ".$query_WAsortitemlist."<br>");
  $WAsortitemlist = mysql_query($query_WAsortitemlist, $conn_falken) or die(mysql_error());
  $row_WAsortitemlist = mysql_fetch_assoc($WAsortitemlist);
  $totalRows_WAsortitemlist = mysql_num_rows($WAsortitemlist);

Next we need to check the value of our keepOrder variable if its set to 0 (as it should be via the hidden field on the update/delete forms) we set the new order number to 1 so we can reorder all the records from 1 upwards.

if($WA_keepOrder == 0){
	  $WA_sortValue = 1;
  if($WA_keepOrder == 1){
	  $WA_sortValue = $row_sortvalue[$WA_orderField];

Next we loop through our relational records and apply the ordering

  // check and make sure the recordset is not empty
  if($totalRows_WAsortitemlist != 0){
	//begin our loop
	do {
                //store the id of the current record we are working on
		$WA_currentID = $row_WAsortitemlist['id_pim'];
                // store the current records order value
                // for newly inserted records this value will be 0
                $WA_currentOrder = $row_WAsortitemlist[$WA_orderField];
		// check o see if we are keeping the original order or not
                if($WA_keepOrder == 1){
			//keep the original order so store the existing order
			$WA_currentOrder = $row_WAsortitemlist[$WA_orderField];
			//check to see if the order is a newly inserted record
			if($WA_currentOrder == 0){
				// newly inserted record so set the order to the next avaliable sort order
                                $WA_currentOrder = $WA_sortValue;
				// increment the order so its ready for the next record in the loop
		}else{ // keep order is set to 0 so apply the new order regardless
			$WA_currentOrder = $WA_sortValue;
			// increment the order so its ready for the next record in the loop
		//update the order of the current record
		// for effiency we check to see if the new order value has changed
                if($row_WAsortitemlist[$WA_orderField] != $WA_currentOrder){
			// new order value is diffirent so update the record
			$WA_orderUpdateQuery = ("UPDATE ".$WA_relationalTable." SET ".$WA_orderField." = ".$WA_currentOrder." WHERE ".$WA_primaryKey." = ".$WA_currentID."");
			mysql_query($WA_orderUpdateQuery) or die(mysql_error());
        // end the loop
	} while ($row_WAsortitemlist = mysql_fetch_assoc($WAsortitemlist));
// seting the new order of records is finished perform the redirect
  if ($WA_redirectURL != "")  {
    if ($WA_keepQueryString && $WA_redirectURL != "" && isset($_SERVER["QUERY_STRING"]) && $_SERVER["QUERY_STRING"] !== "" && sizeof($_POST) > 0) {
      $WA_redirectURL .= ((strpos($WA_redirectURL, '?') === false)?"?":"&").$_SERVER["QUERY_STRING"];
    header("Location: ".$WA_redirectURL);

so Thats pretty much it hope you find it usefull.

Liked it? why not share it...