Setting and resetting record priorities

[garwil]

I have an accommodation reservation system that allows managing agents to list all the properties under their control. There are various 'locations' that have a priority such as:- 'Beach Front' has priority of 1 and 'Off beach' a priority of 2 etc. Setting these are pretty much a straight forward issue as they dont change. The issue I am facing is that within each location there are properties and they too have priorities. When someone searches for a holiday home the results are sorted by location.priority and then property.priority. This is done so the admin can push higher priced establishments to the top of the list with 1 being more expensive and desirable than 3 etc

I am facing an issue with the properties priority.

Say there are 20 properties with priorities 1-20 all in one location. Along comes another property and that is given priority 5. So all priorities > 5 must shift up one to make space or the new number 5. This is ok. Problem is when a property's priority changes downwards from say 5 to 2 then everything below 5 gets reduced by 1. So now I have 1 becoming 0 and then -1 and so forth and can eventually become -100 if more properties are added and get changed regularly. I discovered this when a few properties were deleted by the admin which left the listing looking like 1-3, then 38,39,40. Then she changed 38 to 2 and this reduced existing 2 to 1 and existing 1 to 0. I have posted my code here and would like to ask if anyone can review it and give me some pointers as to whether the code can be improved or how to do this properly. Ideally the system should re-sort everything into numerical order so there are no huge gaps in the priorities and if one gets deleted then the list must resort so there is always a numerical orderly list..A typical example where this also goes wrong is that if 2 gets changed to 1 then ideally 1 should become 2 and not 0. If one gets deleted then the ones above must shift down so I always have a list that is sorted numericlly when anything is changed.(have tried looking for some direction on this so I can redo my existing code into something more compact and that works better than what I have now but no luck... hence me posting here)

what I have done is create a column "old_priority" and before any changes takes place then the existing priority is placed into the old_priority so I have a reference to work off to compare what the priority was before the change. Then the new priority is compared to that.. If higher then all priorities > than old priority must shift one and so on.

if( isset($_POST['Submit']) && is_numeric($_POST['id']) && $_POST['id']>0) {
        $validation=new formValidation($_POST,'addprops');
        $error=$validation->validate1();
        $featured=(isset($_POST['featured']) && $_POST['featured']==1)?"1":"0";
        $query = "UPDATE venues SET `venuename` = '".$_POST['venuename']."', `venuetype` = '".$_POST['venuetype']."',`priority` = '".$_POST['priority']."', `postaladdress` = '".$_POST['postaladdress']."', `tel` = '".$_POST['tel']."', `fax` = '".$_POST['fax']."', `website` = '".$_POST['website']."', `checkin` = '".$_POST['checkin']."', `checkout` = '".$_POST['checkout']."', `supp_rates` = '".$_POST['supp_rates']."', `supp_max_person` = '".$_POST['supp_max_person']."', `currency` = '".$_POST['currency']."', `tax` = '".$_POST['tax']."', `description` = '".$_POST['description']."',`rating` = '".$_POST['rating']."',`nogarages` ='".$_POST['nogarages']."',`nofloors` ='".$_POST['nofloors']."' ,`nobaths` ='".$_POST['nobaths']."' ,`featured` ='".$featured."'WHERE id = '".$_POST['id']."'";
        //var_dump($query);
        $result = mysql_query($query) or die( "Error: " . mysql_error() );
    }
    if( $result ) {
        $priorities = dbGetRows("venues", "id = '".$_POST['id']."'");
        $priority = mysql_fetch_array($priorities, MYSQL_ASSOC);
        $old_priority = $priority['old_priority'];
        $id = $priority['id'];
        $new_priority =$priority['priority'];
        $location = $priority['location'];
    }
    if($new_priority < $old_priority){
        $query = "UPDATE venues SET `priority` = priority - 1 WHERE priority  < '".$old_priority."' AND for_sale=0 AND location = '".$location."' AND id !={$id}";

        //check new priority not zero... if it is then ???
        $result = mysql_query($query) or die( "Error: " . mysql_error() );
        if( $result ) {
        $query = mysql_query("SELECT * FROM  venues where id = '".$_POST['id']."' AND priority == 0");
        while($row = mysql_fetch_array($query)) {
        $num_rows = mysql_num_rows($query);
        echo "num rows where zerro=".$num_rows;
        }

        if($num_rows>'0') echo "You cant do this!";
    }
    }
    if( $new_priority > $old_priority){
        $query = "UPDATE venues SET `priority` = priority + 1 WHERE priority > '".$old_priority."' AND for_sale=0 AND location = '".$location."' AND id !={$id}";
        //var_dump($query);
        $result = mysql_query($query) or die( "Error: " . mysql_error() );
    }
    if( isset($_POST['Submit']) )
    if( $venue['basis'] = "units") {
        $updateroomcat = "UPDATE roomcategories SET `roomname` = '".$_POST['venuename']."' WHERE venueid = '".$_POST['id']."'";
        $result = mysql_query($updateroomcat) or die( "Error: " . mysql_error() );
        //var_dump($updateroomname);

        $updateroomname = "UPDATE rooms SET `name` = '".$_POST['venuename']."' WHERE venueid = '".$_POST['id']."'";
        $result = mysql_query($updateroomname) or die( "Error: " . mysql_error() );
        //var_dump($updateroomname);
    }

[jstfsklh211]

might not be the best solution but just read them into an array and update based on array position after reordering

[jkon]

To be honest I can’t get It. I am tending to believe that there should be a hidden (till now) field … lets call it for now the timestamp of a priority. To give you a full answer if this is the case I would need the schema (the PHP code here is ….. not good at least) . But just guessing with your clues I would guess that you need one more field like timestamp of priority.

[diafol]

Example, php...

$id = 8; //from some form submission
$newvalue = 7; //again from some form or other
$oldvalue = 25; //ditto

UPDATE table SET priority = priority + 1 WHERE priority >= $newvalue AND priority < $oldvalue AND id != $id

If you increase the priority number, e.g. from 7 to 25, then all priorities > 7 and <= 25 (except id=8) are decremented by 1

UPDATE table SET priority = priority-1 WHERE priority > $oldvalue AND priority <= $newvalue AND id != $id

So check for $newvalue and $oldvalue (as you've done - but more like this...):

if($newvalue > $oldvalue){
    //second statement
}elseif($newvalue < $oldvalue){
    //first statement
}else{
    //do nothing
}

[garwil]

Hi all....Many thanks for taking the time to give me some pointers. My code is not the best as am still learning but am getting there slowly:)
The timestamp method looks interesting as a possible solution so will put my mind into that and also try some of the other pointers provided and see howi get on.
Many thanks once again.. Will post back as to how it all worked out...

[garwil]

How does one add a db schema here so it is readable? I tried to paste in <code></code> but it becomes unreadable...