0

hello guys, i have a problem with a query.
I have a table actions which has no primary key. any one particular record can be identified by the combination of actionID, taskID and ProjectID which are the other fields in actions table.
Here is an example of the possible situations of entries in the actions table(so that you get an idea of what i am talking about)

ActionID 1 for taskID 1 of ProjectID 10-000
ActionID 2 for taskID 1 of ProjectID 10-000
ActionID 1 for taskID 2 of ProjectID 10-000
ActionID 1 for taskID 2 of ProjectID 10-001
ActionID 2 for taskID 2 of ProjectID 10-001
ActionID 3 for taskID 1 of ProjectID 10-000
ActionID 1 for taskID 1 of ProjectID 10-002
ActionID 1 for taskID 2 of ProjectID 10-003

When i am creating a new action for taskID 1 of ProjectID 10-000, then what i am doing is i am scanning the all the ActionIDs of the taskID 1 of ProjectID 10-000, ordered by ActionID and incrementing the last obtained ActionID by 1 to generate a new ActionID for the new action.

Now suppose the entries of ActionID for taskID 1 of ProjectID 10-000 in the database are 1,2,5,6,8,10,15,16, then according to my logic, the newly generated ActionID would be 17, but that is not what i want. I want the newly generated ActionID to be 3, because 3 is not present in the ActionID field of taskID 1 of ProjectID 10-000 combination .

In the above example, if i delete ActionID 2 for taskID 1 of ProjectID 10-000 and then when a new action is added to the same combination of taskID and ProjectID, i want the newly generated ActionID to be 2 and not 4 which is what i am getting from my logic.

Can anyone help me with this???

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by diafol
0

I don't really know if there's a nice succinct MYSQL function for this, but you could try looping the recordset with something like this:

$i = 1;
while($r = mysql_fetch_array($recordsetobject)){
  if($r['ActionID'] != $i){
     ...use this $i number to create an INSERT query...
     $flag_add = 1;
  }
  $i = $i + 1;
}
if(!isset($flag_add)){
  ...add a record to the end (increment) using $i...
}

I'm not a big fan of giving help without seeing some code, but I guess you need to modify the above quite a bit to get it to work.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.