Hw to create a counter field?

[maxinic]

How to create a counter field?

I have the database authors & books
Authors list:
id, name, etc..

List Books
id, title, authorid, numord

I wish numord were to increase by 1 each time I post a book by that author.

I set numord as calc with the following code:

$a={libri___authorid_raw};
$db =& JFactory::getDBO();
$query = "SELECT NumOrd FROM libri where authorid=($a) ORDER BY NumOrd DESC LIMIT 1";
$db->setQuery($query);
$result = $db->loadResult();
return $result+1;

but when I insert a new book increases the value of a numord but also changes the value of all previous records

Eg.

id title authorid numorder
1 xxx 5 1
2 xxy 1 1
3 xyx 5 2
4 yxx 5 ? I want to insert records

after the insertion becomes:

id title authorid numorder
1 xxx 5 3
2 xxy 1 1
3 xyx 5 3
4 yxx 5 3

Help me please

 

[Bauer]

I didn't see this post or I wouldn't have started this thread (small world we live in)...
Row Counter

I think I figured it out for us. (Thanks for pointing me at least to what field type to use ;D )

 

[cheesegrits]

What do you have the "Calc on save only" set to on your Calc element?

-- hugh

 

[maxinic]

What do you have the "Calc on save only" set to on your Calc element?

-- hugh

these are my setting

 

[maxinic]

changing the value of "re-run in table view" works, but, that will change the value even when I do the update

 

[felixkat]

I think you are going to have problems doing this with the calc and you may be better off having this update on store, (form php plugin). This would then only ever happen when saving a new record.... However what considerations are put in place if you delete a record?

If you have 1,2,3,4 and then you delete 2 the data isn't going to be accurate.

Do you just need a total count of books from that author or are you trying to achieve something else?

 

[Bauer]

I?m lost trying to figure out what the end-game is here.

What is wrong with having numorder for all the records for author ?5? set to ?3?? Isn?t that the number of ?adds? you?ve done for that author ? and isn?t that what you?d want that field to hold? Why the need for each record for each author to have a different numorder? Or do you want the numorder to be just specific to that book?

In that case why not just add an ?added? field that defaults to zero ? and as you add books fill in that number with the number of books you are adding and use autofill or php plugin to update the numorder field (as numorder + added).

What is the order/filters used on the actual list?

If it?s just a matter of wanting to show a list of books by that author in the order you entered them ? with an incremental counter in each row - then just filter by author and sort by id and use the method I posted in the linked thread of my last post to show the row counter.

Well, no actually - Here?s a fix to that ?counter? calculation that takes care of pagination. (Hoping to make Hugh happy.)

$posta = JRequest::get( 'post' );
$list_id = $posta['listid'];
$start = (INT) $posta['limitstart'.$list_id];
$db =& JFactory::getDBO();
$myQuery = "SET @this_count = if(@this_count,@this_count,".$start.")";
$db->setQuery($myQuery);
$db->query();
$myQuery = "SELECT @this_count:=@this_count+1";
$db->setQuery($myQuery);
$kount=$db->loadResult();
return $kount;

Use that code as the "Calculation" in a calc field in any List and publish it as the 1st column in the List - it should show the cal field as sequential numbers in any list - regardless the filters or pagination.

 

[maxinic]

Meanwhile, thanks to everyone for the answers.
Explain the situation better.
Meanwhile the db authors and books is just one example but I'm working on a db a bit 'more complex, but the concept is the same.
I want that books counter starts at 1 for each new author (for this I think is not good solution Bauer, thanks anyway).
Once given the number should remain that, like a code.
eg:
AuthorID NameAuth idBook Title
1 Max 1 XXX
1 Max 2 XY
1 Max 3 XX
2 Felix 1 AA
3 Hugh 1 BB
2 Felix 2 AB

If I delete the book 1 of Max db becomes:

1 Max 2 XY
1 Max 3 XX
2 Felix 1 AA
3 Hugh 1 BB
2 Felix 2 AB


Now, I think it is right the tip of the felix about plugin php but I do not know how to enter in the field numordine. Can you give me an example?

 

[maxinic]

Perhaps I can explain better and in a nutshell
I have a db with tables practice and Sub-practice

practice
Id, NumPractice
1 (Int Field by user)

Sub-Practice
NumSub-Practice Name
(famous counter) (Field)

I want to get a number given by NumPractice + Num Sub-Practice

Eg.

Id Num. Practice Under-Practice Name
1 55 1 XX result= 55/1
2 55 2 XY result= 55/2
3 56 1 AA result= 56/1
4 55 3 BB result= 55/3
5 56 2 CC result= 56/2