Php在表中计算+1插入值

Question

Hi guys i got 3 tables:

Propossals:  
id_propossal, date  

Articles:  
id_article, description  

Data:  
id, id_propossal, id_artigo, position  

So the problem here is, i would like create something that counts automatically the position of the article on the table data.

Example:

Propossals  
-------------
1 04/06/2014  
2 05/06/2014

Articles
-------------
1 article01  
2 article02

Data
-------------
1(id) 1(id_propossal)  1(id_article) 1(position defined by user using INSERT)  
2(id) 1(id_propossal)  2(id_article) 2(position|this number calculates automatically +1)  
3(id) 1(id_propossal)  1(id_article) 3(position|this number calculates automatically +1)  

and if i do for another propossal,

4(id) 2(id_propossal)  1(id_article) 1(position|goes back to 1 because is a new propossal)  

Something like this, appreciate all the help.

Answer 1

Make a request on the Data table and find the max+1 position of any row with id_propossal equals with the one you want to insert.

For example if you want to insert 123 id_propossal

SELECT (COALESCE(MAX(number),0)+1) AS nextPosition  FROM Data WHERE id_propossal=123

This will return 1.

(COALESCE is used in case max returns null)

An other way is to save the last position on Propossalstable.

But, what if you want to remove rows from Data table? You will have unused positions.