query help for table with no primary key

[scorpio22]

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???

 

[jacsdev]

hi man, how are you? try this, I think this script can solve it..

by the way, check attach file, it have a table named data.sql and example.php

 

<?php
// By @jacsdev
$cnx = mysql_connect("localhost","root","",true);
mysql_select_db("test",$cnx);

// i have a table called data
// data have field1, field2, field3  (without primary key)
// we want insert values, but we need to have ascendents numbers in field1

// sql script (data.sql) have in field1 next values: 1, 3, 4, 5    ( however not number 2)
// we need than next inserted row be 2 (in field1)  

$cn = mysql_query("select field1, field2, field3 from data order by field1 asc",$cnx);
$numRows = mysql_num_rows($cn);

$i = 1;
while($rs = mysql_fetch_array($cn))
{

	if( $rs['field1'] != ( $numRows - ($numRows - $i) ) )
	{                       
		break;
	}
	$i++;
}

// now, we can do insert query, and field1 will have correct number

$field2 = 4;
$field3 = 5;

mysql_query("insert into data (field1, field2, field3) values ($i,$field2, $field2 )",$cnx);


// let see result
echo "F1 - F2 - F3<br>";
$cn = mysql_query("select field1, field2, field3 from data order by field1 asc");
while ($rs = mysql_fetch_array($cn) )
{	
	// you must see number 2 in field1 position
	echo $rs['field1']."  -   ".$rs['field2']."  -  ".$rs['field3']."<br>";
}
?>

 

[attachment deleted by admin]

[fenway]

Two things.

 

1) You can have a primary key across multiple columns.

2) Don't EVER re-use ActionID -- terrible idea -- you'll thank me later.

[scorpio22]

by the way, check attach file, it have a table named data.sql and example.php

 

Thanks jacsdev. I made some modifications on my code and your logic works perfectly fine.

Thanks a lot

[fenway]

Then post your solution for the benefit of others.

[scorpio22]

Then post your solution for the benefit of others.

 

hey mate, the same solution is what i am using, i just modified things the way things should be according to my project, but the logic is the same.